Analyzing pedestrian traffic through Melbourne CBD

Now that we’ve prepared our dataset and covered the tools we’re going to use, let’s jump into some analysis of the Melbourne pedestrian counting dataset. We’ll continue to use DuckDB with JupySQL to query our pedestrian_counts table, and Plotly to make visualizations. Note that we won’t always display the dataframe showing the results of our query. As you’re working through the examples, we encourage you to inspect the contents of the dataframes yourself.

Visualizing total pedestrian counts over time

To start with, let’s get a sense of how the total number of pedestrian counts registered by the sensor network has changed over the years in the dataset. We’ll start by querying the pedestrian_counts table to get the sum of all counts within each year in the dataset:

%%sql year_counts_df <<
SELECT Year, sum(Hourly_Counts)::BIGINT AS Total_Counts
FROM pedestrian_counts...