Pandas "Advanced" Merge on Substring

Question

I have two dataframes:

df1:

   locality
0  Chicago, IL
1  San Francisco, CA
2  Chic, TN

df2:

  City           County
0 San Francisco  San Francisco County
1 Chic           Dyer County
2 Chicago        Cook County

I want to find all values (or their corresponding indices) in locality that start with each value in City so that I can eventually merge the two dataframes. The answer to this post is close to what I want to achieve, but is greedy and only extracts one value -- I want all matches, even if somewhat incorrect

For example, I would like to know that "Chic" in df2 matches both "Chicago, IL" and "Chic, TN" in df1 (and/or that "Chicago, IL" matches both "Chic" and "Chicago")

So far, I've accomplished this by using pandas apply and a custom function:

def getMatches(row):
    matches = df1[df1['locality'].str.startswith(row['City'])]
    return matches.index.tolist(), matches['locality']

df2.apply(getMatches, axis=1)

0                ([1], [San Francisco, CA, USA])
1    ([0, 2], [Chicago, IL, USA, Chic, TN, USA])
2                      ([0], [Chicago, IL, USA])

This works fine until both df1 and df2 are large (100,000+ rows), where I run into time and memory issues (Even using Dask's parallelized apply). Is there a better alternative?

Pandas "Advanced" Merge on Substring

0 Answers