Title: Group Data By Time
Slug: pandas_group_data_by_time
Summary: Group Data By Time
Date: 2016-03-11 12:00
Category: Python
Tags: Data Wrangling
Authors: Chris Albon
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
In [1]:
# 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.
In [2]:
# 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)]
In [3]:
# Create a list variable of 365 numeric values
score_list = list(np.random.randint(low=1, high=1000, size=365))
In [4]:
# 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
In [5]:
# Let's take a took at the data
df.head()
Out[5]:
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.)
In [6]:
# Group the data by month, and take the mean for each group (i.e. each month)
df.resample('M').mean()
Out[6]:
In [7]:
# Group the data by month, and take the sum for each group (i.e. each month)
df.resample('M').sum()
Out[7]:
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 |