python pandas optimization: filtering on text index values
I've found a solution: instead of filtering through all the values, I compute a hash table (in fact a DataFrame) of the possible values and filter on those values:


Step 0: 0:00:00.014251
Step 1: 0:00:00.010097
Init fast filter: 0:00:00.353645
Step 2: 0:00:00.027462
Step 3: 0:00:00.002550
Step 4: 0:00:00.027452
Globally: 0:00:00.435839


It runs faster because there are lots of duplicated values.

Here's a class which is doing that stuff. It works for indexes and normal columns:

import pandas as pd
import numpy as np


class DataFrameFastFilterText(object):
    """This class allows fast filtering of text column or index of a data frame
    Instead of filtering directy the values, it computes a hash of the values
    and applies the filtering to it.
    Then, it filters the values based on the hash mapping.
    This is faster than direct filtering if there are lots of duplicates values.
    Please note that missing values will be replaced by "" and every value will
    be converted to string.
    Input data won't be changed (working on a copy).
    """

    def __init__(self, data, column, isIndex):
        """Constructor
        - data: pandas Data Frame
        - column: name of the column - the column can be an index
        - isIndex: True if the column is an index
        """
        self._data = data.copy()  # working with a copy because we will make some changes
        self._column = column
        self._isIndex = isIndex
        self._hashedValues = None
        self._initHashAndData()

    def _initHashAndData(self):
        """Initialize the hash and transforms the data
        1. data: a. adds an order column
                 b. creates the column from the index if needed,
                 c. rename the column as 'value' and
                 d. drops all the other columns
                 e. fill na with ""
                 f. convert to strings
        2. hash: data frame of 'hashId', 'hashValue'
        3. data: a. sort by initial order
                 b. delete order
        """
        self._data['order'] = np.arange(self._data.shape[0])
        if self._isIndex:
            self._data[self._column] = self._data.index.get_level_values(self._column)
        self._data = self._data.loc[:, [self._column, 'order']]
        self._data.rename(columns={self._column: 'value'}, inplace=True)
        self._data['value'] = self._data['value'].fillna("", inplace=False)
        self._data['value'] = self._data['value'].astype(str)
        self._hash = pd.DataFrame({'value': pd.Series(self._data['value'].unique())})
        self._hash['hashId'] = self._hash.index
        self._data = pd.merge(
            left=self._data,
            right=self._hash,
            on='value',
            how='inner',
        )
        self._data.sort_values(by='order', inplace=True)
        del self._data['order']

    def getFilter(self, *args, **kwargs):
        """Returns the filter as a boolean array. It doesn't applies it.
        - args: positional arguments to pass to pd.Series.str.contains
        - kwargs: named arguments to pass to pd.Series.str.contains
        """
        assert "na" not kwargs.keys()  # useless in this context because all the NaN values are already replaced by empty strings
        h = self._hash.copy()
        h = h.loc[h['value'].str.contains(*args, **kwargs), :].copy()
        d = self._data.copy()
        d['keep'] = d['hashId'].isin(h['hashId'])
        return np.array(
            d['keep'],
            dtype=np.bool
        ).copy()


And the example code is adapted as follows:

...
tic = datetime.datetime.now()
fastFilter = DataFrameFastFilterText(data, 'identifier')
print("- Init fast filter: %s" % str(datetime.datetime.now() - tic))

tic = datetime.datetime.now()
filt = filt | fastFilter.getFilter(rexpPlus)
print("- Step 2: %s" % str(datetime.datetime.now() - tic))

tic = datetime.datetime.now()
filt = filt &amp; (~fastFilter.getFilter(rexpMinus))
print("- Step 3: %s" % str(datetime.datetime.now() - tic))
...