按透视值Python对透视表进行排序

Question

我下面有一个数据帧，它希望按周进行数据透视，但在输出中，周值从最新日期到最早日期排序。

data = {'CustomerID': ['001', '002', '003', '004'], 'Name': [ 'Tom', 'Joseph', 'Krish', 'John'], 'ClosingDate': ['02/05/2021', '09/05/2021', '18/04/2021', '24/04/2021'], 'Quantity': [5, 6, 19, 4], 'Week': ['Quantity for Week Ending 02/05/2021', 'Quantity for Week Ending 09/05/2021', 'Quantity for Week Ending 18/04/2021', 'Quantity for Week Ending 24/04/2021']}
# Create DataFrame.
df = pd.DataFrame(data)

数据透视表如下：-

pivot_table = df.pivot_table('Quantity', ['CustomerID','Name','ClosingDate'], 'Week', aggfunc='sum').fillna(0).reset_index()

输出如下：

Week    CustomerID  Name    ClosingDate Quantity for Week Ending 02/05/2021 Quantity for Week Ending 09/05/2021 Quantity for Week Ending 18/04/2021 Quantity for Week Ending 24/04/2021
0        001        Tom       02/05/2021    5.0                                0.0                                             0.0                       0.0
1        002        Joseph    09/05/2021    0.0                                6.0                                             0.0                       0.0
2        003        Krish     18/04/2021    0.0                                0.0                                             19.0                      0.0
3        004        John      24/04/2021    0.0                                0.0                                             0.0                       4.0

如何将此数据透视表输出如下，从数据集中的最新日期到最早日期？

Week    CustomerID  Name    ClosingDate Quantity for Week Ending 09/05/2021 Quantity for Week Ending 02/05/2021     Quantity for Week Ending 24/04/2021 Quantity for Week Ending 18/04/2021 
0        001        Tom       02/05/2021    0.0                               5.0                                             0.0                        0.0
1        002        Joseph    09/05/2021    6.0                               0.0                                             0.0                        0.0
2        003        Krish     18/04/2021    0.0                                0.0                                            0.0                        19.0
3        004        John      24/04/2021    0.0                                0.0                                            4.0                        0.00        

Answer 1

尝试：

sorted_weeks = sorted(
df["Week"], key=lambda k: k.split()[-1].split("/")[::-1], reverse=True
)
pivot_table = df.pivot_table(
"Quantity", ["CustomerID", "Name", "ClosingDate"], "Week", aggfunc="sum"
).fillna(0)
pivot_table = pivot_table.reindex(sorted_weeks, axis=1).reset_index()
print(pivot_table)

打印：

Week CustomerID    Name ClosingDate  Quantity for Week Ending 09/05/2021  Quantity for Week Ending 02/05/2021  Quantity for Week Ending 24/04/2021  Quantity for Week Ending 18/04/2021
0           001     Tom  02/05/2021                                  0.0                                  5.0                                  0.0                                  0.0
1           002  Joseph  09/05/2021                                  6.0                                  0.0                                  0.0                                  0.0
2           003   Krish  18/04/2021                                  0.0                                  0.0                                  0.0                                 19.0
3           004    John  24/04/2021                                  0.0                                  0.0                                  4.0                                  0.0