How to search a string in one pandas dataframe column as a substring in another dataframe

user_14447108 0
Viewed 117

I have two pandas dataframes df1 and df2. I need to create a new column in df1 by searching df2['B'] to see if df1['A'] is a substring of df2['B'] or vice versa. If there is a match return the value of df2['A'] for the new column in df1['B'].

Below are sample dataframes

df1

  A                     B     
  8GSHDK1               ?
  SDFAGHJFDJ GSHJGGFV
  678HJDGGH
  576GHJHJJKHJJH
  YRYWEUIYWRE

df2

 A                B
 1                GSHJGGFV
 2                XXXYYYYY
 3                8GSHDK1 TO BE DEL

Merge wouldn't work in this case since df1['A'] contains substring of df2['B'] Or df2['B'] contains substring of df1['A'].

I have tried below but it runs for 7 to 8 hours. df1 has 25k records and df2 has 720k records

df1['B']=df1['A'].apply(lambda x: df2[df2['B'].str.contains(x) | df2['B'].apply(lambda y : y in x)]['B'].any())

Any help will be very much appreciated. Expected output :

df1

  A                     B     
  8GSHDK1               8GSHDK1 TO BE DEL   
  SDFAGHJFDJ GSHJGGFV   GSHJGGFV
  678HJDGGH             None
  576GHJHJJKHJJH        None
  YRYWEUIYWRE           None
pandas python
3 Answers

The multiple apply statements can be avoided in your code and simplified to below. This should run faster.

df1['B'] = df1['A'].apply(lambda x: [y for y in df2['B'] if x.upper() in y.upper() or y.upper() in x.upper()]).str[0]

This prints:

                     A                  B
0              8GSHDK1  8GSHDK1 TO BE DEL
1  SDFAGHJFDJ GSHJGGFV           GSHJGGFV
2            678HJDGGH                NaN
3       576GHJHJJKHJJH                NaN
4          YRYWEUIYWRE                NaN
user_136288830

I tried using the LCS algorithm, my logic is:

If there are two strings A and B, one can be the substring of other:

iff, len(LCS(A, B))=min(len(A), len(B))

so rather than matching substring both way, I am doing it both way. Maybe you need to optimize the implementation a bit, but for sure it is faster than a both way search.

Code

%%time
from functools import lru_cache

@lru_cache(maxsize=2048)
def checkele(A, B):
    return ((len(B) >= len(A)) and (A in B)) or ((len(A) >= len(B)) and (B in A))

def check(A, Bs):
    for B in Bs:
        if checkele(*sorted([A, B])):
            return B
    return None
    
df1['B']=df1.A.apply( lambda x: check(x, df2.B))
df1
user_137178510

I think what you are looking for is pandas.concat.

df = pd.concat([df1['A'], df2['B']], axis=1)

Afterwards you can use apply, with a similar logic to what you wrote in your question.

user_144014470
Related