Pandas Resample Time Series

Intro

Resampling is a common task when working with time series dta. Resampling goes in two directions, upsampling and downsampling. Upsampling allows us to go from a lower time frame to a higher, i.e. minutes to hours. Downsampling is the reverse. In this article, we will learn how to do resampling in python with pandas.

Loading the Data

The data we will use is air quality data for city and countries around the world. The main columns we will use are the date.utc and value.

import pandas as pd

df = pd.read_csv('https://raw.githubusercontent.com/pandas-dev/pandas/master/doc/data/air_quality_no2_long.csv')

df.head()

Indexing the Date

When working with time series, we often want to index our data frame by a date field. We first use to_datetime to convert date.utc to a date then we use set_index to set a date index.

df['date.utc'] = pd.to_datetime(df['date.utc'])
df = df.set_index('date.utc')

df = df.sort_index()
df.head()

Downsampling in Pandas

The first type of resample we will do is downsampling. We can do this by using the resample method and passing in a number plus an offset alias. Here we use 1D for one day. This will sum our data per a day.

You can check out more offsets here: https://pandas.pydata.org/pandas-docs/stable/user_guide/timeseries.html#offset-aliases.

df.resample('1D').sum().head()

We can also run multiple aggregate functions by using the agg function.

df['value'].resample('1D').agg(['min', 'max', 'sum']).head()

We can also run more customize indexes by passing the offset parameter. Here aggregate by 5days with a 2 day offset.

df.resample('5D', offset="2D").sum()

Upsampling in Pandas

Upsampling is the opposite of downsampling, as the name implies. We can run upsampling in a similar way, however, we will need to fill in missing data. Let's start with an example of upsampling our data to quarters.

We will need a new data frame for this. We will be moving from years to qurters, so there will be missing data. First let's create a fake data frame with yearly sales.

df = pd.DataFrame(
    { 
        'value': [1000, 2000, 3000]
    }, 
    index = pd.period_range(
        '2016-01-01',
         freq = 'A',
         periods = 3
    )
)
df.head()

Just like before, we can use the resample method. This time, we will resample by Q for quarter.

df.resample('Q').sum()

As you may notice from above, we are missing data for the quarters. We will need to use a fill strategy to resolve this.

The first way we can resolve this is with the ffill method which will conduct forward fill. This takes the values from the year and moves them until it finds a non empty value.

df.resample('Q').ffill()

Similarly, we can use the bfill to fill backwards.

df.resample('Q').bfill()