Get rows whose timestamps are within specific sliding window time interval pandas (Time Series)

user_5431791 0
Viewed 760

I have a dataframe like this:

i = pd.to_datetime(np.random.randint(time.time(), time.time()+5000, 10), unit='ms').sort_values()
df = pd.DataFrame({'A':range(10),'B':range(10,30,2),'C':range(10,40,3)},index = i)

df
                         A   B   C
1970-01-19 04:28:30.030  0  10  10
1970-01-19 04:28:30.374  1  12  13
1970-01-19 04:28:31.055  2  14  16
1970-01-19 04:28:32.026  3  16  19
1970-01-19 04:28:32.234  4  18  22
1970-01-19 04:28:32.569  5  20  25
1970-01-19 04:28:32.595  6  22  28
1970-01-19 04:28:33.520  7  24  31
1970-01-19 04:28:33.882  8  26  34
1970-01-19 04:28:34.019  9  28  37

What I want is, for each index, the last row which is within '1s' interval from that index:

df2
                                    ix            A   B   C
1970-01-19 04:28:30.030  1970-01-19 04:28:30.374  1  12  13
1970-01-19 04:28:30.374  1970-01-19 04:28:31.055  2  14  16
1970-01-19 04:28:31.055  1970-01-19 04:28:32.026  3  16  19
1970-01-19 04:28:32.026  1970-01-19 04:28:32.595  6  22  28
1970-01-19 04:28:32.234  1970-01-19 04:28:32.595  6  22  28
1970-01-19 04:28:32.569  1970-01-19 04:28:33.520  7  24  31
1970-01-19 04:28:32.595  1970-01-19 04:28:33.520  7  24  31
1970-01-19 04:28:33.520  1970-01-19 04:28:34.019  9  28  37
1970-01-19 04:28:33.882  1970-01-19 04:28:34.019  9  28  37
1970-01-19 04:28:34.019             nan          nan nan nan

I am currently doing this with loops. At each index I am using df.between_time to get all the rows in the time interval and then selecting the last row. But it is really slow, as expected. I need something like df.shift for time, I checked out tshift and shift(periods = 1, freq = 'S') but they do not work like shift, rather they add specified time to each index. Can somebody help me in achieving this? Thanks.

Note: The ix columns in the desired output is optional.

PS: If a min_periods parameter (like pd.df.rolling) is possible, that would be great!

EDIT:

For a starting df:

                         A   B   C
1970-01-19 04:28:34.883  0  10  10
1970-01-19 04:28:34.900  1  12  13
1970-01-19 04:28:35.531  2  14  16
1970-01-19 04:28:36.845  3  16  19
1970-01-19 04:28:37.664  4  18  22
1970-01-19 04:28:38.332  5  20  25
1970-01-19 04:28:38.444  6  22  28
1970-01-19 04:28:38.724  7  24  31
1970-01-19 04:28:38.787  8  26  34
1970-01-19 04:28:38.951  9  28  37

df['time'] = df.index
def last_time(time):
    time = str(time)
    start_time = datetime.datetime.strptime(time[11:],'%H:%M:%S.%f')
    end_time = start_time + datetime.timedelta(0,1)
    return df.between_time(start_time = str(start_time)[11:-7],end_time= 
                                        str(end_time)[11:-7]).iloc[-1]
df.apply(lambda x:last_time(x['time']),axis = 1)

# Output:
                         A   B   C                    time
1970-01-19 04:28:34.883  1  12  13 1970-01-19 04:28:34.900
1970-01-19 04:28:34.900  1  12  13 1970-01-19 04:28:34.900
1970-01-19 04:28:35.531  2  14  16 1970-01-19 04:28:35.531
1970-01-19 04:28:36.845  3  16  19 1970-01-19 04:28:36.845
1970-01-19 04:28:37.664  4  18  22 1970-01-19 04:28:37.664
1970-01-19 04:28:38.332  9  28  37 1970-01-19 04:28:38.951
1970-01-19 04:28:38.444  9  28  37 1970-01-19 04:28:38.951
1970-01-19 04:28:38.724  9  28  37 1970-01-19 04:28:38.951

But as you can see, I can only get second level accuracy, that is it is considering between 34 to 35, hence it is missing 35.531 which is within interval from both 34.883 and 34.900.

pandas time-series date datetime python
2 Answers

assuming your time is sorted, then the corresponding row for row 2 would be strictly larger than that for row 1. eg: if row 6 is the row for row1, then row2 would only need to search row that is >=6

