Sorting pivot table by the pivot value Python

user_15760693 0
Viewed 90

I have this dataframe below that would like to pivot by week but have the Week values sorted from the latest date to the oldest in the output.

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 is below:-

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

Output will be as below:

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

How can I have this pivot table output as below, from the latest date to the oldest in the dataset?

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
pandas pivot-table python
1 Answers

Try:

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)

Prints:

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
user_100359850
Related