Transposing columns to rows Pandas

September 26, 2023

I have a situation like following:

ID	Old value.Carbon	Old value.Dioxide	New value.Carbon	New Value.Dioxide
123	34.89	13.45	56.66	11.11
456	12.13	55.66	66.88	12.33

My output that i want should be:( i want the column to be transposed to rows like following, i have renamed the fields as Old Value. and New Value. because i thought this might help but if you have any other ideas i am more than happy to try something different. Thanks a lot for all the help!

ID	Field	New Value	Old Value
123	Carbon	56.66	34.89
123	Dioxide	11.11	13.45
456	Dioxide	12.33	12.33
456	Carbon	66.88	12.13

>Solution :

Using stack and a MultiIndex to reshape:

tmp = df.set_index('ID')
out = (tmp.set_axis(tmp.columns.str.capitalize()
                       .str.split('\.', expand=True),
                    axis=1)
          .rename_axis(columns=(None, 'Field'))
          .stack()
          .reset_index()       
      )

Output:

    ID    Field  Old value  New value
0  123   carbon      34.89      56.66
1  123  dioxide      13.45      11.11
2  456   carbon      12.13      66.88
3  456  dioxide      55.66      12.33