CODEWITHSUNDEEP
pythonpython-3.xpandasdataframepandas-groupby
The Great
The Great

Reputation: 7733

How to use custom column names and change structure during unstack?

I have a dataframe like as shown below

op1 = pd.DataFrame({
'subject_id':[1,1,1,1,2,2,2,2],
'date' : ['1/1/2017','1/2/2017','1/1/2017','1/2/2017','1/11/2017','1/12/2017','1/11/2017','1/12/2017'],
'val' :[5,6,5,11,5,7,16,12],
'item_name':['P','P','F','F','P','P','F','F'],
'val_max':[11,13,15,16,21,22,24,25]
 })

enter image description here

Please note that I already referred this post to adapt my code

I would like to transform this to find the maximum of **val_max** for each subject.

This is what I tried. Though it works and gives me the output, the structure isn't accurate yet

t1 = op1.groupby(['subject_id','item_name'])['val_max'].max().reset_index()
t1.set_index(['subject_id','item_name']).unstack().add_prefix('Max_').rename_axis(None).reset_index()

enter image description here

Instead, I would like to have my output like as shown below

Please note the column names are custom. I don't wish to stick to the prefix/suffix constraint

enter image description here

Upvotes: 1

Views: 158

Answers (1)

jezrael
jezrael

Reputation: 863256

Use DataFrame.add_suffix and axis=1 in DataFrame.rename_axis for remove columns names:

t1 = (op1.groupby(['subject_id','item_name'])['val_max']
         .max()
         .unstack()
         .add_suffix('_max')
         .rename_axis(None, axis=1)
         .reset_index())
print (t1)
   subject_id  F_max  P_max
0           1     16     13
1           2     25     22

Upvotes: 1

Related Questions