Data frame transformation in Pandas

user_12226377 0
Viewed 66

I have the following data frame which I want to transform:

ID       Level        Band      Date
1        A            A1        2020-06-01
1        B            B1        2020-06-03
1        C            C1        2020-06-04

to

ID       First_Level      Second_Level    Third_Level  First_Band   Second_Band  Third_Band      First_Data   Second_Date     Third_Date
1        A                B                C           A1            B1          C1             2020-06-01   2020-06-03     2020-06-04

and this is a sample data. There are multiple IDs, levels, bands and associated dates. How to achieve the solution for this problem?

pandas-groupby pandas pivot-table python-3.x
1 Answers

Here is another approach for dynamic renaming:

import inflect
e = inflect.engine()

s = df.set_index('ID').stack()
idx_1 = (s.index.get_level_values(1) + 
         s.groupby(s.index).cumcount().add(1).map(e.ordinal).radd('_'))
s.index = pd.MultiIndex.from_arrays((s.index.get_level_values(0),idx_1))
print(s.unstack())

enter image description here

user_98406370
Related