Y over Y change in Pivot Table

Question

I have a pivot table and I'd want to create another pivot table of the same format but that now it contains Year over Year percent change.

This is a simple example:

my_data = {
    'date': [datetime.date(2000,1,7), datetime.date(2000,1,14),
             datetime.date(2001,1,5), datetime.date(2001,1,12)],
    'week_number': [1,2,1,2],
    'quarter_number': [1,1,1,1],
    'name': ['hi','bye','hi','bye'],
    'category': ['clothing','electronics','clothing','electronics'],
    'total sales': [123,456,180,350]
}
my_df = pd.DataFrame(my_data)
my_df.pivot_table(index=['date','week_number','quarter_number'], columns=['name', 'category'])

Resulting in following pivot table:

                                      total sales         
name                                          bye       hi
category                              electronics clothing
date       week_number quarter_number                     
2000-01-07 1           1                      NaN    123.0
2000-01-14 2           1                    456.0      NaN
2001-01-05 1           1                      NaN    180.0
2001-01-12 2           1                    350.0      NaN

Now let us say I want to compute percent change Year over Year. The resulting pivot table would look like:

                                      total sales pchg Y/Y         
name                                          bye       hi
category                              electronics clothing
date       week_number quarter_number                     
2000-01-07 1           1                      NaN      NaN
2000-01-14 2           1                      NaN      NaN
2001-01-05 1           1                      NaN    0.463
2001-01-12 2           1                    -0.23      NaN

Note that in the general case we have N names, many years of data and K categories.

I provide here a more general case too to show that pct_change does not work in default mode since it'd not do percent change Year over Year.

my_data = {
    'date': [datetime.date(2000,1,7), datetime.date(2000,1,14),
             datetime.date(2001,1,5), datetime.date(2001,1,12),
             datetime.date(2000, 1, 7), datetime.date(2000, 1, 14),
             datetime.date(2001, 1, 5), datetime.date(2001, 1, 12),
             datetime.date(2000, 1, 7), datetime.date(2000, 1, 14),
             datetime.date(2001, 1, 5), datetime.date(2001, 1, 12),
             datetime.date(2000, 1, 7), datetime.date(2000, 1, 14),
             datetime.date(2001, 1, 5), datetime.date(2001, 1, 12)],
    'week_number': [1,2,1,2,1,2,1,2,1,2,1,2,1,2,1,2],
    'quarter_number': [1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1],
    'name': ['hi','hi','hi','hi','hi','hi','hi','hi','bye','bye','bye','bye','bye','bye','bye','bye'],
    'category': ['clothing','clothing','clothing','clothing','electronics','electronics','electronics','electronics',
                 'clothing', 'clothing', 'clothing', 'clothing', 'electronics', 'electronics', 'electronics','electronics'],
    'total sales': [123,456,180,350,123,456,180,350,123,456,180,350,123,456,180,350]
}
my_df = pd.DataFrame(my_data)
my_df.pivot_table(index=['date','week_number','quarter_number'], columns=['name', 'category'])

my_df.pivot_table(index=['date','week_number','quarter_number'], columns=['name', 'category']).apply(pd.Series.pct_change)
                                      total sales     ...                
name                                          bye     ...              hi
category                                 clothing     ...     electronics
date       week_number quarter_number                 ...                
2000-01-07 1           1                      NaN     ...             NaN
2000-01-14 2           1                 2.707317     ...        2.707317
2001-01-05 1           1                -0.605263     ...       -0.605263
2001-01-12 2           1                 0.944444     ...        0.944444

The pct_change is clearly wrong as it does not provide Y/Y changes but rather row i to row i+1.

Answer 1

You can achieve desired result with pct_change:

pivoted = pd.pivot_table(my_df, index=['date','week_number','quarter_number'], columns=['name', 'category'])
pivoted.groupby(level='week_number').transform(pd.Series.pct_change)
#                                      total sales          
#name                                          bye        hi
#category                              electronics  clothing
#date       week_number quarter_number                      
#2000-01-07 1           1                      NaN       NaN
#2000-01-14 2           1                      NaN       NaN
#2001-01-05 1           1                      NaN  0.463415
#2001-01-12 2           1                -0.232456       NaN