I have a Pandas dataframe (donations_df) that contains thousands of donations. Each donation row has many columns, but the two key ones for my question are:

  • A recipient_id column indicating who received the donation
  • An office column indicating what legislative office the recipient holds.

Some of the values of the office column in donations_df are outdated, and I want to replace/override them.

So I have a second, much smaller dataframe (overrides_df). It contains the preferred office names I want to use. It has only a few rows (one for each recipient). For example:

| recipient_id | office             | 
| ------------ | ------------------ |
| 3839314      | Governor           |
| 24811446     | Secretary of State |
| 12733609     | Auditor            |
| 6676482      | Attorney General   |

What is the proper way to join/merge/update this?

What I want to happen is that for each donation in donations_df, if that donation's recipient_id exists in overrides_df, then then the donation would pick up the new office value from overrides_df

What is the proper way to join/merge/update these two to achieve that result?


Try this:

for recipient_id, office in override_df.values:
    donations_df.loc[donations_df["recipient_id"] == recipient_id, "office"] = office

However, you should use a reference table to avoid this problem!


Not the answer you're looking for? Browse other questions tagged or ask your own question.