I have 2 dataframes, the [Trade] column in first dataframe has 3 NaN values, I need to fill them with a referenc dataframe, which you might see below this the first df. You can use various methods to populate NaN value. Join, merge, concat, replace, whatever that works easily. thank you

    Trade        ID     Toy_company
0   D255/FE     192     Duplo
1   L217/SN     255     Duplo
2   NaN         195     Lego
3   NaN         256     Duplo
4   D255/FE     192     Duplo
5   D255/FE     192     Duplo
6   D250/AT     192     Duplo
7   D250/AT     192     Duplo
8   L195/AE     195     Lego
9   NaN         195     Lego
10  L256/PE     256     Lego

reference dataframe:

   Trade_ID     Item    Company
0   D255/FE     192     Duplo
1   D217/SN     255     Duplo
2   L12A/BA     195     Lego
3   L999/CC     256     Lego

Requirement: 1. Only fill NaN value according to the info in reference dataframe, on TWO keys: Item+Company. Leave it NaN if can't be referenced. 2. Do not correct any exsiting info, only impact on NaN values

So the final result should be:

        Trade        ID     Toy_company
    0   D255/FE     192     Duplo
    1   L217/SN     255     Duplo
    2   L12A/BA     195     Lego
    3   NaN         256     Duplo
    4   D255/FE     192     Duplo
    5   D255/FE     192     Duplo
    6   D250/AT     192     Duplo
    7   D250/AT     192     Duplo
    8   L195/AE     195     Lego
    9   L12A/BA     195     Lego
    10  L256/PE     256     Lego

score:2

Accepted answer

you can use merge() method:

In [347]: d1['Trade'] = (d1['Trade'].fillna(
                             d1.merge(d2.rename(columns={'Item':'ID','Company':'Toy_company'}),
                                      how='left')['Trade_ID']))

In [348]: d1
Out[348]:
      Trade   ID Toy_company
0   D255/FE  192       Duplo
1   L217/SN  255       Duplo
2   L12A/BA  195        Lego
3       NaN  256       Duplo
4   D255/FE  192       Duplo
5   D255/FE  192       Duplo
6   D250/AT  192       Duplo
7   D250/AT  192       Duplo
8   L195/AE  195        Lego
9   L12A/BA  195        Lego
10  L256/PE  256        Lego

Related Query