Appending a column in a pandas DataFrame based on a different DataFrame

Question

I have these two DataFrames:

df1

       A   B
    0  8   x
    1  3   y
    2  1   x
    3  2   z
    4  7   y

    and

df2    
       A   B
    0  x   hey
    1  y   there
    2  z   sir

I am trying to append df1['B'] based on df2['B'] so that my desired output would be:

df3

       A   B
    0  8   hey
    1  3   there
    2  1   hey
    3  2   sir
    4  7   there

Is there a built in method for doing this kind of transformation? I was experimenting with something similar to this, but I could not get it to function properly:

for x in df1['B']:
    if df2['A'] == x:
        df1['B'].append.df2['B']

jme · Accepted Answer · 2014-03-11 03:20:51Z

1

I believe you'll want a simple merge:

In [10]: df1.merge(df2, left_on='B', right_on='A')
Out[10]:
   A_x B_x A_y    B_y
0    8   x   x    hey
1    1   x   x    hey
2    3   y   y  there
3    7   y   y  there
4    2   z   z    sir

And if you'd like to keep the order of the index:

In [11]: df1.reset_index().merge(df2, left_on='B', right_on='A').set_index('index').sort_index()
Out[11]:
       A_x B_x A_y    B_y
index
0        8   x   x    hey
1        3   y   y  there
2        1   x   x    hey
3        2   z   z    sir
4        7   y   y  there

answered Mar 11, 2014 at 3:20

jme

20.8k6 gold badges44 silver badges40 bronze badges

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

Comments

behzad.nouri · Accepted Answer · 2014-03-11 10:40:48Z

1

you can set column A as an index to look-up values in column B:

>>> df1.B = df2.set_index('A').loc[df1.B,'B'].values
>>> df1
   A      B
0  8    hey
1  3  there
2  1    hey
3  2    sir
4  7  there

[5 rows x 2 columns]

answered Mar 11, 2014 at 10:40

behzad.nouri

78.5k18 gold badges130 silver badges127 bronze badges

Collectives™ on Stack Overflow

Appending a column in a pandas DataFrame based on a different DataFrame

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related