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.

Hello and welcome to Stack Overflow. I would first suggest to read https://stackoverflow.com/help/how-to-ask — ZombieChowder, Jul 25 '17 at 15:15

score 15 · Accepted Answer · answered Jul 25 '17 at 15:20

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

Appreciate the fact that you somehow converted those pictures to code. +1 — cs95, Jul 25 '17 at 15:25

Join/Merge two Pandas dataframes and use columns as multiindex

1 Answers1