merge multiple data frames by multiple columns, Pandas, Python

Question

I would like to merge multiple data frames in the sequence of df3 - df2 - df1. df3 and df2 are merged first by df3(id, Feature 1) on df2(id, Feature 1). However then, df2(id, Feature 2) and df1(id, Feature 2) are merged together. The output would look like:

Here is my code:

import pandas as pd

data1 = {
    'id': ['1', '2', '3', '4', '5'],
    'Feature1': ['K', 'C', 'E', 'G', 'I'],
    'Feature2': ['L', 'N', 'F', 'H', 'J']}

df1 = pd.DataFrame(data1, columns = ['id', 'Feature1', 'Feature2'])

data2 = {
    'id': ['1', '2', '6', '7', '8'],
    'Feature1': ['K', 'M', 'O', 'Q', 'S'],
    'Feature2': ['L', 'N', 'P', 'R', 'T']}
df2 = pd.DataFrame(data2, columns = ['id', 'Feature1', 'Feature2'])

data3 = {
    'id': ['1', '2', '3', '4', '5', '7', '8', '9', '10', '11'],
    'Feature1': ['K', 'M', 'O', 'Q', 'S','X','Y','Z','W','P'],
    'Feature2': ['B', 'D', 'F', 'H', 'J','O', 'Q', 'S','X','Y'],
    'Feature3': [12, 13, 14, 'K', 'M','S', 'Q',15, 16, 17,]}


df3 = pd.DataFrame(data3, columns = ['id', 'Feature1', 'Feature2','Feature3'])

df1, df2, and df3 are shown in the figure above.

Could I ask how to do it? Thanks!

Mayank Porwal · Accepted Answer · 2020-07-13 16:36:48Z

Like this:

In [114]: df3.merge(df2, on=['id','Feature1'], how='left').merge(df1, left_on=['id','Feature2_y'], right_on=['id','Feature2'], how='left') 
Out[114]: 
   id Feature1_x Feature2_x Feature3 Feature2_y Feature1_y Feature2
0   1          K          B       12          L          K        L
1   2          M          D       13          N          C        N
2   3          O          F       14        NaN        NaN      NaN
3   4          Q          H        K        NaN        NaN      NaN
4   5          S          J        M        NaN        NaN      NaN
5   7          X          O        S        NaN        NaN      NaN
6   8          Y          Q        Q        NaN        NaN      NaN
7   9          Z          S       15        NaN        NaN      NaN
8  10          W          X       16        NaN        NaN      NaN
9  11          P          Y       17        NaN        NaN      NaN

Collectives™ on Stack Overflow

merge multiple data frames by multiple columns, Pandas, Python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related