title: How to speed up multiple str.contains searches for millions of rows?
body: <p>I have a dataframe of shop names that I'm trying to standardize. Small sample to test here:</p>
  
  <pre><code>import pandas as pd
  
  df = pd.DataFrame({'store': pd.Series(['McDonalds', 'Lidls', 'Lidl New York 123', 'KFC', 'Lidi Berlin', 'Wallmart LA 90210', 'Aldi', 'London Lidl', 'Aldi627', 'mcdonaldsabc123', 'Mcdonald_s', 'McDonalds12345', 'McDonalds5555', 'McDonalds888', 'Aldi123', 'KFC-786', 'KFC-908', 'McDonalds511', 'GerALDInes Shop'],dtype='object',index=pd.RangeIndex(start=0, stop=19, step=1)), 'standard': pd.Series([pd.np.nan, pd.np.nan, pd.np.nan, pd.np.nan, pd.np.nan, pd.np.nan, pd.np.nan, pd.np.nan, pd.np.nan, pd.np.nan, pd.np.nan, pd.np.nan, pd.np.nan, pd.np.nan, pd.np.nan, pd.np.nan, pd.np.nan, pd.np.nan, pd.np.nan],dtype='float64',index=pd.RangeIndex(start=0, stop=19, step=1))}, index=pd.RangeIndex(start=0, stop=19, step=1))
  
                  store  standard
  0           McDonalds       NaN
  1               Lidls       NaN
  2   Lidl New York 123       NaN
  3                 KFC       NaN
  4         Lidi Berlin       NaN
  5   Wallmart LA 90210       NaN
  6                Aldi       NaN
  7         London Lidl       NaN
  8             Aldi627       NaN
  9     mcdonaldsabc123       NaN
  10         Mcdonald_s       NaN
  11     McDonalds12345       NaN
  12      McDonalds5555       NaN
  13       McDonalds888       NaN
  14            Aldi123       NaN
  15            KFC-786       NaN
  16            KFC-908       NaN
  17       McDonalds511       NaN
  18    GerALDInes Shop       NaN
  </code></pre>
  
  <p>I set up a regex dictionary to search for a string, and insert a standardized version of the shop name into the column <code>standard</code>. This works fine for this small dataframe:</p>
  
  <pre><code># set up the dictionary
  regex_dict = {
   "McDonalds": r'(mcdonalds|mcdonald_s)',
   "Lidl" : r'(lidl|lidi)',
   "Wallmart":r'wallmart',
   "KFC": r'KFC',
   "Aldi":r'(\baldi\b|\baldi\d+)'
  }
  
  # loop through dictionary, using str.replace 
  for regname, regex_formula in regex_dict.items(): 
  
      df.loc[df['store'].str.contains(regex_formula,na=False,flags=re.I), 'standard'] = regname
  
  print(df)
  
                  store   standard
  0           McDonalds  McDonalds
  1               Lidls       Lidl
  2   Lidl New York 123       Lidl
  3                 KFC        KFC
  4         Lidi Berlin       Lidl
  5   Wallmart LA 90210   Wallmart
  6                Aldi       Aldi
  7         London Lidl       Lidl
  8             Aldi627       Aldi
  9     mcdonaldsabc123  McDonalds
  10         Mcdonald_s  McDonalds
  11     McDonalds12345  McDonalds
  12      McDonalds5555  McDonalds
  13       McDonalds888  McDonalds
  14            Aldi123       Aldi
  15            KFC-786        KFC
  16            KFC-908        KFC
  17       McDonalds511  McDonalds
  18    GerALDInes Shop        NaN
  </code></pre>
  
  <p>The problem is I have about SIX million rows to standardize, with a regex dictionary much larger than the one shown here. (many different shop names with some mispellings etc.)</p>
  
  <p>What I would like to do is at each loop, only use <code>str.contains</code> for rows that have <strong>not</strong> been standardized, and ignore the rows that have been standardized. The idea is to reduce the search space with each loop, therefore reducing the overall processing time. </p>
  
  <p>I have tested indexing by the <code>standard</code> column, only performing <code>str.contains</code> on rows where <code>standard</code> is <code>Nan</code>, but it does not result in any real speedup. It still takes time to figure out which rows are <code>Nan</code> before applying <code>str.contains</code>. </p>
  
  <p>Here is what I tried to reduce the processing time each loop:</p>
  
  <pre><code>for regname, regex_formula in regex_dict.items(): 
  
      # only apply str.contains to rows where standard == NAN
      df.loc[df['standard'].isnull() &amp; df['store'].str.contains(regex_formula,na=False,flags=re.I), 'standard'] = regname
  </code></pre>
  
  <p>This works .. but using this on my full 6 million rows makes no real difference in speed. </p>
  
  <p>Is it even possible to speed this up on a dataframe of 6 million rows? </p>