Nipun Batra Blog - Aggregation in Timeseries using Pandas (original) (raw)

Pandas excellence in timeseries!

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")

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

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

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()

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

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

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.