在 Python 中的数据透视表列之间进行区分 [英] Take difference between pivot table columns in Python

问题描述

我有一个包含 Week 、 Campaign 、 Placement 和 Count 列的数据框.

I have a dataframe with a Week , Campaign , Placement and Count column.

为了按活动和展示位置比较每周计数，我创建了一个非常有效的数据透视表.如何使用这 2 周之间的差异创建新列(如果可能，以百分比表示)?

In order to compare counts per weeks by Campaign and Placement I created a pivot table that works great. How do I create a new column with the difference between these 2 weeks (in percentage if possible)?

代码:

dfPivot = pd.pivot_table(dfPivot, values='Count',\
                           index=['Campaign', 'Placement'],columns=['Week'], aggfunc=np.sum)

电流输出:

                      Week  2019-10-27  2019-11-03
Campaign    Placement Code      
A              111111111        4288.0    615.0
               111111112         243.0    11.0
               111111113         598.0    30.0
               111111114        1041.0    377.0
               111111115        7759.0    161.0
B              111111111        1252.0    241.0
               111111112         643.0    124.0
               111111113         135.0    30.0
               111111114        8753.0    2327.0
               111111115        7242.0    112.0

预期输出:

                      Week  2019-10-27  2019-11-03  Difference
Campaign    Placement Code      
A              111111111        4288.0    615.0     -85.7%
               111111112         243.0    11.0      -95.4%
               111111113         598.0    30.0      -94.9%
               111111114        1041.0    377.0     [...]
               111111115        7759.0    161.0     [...]
B              111111111        1252.0    241.0     [...]
               111111112         643.0    124.0     [...]
               111111113         135.0    30.0      [...]
               111111114        8753.0    2327.0    [...]
               111111115        7242.0    112.0     [...]

谢谢！

推荐答案

使用 DataFrame.pct_change 按位置选择最后一行并按 100 乘以百分比:

df['diff'] = df.pct_change(axis=1).iloc[:, -1].mul(100)
print (df)
                         2019-10-27  2019-11-03       diff
Campaign Placement Code                                   
A        111111111           4288.0       615.0 -85.657649
         111111112            243.0        11.0 -95.473251
         111111113            598.0        30.0 -94.983278
         111111114           1041.0       377.0 -63.784822
         111111115           7759.0       161.0 -97.924990
B        111111111           1252.0       241.0 -80.750799
         111111112            643.0       124.0 -80.715397
         111111113            135.0        30.0 -77.777778
         111111114           8753.0      2327.0 -73.414829
         111111115           7242.0       112.0 -98.453466

这篇关于在 Python 中的数据透视表列之间进行区分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！