How To Use Agg_func = 'all' In Python Pivot_table

I have an input dataframe like given below df = pd.DataFrame({'person_id' :[1,1,1,2,2,2,2,2,2],'level_1': ['L1FR','L1Date','L1value','L1FR','L1Date','L1value','L2FR','L2Date','L2va

Solution 1:

You're pretty close to have what you want.

The trick here is to add another index to your second level like this :

df = pd.DataFrame({'person_id' :[1,1,1,2,2,2,2,2,2],'level_1': ['L1FR','L1Date','L1value','L1FR','L1Date','L1value','L2FR','L2Date','L2value'], 'val3':['Fasting','11/4/2005',1.33,'Random','18/1/2007',4.63,'Fasting','18/1/2017',8.63]})
g = df.level_1.str[-2:]
# Extracting level's number
df['lvl'] = df.level_1.apply(lambda x: int(''.join(filter(str.isdigit, x))))
# Then you pivot with person_id and lvl
df = df.pivot_table(index=['person_id', 'lvl'], columns=g, values='val3', aggfunc='first')

Output should be :

            level_1     FR          te          ue
person_id   lvl             
        1   1       Fasting     11/4/2005   1.33
        2   1           Random      18/1/2007   4.63
            2           Fasting     18/1/2017   8.63

Then if you reset the level 1 index like this :

df.reset_index(level=1).drop("lvl", axis=1)

The output is :

level_1     FR          te          ue
person_id           
1           Fasting     11/4/2005   1.33
2           Random      18/1/2007   4.63
2           Fasting     18/1/2017   8.63

And there you go !