Search and update values in other dataframe for specific columns

Thosifer MD 0
Viewed 27

I have two different dataframe in pandas.

First

A B C D VALUE
1 2 3 5 0
1 5 3 2 0
2 5 3 2 0

Second

A B C D Value
5 3 3 2 1
1 5 4 3 1

I want column values A and B in the first dataframe to be searched in the second dataframe. If A and B values match then update the Value column.Search only 2 columns in other dataframe and update only 1 column. Actually the process we know in sql.

Result

A B C D VALUE
1 2 3 5 0
1 5 3 2 1
2 5 3 2 0

If you focus on the bold text, you can understand it more easily.Despite my attempts, I could not succeed. I only want 1 column to change but it also changes A and B. I only want the Value column of matches to change.

dataframe pandas python
1 Answers

You can use a merge:

cols = ['A', 'B']
df1['VALUE'] = (df2.merge(df1[cols], on=cols, how='right')
                ['Value'].fillna(df1['VALUE'], downcast='infer')
               )

output:

   A  B  C  D  VALUE
0  1  2  3  5      0
1  1  5  3  2      1
2  2  5  3  2      0
mozway0
Related