Replace values in column with column values from other dataframe in Python

Question

I have two dataframes, one with more columns (but two of them are important) and the second with two columns. df1:

col_a  col_b
101     104
102     201
103     301
505     601

df2:

col_a_a col_b_b
420      637
425      643
201      701
601      702

I need the next thing. Lookup at df1['col_b'] and if exist in df2['col_a_a'] then replace it with df2['col_b_b'].

I have tried two ways:

df1['col_b'] = np.where(df1['col_b'] == df2['col_a_a'], df2['col_b_b'], df1['col_b'])

df1.col_b[df1.col_b == df2.col_a_a] = df2.col_b_b

But both of them return me the same error: ValueError: Can only compare identically-labeled Series objects

Desired output:

col_a  col_b
    101     104
    102     701
    103     301
    505     702

jezrael · Accepted Answer · 2018-02-06 13:18:48Z

2

Use replace by Series created by set_index:

df1['col_b'] = df1['col_b'].replace(df2.set_index('col_a_a')['col_b_b'])
print (df1)
   col_a  col_b
0    101    104
1    102    701
2    103    301
3    505    702

answered Feb 6, 2018 at 13:18

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

Comments

jpp · Accepted Answer · 2018-02-06 13:40:51Z

1

Another way, using pd.Series.map:

df1['col_b'] = df1['col_b'].map(df2.set_index('col_a_a')['col_b_b']).fillna(df1['col_b'])

Depending on your data, this may be more efficient.

answered Feb 6, 2018 at 13:40

jpp

166k37 gold badges301 silver badges362 bronze badges

Collectives™ on Stack Overflow

Replace values in column with column values from other dataframe in Python

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