Assign df columns values based on another df column value

Question

I have 2 data frames df1 and df2

df1 = pd.DataFrame({'ID': [1, 2, 3, 5], 
                    'Name': ['client', 'detail_client', 'operations', audit],
                    'Type': ['str', 'var', 'str', 'nvar']})
df2 = pd.DataFrame({'ID': [5, 3, 7, 2], 
                    'Name': ['audit', 'operations', 'C', 'detail_client'],
                    'Type': ['nan', 'nan', 'nan', 'nan']})

I would like to create a function that takes as arguments df1, df2, df1['ID'], df2['ID'], df1['Name'], df2['Name'], df1['Type'] and df2['Type'] because columns label may not always identical.
For each row of df1. Iterate over df2. Compare df1['ID'] value with df2['ID'] and df1['Name'] value with df2['Name']. When true. Set df2['Type']=df1['Type']. The function should return df2 with df2['Type'] equal to df1['Type'] when the condition is true. I expect df2 to be like the following df:

df2 = pd.DataFrame({'ID': [5, 3, 7, 2], 
                    'Name': ['audit', 'operations', 'nan', 'detail_client'],
                    'Type': ['nvar', 'str', 'nan', 'var']})

Any help is welcomed. Thanks in advance.

Nick · Accepted Answer · 2023-11-28 23:32:43Z

1

You could use a function something like this, which borrows its method from this answer:

def update_columns(df1, df2, match_cols, merge_cols):
    res = df1.set_index(match_cols)
    res.update(df2.set_index(match_cols)[merge_cols])
    return res.reset_index()

df1 = pd.DataFrame({'ID': [1, 2, 3, 5], 
                    'Name': ['client', 'detail_client', 'operations', 'audit'],
                    'Type': ['str', 'var', 'str', 'nvar']})
df2 = pd.DataFrame({'ID': [5, 3, 7, 2], 
                    'Name': ['audit', 'operations', 'C', 'detail_client'],
                    'Type': ['nan', 'nan', 'nan', 'nan']})

out = update_columns(df2, df1, ['ID', 'Name'], ['Type'])

Output:

   ID           Name  Type
0   5          audit  nvar
1   3     operations   str
2   7              C   nan
3   2  detail_client   var

answered Nov 28, 2023 at 23:32

Nick

147k23 gold badges67 silver badges106 bronze badges

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

Comments

Andrej Kesely · Accepted Answer · 2023-11-28 23:36:06Z

1

You can merge the two dataframes on ID and Name:

merged = df2.merge(df1, on=["ID", "Name"], how="left")
mask = merged["Type_y"].notna()
df2.loc[mask, "Type"] = merged.loc[mask, "Type_y"]

print(df2)

Prints:

   ID           Name  Type
0   5          audit  nvar
1   3     operations   str
2   7              C   nan
3   2  detail_client   var

edited Nov 28, 2023 at 23:36

answered Nov 28, 2023 at 23:22

Andrej Kesely

196k15 gold badges60 silver badges105 bronze badges

Collectives™ on Stack Overflow

Assign df columns values based on another df column value

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related