Performance issues with pandas and filtering on datetime column

It's a bit of speculation because I can't test it but two ideas come to my mind.

1. Using a lookup series to determine the start and end indices of a data frame to be returned:

   s = pd.Series(np.arange(len(df)), index=df.time)
   start = s.asof(start)
   end = s.asof(end)
   ret = df.iloc[start + 1 : end]
   

2. Setting df.time column as an index and taking a slice. (It may or may not be a good solution for other reasons since this column contains duplicates.)

   df = df.set_index('time')
   ret = df.loc[start : end]
   

   You may need to add some small Timedelta to start.

In both cases you can do the main step (construct the series or set the index) only once per each data frame.

Pandas query can use numexpr as engine to speed up evaluation:

df.query('time > @start & time <= @end')

http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.query.html

it seems to me that using the ix locator is faster

df.sort_values(by='time',inplace=True)
df.ix[(df.time > start) & (df.time <= end),:]

I've learned that those datetime objects can become very memory hungry and require more computational effort, Especially if they are set to be the index (DatetimeIndex objects?)

I think your best bet is to just cast the df.time, start and end into UNIX timestamps (as ints, no longer the datetime dtypes), and do a simple integer comparison.

The UNIX timestamp will look like: 1471554233 (the time of this posting). More on that here: https://en.wikipedia.org/wiki/Unix_time

Some considerations when doing this (eg keep in mind timezones): Convert datetime to Unix timestamp and convert it back in python

I guess you are already running things in a relatively efficient way.

When working with time series, it's usually best practice use the column using your timestamps as the DataFrame index. Using a RangeIndex as your index isn't of much use. However, I ran a couple of tests on a (2650069, 2) DataFrame containing 6 months of trade tick data from a given stock at a given exchange and it turns out your approach (creating a boolean array and using it to slice the DataFrame) seems to be 10x faster than regular DatetimeIndex slicing (which I thought was faster).

The data I tested looks like:

                                Price  Volume
time                                         
2016-02-10 11:16:15.951403000  6197.0   200.0
2016-02-10 11:16:16.241380000  6197.0   100.0
2016-02-10 11:16:16.521871000  6197.0   900.0
2016-02-10 11:16:16.541253000  6197.0   100.0
2016-02-10 11:16:16.592049000  6196.0   200.0

Setting start/end:

start = df.index[len(df)/4]
end = df.index[len(df)/4*3]

Test 1:

%%time
_ = df[start:end]  # Same for df.ix[start:end]

CPU times: user 413 ms, sys: 20 ms, total: 433 ms
Wall time: 430 ms

On the other hand, using your approach:

df = df.reset_index()
df.columns = ['time', 'Price', 'Volume']

Test 2:

%%time
u = (df['time'] > start) & (df['time'] <= end)

CPU times: user 21.2 ms, sys: 368 µs, total: 21.6 ms
Wall time: 20.4 ms

Test 3:

%%time
_ = df[u]

CPU times: user 10.4 ms, sys: 27.6 ms, total: 38.1 ms
Wall time: 36.8 ms

Test 4:

%%time
_ = df[(df['time'] > start) & (df['time'] <= end)]

CPU times: user 21.6 ms, sys: 24.3 ms, total: 45.9 ms
Wall time: 44.5 ms

Note: Each code block corresponds to an Jupyter notebook cell and its output. I am using the %%time magic because %%timeit usually yields some caching problems which make the code seems faster than it actually is. Also, the kernel has been restarted after each run.

I am not totally sure why that is the case (I thought slicing using a DatetimeIndex would make things faster), but I guess it probably has something to do with how things work under the hood with numpy (most likely the datetime slicing operation generates a boolean array which is then used internally by numpy to actually do the slicing - but don't quote me on that).