CODEWITHSUNDEEP
pythonpandas
Adam
Adam

Reputation: 1295

Combining multiple dataframes side by side and renaming them

I have multiple data frames in my analysis. For example dataframe 1 where this is the number of people by activity in China

Activity            No of people
Activity 1              100
Activity 2              200 
Activity 3              300

and data frame 2 where this is the number of people by activity in Amercia

Activity            No of people
Activity 1              400
Activity 2              500 
Activity 3              500

What I am trying to achieve is this:

              ------China Analysis------America Analysis
Activity            No of people           No of people  
Activity 1              100                      400
Activity 2              200                      500
Activity 3              300                      500

I thought of doing a merge left on these 2 data frame but how am i gg to do the header? Any suggestions?

Upvotes: 2

Views: 5786

Answers (2)

DavidK
DavidK

Reputation: 2564

A way to do it :

table1 = pd.DataFrame({'Activity':['Activity 1', 'Activity 2', 'Activity 3'], 'No of people':[100,200,300]}).rename(columns={'No of people':('No of people', 'France')})
table2 = pd.DataFrame({'Activity':['Activity 1', 'Activity 2', 'Activity 3'], 'No of people':[101,201,500]}).rename(columns={'No of people':('No of people', 'America')})
table3 = pd.DataFrame({'Activity':['Activity 1', 'Activity 2', 'Activity 3'], 'No of people':[566,22,38]}).rename(columns={'No of people':('No of people', 'Espana')})

merged = table1.merge(table2, on='Activity').merge(table3, on='Activity')
merged = merged.set_index('Activity')

merged.columns = pd.MultiIndex.from_tuples(merged.columns)

print(merged)

It outputs :

           No of people               
                 France America Espana
Activity                              
Activity 1          100     101    566
Activity 2          200     201     22
Activity 3          300     500     38

Upvotes: 1

jezrael
jezrael

Reputation: 863301

Use concat.

If Activity are indexes in both dataframes use:

df = pd.concat([df1, df2], axis=1, keys=('China Analysis','America Analysis'))

If not, first set_index:

df = pd.concat([df1.set_index('Activity'), 
                df2.set_index('Activity')], 
                axis=1, 
                keys=('China Analysis','America Analysis'))
print (df)
           China Analysis America Analysis
             No of people     No of people
Activity                                  
Activity 1            100              400
Activity 2            200              500
Activity 3            300              500

Upvotes: 3

Related Questions