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
CodePudding user response:
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