Compare two df's and populate value from one df to another

Question

I would like to compare the amount_spent column in df1 and df2 and if the amount_spent column in df1 is null (null is a string not nan) then populate the value from df2 to df1 for that particular customer_id.

df1

customer_id  amount_spent 
3021         144
0535         042
7532         null 
2131         932

df2

3021         144
0535         042
7532         945 

Desired output df

3021         144
0535         042
7532         945

Answer 1

Try this:

import pandas as pd

df1 = pd.DataFrame({"customer_id": ['3021', '0535', '7532'], "amount_spent": ['144', '042', 'null']})
df2 = pd.DataFrame({"customer_id": ['3021', '0535', '7532'], "amount_spent": ['144', '042', '945']})

null_list = df1.index[df1['amount_spent'] == 'null'].tolist()

for null in null_list:
    df1["amount_spent"][int(null)] = df2["amount_spent"][int(null)]

It creates a list with all indices that underly the condition and populates the right value.

Answer 2

You can try:

import pandas as pd

df1 = pd.DataFrame({"customer_id": ['3021', '0535', '7532'], "amount_spent": ['144', '042', 'null']})
df2 = pd.DataFrame({"customer_id": ['3021', '0535', '7532'], "amount_spent": ['144', '042', '945']})


df1 = df1.set_index("customer_id")
df2 = df2.set_index("customer_id")
df1.loc[df1['amount_spent'] == "null", 'amount_spent'] = df2['amount_spent']
df1 = df1.reset_index()
print(df1)

It gives:

  customer_id amount_spent
0        3021          144
1        0535          042
2        7532          945