Change from .apply() to a function that uses list comprehension to compare one dataframe with a column of lists to values in another dataframe

Let's try, using explode and regex with extract:

d1e = d1['B'].explode()
regstr = '('+'|'.join(sorted(d1e)[::-1])+')'
d2['B'] = d2['C'].astype('str').str.extract(regstr)

Output:

         C     B
0  8420513  8420
1  8421513  8421
2  8426513  8426
3  8427513    84
4  8470513  8470
5  8470000  8470
6  8475000  8475

Since, .str access is slower than list comprehension

import re
regstr = '|'.join(sorted(d1e)[::-1])
d2['B'] = [re.match(regstr, i).group() for i in d2['C'].astype('str')]

Timings:

from timeit import timeit
import re

d1 = pd.DataFrame({'A' : ['A', 'B', 'C', 'D'], 'B' : [['84'], ['8420', '8421', '8422', '8423', '8424', '8425', '8426'], ['847', '8475'], ['8470', '8471']]})
d2 = pd.DataFrame({'C' : [8420513, 8421513, 8426513, 8427513, 8470513, 8470000, 8475000]})
d2['C'] = d2['C'].astype(str)


def orig(d):
    d['B'] = d['C'].apply(lambda x: [z for y in d1['B'] for z in y if x.startswith(z)])
    d['B'] = d['B'].apply(max)
    return d
    
def comtorecords(d):
    d['B']=[max([z for y in d1.B for z in y if str(row[1]) .startswith(z)]) for row in d.to_records()]
    return d

def regxstracc(d):
    d1e = d1['B'].explode()
    regstr = '('+'|'.join(sorted(d1e)[::-1])+')'
    d['B'] = d['C'].astype('str').str.extract(regstr)
    return d

def regxcompre(d):
    regstr = '|'.join(sorted(d1e)[::-1])
    d['B'] = [re.match(regstr, i).group() for i in d['C'].astype('str')]
    return d


res = pd.DataFrame(
    index=[10, 30, 100, 300, 1000, 3000, 10000, 30000],
    columns='orig comtorecords regxstracc regxcompre'.split(),
    dtype=float
)

for i in res.index:
    d = pd.concat([d2]*i)
    for j in res.columns:
        stmt = '{}(d)'.format(j)
        setp = 'from __main__ import d, {}'.format(j)
        print(stmt, d.shape)
        res.at[i, j] = timeit(stmt, setp, number=100)

# res.groupby(res.columns.str[4:-1], axis=1).plot(loglog=True);
res.plot(loglog=True);

Output:

I have found yet faster solution, compared to both propositions of Scott.

def vect(d):
    def extr(txt):
        mtch = pat.match(txt)
        return mtch.group() if mtch else ''
    d1e = d1.B.explode()
    pat = re.compile('|'.join(sorted(d1e)[::-1]))
    d['B'] = np.vectorize(extr)(d.C)
    return d

One speed gain is from prior compilation of the regex.

The second gain is due to use of a Numpy vectorization, instead of a list comprehension.

Running a testing loop like the one employed by Scott, I received the following result:

So "my" execution time (the red line), especially for larger data volumes, is about 60 % of both regxstracc and regxcompre.

Try explode the d1 so we reduce one for loop

[max([z for z in d1.B.explode() if  x.startswith(z)]) for x in d2.C.astype(str) ]

['8420', '8421', '8426', '84', '8470', '8470', '8475']

Using re.compile() to compile the pattern prior to execution (i.e. outside of the ‘loop’) helps a lot when solving these big data row-by-row ‘requires a custom solution that isn’t a NumPy vector function already‘ problems.

Using the correct flags when compiling a pattern is work checking too.

Not to sound like a broken record but with NLP/text processing it’s definitely significant.

Another suggestion: Also use efficient chunking.

Each computing project has some X memory threshold and some Y processing threshold; balancing these by chunking helps improve execution speed.

Pandas chunking with its read/write functions are useful from my experience.

Finally, if you are executing I/O operations you might squeeze out some extra performance mapping the function within a ThreadPoolExecutor (A quick google can give you some good template code, and the documentation explains the utility well; I found the template code from documentation hard to implement at first so I’d check other resources).

Best of luck!