How do I merge pandas df with multiple columns using one key from another column?

Question

I have a Dataframe with two columns A and B (df1)

And a Dataframe (df2) with a "dictionary" describing 1, 2 and 3

O   P   Q
1   s   a
2   s   b
3   t   b

Now I want to merge the first table with the second table such that I get the following:

A   B   P1   Q1   P2   Q2
1   2    s    a    s    b
1   3    s    a    t    b
2   3    s    b    t    b

I've tried df1.merge(df2, left_on=["A","B"], right_on=["O","O"])

yatu · Accepted Answer · 2019-06-26 12:30:26Z

2

You have two separate merging schemes here, so you'll have to call merge twice:

(df1.merge(df2, left_on="A", right_on="O")
    .merge(df2, left_on="B", right_on="O")
    .drop(columns=['O_x', 'O_y']))

   A  B P_x Q_x P_y Q_y
0  1  2   s   a   s   b
1  1  3   s   a   t   b
2  2  3   s   b   t   b

answered Jun 26, 2019 at 12:30

yatu

88.7k12 gold badges93 silver badges148 bronze badges

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

1 Comment

Dervin Thunk Over a year ago

Thanks. I thought maybe I was missing some generalization that would've allowed me to use the df2 as some sort of "dictionary" for df1. Much appreciated.

Collectives™ on Stack Overflow

How do I merge pandas df with multiple columns using one key from another column?

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