I am trying to reshape a multiindex pandas dataframe. A sample of the original dataframe is below.
import pandas as pd
import numpy as np
idx = pd.MultiIndex.from_product([
['2023-06-30', '2023-07-31', '2023-08-31'],
['Post', 'Pre', 'Curr']
], names=['portdate', 'recontype'])
cols = ['begbal', 'unschd_pmt', 'unschd_part_pmt', 'unschd_full_pmt']
df = pd.DataFrame(np.random.rand(9,4), idx, cols)
My desired output would also be a multiindex dataframe. Something that looks like this.
6/30/2024 | 7/31/2023 | 8/31/2023
recontype | balancetype |
Curr | begbal | 0.130718 | 0.109284 | 0.642785
Curr | unschd_pmt | 0.510886 | 0.940061 | 0.391445
Curr | unschd_part_pmt | 0.746261 | 0.431154 | 0.385217
Curr | unschd_full_pmt | 0.062011 | 0.170478 | 0.757842
Pre | begbal | 0.566243 | 0.594380 | 0.694061
...
Post | begbal | 0.729325 | 0.782333 | 0.465907
...
I can get the portdate
attribute to columns using df.unstack(level=0)
but I’m struggling to get the recontype
attributes where I want them.
df.unstack(level=1).T
keeps the portdate
attribute as columns and the .T
transposes the balancetype
attribute to rows but they are at level 0
and there is a row for each recontype
attribute. I’d like to flip that so that recontype
is at level 0
and ‘balancetypeis at
level 1`.