A window function computes a value across related rows while keeping each row available for further analysis. It is useful when hourly values must be compared with each location’s daily total rather than collapsed into a separate summary table. section 9.2.1
In SQL, partition rows with PARTITION BY to place the hourly and daily measures side by side. A separate aggregation followed by a join is also valid, but a window-like calculation appends the group total directly to the detailed data. section 9.2.1
Hourly share of daily cycling traffic
First aggregate cycling counts to one row per location, date, and hour. Then group by location and date, use pandas transform('sum') to add TotalDailyCount to every hourly row, and divide Pedal_cycles by that total to calculate hourly_pct. Check the resulting daily counts and percentages before plotting their distribution by hour. section 9.2.1
cycling_daily_hourly['TotalDailyCount'] = (
cycling_daily_hourly
.groupby(['Count_point_id', 'Count_date'])
['Pedal_cycles']
.transform('sum') #1
)
cycling_daily_hourly['hourly_pct'] = (
cycling_daily_hourly['Pedal_cycles']
/ cycling_daily_hourly['TotalDailyCount']
)
cycling_daily_hourly
Inspect the transform-based calculation that preserves hourly rows while adding each location-date daily total. section 9.2.1
