python - 数据透视表中 Y 相对于 Y 的变化

Question

我有一个数据透视表，我想创建另一个相同格式的数据透视表，但现在它包含逐年百分比变化。

这是一个简单的例子:

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'])

产生以下数据透视表:

                                      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

现在假设我想计算逐年变化百分比。生成的数据透视表如下所示:

                                      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

请注意，在一般情况下，我们有 N 个名称、多年的数据和 K 个类别。

我在这里还提供了一个更一般的情况，以表明 pct_change 在默认模式下不起作用，因为它不会逐年发生百分比变化。

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

pct_change 显然是错误的，因为它不提供 Y/Y 更改，而是提供第 i 行到第 i+1 行的更改。

Answer 1

您可以使用 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