Remove loop calculating a non-continous temporal averaging feature in Python 3

Question

I have a for loop doing something I would have thought relatively straight forward on Python 3

# input_array is a time series of 1 minute data from x number of sites (cols), it is not necessarily continuous, though each timestep is unique. 
IN[1]: input_array
Out[1]: 
array([[0.14013903, 0.32521612, 0.11172546, ..., 0.37796091, 0.39655287, 0.09020338],
       [0.13153962, 0.34140063, 0.11812597, ..., 0.35862739, 0.4282353 , 0.08757173],
       [0.12921372, 0.35564918, 0.11834597, ..., 0.35499742, 0.4506885 , 0.0851089 ],
       ...,
       [       nan,        nan,        nan, ...,        nan,        nan,        nan],
       [       nan,        nan,        nan, ...,        nan,        nan,        nan],
       [       nan,        nan,        nan, ...,        nan,        nan,        nan]])


# time_5m is the 5min timestamps that i desire an average of:

In[2]: time_5m
Out[2]: 
0    2020-04-01 09:42:30
1    2020-04-01 09:47:30
2    2020-04-01 09:52:30
3    2020-04-01 09:57:30
4    2020-04-01 10:02:30
       
95   2020-04-01 17:37:30
96   2020-04-01 17:42:30
97   2020-04-01 17:47:30
98   2020-04-01 17:52:30
99   2020-04-01 17:57:30
Length: 100, dtype: datetime64[ns]

In[3]: type(time_5m)
Out[3]: pandas.core.series.Series


# time_1m is the 1min timestamps corresponding to the input_array
In[4]: time_1m
Out[4]: 
3638583   2020-04-01 09:41:00
3638584   2020-04-01 09:42:00
3638585   2020-04-01 09:43:00
3638586   2020-04-01 09:44:00
3638587   2020-04-01 09:45:00
       
3639078   2020-04-01 17:56:00
3639079   2020-04-01 17:57:00
3639080   2020-04-01 17:58:00
3639081   2020-04-01 17:59:00
3639082   2020-04-01 18:00:00
Name: Tm, Length: 500, dtype: datetime64[ns]

What I currently do is loop each output desired timestamp, find all minutes within that range, and take the average. However, this is wildly inefficient for large time series

import numpy as np
from datetime import timedelta

# pre allocate the output array
output_array = np.full((5, time_5m.shape[1]), np.nan)

for i, t in enumerate(time_5m):
  # calculate the mean of the corresponding 1-min
  inds = np.logical_and( time_1m > t - timedelta(minutes=5/2), time_1m < t + timedelta(minutes=5/2) )
  if sum(inds)>1:
    output_array[i, :] = np.nanmean(input_array[inds, :], axis=0)

I would really appreciate some advice how to remove this issue.

Every solution I usually use depends on the data being continuous and complete.

user_732596 · Answer

If I understand correctly, you can't use pd.DataFrame.resample('5 min').mean() out-of-the-box because time_5m isn't at 'normal' positions past the hour (i.e. time_5m is at 2:30, 7:30, ..., 57:30 past the hour.) That is, time_5m is 2.5 minutes offset from the 'normal' positions past the hour (where the 'normal' positions are at 0, 5, 10, ..., 55 minutes past the hour).

Pandas version 1.1 introduced two new keyword arguments for resample(): origin and offset (here are the docs for DataFrame.resample)

So something like this should work:

import pandas as pd
input_df = pd.DataFrame(input_array, index=time_1m)
resampled = input_df.resample('5 min', offset='2.5 min').mean()

Remove loop calculating a non-continous temporal averaging feature in Python 3

1 Answers