How to transform the data from one to another in pandas-CodePudding

I have following data

    Mar-22  Mar-22  Apr-22  Apr-22
Dimension 1 Dimension 2 AB  CD  AB  CD
X       Y       1   2   5   6
P       Q       3   4   5   7

which has to convert to below

I have to initialize a header Date

Dimension 1 Dimension 2 Date    AB  CD
X       Y       Mar-22  1   2
X       Y       Apr-22  5   6
P       Q       Mar-22  3   4
P       Q       Apr-22  5   7

CodePudding user response：

Use MultiIndex.set_levels for convert columns to datetimes, then use DataFrame.rename_axis for new column name Date and reshape by DataFrame.stack with DataFrame.reset_index, last convert Dates to original format by Series.dt.strftime:

print (df.columns)
MultiIndex([('Mar-22', 'AB'),
            ('Mar-22', 'CD'),
            ('Apr-22', 'AB'),
            ('Apr-22', 'CD')],
           )

print (df.index)
MultiIndex([('X', 'Y'),
            ('P', 'Q')],
           names=['Dimension 1', 'Dimension 2'])

df.columns = df.columns.set_levels(pd.to_datetime(df.columns.levels[0], 
                                   format='%b-%y'), level=0)

df = (df.rename_axis(['Date', None], axis=1)
      .stack(0)
      .reset_index()
      .assign(Date = lambda x: x.Date.dt.strftime('%b-%y'))

print (df)
  Dimension 1 Dimension 2    Date  AB  CD
0           X           Y  Mar-22   1   2
1           X           Y  Apr-22   5   6
2           P           Q  Mar-22   3   4
3           P           Q  Apr-22   5   7