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 stackoverflow.com/help/how-to-ask — ZombieChowder
– ZombieChowder, Commented Jul 25, 2017 at 15:15

Scott Boston · Accepted Answer · 2017-07-25 15:20:36Z

17

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

answered Jul 25, 2017 at 15:20

Scott Boston

154k15 gold badges160 silver badges207 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

cs95 Over a year ago

Appreciate the fact that you somehow converted those pictures to code. +1

Collectives™ on Stack Overflow

Join/Merge two Pandas dataframes and use columns as multiindex

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related