Merging two data frames based on row values in python pandas

Question

I have two dataframes in pandas like the following:

df1:                                 df2:

      Column1  Column2  Column3           ColumnA  ColumnB ColumnC
    0    a        x        x            0    c        y       y
    1    c        x        x            1    e        z       z
    2    e        x        x            2    a        s       s
    3    d        x        x            3    d        f       f

What I want to do now is to compare Column1 with ColumnA and append the rows of df2 to the rows of df1 that have the same value in Column1 as df2 has in Column A, so that the result looks like this:

df1:
    Column1  Column2  Column3  ColumnB  ColumnC
  0    a        x        x        s        s
  1    c        x        x        y        y
  2    e        x        x        z        z
  3    d        x        x        f        f

I was thinking of using pandas .groupby() function and set the columns 1 and A as keys,compare them and then merge the grouped objects where the keys are identical, but I could not find an efficient way to compare the keys of grouped objects of 2 dataframes. Does anybody have a good idea how to do this?

Are you after df1.merge(df2,left_on='Column1', right_on='ColumnA')? — EdChum
– EdChum, Commented Oct 8, 2015 at 13:39

EdChum · Accepted Answer · 2015-10-08 15:22:48Z

5

You can specify which columns to merge for the lhs and rhs dfs:

In [159]:
df1.merge(df2, left_on='Column1', right_on='ColumnA')

Out[159]:
  Column1 Column2 Column3 ColumnA ColumnB ColumnC
0       a       x       x       a       s       s
1       c       x       x       c       y       y
2       e       x       x       e       z       z
3       d       x       x       d       f       f

answered Oct 8, 2015 at 15:22

EdChum

397k204 gold badges836 silver badges583 bronze badges

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

Collectives™ on Stack Overflow

Merging two data frames based on row values in python pandas

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related