Merge two dataframes based on partial startswith match

user_5425172 0
Viewed 747

I have two dataframe that I want to merge based on a partial match of start with (rows in df2.B that start with df1.A in the example below and the values of these columns are strings, which could be of any length).

I can do that the following way, but it is very slow on my actual dataframes, which have millions of rows each.

df1 = pd.DataFrame({'A': ['a', 'b', 'cc']})

df2 = pd.DataFrame({'B': ['ar', 'd', 'ar'],
                    'C': ['x1', 'x1', 'x2']})

df_m = pd.DataFrame(columns=['A','B','C'])
for index, row in df1.iterrows():
    df_ = df2[df2['B'].str.startswith(row['A'])]
    if not df_.empty:
        df_['A'] = row['A']
        df_m = df_m.append(df_)

df_m:

    A   B   C
0   a   ar  x1
2   a   ar  x2
pandas merge join python string
3 Answers

Use, Series.str.extract to extract the key from the column B in df2 which starts with column A in df1, then use DataFrame.merge to merge the datframe df1 on column A and df2 on column key:

key = df2['B'].str.extract('^(' + '|'.join(df1['A']) + ')')
df3 = df1.merge(df2.assign(key=key), left_on='A', right_on='key').drop('key', 1)

Result:

df3
   A   B   C
0  a  ar  x1
1  a  ar  x2
user_128331660

We can do findall with regex

reg='^('+'|'.join(df1.A.tolist())+')'
df2['A']=df2.B.str.findall(reg).str[0]
df2
Out[60]: 
    B   C  A
0  ar  x1  a
1  ba  x1  b
2  ar  x2  a
user_79645270

If you want to use df.merge, you can do this:

df2[['l1','l2']] = pd.DataFrame(df2.B.apply(list).tolist(),index= df2.index)     
df_m = df1.merge(df2, left_on='A', right_on='l1').drop(['l1', 'l2'], 1)

Output:

In [70]: df_m 
Out[70]: 
   A   B   C
0  a  ar  x1
1  a  ar  x2
user_58208140
Related