Python: How to turn a dataframe (matrix table) into a single row combining header and index to new h-CodePudding

I got a dataframe with months as index and years as header. Something like this:

    2021  2020  2019
jan    1     7     3
feb    2     4     5
mar    2     4     5 
apr    2     4     6
may    2     4     5
jun    4     4     5
jul    2     4     5
aug    2     4     8
sep    7     4     5
oct    2     4     5
nov    2     4     5
dec    2     4     5

How can I turn this into a df with a single row with columns 2021-jan, 2021-feb, 2021-mar ... 2021-dec, 2020-jan... Something like this:

   2021-jan  2021-feb  2021-mar
0         1         2         2

CodePudding user response：

Try with stack and pivot_table:

output = df.stack().reset_index().pivot_table(columns=["level_1","level_0"], values=0)

#convert columns to datetime and sort
output.columns = pd.to_datetime(["-".join(col).strip() for col in output.columns.values],format="%Y-%b")
output = output.reindex(sorted(output.columns, key=lambda x: (-x.year, x.month)), axis=1)

#rename columns to strings if needed
output.columns = [col.strftime("%Y-%b") for col in output.columns]

>>> output
   2021-Jan  2021-Feb  2021-Mar  ...  2019-Oct  2019-Nov  2019-Dec
0         1         2         2  ...         5         5         5

[1 rows x 36 columns]