Dataframes - equivalent to JOIN with LIKE condition, or value in sublist

user_2052624 0
Viewed 34

I have 2 dataframes from 2 different sources.

System A

system_a_id designation
A10001 Catalog A1234
A10002 Catalog A1235

System B

system_b_id name other_ids
B20008 Thing_B20008 Yabbadabbadoo, Bender, Catalog A1234
B20009 Thing_B20009 Snark Snark, Catalog A1235, Leela

I would like to be able to join these together, into one row with all columns, based on 'designation' being found as a substring within 'other_ids'

In SQL this would be written simply as:

SELECT A.*, B.*
FROM A
LEFT JOIN B
  ON B.other_ids LIKE CONCAT('%', A.designation, '%')

Now, I imagine that there is either substring 'contains' search to use here, or I could break B.other_ids into its own list and try to do a apply function of some kind - but I'm struggling on syntax for either method, yet alone performance. (This is going to be a LOT of records joined - half a million)

pandas dataframe python
1 Answers

Looks like "designation" appears after comma in "other_ids". You could split "other_ids" on comma, take the second parts and assign it as "designation" column to systemB. Then merge it with systemA on "designation":

out = systemB.assign(designation=systemB['other_ids'].str.split(', ').str[-1]).merge(systemA, on='designation', how='left')

In general, you could use str.contains to identify the indices that match, rearrange then join:

idx = A['designation'].apply(lambda x: B.index[B['other_ids'].str.contains(x)][0])
out = B.loc[idx].join(A)

Output:

  system_b_id          name                             other_ids  \
0      B20008  Thing_B20008  Yabbadabbadoo, Bender, Catalog A1234   
1      B20009  Thing_B20009     Snark Snark, Leela, Catalog A1235   

     designation system_a_id  
0  Catalog A1234      A10001  
1  Catalog A1235      A10002
Admin1
Related