How to make pandas dataframe str.contains search faster

Question

I'm searching for a substring or multiple substrings in the dataframe of 4 million rows.

df[df.col.str.contains('Donald',case=True,na=False)]

or

df[df.col.str.contains('Donald|Trump|Dump',case=True,na=False)]

DataFrame(df) looks like below (with 4 million string rows)

df = pd.DataFrame({'col': ["very definition of the American success story, continually setting the standards of excellence in business, real estate and entertainment.",
                       "The myriad vulgarities of Donald Trump—examples of which are retailed daily on Web sites and front pages these days—are not news to those of us who have",
                       "While a fearful nation watched the terrorists attack again, striking the cafés of Paris and the conference rooms of San Bernardino"]})

Is there any tip to make this string search faster? For example, sorting dataframe first, certain way of indexing, changing column names to numbers, dropping "na=False" from the query, etc.? Even milliseconds of speed increase will be very helpful!

Answer 1

If the number of substrings is small, it may be faster to search for one at a time, because you can pass the regex=False argument to contains, which speeds it up.

On a sample DataFrame of about 6000 rows that I tested it with on two sample substrings, blah.contains("foo", regex=False)| blah.contains("bar", regex=False) was about twice as fast as blah.contains("foo|bar"). You'd have to test it with your data to see how it scales.

Answer 2

You could converting it to a list. It seems that searching in a list rather than applying string methods to a series is significantly faster.

Sample code:

import timeit
df = pd.DataFrame({'col': ["very definition of the American success story, continually setting the standards of excellence in business, real estate and entertainment.",
                       "The myriad vulgarities of Donald Trump—examples of which are retailed daily on Web sites and front pages these days—are not news to those of us who have",
                       "While a fearful nation watched the terrorists attack again, striking the cafés of Paris and the conference rooms of San Bernardino"]})



def first_way():
    df["new"] = pd.Series(df["col"].str.contains('Donald',case=True,na=False))
    return None
print "First_way: "
%timeit for x in range(10): first_way()
print df

df = pd.DataFrame({'col': ["very definition of the American success story, continually setting the standards of excellence in business, real estate and entertainment.",
                       "The myriad vulgarities of Donald Trump—examples of which are retailed daily on Web sites and front pages these days—are not news to those of us who have",
                       "While a fearful nation watched the terrorists attack again, striking the cafés of Paris and the conference rooms of San Bernardino"]})


def second_way():
    listed = df["col"].tolist()
    df["new"] = ["Donald" in n for n in listed]
    return None

print "Second way: "
%timeit for x in range(10): second_way()
print df

Results:

First_way: 
100 loops, best of 3: 2.77 ms per loop
                                                 col    new
0  very definition of the American success story,...  False
1  The myriad vulgarities of Donald Trump—example...   True
2  While a fearful nation watched the terrorists ...  False
Second way: 
1000 loops, best of 3: 1.79 ms per loop
                                                 col    new
0  very definition of the American success story,...  False
1  The myriad vulgarities of Donald Trump—example...   True
2  While a fearful nation watched the terrorists ...  False