python pandas optimization: filtering on text index values
I have to filter a pandas data frame by matching a complex regular expression on a text index.
The data frame is multi level indexed, and contains more than 2 million records.
The way I'm doing is:
identifiers = self._data.index.get_level_values('identifier')
filt = ... # an_array_of_np.bool_with_the_same_length_as_my_data
pattern = ... 392
# a complex regular expression, as a string
filt = filt & np.array(identifiers.str.contains(pat=pattern, case=False, regex=True), dtype=np.bool)
... # other filterings
Unfortunately, the line beginning by filt = filt & is very slow.
I'm wondering if you have some ideas to make it faster. I guess that's because of the identifiers.str.contains
Thanks a lot!
EDIT:
Thanks @Emre
I'm not allowed to share those data, but the code below demonstrates the problem:
* Step 0: 0:00:00.013527
* Step 1: 0:00:00.010127
* Step 2: 0:00:<PHONE_NUMBER>
* Step 3: 0:00:<PHONE_NUMBER>
* Step 4: 0:00:00.027437
In fact, my feeling is that we apply the regular expression on all the values of the identifiers, while I would expect that the filter applies on the possible values of the index (lost of values are reused many times).
import pandas as pd
import numpy as np
import datetime
N = 2000000
N_DVC = 10000
def getData():
identifiers = np.random.choice(np.array([
"need", "need: foo", "need: bar", "need: foo: bar", "foo: need", "bar: need",
"not: need", "not: need: foo", "not: need: bar", "not: need: foo: bar", "foo: need: not", "bar: need: not",
"need ign", "need: foo ign", "need: bar ign", "need: foo: bar ign", "foo: need ign", "bar: need ign",
"ign need", "need: ign foo", "need: ign bar", "need: foo: ign bar",
]), N)
devices = np.random.choice(np.arange(N_DVC))
timestamps = np.random.choice(pd.date_range('1/1/2016 00:00:00', periods=60*60*24, freq='s'), N)
x = np.random.rand(N)
y = np.random.rand(N)
data = pd.DataFrame({'identifier': identifiers, 'device': devices, 'timestamp': timestamps, 'x': x, 'y': y})
data.set_index(['device', 'identifier', 'timestamp'], drop=True, inplace=True)
return data
def filterData(data):
# I know those regular expressions are not perfect for the example,
# but it mimics the real expressions I have
rexpPlus = '^(?:[^\s]+:\s)*need(?:(?<IP_ADDRESS>\s[^\s]+)*:\s[^\s]+)?$'
rexpMinus = '(?<IP_ADDRESS>\s)(?:(?:not)|(?:ign))(?<IP_ADDRESS>\s)'
tic = datetime.datetime.now()
identifiers = data.index.get_level_values('identifier')
print("- Step 0: %s" % str(datetime.datetime.now() - tic))
tic = datetime.datetime.now()
filt = np.repeat(np.False_, data.shape[0])
print("- Step 1: %s" % str(datetime.datetime.now() - tic))
tic = datetime.datetime.now()
filt = filt | np.array(identifiers.str.contains(pat=rexpPlus, case=False, regex=True), dtype=np.bool)
print("- Step 2: %s" % str(datetime.datetime.now() - tic))
tic = datetime.datetime.now()
filt = filt & (~np.array(identifiers. 613