My favorite pandas pattern

I work with a lot of transactional timeseries data that includes categories. I often want to create timeseries plots with each category as its own line. This is the method that I use almost data to achieve this result. Typically the data that am working with changes very slowly and trends happen over years not days or weeks. Plotting daily/weekly data tends to be noisy and hides the trend. I use this pattern because it works well with my data and is easy to explain to my stakeholders.

import pandas as pd
import numpy as np
% matplotlib inline

Lets Fake some data

Here I am trying to simulate a subset of a large transactional data set. This could be something like sales data, production data, hourly billing, anything that has a date, category, and value. Since we generated this data we know that it is clean. I am still going to assume that it contains some nulls, and an irregular date range.

n = 365*5
cols = {'level_0': 'date',
        'level_1': 'item',
        0: 'qty', }
data = (pd.DataFrame(np.random.randint(0, 10, size=(n, 4)),
                     columns=['paper', 'pencils', 'note cards', 'markers'],
                     index=pd.date_range('1/1/2017', periods=n, freq='d'),
                     )
        .stack()
        .to_frame()
        .reset_index()
        .rename(columns=cols))
data.head()

The pattern

Here I am going to take my groupby date and item, this will take care of duplicate entries with the same time stamp. Select the value I want to sum on. unstack the items index into columns. Resample the data by month. I could easily use any of the available rules. Fill any missing months with 0, since there wasnt a transaction during that month. Apply a rolling window to get the annual sum. I find that this helps to ground values in values that my stakeholders are used to seeing on a regular basis and reduces the need for them to recalculate in their head. Then I am going to drop the nulls created by the rolling window for the first 11 rows.

plot_data = (data
             .groupby(['date', 'item'])
             .sum()
             ['qty']
             .unstack()
             .resample('m')
             .sum()
             .fillna(0)
             .rolling(12)
             .sum()
             .dropna()
             )
plot_data.head()

plot_data.plot(title='Rolling annual sum of Categorical Random Data');

For the Visual Learners

Groupby

plot_data = (data
             .groupby(['date', 'item'])
             .sum()
             )
plot_data.head()

Select Values

In this case I chose to do this to avoid working with a multiple levels in the columns that would be created in the unstack() step.

plot_data = plot_data['qty']
plot_data.head()

date        item
2017-01-01  markers       9
            note cards    5
            paper         1
            pencils       4
2017-01-02  markers       4
Name: qty, dtype: int32

unstack

transform the last column in the index ('item') into rows.

plot_data = plot_data.unstack()
plot_data.head()

resample

This step is important for irregular data in order to get the data into regular intervals.

plot_data = plot_data.resample('m').sum()
plot_data.head()

rolling

I like to use rolling because it get the data into annual numbers, and reduces noise. I have found that most of my datasets have patterns and trends that are greater than 1y. This is just due to the industry that I am in. Play with the resample and rolling rules to fit the need of your own data.

plot_data = plot_data.rolling(12).sum()
plot_data.head(20)

dropna

get rid of the first 11 null rows

plot_data = plot_data.dropna()
plot_data.head(10)