On March 13, 2016, version 0.18.0 of Pandas was released, with significant changes in how the resampling function operates. This tutorial follows v0.18.0 and will not work for previous versions of pandas.
First let’s load the modules we care about
# Import required packages import pandas as pd import datetime import numpy as np
Next, let’s create some sample data that we can group by time as an sample. In this example I am creating a dataframe with two columns with 365 rows. One column is a date, the second column is a numeric value.
# Create a datetime variable for today base = datetime.datetime.today() # Create a list variable that creates 365 days of rows of datetime values date_list = [base - datetime.timedelta(days=x) for x in range(0, 365)]
# Create a list variable of 365 numeric values score_list = list(np.random.randint(low=1, high=1000, size=365))
# Create an empty dataframe df = pd.DataFrame() # Create a column from the datetime variable df['datetime'] = date_list # Convert that column into a datetime datatype df['datetime'] = pd.to_datetime(df['datetime']) # Set the datetime column as the index df.index = df['datetime'] # Create a column from the numeric score variable df['score'] = score_list
# Let's take a took at the data df.head()
|2016-06-02 09:57:54.793972||2016-06-02 09:57:54.793972||900|
|2016-06-01 09:57:54.793972||2016-06-01 09:57:54.793972||121|
|2016-05-31 09:57:54.793972||2016-05-31 09:57:54.793972||547|
|2016-05-30 09:57:54.793972||2016-05-30 09:57:54.793972||504|
|2016-05-29 09:57:54.793972||2016-05-29 09:57:54.793972||304|
Group Data By Date
In pandas, the most common way to group by time is to use the .resample() function. In v0.18.0 this function is two-stage. This means that ‘df.resample(’M’)’ creates an object to which we can apply other functions (‘mean’, ‘count’, ‘sum’, etc.)
# Group the data by month, and take the mean for each group (i.e. each month) df.resample('M').mean()
# Group the data by month, and take the sum for each group (i.e. each month) df.resample('M').sum()
There are many options for grouping. You can learn more about them in Pandas’s timeseries docs, however, I have also listed them below for your convience.
| Value | Description |—| |B | business day frequency |C | custom business day frequency (experimental) |D | calendar day frequency |W | weekly frequency |M | month end frequency |BM | business month end frequency |CBM | custom business month end frequency |MS | month start frequency |BMS | business month start frequency |CBMS| custom business month start frequency |Q | quarter end frequency |BQ | business quarter endfrequency |QS | quarter start frequency |BQS | business quarter start frequency |A | year end frequency |BA | business year end frequency |AS | year start frequency |BAS | business year start frequency |BH | business hour frequency |H | hourly frequency |T | minutely frequency |S | secondly frequency |L | milliseonds |U | microseconds |N | nanosecondsa