Fill Dataframe Column With A Value If Multiple Columns Match Values In A Dictionary

I have two dataframes - one large dataframe with multiple categorical columns and one column with missing values, and another that's sort of a dictionary with the same categorical

Solution 1:

Does this work?

>>> df_1[['Color', 'Number', 'Letter']].merge(df_2, 
...                                           on=('Color', 'Number', 'Letter'),   
...                                           how='left')
   ColorNumberLetterValue0Red2B151Green2A222Red2B153Red1B44Green1A215Red2B156Green1B97Green2A22

Thought it worth mentioning - a very simple way to convert examples from stackoverflow pandas questions into a dataframe, just cut and paste it into a string like this:

>>>df_1 = pd.read_csv(StringIO("""...    Color      Number    Letter       Value...0   Red          2          B          NaN...1   Green        2          A          NaN...2   Red          2          B          NaN...3   Red          1          B          NaN...4   Green        1          A          NaN...5   Red          2          B          NaN...6   Green        1          B          NaN...7   Green        2          A          NaN..."""), sep=r'\s+')

Solution 2:

Try:

missing_df.reset_index()[['index', 'Color', 'Number', 'Letter']]\
          .merge(dict_df, on = ['Color', 'Number', 'Letter'])\
          .set_index('index').reindex(missing_df.index)

Output:

Color  Number Letter  Value
0    Red       2B151  Green       2A222    Red       2B153    Red       1B44  Green       1A215    Red       2B156  Green       1B97  Green       2A22

Solution 3:

I will be calling Missing value df as: df and Dictionary df as: ddf, considering both as dataframes

First drop the null values column from Missing value df:

df.drop(['Value'], axis=1)

Secondly run the below command, which should do the task for you.

df.assign(Value=ddf['Value'])