“轴”选项卡中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\u的变化在默认模式下是不起作用的，因为它不会逐年改变百分比。你知道吗

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\ U更改显然是错误的，因为它不提供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