Join/Merge two Pandas dataframes and use columns as multiindex

Question

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 1

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