[Python] Merging Pandas dataframes on column combinations

Scenario: I am trying to merge 2 pandas dataframes. DF1 has the bulk data, and DF2 is a sort of mapping. Based on the combination of the values of 3 different columns, I want to put a column from DF2 into DF1.

Data sample (just a snippet of both dfs): DF1:

+-------+---------------------------------------------+---------------------------------------------+------+----------------+----------------+
| AGENT | 5283 | 5288 | 5318 | 7934 | 7935 |
+-------+---------------------------------------------+---------------------------------------------+------+----------------+----------------+
| 33 | No | No | No | No | No |
| 34 | Yes, formal discussion | General reference | No | No | No |
| 55 | No | No | No | No | No |
| 129 | No | No | No | No | No |
| 307 | No | No | No | No | No |
| 441 | Yes, formal discussion | Formal board consideration | No | No | No |
| 522 | Yes, specific reference but limited details | Formal board commtment with limited details | No | Not Meaningful | Not Meaningful |
| 690 | No | No | No | Not Meaningful | Not Meaningful |
| 749 | Yes, formal discussion | General reference | No | No | No |
| 1011 | No | No | No | No | No |
| 1067 | Yes, formal discussion | Formal board consideration | No | Not Meaningful | Not Meaningful |
| 1272 | No | No | No | Not Meaningful | Not Meaningful |
| 1592 | No | No | No | Not Meaningful | Not Meaningful |
| 1908 | Yes, formal discussion | Formal board commtment with limited details | No | No | No |
| 1949 | No | No | No | Not Meaningful | Not Meaningful |
| 2040 | Yes, formal discussion | Formal board consideration | No | No | No |
| 2102 | Yes, formal discussion | Formal board consideration | No | No | No |
| 2114 | Yes, formal discussion | Formal board consideration | No | Not Meaningful | Not Meaningful |
| 2266 | Yes, formal discussion | Formal board commtment with limited details | No | No | No |
| 2365 | Yes, formal discussion | Formal board consideration | No | No | No |
| 2674 | Yes, formal discussion | Formal board consideration | No | No | No |
| 3109 | No | General reference | No | No | No |
| 3170 | Yes, specific reference but limited details | Formal board commtment with limited details | No | Not Meaningful | Not Meaningful |
| 3295 | Yes, specific reference but limited details | Formal board commtment with limited details | No | No | No |
| 3323 | General reference | General reference | No | Not Meaningful | Not Meaningful |
| 3366 | Yes, specific reference but limited details | Formal board consideration | No | No | No |
| 3840 | General reference | Formal board commtment with limited details | No | No | No |
| 3914 | Yes, specific reference but limited details | Formal board commtment with limited details | No | No | No |
| 3967 | Yes, formal discussion | Formal board consideration | No | Yes | No |
| 4108 | No | No | No | No | No |
+-------+---------------------------------------------+---------------------------------------------+------+----------------+----------------+


DF2:

+---------------------------------------------+---------------------------------------------+------+-------+
| 5283 | 5288 | 5318 | SCORE |
+---------------------------------------------+---------------------------------------------+------+-------+
| Yes, formal discussion | Formal board consideration | Yes | 10 |
| Yes, formal discussion | Formal board consideration | No | 8 |
| Yes, formal discussion | Formal board commtment with limited details | Yes | 7 |
| Yes, formal discussion | Formal board commtment with limited details | No | 6 |
| Yes, formal discussion | General reference | Yes | 6 |
| Yes, formal discussion | General reference | No | 5 |
| Yes, formal discussion | No specific reference | Yes | 4 |
| Yes, formal discussion | No specific reference | No | 2 |
| Yes, specific reference but limited details | Formal board consideration | Yes | 8 |
| Yes, specific reference but limited details | Formal board consideration | No | 7 |
| Yes, specific reference but limited details | Formal board commtment with limited details | Yes | 6 |
| Yes, specific reference but limited details | Formal board commtment with limited details | No | 5 |
| Yes, specific reference but limited details | General reference | Yes | 5 |
| Yes, specific reference but limited details | General reference | No | 5 |
| Yes, specific reference but limited details | No specific reference | Yes | 3 |
| Yes, specific reference but limited details | No specific reference | No | 2 |
| General reference | Formal board consideration | Yes | 7 |
| General reference | Formal board consideration | No | 6 |
| General reference | Formal board commtment with limited details | Yes | 4 |
| General reference | Formal board commtment with limited details | No | 4 |
| General reference | General reference | Yes | 4 |
| General reference | General reference | No | 3 |
| General reference | No specific reference | Yes | 3 |
| General reference | No specific reference | No | 1 |
| No reference | Formal board consideration | Yes | 6 |
+---------------------------------------------+---------------------------------------------+------+-------+


Objective: Using columns 5283, 5288 and 5318, I need to add the Score value to DF1 as a new column.

What I tried: I tried adding a new combined column to both dfs and use it as a merge index, but still, the Score column values in DF always end up as nan:

