Aggregation in Timeseries using Pandas

We’ve all grown up studying groupy by and aggregations in SQL. Pandas provides excellent functionality for group by and aggregations. However, for time series data, we need a bit of manipulation. In this post, I’ll take a small example of weather time series data.

import pandas as pd
import matplotlib.pyplot as plt
plt.style.use('ggplot')
%matplotlib inline

df = pd.read_csv("weather.csv", index_col=0, parse_dates=True).tz_localize("UTC").tz_convert("US/Central")

df.head()

	humidity	temperature
2015-01-01 00:00:00-06:00	0.73	38.74
2015-01-01 01:00:00-06:00	0.74	38.56
2015-01-01 02:00:00-06:00	0.75	38.56
2015-01-01 03:00:00-06:00	0.79	37.97
2015-01-01 04:00:00-06:00	0.80	37.78

Question 1: What is the mean temperature and humidity per hour of the day?

We’ll create a new column in the df containing the hour information from the index.

df["hour"] = df.index.hour

df.head()

	humidity	temperature	hour
2015-01-01 00:00:00-06:00	0.73	38.74	0
2015-01-01 01:00:00-06:00	0.74	38.56	1
2015-01-01 02:00:00-06:00	0.75	38.56	2
2015-01-01 03:00:00-06:00	0.79	37.97	3
2015-01-01 04:00:00-06:00	0.80	37.78	4

mean_temp_humidity = df.groupby("hour").mean()
mean_temp_humidity.head()

	humidity	temperature
hour
0	0.779322	45.976441
1	0.803898	44.859492
2	0.812203	44.244407
3	0.819153	43.724068
4	0.832712	43.105763

mean_temp_humidity.plot(subplots=True);

We can use pivoting to achieve the same dataframe.

mean_temp_humidity_pivoting = pd.pivot_table(df, index=["hour"], values=["temperature", "humidity"])

mean_temp_humidity_pivoting.head()

	humidity	temperature
hour
0	0.779322	45.976441
1	0.803898	44.859492
2	0.812203	44.244407
3	0.819153	43.724068
4	0.832712	43.105763

By default the aggregation function used in pivoting is mean.

Question 2: Can we plot the daily variation in temperature per hour of the day?

For this, we want to have a dataframe with hour of day as the index and the different days as the different columns.

df["day"] = df.index.dayofyear

df.head()

	humidity	temperature	hour	day
2015-01-01 00:00:00-06:00	0.73	38.74	0	1
2015-01-01 01:00:00-06:00	0.74	38.56	1	1
2015-01-01 02:00:00-06:00	0.75	38.56	2	1
2015-01-01 03:00:00-06:00	0.79	37.97	3	1
2015-01-01 04:00:00-06:00	0.80	37.78	4	1

daily_temp = pd.pivot_table(df, index=["hour"], columns=["day"], values=["temperature"])

daily_temp.head()

	temperature
day	1	2	3	4	5	6	7	8	9	10	...	50	51	52	53	54	55	56	57	58	59
hour
0	38.74	39.94	39.57	41.83	33.95	36.98	46.93	29.95	36.57	36.19	...	46.17	54.01	66.57	55.49	37.68	30.34	34.97	39.93	36.19	32.25
1	38.56	39.76	39.75	40.85	32.29	35.89	45.33	28.55	37.31	36.40	...	41.38	54.56	66.57	55.49	36.76	30.04	34.97	36.37	36.38	32.25
2	38.56	39.58	39.94	39.73	31.59	36.44	44.51	27.44	37.78	36.59	...	39.99	55.81	66.57	55.34	35.56	30.57	34.75	34.74	36.20	32.25
3	37.97	38.83	40.16	38.78	30.48	36.85	43.92	25.97	37.97	36.38	...	39.05	57.14	66.38	55.27	34.94	30.59	35.15	34.31	36.20	32.52
4	37.78	39.02	40.65	39.74	29.89	35.72	44.37	24.74	37.82	35.49	...	37.99	57.51	66.57	55.49	34.04	30.38	35.15	33.02	34.49	32.52

5 rows × 59 columns

daily_temp.plot(style='k-', alpha=0.3, legend=False)
plt.ylabel("Temp");

So, we can see some pattern up there! Around 15 hours, the temperature usually peaks.

There you go! Some recipes for aggregation and plotting of time series data.