在大熊猫数据框架上添加与Colunm之间的差异的行

Question

数据源（CSV）：

Year;Month;website;status,XXX;Requests
2021;1;web.com;2XX;94557605
2021;1;web.com;3XX;18113
2021;1;web.com;4XX;412104
2021;1;web.com;5XX;1944
2021;2;web.com;2XX;65915413
2021;2;web.com;3XX;8176
2021;2;web.com;4XX;98080
2021;2;web.com;5XX;404
2021;3;web.com;2XX;73385551
2021;3;web.com;3XX;8204
2021;3;web.com;4XX;51097
2021;3;web.com;5XX;866
2021;4;web.com;2XX;19161899
2021;4;web.com;3XX;3113
2021;4;web.com;4XX;92152
2021;4;web.com;5XX;1110
2021;5;web.com;2XX;68930518
2021;5;web.com;3XX;3753
2021;5;web.com;4XX;80019
2021;5;web.com;5XX;69806
2021;1;api.com;2XX;94557605
2021;1;api.com;3XX;18113
2021;1;api.com;4XX;412104
2021;1;api.com;5XX;1944
2021;2;api.com;2XX;65915413
2021;2;api.com;3XX;8176
2021;2;api.com;4XX;98080
2021;2;api.com;5XX;404
2021;3;api.com;2XX;73385551
2021;3;api.com;3XX;8204
2021;3;api.com;4XX;51097
2021;3;api.com;5XX;866
2021;4;api.com;2XX;19161899
2021;4;api.com;3XX;3113
2021;4;api.com;4XX;92152
2021;4;api.com;5XX;1110
2021;5;api.com;2XX;68930518
2021;5;api.com;3XX;3753
2021;5;api.com;4XX;80019
2021;5;api.com;5XX;69806

我已经在上面的数据源上创建了一个熊猫枢轴 - 桌子：

=“ https://i.sstatic.net/16he0.png” alt =“在此处输入图像描述”>

目标是创建一个带有以下信息的新数据框：

• 带有diff的新行。每个“年，月，网站，status.xxx”上一个月。

示例：月（n-1） - （n） - ＆gt; （1） - （2） - ＆GT; 94557605-65915413网站：web.com，status.xxx：2xx

请帮助我提供任何建议。 提前提前提取帮助。

Answer 1

pivoted = df.pivot(index=['Year', 'Month'], columns=['website', 'status,XXX'])
piv_diff = pivoted.diff(-1)
out = pivoted.join(piv_diff, lsuffix='_val', rsuffix='_diff').T.reorder_levels([-2, -1, -3]).sort_index(ascending=[False, True, False])
print(out)

输出：

Year                                    2021
Month                                      1           2           3           4           5
website status,XXX
web.com 2XX        Requests_val   94557605.0  65915413.0  73385551.0  19161899.0  68930518.0
                   Requests_diff  28642192.0  -7470138.0  54223652.0 -49768619.0         NaN
        3XX        Requests_val      18113.0      8176.0      8204.0      3113.0      3753.0
                   Requests_diff      9937.0       -28.0      5091.0      -640.0         NaN
        4XX        Requests_val     412104.0     98080.0     51097.0     92152.0     80019.0
                   Requests_diff    314024.0     46983.0    -41055.0     12133.0         NaN
        5XX        Requests_val       1944.0       404.0       866.0      1110.0     69806.0
                   Requests_diff      1540.0      -462.0      -244.0    -68696.0         NaN
api.com 2XX        Requests_val   94557605.0  65915413.0  73385551.0  19161899.0  68930518.0
                   Requests_diff  28642192.0  -7470138.0  54223652.0 -49768619.0         NaN
        3XX        Requests_val      18113.0      8176.0      8204.0      3113.0      3753.0
                   Requests_diff      9937.0       -28.0      5091.0      -640.0         NaN
        4XX        Requests_val     412104.0     98080.0     51097.0     92152.0     80019.0
                   Requests_diff    314024.0     46983.0    -41055.0     12133.0         NaN
        5XX        Requests_val       1944.0       404.0       866.0      1110.0     69806.0
                   Requests_diff      1540.0      -462.0      -244.0    -68696.0         NaN