With this in mind we just need to loop through the index once(complexity linear : O(n)):

import pandas as pd
from datetime import datetime

def time_compare(t1,t2):
     return datetime.strptime(t1,'%Y-%m-%d %H:%M:%S.%f').timestamp() - datetime.strptime(t2,'%Y-%m-%d %H:%M:%S.%f').timestamp() < 1

index_j = []
cursor = 0
tmp = list(df.index)
for i in tmp:
    if cursor < len(tmp):
        pass
    else:
        index_j.append(cursor-1)
        continue
    while time_compare(tmp[cursor],i):
        cursor += 1
        if cursor < len(tmp):
            pass
        else:
            break
    index_j.append(cursor-1)

Using this df:

>>> df
                         A   B   C
1970-01-19 04:28:34.883  0  10  10
1970-01-19 04:28:34.900  1  12  13
1970-01-19 04:28:35.531  2  14  16
1970-01-19 04:28:36.845  3  16  19
1970-01-19 04:28:37.664  4  18  22
1970-01-19 04:28:38.332  5  20  25
1970-01-19 04:28:38.444  6  22  28
1970-01-19 04:28:38.724  7  24  31
1970-01-19 04:28:38.787  8  26  34
1970-01-19 04:28:38.951  9  28  37



>>> index_j
[2, 2, 2, 4, 6, 9, 9, 9, 9, 9]

Using the index:

>>> [tmp[i] for i in index_j]
['1970-01-19 04:28:35.531', '1970-01-19 04:28:35.531', '1970-01-19 04:28:35.531', '1970-01-19 04:28:37.664', '1970-01-19 04:28:38.444', '1970-01-19 04:28:38.951', '1970-01-19 04:28:38.951', '1970-01-19 04:28:38.951', '1970-01-19 04:28:38.951', '1970-01-19 04:28:38.951']
user_85603820

I kind of got an answer, hence sharing, if anyone has a better answer you are most welcome to add it.

i = pd.to_datetime(np.random.randint(time.time(), time.time()+5000, 10), unit='ms').sort_values()
df = pd.DataFrame({'A':range(10),'B':range(10,30,2),'C':range(10,40,3)},index = i)
df
df
                         A   B   C
1970-01-19 04:28:30.030  0  10  10
1970-01-19 04:28:30.374  1  12  13
1970-01-19 04:28:31.055  2  14  16
1970-01-19 04:28:32.026  3  16  19
1970-01-19 04:28:32.234  4  18  22
1970-01-19 04:28:32.569  5  20  25
1970-01-19 04:28:32.595  6  22  28
1970-01-19 04:28:33.520  7  24  31
1970-01-19 04:28:33.882  8  26  34
1970-01-19 04:28:34.019  9  28  37

df['time'] = df.index
def last_time(time):
    time = str(time)
    start_time = datetime.datetime.strptime(time[11:],'%H:%M:%S.%f')
    end_time = start_time + datetime.timedelta(0,1)
    tempdf = df.between_time(*pd.to_datetime([str(start_time),str(end_time)]).time).iloc[-1]
    if str(tempdf['time']) == str(time):
        tempdf.iloc[:] = np.nan
        return tempdf
    else:
        return tempdf
df.apply(lambda x:last_time(x['time']),axis = 1)

                           A     B     C                        time
1970-01-19 04:28:34.883  2.0  14.0  16.0  1970-01-19 04:28:35.531000
1970-01-19 04:28:34.900  2.0  14.0  16.0  1970-01-19 04:28:35.531000
1970-01-19 04:28:35.531  NaN   NaN   NaN                         NaN
1970-01-19 04:28:36.845  4.0  18.0  22.0  1970-01-19 04:28:37.664000
1970-01-19 04:28:37.664  6.0  22.0  28.0  1970-01-19 04:28:38.444000
1970-01-19 04:28:38.332  9.0  28.0  37.0  1970-01-19 04:28:38.951000
1970-01-19 04:28:38.444  9.0  28.0  37.0  1970-01-19 04:28:38.951000
1970-01-19 04:28:38.724  9.0  28.0  37.0  1970-01-19 04:28:38.951000
1970-01-19 04:28:38.787  9.0  28.0  37.0  1970-01-19 04:28:38.951000
1970-01-19 04:28:38.951  NaN   NaN   NaN                         NaN
user_54317910
Related