import duckdb records = duckdb.read_csv("data/C11/Pedestrian_Counting_System_Monthly_counts_per_hour_may_2009_to_14_dec_2022.csv") records.show(max_width=200).limit(10)
┌─────────┬───────────────────────────────┬───────┬──────────┬───────┬──────────┬───────┬───────────┬───────────────────────────────┬───────────────┐ │ ID │ Date_Time │ Year │ Month │ Mdate │ Day │ Time │ Sensor_ID │ Sensor_Name │ Hourly_Counts │ │ int64 │ varchar │ int64 │ varchar │ int64 │ varchar │ int64 │ int64 │ varchar │ int64 │ ├─────────┼───────────────────────────────┼───────┼──────────┼───────┼──────────┼───────┼───────────┼───────────────────────────────┼───────────────┤ │ 2887628 │ November 01, 2019 05:00:00 PM │ 2019 │ November │ 1 │ Friday │ 17 │ 34 │ Flinders St-Spark La │ 300 │ │ 2887629 │ November 01, 2019 05:00:00 PM │ 2019 │ November │ 1 │ Friday │ 17 │ 39 │ Alfred Place │ 604 │ │ 2887630 │ November 01, 2019 05:00:00 PM │ 2019 │ November │ 1 │ Friday │ 17 │ 37 │ Lygon St (East) │ 216 │ │ 2887631 │ November 01, 2019 05:00:00 PM │ 2019 │ November │ 1 │ Friday │ 17 │ 40 │ Lonsdale St-Spring St (West) │ 627 │ │ 2887632 │ November 01, 2019 05:00:00 PM │ 2019 │ November │ 1 │ Friday │ 17 │ 36 │ Queen St (West) │ 774 │ │ 2887633 │ November 01, 2019 05:00:00 PM │ 2019 │ November │ 1 │ Friday │ 17 │ 29 │ St Kilda Rd-Alexandra Gardens │ 644 │ │ 2887634 │ November 01, 2019 05:00:00 PM │ 2019 │ November │ 1 │ Friday │ 17 │ 42 │ Grattan St-Swanston St (West) │ 453 │ │ 2887635 │ November 01, 2019 05:00:00 PM │ 2019 │ November │ 1 │ Friday │ 17 │ 43 │ Monash Rd-Swanston St (West) │ 387 │ │ 2887636 │ November 01, 2019 05:00:00 PM │ 2019 │ November │ 1 │ Friday │ 17 │ 44 │ Tin Alley-Swanston St (West) │ 27 │ │ 2887637 │ November 01, 2019 05:00:00 PM │ 2019 │ November │ 1 │ Friday │ 17 │ 35 │ Southbank │ 2691 │ │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ │ 2897597 │ November 09, 2019 10:00:00 AM │ 2019 │ November │ 9 │ Saturday │ 10 │ 27 │ QV Market-Peel St │ 371 │ │ 2897598 │ November 09, 2019 10:00:00 AM │ 2019 │ November │ 9 │ Saturday │ 10 │ 28 │ The Arts Centre │ 1188 │ │ 2897599 │ November 09, 2019 10:00:00 AM │ 2019 │ November │ 9 │ Saturday │ 10 │ 31 │ Lygon St (West) │ 229 │ │ 2897600 │ November 09, 2019 10:00:00 AM │ 2019 │ November │ 9 │ Saturday │ 10 │ 30 │ Lonsdale St (South) │ 391 │ │ 2897601 │ November 09, 2019 10:00:00 AM │ 2019 │ November │ 9 │ Saturday │ 10 │ 34 │ Flinders St-Spark La │ 111 │ │ 2897602 │ November 09, 2019 10:00:00 AM │ 2019 │ November │ 9 │ Saturday │ 10 │ 37 │ Lygon St (East) │ 133 │ │ 2897603 │ November 09, 2019 10:00:00 AM │ 2019 │ November │ 9 │ Saturday │ 10 │ 40 │ Lonsdale St-Spring St (West) │ 154 │ │ 2897604 │ November 09, 2019 10:00:00 AM │ 2019 │ November │ 9 │ Saturday │ 10 │ 36 │ Queen St (West) │ 249 │ │ 2897605 │ November 09, 2019 10:00:00 AM │ 2019 │ November │ 9 │ Saturday │ 10 │ 29 │ St Kilda Rd-Alexandra Gardens │ 448 │ │ 2897606 │ November 09, 2019 10:00:00 AM │ 2019 │ November │ 9 │ Saturday │ 10 │ 42 │ Grattan St-Swanston St (West) │ 288 │ ├─────────┴───────────────────────────────┴───────┴──────────┴───────┴──────────┴───────┴───────────┴───────────────────────────────┴───────────────┤ │ ? rows (>9999 rows, 20 shown) 10 columns │ └───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
records = duckdb.read_csv( "data/C11/Pedestrian_Counting_System_Monthly_counts_per_hour_may_2009_to_14_dec_2022.csv", dtype={"Date_Time": "TIMESTAMP"}, timestamp_format="%B %d, %Y %H:%M:%S %p", )
records.limit(5).show(max_width=200)
┌─────────┬─────────────────────┬───────┬──────────┬───────┬─────────┬───────┬───────────┬──────────────────────────────┬───────────────┐ │ ID │ Date_Time │ Year │ Month │ Mdate │ Day │ Time │ Sensor_ID │ Sensor_Name │ Hourly_Counts │ │ int64 │ timestamp │ int64 │ varchar │ int64 │ varchar │ int64 │ int64 │ varchar │ int64 │ ├─────────┼─────────────────────┼───────┼──────────┼───────┼─────────┼───────┼───────────┼──────────────────────────────┼───────────────┤ │ 2887628 │ 2019-11-01 17:00:00 │ 2019 │ November │ 1 │ Friday │ 17 │ 34 │ Flinders St-Spark La │ 300 │ │ 2887629 │ 2019-11-01 17:00:00 │ 2019 │ November │ 1 │ Friday │ 17 │ 39 │ Alfred Place │ 604 │ │ 2887630 │ 2019-11-01 17:00:00 │ 2019 │ November │ 1 │ Friday │ 17 │ 37 │ Lygon St (East) │ 216 │ │ 2887631 │ 2019-11-01 17:00:00 │ 2019 │ November │ 1 │ Friday │ 17 │ 40 │ Lonsdale St-Spring St (West) │ 627 │ │ 2887632 │ 2019-11-01 17:00:00 │ 2019 │ November │ 1 │ Friday │ 17 │ 36 │ Queen St (West) │ 774 │ └─────────┴─────────────────────┴───────┴──────────┴───────┴─────────┴───────┴───────────┴──────────────────────────────┴───────────────┘
Before loading our dataset into an on-disk database for analysis, we can also consider whether there are any data transformations we may want to apply.
transformed = records.select("* EXCLUDE ID").sort("Date_Time")
transformed.limit(5).show(max_width=200)
┌─────────────────────┬───────┬─────────┬───────┬─────────┬───────┬───────────┬───────────────────────────────────┬───────────────┐ │ Date_Time │ Year │ Month │ Mdate │ Day │ Time │ Sensor_ID │ Sensor_Name │ Hourly_Counts │ │ timestamp │ int64 │ varchar │ int64 │ varchar │ int64 │ int64 │ varchar │ int64 │ ├─────────────────────┼───────┼─────────┼───────┼─────────┼───────┼───────────┼───────────────────────────────────┼───────────────┤ │ 2009-05-01 00:00:00 │ 2009 │ May │ 1 │ Friday │ 0 │ 1 │ Bourke Street Mall (North) │ 53 │ │ 2009-05-01 00:00:00 │ 2009 │ May │ 1 │ Friday │ 0 │ 2 │ Bourke Street Mall (South) │ 52 │ │ 2009-05-01 00:00:00 │ 2009 │ May │ 1 │ Friday │ 0 │ 4 │ Town Hall (West) │ 209 │ │ 2009-05-01 00:00:00 │ 2009 │ May │ 1 │ Friday │ 0 │ 5 │ Princes Bridge │ 157 │ │ 2009-05-01 00:00:00 │ 2009 │ May │ 1 │ Friday │ 0 │ 6 │ Flinders Street Station Underpass │ 139 │ └─────────────────────┴───────┴─────────┴───────┴─────────┴───────┴───────────┴───────────────────────────────────┴───────────────┘
with duckdb.connect("pedestrian.duckdb") as conn: result = ( conn.read_csv( "data/C11/Pedestrian_Counting_System_Monthly_counts_per_hour_may_2009_to_14_dec_2022.csv", dtype={"Date_Time": "TIMESTAMP"}, timestamp_format="%B %d, %Y %H:%M:%S %p", ) .select("* EXCLUDE ID") .sort("Date_Time") ) result.to_table("pedestrian_counts")
conn = duckdb.connect("pedestrian.duckdb") conn.sql( """ SELECT sum(Hourly_Counts) AS Total_Counts FROM pedestrian_counts WHERE Year = 2022 AND Sensor_Name = 'Melbourne Central' """ )
┌──────────────┐ │ Total_Counts │ │ int128 │ ├──────────────┤ │ 6897406 │ └──────────────┘
conn.close()
%load_ext sql
conn = duckdb.connect() %sql conn --alias duckdb
conn.close()
In our context, we want to work with the database we created in the previous section; so, let’s configure JupySQL so that it uses a new connection to our on-disk pedestrian.duckdb database while also giving it an appropriate alias:
conn = duckdb.connect("pedestrian.duckdb")
%sql conn --alias pedestrian.duckdb
%config SqlMagic.autopandas = True
%%sql SELECT sum(Hourly_Counts) AS Total_Counts FROM pedestrian_counts WHERE Year = 2022 AND Sensor_Name = 'Melbourne Central'
type(_) # pandas.core.frame.DataFrame
%%sql sensors_2022_df << SELECT Sensor_Name, sum(Hourly_Counts)::BIGINT AS Total_Counts FROM pedestrian_counts WHERE Year = 2022 GROUP BY Sensor_Name ORDER BY Total_Counts DESC
sensors_2022_df.head(10)
sensors_2022_df.dtypes
Sensor_Name object Total_Counts int64 dtype: object
In addition to confirming that the Total Counts column of our dataframe has an int64 data type, we can also see that the Sensor Name column contains the dtype object. While this can be a little misleading at first, this is pandas’ default way of representing string values and is therefore appropriate for this column.
import plotly.express as px
With the Plotly Express module loaded, let’s see it in action by using the px.bar() function to create a bar chart of the top 10 sensors by traffic in 2022 while using the sensors_2022_df dataframe we created in the previous section:
figure = px.bar( sensors_2022_df.head(10), x="Sensor_Name", y="Total_Counts", height=500, title="Top 10 sensors by traffic for 2022", ) figure
Let’s make one more visualization with Plotly Express. This time, we’ll visualize the number of active sensors across each year using a line plot. Plotly provides this functionality through the px.line() function. First, we’ll need to query our database to get the distinct number of sensor names seen across each year, which we can do with the following DuckDB query:
%%sql sensor_years_df << SELECT Year, COUNT(DISTINCT Sensor_Name) AS Total_Sensors FROM pedestrian_counts GROUP BY Year ORDER BY Year
sensor_years_df.head(5)
figure = px.line( sensor_years_df, x="Year", y="Total_Sensors", markers=True, height=500, title="Total number of active sensors by year" ) figure
figure.update_layout(xaxis={"dtick": 1}, title={"x": 0.5})
figure.update_layout(xaxis_dtick=1, title_x=0.5)
In this short introduction to making visualizations with Plotly, we’ve focused on two Plotly Express functions, as well as Plotly chart customizations via a handful of figure-layout properties; however, this is by no means a comprehensive overview of using the library. Two resources we’d recommend consulting if you want to further explore using Plotly in Python are as follows:
• The Plotly Express guide: https://plotly.com/python/plotly-express
• The Plotly Python Figure reference: https://plotly.com/python/reference/index
%%sql year_counts_df << SELECT Year, sum(Hourly_Counts)::BIGINT AS Total_Counts FROM pedestrian_counts GROUP BY Year ORDER BY Year
px.line( year_counts_df, x="Year", y="Total_Counts", markers=True, height=500, title="Total pedestrian counts by year", )
%sql SELECT count(DISTINCT Year) FROM pedestrian_counts
%%sql CREATE OR REPLACE TABLE common_sensors AS SELECT Sensor_Name FROM pedestrian_counts GROUP BY Sensor_Name HAVING COUNT(DISTINCT Year) = 14;
Ordinarily, when issuing a CREATE TABLE statement in DuckDB, no results are returned. When using JupySQL to submit database-modifying SQL, however, it returns the number of rows that were inserted into the database. This means we’ll see the following dataframe as output:
%%sql year_counts_filtered_df << SELECT Year, sum(Hourly_Counts)::BIGINT AS Total_Counts FROM pedestrian_counts WHERE Sensor_Name IN (FROM common_sensors) GROUP BY Year ORDER BY Year
px.line( year_counts_filtered_df, x="Year", y="Total_Counts", markers=True, height=500, title="Yearly traffic for sensors active all years", )
%%sql year_month_counts_df << SELECT Year, Month, month(Date_Time) AS Month_Num, sum(Hourly_Counts)::BIGINT AS Total_Counts FROM pedestrian_counts WHERE Year IN (2019, 2020, 2021) AND Sensor_Name in (FROM common_sensors) GROUP BY Year, Month, Month_Num ORDER BY Year, Month_Num
year_month_counts_df.head(15)
px.line( year_month_counts_df, x="Month", y="Total_Counts", color="Year", symbol="Year", symbol_sequence=["square", "diamond", "circle"], markers=True, height=500, title="Monthly traffic for sensors active 2019-2021", ).update_traces(marker_size=8)
%%sql sensor_2020_df << SELECT Hourly_Counts, Date_Time FROM pedestrian_counts WHERE Sensor_Name = 'Flinders La-Swanston St (West)' AND Year = 2020
Then, we’ll use the px.line() function to create a time series plot of all readings for that year:
%%sql multi_sensor_df << SELECT Sensor_Name, Hourly_Counts, Date_Time FROM pedestrian_counts WHERE Sensor_Name IN ( 'Flinders St-Spark La', 'Bourke Street Mall (North)', 'Southern Cross Station' ) AND Year = 2019 AND Month = 'September'
px.line( multi_sensor_df, y="Hourly_Counts", x="Date_Time", facet_col="Sensor_Name", facet_col_wrap=1, title="Hourly pedestrian traffic for December 2019", height=800, ).update_layout(yaxis_fixedrange=True)
%%sql bourke_daily_df << SELECT Year, Date_Time::DATE AS Date, sum(Hourly_Counts)::BIGINT AS Daily_Counts, FROM pedestrian_counts WHERE Sensor_Name = 'Bourke Street Mall (North)' AND Year IN (2019, 2020, 2021) GROUP BY Year, Date
bourke_daily_df.head()
px.box( bourke_daily_df, x="Year", y="Daily_Counts", points="all", height=600, title="Distributions of daily traffic for a sensor", )
浙公网安备 33010602011771号