Search code examples
pythonpandasdatabasedataframepandas-melt

Python - rearranging dataframe data

I wanna rearrange my dataframe from the left one to the right table, like I show you in the next picture:

enter image description here

df = pd.DataFrame({
"Unnamed:0": ["Entity","","Var1","Var2","Var3","Var4"],
"Unnamed:1": ["A","X","0.45","0.14","0.16","0.28"],
"Unnamed:2": ["A","Y","0.66","0.55","0.39","0.49"],
"Unnamed:3": ["A","Z","0.3","0.24","0.31","0.13"],
"Unnamed:4": ["B","X","0.22","0.08","0.74","0.41"],
"Unnamed:5": ["B","Y","0.94","0.47","0.17","0.16"],
"Unnamed:6": ["B","Z","0.76","0.4","0.93","0.15"],
"Unnamed:7": ["C","X","0.4","0.76","0.71","0.01"],
"Unnamed:8": ["C","Y","0.86","1","0.26","0.32"],
"Unnamed:9": ["C","Z","0.35","0.1","0.36","0.4"],
})

I try using pd.melt, but I can't get what I want. Ty in advance

Solution

  • As your dataframe is not clean (the 2 first rows are a multiindex column name), you can first create the inner dataframe before melting it :

    new_df = pd.DataFrame(df.iloc[2:,1:]).set_index(df.iloc[2:,0])
new_df.columns = pd.MultiIndex.from_frame(df.iloc[:2,1:].T)
new_df.melt(ignore_index=False).reset_index()