Multimatch join in pandas

userUD4N 0
Viewed 17

I am looking for joining two data frame on one column and if there is a multi match then append the results to another column.

First Dataframe

2nd data frame

Expected Output from the Dataframe

merge pandas
1 Answers

NB. using a different example as yours is not reproducible.

You can convert to str.lower, then explode and map the values to groupby.agg again as string:

mapper = df2.set_index('name')['ID'].astype(str)
df1['ID'] = (df1['name']
             .str.upper().str.split(',')
             .explode()
             .map(mapper)
             .groupby(level=0).agg(','.join)
            )

Or, with a list comprehension:

mapper = df2.set_index('name')['ID'].astype(str)
df1['ID'] = [','.join([mapper[x] for x in s.split(',') if x in mapper])
             for s in df1['name']]

output:

  name   ID
0    A    1
1    b    2
2  A,B  1,2
3  C,a  3,1
4    D    4

Used input:

# df1
  name
0    A
1    b
2  A,B
3  C,a
4    D

# df2
  name  ID
0    A   1
1    B   2
2    C   3
3    D   4
mozway0
Related