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.