CODEWITHSUNDEEP
pythonpython-3.xpandasdataframedata-manipulation
Nirali Khoda
Nirali Khoda

Reputation: 1691

Groupby with transform and rename columns pandas

I have a of groupby-transform and rename columns

data = [['X100',"Mar '20" ,10,1,10,67], ['X110',"Apr '20", 15,7687,665,988], \
    ['X100',"Jun '20", 14,90,73,23]\
    ,['X192',"Jul '20", 14,990,273,623]]

df = pd.DataFrame(data, columns = ['Unique_ID', 'date','x','y','z','a'])

I want to groupby "Unique_ID" and Transform and Rename with Date Column

Expected Output is:

enter image description here

Upvotes: 1

Views: 153

Answers (1)

jezrael
jezrael

Reputation: 862591

Use to_datetime for correct ordering, DataFrame.set_index with DataFrame.unstack and DataFrame.sort_index for reshape and last list comprehension with f-strings for flatten MultiIndex:

df['date'] = pd.to_datetime(df['date'], format='%b \'%y')
df = (df.set_index(['Unique_ID','date'])
        .unstack()
        .sort_index(level=1, axis=1, sort_remaining=False))
df.columns = [f'{y.strftime("%b%y")}_{x}' for x, y in df.columns]
df = df.reset_index()
print (df)
  Unique_ID  Mar20_x  Mar20_y  Mar20_z  Mar20_a  Apr20_x  Apr20_y  Apr20_z  \
0      X100     10.0      1.0     10.0     67.0      NaN      NaN      NaN   
1      X110      NaN      NaN      NaN      NaN     15.0   7687.0    665.0   
2      X192      NaN      NaN      NaN      NaN      NaN      NaN      NaN   

   Apr20_a  Jun20_x  Jun20_y  Jun20_z  Jun20_a  Jul20_x  Jul20_y  Jul20_z  \
0      NaN     14.0     90.0     73.0     23.0      NaN      NaN      NaN   
1    988.0      NaN      NaN      NaN      NaN      NaN      NaN      NaN   
2      NaN      NaN      NaN      NaN      NaN     14.0    990.0    273.0   

   Jul20_a  
0      NaN  
1      NaN  
2    623.0

Upvotes: 1

Related Questions