Gov_JT_pivot['merge_key'] = tuple(zip(Gov_JT_pivot['5283'], Gov_JT_pivot['5288'], Gov_JT_pivot['5318']))
Gov_lookup_df['merge_key'] = tuple(zip(Gov_lookup_df['5283'], Gov_lookup_df['5288'], Gov_lookup_df['5318']))

# Perform the merge using the combined key
Gov_JT_pivot= pd.merge(Gov_JT_pivot, Gov_lookup_df[['merge_key', 'SCORE']], on='merge_key', how='left')


Which results in:

+-------+---------------------------------------------+---------------------------------------------+------+------------------------------------------------------------------------------------------------------+-------+
| AGENT | 5283 | 5288 | 5318 | merge_key | SCORE |
+-------+---------------------------------------------+---------------------------------------------+------+------------------------------------------------------------------------------------------------------+-------+
| 33 | No | No | No | ('No', 'No', 'No') | |
| 34 | Yes, formal discussion | General reference | No | ('Yes, formal discussion', 'General reference', 'No') | |
| 55 | No | No | No | ('No', 'No', 'No') | |
| 129 | No | No | No | ('No', 'No', 'No') | |
| 307 | No | No | No | ('No', 'No', 'No') | |
| 441 | Yes, formal discussion | Formal board consideration | No | ('Yes, formal discussion', 'Formal board consideration', 'No') | |
| 522 | Yes, specific reference but limited details | Formal board commtment with limited details | No | ('Yes, specific reference but limited details', 'Formal board commtment with limited details', 'No') | |
| 690 | No | No | No | ('No', 'No', 'No') | |
| 749 | Yes, formal discussion | General reference | No | ('Yes, formal discussion', 'General reference', 'No') | |
| 1011 | No | No | No | ('No', 'No', 'No') | |
| 1067 | Yes, formal discussion | Formal board consideration | No | ('Yes, formal discussion', 'Formal board consideration', 'No') | |
| 1272 | No | No | No | ('No', 'No', 'No') | |
| 1592 | No | No | No | ('No', 'No', 'No') | |
| 1908 | Yes, formal discussion | Formal board commtment with limited details | No | ('Yes, formal discussion', 'Formal board commtment with limited details', 'No') | |
| 1949 | No | No | No | ('No', 'No', 'No') | |
| 2040 | Yes, formal discussion | Formal board consideration | No | ('Yes, formal discussion', 'Formal board consideration', 'No') | |
| 2102 | Yes, formal discussion | Formal board consideration | No | ('Yes, formal discussion', 'Formal board consideration', 'No') | |
| 2114 | Yes, formal discussion | Formal board consideration | No | ('Yes, formal discussion', 'Formal board consideration', 'No') | |
| 2266 | Yes, formal discussion | Formal board commtment with limited details | No | ('Yes, formal discussion', 'Formal board commtment with limited details', 'No') | |
| 2365 | Yes, formal discussion | Formal board consideration | No | ('Yes, formal discussion', 'Formal board consideration', 'No') | |
| 2674 | Yes, formal discussion | Formal board consideration | No | ('Yes, formal discussion', 'Formal board consideration', 'No') | |
| 3109 | No | General reference | No | ('No', 'General reference', 'No') | |
| 3170 | Yes, specific reference but limited details | Formal board commtment with limited details | No | ('Yes, specific reference but limited details', 'Formal board commtment with limited details', 'No') | |
| 3295 | Yes, specific reference but limited details | Formal board commtment with limited details | No | ('Yes, specific reference but limited details', 'Formal board commtment with limited details', 'No') | |
| 3323 | General reference | General reference | No | ('General reference', 'General reference', 'No') | |
| 3366 | Yes, specific reference but limited details | Formal board consideration | No | ('Yes, specific reference but limited details', 'Formal board consideration', 'No') | |
| 3840 | General reference | Formal board commtment with limited details | No | ('General reference', 'Formal board commtment with limited details', 'No') | |
| 3914 | Yes, specific reference but limited details | Formal board commtment with limited details | No | ('Yes, specific reference but limited details', 'Formal board commtment with limited details', 'No') | |
| 3967 | Yes, formal discussion | Formal board consideration | No | ('Yes, formal discussion', 'Formal board consideration', 'No') | |
| 4108 | No | No | No | ('No', 'No', 'No') | |
| 4525 | Yes, specific reference but limited details | Formal board commtment with limited details | No | ('Yes, specific reference but limited details', 'Formal board commtment with limited details', 'No') | |
| 4528 | General reference | No | No | ('General reference', 'No', 'No') | |
| 4608 | Yes, formal discussion | Formal board consideration | No | ('Yes, formal discussion', 'Formal board consideration', 'No') | |
| 4641 | No | No | No | ('No', 'No', 'No') | |
| 4650 | No | Formal board consideration | No | ('No', 'Formal board consideration', 'No') | |
+-------+---------------------------------------------+---------------------------------------------+------+------------------------------------------------------------------------------------------------------+-------+


Question: What am I doing incorrectly and how to fix it?

Continue reading...