Pandas can convert datetime frequencies by resampling. This allows you to turn per-second data into 5-minutely data. This is a very common task when you're dealing with timeseries. So let's explore how this works.
Let's start by making a subset of our data.
subset_df = (df .assign(date=lambda d: pd.to_datetime(d['date'], format="%Y-%m-%d")) [['state', 'date', 'births']] .loc[lambda d: d['state'] == 'CA'])
This dataframe represents the births in the state of California. Here's the top 5 rows.
If you're interested in calculating aggregates here you could could generate
a grouping-feature, like year, pass it in a group-by and aggregate. There is
a more convenient method though, which involves using the
.resample() you'll need to make sure that the dataframe has an index
that's a datetime column first. Then you'll be able to call resample, which
acts kind of like a group-by but has a convenient string-syntax to declare
time windows. After that you'll be able to call an aggregation method to summarise
Calculate the sum per year.
This example calculates the total birhts per year in California.
Calculate the average per two weeks.
This example calculates the average births every two weeks in California. Note that we're able to use a number here to indicate we're dealing with two weeks instead of one!
Together with Groupby
In the previous example we calculated statistics but we only calculated statistics
for the state of California. We could combine the use of
to calculate these numbers for all states. If you do this, remember that you can
.groupby(), not the other way around.
(df[['state', 'date', 'births']] .assign(date=lambda d: pd.to_datetime(d['date'], format='%Y-%m-%d')) .set_index('date') .groupby('state') .resample('2W').sum())
The top 5 rows of this query look like:
The bottom 5 of this table look like:
Offsets in Resample
There are many ways to group dates together using the string-like syntax in resample. The table below shows a subset of all possible offsets.
|B||business day frequency|
|D||calendar day frequency|
|M||month end frequency|
|Q||quarter end frequency|
|T, min||minutely frequency|
This table represents a subset of all offsets found on the pandas documentation
Summary Methods in Resample
Typically, you'll be interested in calculating the
.mean() or the
good to know though that many other methods are also supported.
||Compute count of group, excluding missing values.|
||Return number of unique elements in the group.|
||Compute first of group values.|
||Compute last of group values.|
||Compute max of group values.|
||Compute mean of groups, excluding missing values.|
||Compute median of groups, excluding missing values.|
||Compute min of group values.|
||Compute group sizes.|
||Compute sum of group values.|
||Return value at the given quantile.|
A full list of descriptive statistics can be found on the pandas documentation.
There is also a more general .agg() method which is similar to the
you may know from
.groupby-objects but there are subtle differences to be aware of.
One of the main difference is that named aggregations are not impemented yet
at the time of writing this document.