Pandas: vectorize sliding time window aggregation

user_1580469 0
Viewed 154

I have a big dataframe from which I need sliding time windows averages for a given set of query points. I tried with df.rolling but this wouldn't allow me for querying arbitary points. The following works, but seems inefficient and does not allow for vectorized usage:

import pandas as pd
df = pd.DataFrame({'B': range(5)},
              index = [pd.Timestamp('20130101 09:00:00'),
                       pd.Timestamp('20130101 09:00:02'),
                       pd.Timestamp('20130101 09:00:03'),
                       pd.Timestamp('20130101 09:00:05'),
                       pd.Timestamp('20130101 09:00:06')])
query = pd.date_range(df.index[0], df.index[-1], freq='s')
time_window = pd.Timedelta(seconds=2)

f = lambda t: df[(t - time_window < df.index) & (df.index <= t)]["B"].mean()

[f(t) for t in query] # works but is slow
f(query) # throws ValueError length must match

Probably this can be done better ...

Edit: The real application has measures which appear randomly between 30 and 90 seconds. Sometimes there are periods with several days or weeks without data. The time_window is typically 15 minutes. The overall time horizon is 10 years.

pandas vectorization numpy
2 Answers

You're skipping just a small step.

Your "query" is really a time series resampling operation. That is, in addition to calculating a rolling mean, you are also trying to smoothly resample the time series at a frequency of one second. You can do that using the asfreq method, applying it prior to the rolling operation:

resample_rolling = df.asfreq('1s').rolling(pd.Timedelta(seconds=2)).mean()
 
print(np.array([f(t) for t in query]))
print(resample_rolling.to_numpy()[:, 0])

Output:

[0.  0.  1.  1.5 2.  3.  3.5]
[0.  0.  1.  1.5 2.  3.  3.5]

Note that by default, the asfreq method fills missing values in with nan values.

>>> df.asfreq(pd.Timedelta(seconds=1))
                       B
2013-01-01 09:00:00  0.0
2013-01-01 09:00:01  NaN
2013-01-01 09:00:02  1.0
2013-01-01 09:00:03  2.0
2013-01-01 09:00:04  NaN
2013-01-01 09:00:05  3.0
2013-01-01 09:00:06  4.0

The rolling operation then ignores those values. If instead you want to fill the values with something other than nans, you have two options. You can supply a fill_value:

>>> df.asfreq('1s', fill_value=0.0)
                       B
2013-01-01 09:00:00  0.0
2013-01-01 09:00:01  0.0
2013-01-01 09:00:02  1.0
2013-01-01 09:00:03  2.0
2013-01-01 09:00:04  0.0
2013-01-01 09:00:05  3.0
2013-01-01 09:00:06  4.0

Or you can specify a method, such as backfill, which uses the next value in the series:

>>> df.asfreq('1s', method='backfill')
                     B
2013-01-01 09:00:00  0
2013-01-01 09:00:01  1
2013-01-01 09:00:02  1
2013-01-01 09:00:03  2
2013-01-01 09:00:04  3
2013-01-01 09:00:05  3
2013-01-01 09:00:06  4

The resulting rolling mean is then different, of course:

>>> df.asfreq('1s', method='backfill').rolling('1s').mean()
                       B
2013-01-01 09:00:00  0.0
2013-01-01 09:00:01  1.0
2013-01-01 09:00:02  1.0
2013-01-01 09:00:03  2.0
2013-01-01 09:00:04  3.0
2013-01-01 09:00:05  3.0
2013-01-01 09:00:06  4.0
user_5770880

After some research I came up with the following solution with two rolling windows, one for entering the window and one for leaving:

import pandas as pd, numpy as np
df = pd.DataFrame({'B': range(5)},
              index = [pd.Timestamp('20130101 09:00:00'),
                       pd.Timestamp('20130101 09:00:02'),
                       pd.Timestamp('20130101 09:00:03'),
                       pd.Timestamp('20130101 09:00:05'),
                       pd.Timestamp('20130101 09:00:06')])
query = pd.date_range(df.index[0], df.index[-1], freq='s')
time_window = pd.Timedelta(seconds=2)

aggregates = ['mean']
### Preparation
# one data point for each point entering the window
df1 = df.rolling(window=time_window, closed='right').agg(aggregates)

# one data point for each point leaving the window - use reverted df
df2 = df[::-1].rolling(window=time_window, closed='left').agg(aggregates)
df2.index += time_window
# Caution: for my real data in the reverted rolling method, I had
# to add a small Timedelta to window to function properly

# merge both together and remove duplicates
df_windowed = pd.concat([df1, df2])
df_windowed.sort_index(inplace=True)
df_windowed = df_windowed[~df_windowed.index.duplicated(keep='first')]

### the vectorized function
# Caution: get_indexer returns -1 for not found values (below df.index.min()), 
# which is interpreted as last value. But last value of df_windows is always NaN
f = lambda t: df_windowed.iloc[
        df_windowed.index.get_indexer(t, method='ffill') if isinstance(t, (pd.Index, pd.Series, np.ndarray,)) else
        df_windowed.index.get_loc(t, method='ffill')
    ]["B"]["mean"].to_numpy()

f(query)
user_15804690
Related