Pandas fill empty values on one dataframe from another dataframe-CodePudding

This is something that I would be able to replicate easily in Excel with an XLOOKUP function, but I'm trying to do it with pandas.

I have 2 dataframes, say something like this:

df1

|first_name | last_name | dob      | value |
| Goku      | Saiyan    | 1/1/2021 |       |
| Vegetta   |  Super    | 8/7/1990 |       |
| Gohan     |  Son      | 4/20/1969|       |

df2

|first_name | last_name | dob      | value |
| Goku      | Saiyan    | 1/1/2021 |   50  |
| Vegetta   |  Super    | 8/7/1990 |   92  |
| Gohan     |  Son      | 4/20/1969|   31  |
| Trunks    |  Donald   | 7/1/1921 |   49  |
| New Name  |  Another  | 1/31/1992|   67  |

I would like to fill the value column in df1 from the value column in df2.

I cannot use combine_first because the dataframes have different index and different sizes.

If I use pd.merge then I get the value_x and value_y where value_y has the data that I want, but I need to do more process to have it where I want on df1['value']

I basically want to match the first name, last name and dob on both dataframes and receive the value from df2.

It's probably a simple issue, but I have been struggling with the different methods that I've tried and I think there must be something that I'm missing because it shouldn't be that complicated.

Any help will be really appreciated.

CodePudding user response：

If your value column from df1 does not contain existing value, you can drop it and use merge:

>>> pd.merge(df1.drop(columns='value'), df2, how='left',
             on=['first_name', 'last_name', 'dob'])

  first_name last_name        dob  value
0       Goku    Saiyan   1/1/2021     50
1    Vegetta     Super   8/7/1990     92
2      Gohan       Son  4/20/1969     31

CodePudding user response：

Use map.

Create a dict of firts_name; value from df2 and map to df1's first_name.

df1 =df1.assign(value=df1['first_name'].map(dict(zip(df2['first_name'],df2['value']))))



   first_name last_name        dob  value
0       Goku    Saiyan   1/1/2021     50
1    Vegetta     Super   8/7/1990     92
2      Gohan       Son  4/20/1969     31