Conditionally merge and overwrite pandas DataFrames according to column values-CodePudding

I have two pandas DataFrames:

df1: 
ID count
a 20
b 3

and

df2
ID Info count
a None 1
b 2 2

I would like to merge df2 on df1 such that the values of count in df2 gets overwritten with df1['count'] only if df2 has matching ID but "None" in "Info.

--> Output should look like:

ID Info count
a None 20
b 2 2

I have tried to work with the merges or groupby's, but I can't figure out how to conditionally overwrite data?...

Thanks!

CodePudding user response：

You can merge first then update values:

df3 = df2.merge(df1, on='ID', how='left', suffixes=('', '1'))
print(df3)

# Intermediate output
  ID  Info  count  count1
0  a  None      1      20
1  b     2      2       3

df3 = df3.assign(count=np.where(df3['Info'] == 'None', df3['count1'], df3['count'])) \
         .drop(columns='count1')
print(df3)

# Final output
  ID  Info  count
0  a  None     20
1  b     2      2