Search code examples
pythonpandaspivotmulti-indexmelt

Reshape a multi-index dataframe

arrays = [['indcator_1', 'indcator_1', 'indcator_2', 'indcator_2', 'indcator_3', 'indcator_3', 'indcator_4', 'indcator_4'], ['2018', '2019', '2018', '2019', '2018', '2019', '2018', '2019']]

tuples = list(zip(*arrays))
index = pd.MultiIndex.from_tuples(tuples, names=['indicators', 'state'])
df = pd.DataFrame(np.random.randn(3, 8), index=['AZ', 'CA', 'IL'], columns=index)

df
Out[5]: 
indicators indcator_1           indcator_2           indcator_3            \
state            2018      2019       2018      2019       2018      2019   
AZ           0.823890  0.165838  -1.207324  0.991570   0.424288 -0.697368   
CA           0.323375 -1.301540   1.245174 -0.542040  -0.305037 -0.148536   
IL           0.283575  0.095836   0.542553 -0.179618   1.989065  0.914899   

indicators indcator_4            
state            2018      2019  
AZ          -1.544907 -0.286541  
CA           1.168241  1.702455  
IL           0.524246  2.031239

I want to reshape it like:
this

Is there a way to do that? I could not find a solution for this specific case anywhere.

Sorry for the bad presentation of the question. I'm new here.

Thanks for the help!

Solution

  • Have you tried stack?

    df.stack()