Reformat index of dataframe

Question

I have a dataframe (df) which has a year per row:

           Jan       Feb       Mar       Apr  ...       Aug       Sep       Oct       Nov       Dec
2021  0.852144  0.406946  0.067136  0.686585  ...  0.839023  0.803384  0.506883  0.829171  0.214810
2022  0.442388  0.296960  0.751213  0.690898  ...  0.701342  0.924836  0.534601  0.601725  0.673403

That I would like to reformat so there is one row with all month/years in sequential order (where the index for each row if mmm yyyy format) so it would look like:

                   0
Jan 2021    0.852144
Feb 2021    0.406945
Mar 2021    0.067136
Apr 2021    0.686585
     ...         ...
Aug 2022    0.701342
Sep 2022    0.924836
Oct 2022    0.534601
Nov 2022    0.601725
Dec 2022    0.673403

I have used:

df = df.unstack().to_frame().T.sort_index(0,1).T

but I can't get the index to format correctly for all rows(would like mmm yyyy for each row index - currently only returning the month for alternate rows)

                  0 
Jan  2021  0.852144 
     2022  0.442388 
Feb  2021  0.406946 
     2022  0.296960 
Mar  2021  0.067136 
     2022  0.751213 
Apr  2021  0.686585 
     2022  0.690898 
      ...       ...
Aug  2021  0.839023 
     2022  0.701342 
Sep  2021  0.803384 
     2022  0.924836 
Oct  2021  0.506883 
     2022  0.534601 
Nov  2021  0.829171 
     2022  0.601725 
Dec  2021  0.214810 
     2022  0.673403

jezrael · Accepted Answer · 2022-06-13T10:15:24.853

Use Index.map if need flatten MultiIndex, sorting with transpose is not necessary:

df = df.unstack().to_frame()
df.index = df.index.map(lambda x: f'{x[0]} {x[1]}')

Or use DataFrame.melt:

df = df.melt(ignore_index=False, value_name=0)
df.index = df.pop('variable') + ' ' + df.index.astype(str)
print (df)
                 0
Jan 2021  0.852144
Jan 2022  0.442388
Feb 2021  0.406946
Feb 2022  0.296960
Mar 2021  0.067136
Mar 2022  0.751213
Apr 2021  0.686585
Apr 2022  0.690898
Aug 2021  0.839023
Aug 2022  0.701342
Sep 2021  0.803384
Sep 2022  0.924836
Oct 2021  0.506883
Oct 2022  0.534601
Nov 2021  0.829171
Nov 2022  0.601725
Dec 2021  0.214810
Dec 2022  0.673403

If need sorting joined values by datetimes ordering use DataFrame.sort_index with key parameter:

df = df.sort_index(key=lambda x: pd.to_datetime(x))
print (df)
                 0
Jan 2021  0.852144
Feb 2021  0.406946
Mar 2021  0.067136
Apr 2021  0.686585
Aug 2021  0.839023
Sep 2021  0.803384
Oct 2021  0.506883
Nov 2021  0.829171
Dec 2021  0.214810
Jan 2022  0.442388
Feb 2022  0.296960
Mar 2022  0.751213
Apr 2022  0.690898
Aug 2022  0.701342
Sep 2022  0.924836
Oct 2022  0.534601
Nov 2022  0.601725
Dec 2022  0.673403

Same output is with DataFrame.stack:

df = df.stack().to_frame()
#swapped levels
df.index = df.index.map(lambda x: f'{x[1]} {x[0]}')

Reformat index of dataframe

1 Answers1