Join/Merge two Pandas dataframes and use columns as multiindex

I have two dataframes with KPIs by date. I want to combine them and use multi-index so that each KPI can be easily compared to the other for the two df.

Like this:

I have tried to extract each KPI into a series, rename the series accordingly (df1, df2), and then concatenating them using the keys argument of pd.concat but it doesn’t seem to work.

Any help would be greatly appreciated.

Answer

Let’s use pd.concat with keys parameter, swaplevel, and sort_index:

df1 = pd.DataFrame({'A':['A0','A1','A2'],'B':['B0','B1','B2'],'C':['C0','C1','C2']},index=pd.date_range('2017-01-01',periods=3, freq='M'))
df2 = pd.DataFrame({'A':['A3','A4','A5'],'B':['B3','B4','B5'],'C':['C3','C4','C5']},index=pd.date_range('2017-01-01',periods=3, freq='M'))

pd.concat([df1,df2],axis=1,keys=['df1','df2']).swaplevel(0,1,axis=1).sort_index(axis=1)

Output:

             A       B       C    
           df1 df2 df1 df2 df1 df2
2017-01-31  A0  A3  B0  B3  C0  C3
2017-02-28  A1  A4  B1  B4  C1  C4
2017-03-31  A2  A5  B2  B5  C2  C5