CODEWITHSUNDEEP
pythonpandasdataframereplace
MaMo
MaMo

Reputation: 585

Pandas - replace values of a column if other values from same row appear in second data frame

Input are two dataframes. Below are short versions of them with only a few rows.

df1
+-----+------+------+
| No  | Type | Desc |
+-----+------+------+
| 123 | A    | Bla  |
| 123 | B    | Bla  |
| 123 | D    | Bla  |
| 342 | A    | Bla  |
| 342 | C    | Bla  |
| 543 | B    | Bla  |
| 543 | C    | Bla  |
+-----+------+------+

df2
+-----+------+------+
| No  | Type | Desc |
+-----+------+------+
| 123 | A    | Lala |
| 342 | A    | Lala |
| 342 | C    | Lala |
+-----+------+------+

Both data frames have more than the columns above, but the others do not matter in this case.

I would like to change values of column Desc to Done for rows of df1 in case this row (meaning No and Type) also appear in df2.

df1
+-----+------+------+
| No  | Type | Desc |
+-----+------+------+
| 123 | A    | Done |
| 123 | B    | Bla  |
| 123 | D    | Bla  |
| 342 | A    | Done |
| 342 | C    | Done |
| 543 | B    | Bla  |
| 543 | C    | Bla  |
+-----+------+------+

Thank you :)

Upvotes: 0

Views: 169

Answers (2)

harpan
harpan

Reputation: 8631

You can find the rows of df1, that are present in df2 with a left merge and then change the Desc to Done.

mer = df1.merge(df2, on=['No', 'Type'], how='left')
mer.loc[mer['Desc_y'].notnull(), 'Desc_x'] = 'Done'
df1['Desc'] = mer['Desc_x']

Output:

    No  Type Desc
0   123 A   Done
1   123 B   Bla
2   123 D   Bla
3   342 A   Done
4   342 C   Done
5   543 B   Bla
6   543 C   Bla

Upvotes: 0

jezrael
jezrael

Reputation: 862611

Use merge with numpy.where:

df3 = df1[['No','Type']].merge(df2, on=['No','Type'], how='left')
df3['Desc'] = np.where(df3['Desc'].notnull(), 'Done', df1['Desc'])
print (df3)
    No Type  Desc
0  123    A  Done
1  123    B   Bla
2  123    D   Bla
3  342    A  Done
4  342    C  Done
5  543    B   Bla
6  543    C   Bla

Upvotes: 3

Related Questions