Pandas – Change pivot order to get column in descending order

September 12, 2022

I am trying to create a pivot using the Dataframe that would sort Week wise data in descending order than ascending

Given below is the data I have:

user_id, login_date, num_events, login_week
101, 2022-09-07, 1, Week 36
101, 2022-09-06, 4, Week 36
101, 2022-08-31, 1, Week 35
102, 2022-08-26, 1, Week 34
102, 2022-08-27, 7, Week 34
102, 2022-09-07, 1, Week 36

I am able to get data in this format:

user_id, Week 34, Week 35, Week 36
101, 0, 1, 5
102, 8, 0, 1

This is what I have currently:

df = df.groupby(['login_week', 'user_id'])['num_events'].agg('sum').reset_index()
df = df.pivot(index= ['user_id'], columns = 'login_week', values = 'num_events').reset_index()

I would like the output to be in the below format where the latest week comes up first and then goes to the earliest week

user_id, Week 36, Week 35, Week 34
101, 5, 1, 0
102, 1, 0, 8

>Solution :

Instead your solution use DataFrame.pivot_table with DataFrame.sort_index:

df = (df.pivot_table(index= 'user_id', 
                    columns = 'login_week', 
                    values = 'num_events',
                    aggfunc='sum',
                    fill_value=0)
        .sort_index(axis=1, ascending=False)
        .reset_index()
        .rename_axis(None, axis=1))
print (df)
   user_id  Week 36  Week 35  Week 34
0      101        5        1        0
1      102        1        0        8