How to reformat this pandas multiindex dataframe?

Question

(sorry I don't know how to paste excel table here.)

Been messing around with pandas stack, pivot_table, melt, but can't seem to make it work. I need some ideas. Thanks.

`print(df.to_dict())` for the above sample dataframe and paste it in the question for users to replicate the dataframe into their systems — anky, Mar 19 '20 at 04:49
does this helps https://stackoverflow.com/questions/27576795/flatten-dataframe-with-multi-index-columns? — Pygirl, Mar 19 '20 at 05:38
just upload your excel file to somewhere and give us the link then. — Pygirl, Mar 19 '20 at 08:39

score 0 · Accepted Answer · answered Mar 19 '20 at 09:23

I created excel for the shown screenshot of the sheetfile

df = pd.read_excel('PythonExport.xlsx', 
                   header=[0,1,2],index_col=[0,1],
                 )

df:

x = df.T.swaplevel(0,1).T.stack().stack().reset_index()
x.columns = ['repeat','condition','day','type','length','weight']
x = x.fillna(0)

x:

1 Answers1