Merge two dataframes on value in column of df1 in comma separated values in column of df2

user_3442457 0
Viewed 188

Input: two dataframes with the below data: df1: (note EmployeeID is a string of comma separated values)

| Employee Name | EmployeeID |
|---------------|------------|
| John          | 2, 22      |
| Kim           | 3          |

df2:

| EmployeeID | Hours |
|------------|-------|
| 2          | 8     |
| 3          | 10    |

I want to merge df1 and df2 on df2.EmployeeID in list of ID's in df1.EmployeeID.

Output:

| Employee Name | EmployeeID | Hours |
|---------------|------------|-------|
| John          | 2,22       | 8     |
| Kim           | 3          | 10    |
pandas dataframe merge python
1 Answers

If need match multiple values like EmployeeID = 2,3,22 to Hours=8+10 use mapping by dictionary in comprehension with splitand sum:

#converted to strings for match splitted values
df2['EmployeeID'] = df2['EmployeeID'].astype(str)
d = df2.set_index('EmployeeID')['Hours'].to_dict()

f = lambda x: sum(d[y] for y in x.split(', ') if y in d)
df1['Hours'] = df1['EmployeeID'].apply(f)
print (df1)
  Employee Name EmployeeID Hours
0          John      2, 22     8
1           Kim          3    10

Another idea with matching by integers:

d = df2.set_index('EmployeeID')['Hours'].to_dict()

f = lambda x: sum(d[int(y)] for y in x.split(', ') if int(y) in d)
df1['Hours'] = df1['EmployeeID'].apply(f)
user_29010020
Related