CODEWITHSUNDEEP
pythonpandas
Yank Leo
Yank Leo

Reputation: 582

how to join two dataframes according to third dataframe in pandas?

I have two dataframes of the form: Table 1

sid   student_details
1      A
2      B
3      C

Table 2

did   dept_details
    4      E
    5      F
    6      G

Table 3:

sid    did
1    5
3    4
2    6

Now how to join dataframe1 and dataframe2 according to dataframe3 in pandas?

Upvotes: 2

Views: 241

Answers (2)

jezrael
jezrael

Reputation: 863611

You can use double merge:

print df1
     sid student_details
0    1               A
1    2               B
2    3               C

print df2
   did dept_details
0    4            E
1    5            F
2    6            G

print df3
   sid  did
0    1    5
1    3    4
2    2    6

print pd.merge(pd.merge(df1, df3, on=['sid']), df2, on=['did'])

   sid student_details  did dept_details
0    1               A    5            F
1    2               B    6            G
2    3               C    4            E

Or if you have set indexes:

print df1
    student_details
sid                
1                 A
2                 B
3                 C

print df2
    dept_details
did             
4              E
5              F
6              G

print df3
     did
sid     
1      5
3      4
2      6

print pd.merge(pd.merge(df1, df3, left_index=True, right_index=True).set_index('did'),
                        df2, left_index=True, right_index=True)

    student_details dept_details
did                             
4                 C            E
5                 A            F
6                 B            G

Upvotes: 0

George Petrov
George Petrov

Reputation: 2849

The idea is to join your df3 to the first one.

df = df1.set_index('sid').join(df3.set_index('sid')).reset_index()
df = df.set_index('did').join(df2.set_index('did')).reset_index().drop('did', 1)

Upvotes: 2

Related Questions