# How to transform the data from one to another in pandas

## How to transform the data from one to another in pandas

Contents

Problem Description:

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
``````

## Solution – 1

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 `Date`s 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
``````
Rate this post
We use cookies in order to give you the best possible experience on our website. By continuing to use this site, you agree to our use of cookies.