I have a dataframe that has information about employee's employment info and I am trying to combine with another dataframe that has their Employee ID #. df Name SSN Doe, John A XXXX-XX-1234 Doe, Jane B XXXX-XX-9876 Test, Example XXXX-XX-0192 Employee_Info First_Name Last_Name SSN EmployeeID John Doe 999-45-1234 12 JANE DOE 999-45-9876 13 Example Test 999-45-0192 14 My desired output is: Name SSN EmployeeID Doe, John A XXX-XX-1234 12 Doe, Jane B XXX-XX-9876 13 Test, Example XXX-XX-0192 14 The df dataframe actually has the SSN masked except for the last 4 characters. Here is the code I have currently: df['SSN_Last_4'] = df['SSN4'].str[-4:] Employee_Info['SSN_Last_4'] = Employee_Info['SSN'].str[-4:] df2 = pd.merge(df, Employee_Info, on='SSN', how='left') However because some employees might have the same last 4 digits of SSN, I need to also match based on name. However the caveat is that the Name in df is the employee fullname (which might include middle initial) and the case might be different. My original idea was to split the Name on , and drop middle initial, and then convert all the name columns to be lowercase and modify the join. However I feel that there are better methods to join the data. Continue reading...
