How to melt a dataframe into a long form?-CodePudding

I have the following dataframe

recycling 1 metric tonne (1000 kilogram) per waste type  Unnamed: 1 Unnamed: 2     Unnamed: 3       Unnamed: 4      Unnamed: 5
0    1 barrel oil is approximately 159 litres of oil       NaN          NaN            NaN              NaN             NaN
1                NaN                                       NaN          NaN            NaN              NaN             NaN
2                material                                  Plastic    Glass     Ferrous Metal     Non-Ferrous Metal     Paper
3               energy_saved                               5774 Kwh   42 Kwh         642 Kwh          14000 Kwh        4000 kWh
4               crude_oil saved                            16 barrels   NaN        1.8 barrels        40 barrels      1.7 barrels

For reference look at the image: What I want to do is to get the rows 2, 3, 4 into cols in a new dataframe. It should be looking some like this..

  material   energy_saved   crude_oil saved
  plastic     5774Kwh         16 barrels
  Glass        42 Kwh          NaN
  ...           ...            ...

I tried using .melt but it was not working. If you notice, the col name and its values are in a single row. I just want them to be in a new data frame as col and value.

CodePudding user response：

IIUC, is it just:

out = df.loc[[2,3,4],:].T.reset_index(drop=True)