Search code examples
pythonpandasstacknormalize

normalizing pandas df with stacking level values together

I have the following pandas df and I would like to normalize it:

df
    id         technology  co2_var  co2_fix  eta_elec 
0    1            lignite  0.39960   17.610     0.434
1    2          hard_coal  0.33012   11.660     0.390

normalized df should look like following:

df_norm
    id         technology  parameter     value   unit 
0    1            lignite    co2_var   0.39960   
1    2            lignite    co2_fix    17.610          
2    3            lignite   eta_elec     0.434   
3    4          hard_coal    co2_var   0.33012
4    5          hard_coal    co2_fix    11.660
5    6          hard_coal   eta_elec     0.390

so level values (after technology) of df should be stacked in a level called parameter, the values of them should be placed under level value, and a new level unit should be created empty.

How can I do that?

@W-B ty for df.melt(['id', 'technology']).assign(unit='')

     id         technology  variable    value   unit
0     1            lignite   co2_var  0.39960     
1     2          hard_coal   co2_var  0.33012
2     1            lignite   co2_fix ...   
3     2          hard_coal   co2_fix ...

Problems:

Same id numbers and actually this orders the df in respect to variable(parameter) not respect to technology

Solution

  • IIUC

    df=df.melt(['id', 'technology']).assign(unit='').sort_values(['id','technology'])
df.id=np.arange(len(df))+1