pandasの時間条件で該当する条件の一番最初の値を取得したい

Question

pandasの時間条件で該当する条件の一番最初の値(int)を取得したいと考えているのですがどの様にすると良いでしょうか?

データサンプル

index	time	int
0	2020.05.01 02:00	15
1	2020.05.01 02:03	14
2	2020.05.01 02:11	19
3	2020.05.01 02:16	15
4	2020.05.01 02:18	14
5	2020.05.01 02:23	12
6	2020.05.01 02:26	18
7	2020.05.01 02:29	11
8	2020.05.01 02:30	13
9	2020.05.01 02:33	16
10	2020.05.01 02:37	11
11	2020.05.01 02:44	19
12	2020.05.01 03:45	17

上記データにて index 0 の time 2020.05.01 02:00 の25分後の最初の値を取得する場合。
正解は index 6, time 2020.05.01 02:26, int 18になります。

上記データにて index 2 の time 2020.05.01 02:03 の25分後の最初の値を取得する場合。
正解は index 7, time 2020.05.01 02:29, int 11になります。

上記データにて index 3 の time 2020.05.01 02:11 の25分後の最初の値を取得する場合。
正解は index 10, time 2020.05.01 02:37, int 11になります。

○○分後(今回は25分)は一定です。
timeは一定ではないのでデータをズラして値を取得する事は出来ません。

理想の結果
|index |time |int |int_25M|
|:--|:--:|--:|
|0 |2020.05.01 02:00 |15 | 18|
|1 |2020.05.01 02:03 |14 | 11|
|2 |2020.05.01 02:11 |19 | 11|
|3 |2020.05.01 02:16 |15 | 19|
|4 |2020.05.01 02:18 |14 | 19|
|5 |2020.05.01 02:23 |12 | NaN|
|6 |2020.05.01 02:26 |18 | NaN|
|7 |2020.05.01 02:29 |11 | NaN|
|8 |2020.05.01 02:30 |13 | NaN|
|9 |2020.05.01 02:33 |16 | NaN|
|10 |2020.05.01 02:37 |11 | NaN|
|11 |2020.05.01 02:44 |19 | NaN|
|12 |2020.05.01 03:45 |17 | NaN|

こんな感じのデータを出力したいと考えています。

プログラム書いては見ましたが。。。

最初読み込んだデータを二つにして一行ずつ比較すればいいのかと考えてみました。
※まずはindexが取得出来るかどうかの確認してみました。

df_1 = pd.read_csv("hogehoge.csv") 
df_1['time'] = pd.to_datetime(df_1['time']) 
df_2 = df_1
df_2['data_25m'] = df_2['time'] + dt.timedelta(minutes=25) 

for idx,row in df_2.iterrows():
    try:
        df_1['int_25M'] = df_1[df_1['time'] > row['data_25m']].index[0]
    except:
        print("ありません")  

結果
|index |time |int |int_25M|
|:--|:--:|--:|
|0 |2020.05.01 02:00 |15 | 12|
|1 |2020.05.01 02:03 |14 | 12|
|2 |2020.05.01 02:11 |19 | 12|
|3 |2020.05.01 02:16 |15 | 12|
|4 |2020.05.01 02:18 |14 | 12|
|5 |2020.05.01 02:23 |12 | 12|
|6 |2020.05.01 02:26 |18 | 12|
|7 |2020.05.01 02:29 |11 | 12|
|8 |2020.05.01 02:30 |13 | 12|
|9 |2020.05.01 02:33 |16 | 12|
|10 |2020.05.01 02:37 |11 | 12|
|11 |2020.05.01 02:44 |19 | 12|
|12 |2020.05.01 03:45 |17 | 12|

うまくいきませんでした。

どの様にプログラムを書くと良いでしょうか?
宜しくお願いします。

Answer 1

時刻列がソートされているならば、pd.Series.searchsorted()を使いましょう。

import io

import numpy as np
import pandas as pd

data = """index,time,int
0,2020-05-01 02:00:00,15
1,2020-05-01 02:03:00,14
2,2020-05-01 02:11:00,19
3,2020-05-01 02:16:00,15
4,2020-05-01 02:18:00,14
5,2020-05-01 02:23:00,12
6,2020-05-01 02:26:00,18
7,2020-05-01 02:29:00,11
8,2020-05-01 02:30:00,13
9,2020-05-01 02:33:00,16
10,2020-05-01 02:37:00,11
11,2020-05-01 02:44:00,19
12,2020-05-01 02:45:00,17"""
df = pd.read_csv(io.StringIO(data), parse_dates=['time'])


int_array = np.r_[df['int'].to_numpy(), np.nan]     # int列の最後にnanを継ぎ足した配列
time_25m = df['time'] + pd.Timedelta('25m')         # time列の25分後
out = int_array[df['time'].searchsorted(time_25m)]  
print(out)
# [18. 11. 11. 19. 19. nan nan nan nan nan nan nan nan]

print(df.assign(int_25M=out))
#     index                time  int  int_25M
# 0       0 2020-05-01 02:00:00   15     18.0
# 1       1 2020-05-01 02:03:00   14     11.0
# 2       2 2020-05-01 02:11:00   19     11.0
# 3       3 2020-05-01 02:16:00   15     19.0
# 4       4 2020-05-01 02:18:00   14     19.0
# 5       5 2020-05-01 02:23:00   12      NaN
# 6       6 2020-05-01 02:26:00   18      NaN
# 7       7 2020-05-01 02:29:00   11      NaN
# 8       8 2020-05-01 02:30:00   13      NaN
# 9       9 2020-05-01 02:33:00   16      NaN
# 10     10 2020-05-01 02:37:00   11      NaN
# 11     11 2020-05-01 02:44:00   19      NaN
# 12     12 2020-05-01 02:45:00   17      NaN

Series.searchsorted(値)を実行すると、値がソート済みシリーズの何番目に挿入されるかがわかります。
例えば上の例で値が02:25:00ならば、これは5番目の02:23:00と6番目の02:26:00の間に挿入されるので、6が返ってきます。値を配列にすれば、そのそれぞれが何番目に挿入されるかがわかります。

int列をsearchsortedの結果で引っ張ってくれば求める配列になります。
ただしここで、02:48:00を挿入する場合はsearchsortedの結果13が返ってきますが、int列に13番目はなく、またこの行は最終的な結果を「NaN」にしたいので、int列の最後（13番目）にnp.nanを加えた配列int_arrayを作成しています。

Comments

[aws]

ご回答ありがとうございます。
searchsortedを初めて利用しましたが素晴らしいです。
また実行速度も速いので感動です。

またCSV読み込み時の"parse_dates=['time']"も勉強になりました。
本当にありがとうございます。

Answer 2

処理のところを変えてみました。
1行ずつ回していって、df_tという「その行のdata_25mより大きなtimeをもつ行だけ残したdataframe」です。それが空でなければ、先頭の行のintをdf_2のint_25mに入れています。
ところで、貼られたデータの最終行は12 2020.05.01 03:45 17となっていますが、正しい時刻は02:45で合っていますか。これだと後半の行はすべてint_25mが17になってしまいます。

import datetime as dt
import pandas as pd

df_1 = pd.read_csv("hogehoge.csv") 
df_1['time'] = pd.to_datetime(df_1['time']) 
df_2 = df_1
df_2['data_25m'] = df_2['time'] + dt.timedelta(minutes=25)
df_2['int_25m'] = ""
for i in df_2.index:
    df_t = df_2[df_2['time'] > df_2.at[i,'data_25m']]
    if len(df_t):
        df_2.at[i,'int_25m'] = df_t.iloc[0,2]

print(df_2)
#     index                time  int            data_25m int_25m
# 0       0 2020-05-01 02:00:00   15 2020-05-01 02:25:00      18
# 1       1 2020-05-01 02:03:00   14 2020-05-01 02:28:00      11
# 2       2 2020-05-01 02:11:00   19 2020-05-01 02:36:00      11
# 3       3 2020-05-01 02:16:00   15 2020-05-01 02:41:00      19
# 4       4 2020-05-01 02:18:00   14 2020-05-01 02:43:00      19
# 5       5 2020-05-01 02:23:00   12 2020-05-01 02:48:00        
# 6       6 2020-05-01 02:26:00   18 2020-05-01 02:51:00        
# 7       7 2020-05-01 02:29:00   11 2020-05-01 02:54:00        
# 8       8 2020-05-01 02:30:00   13 2020-05-01 02:55:00        
# 9       9 2020-05-01 02:33:00   16 2020-05-01 02:58:00        
# 10     10 2020-05-01 02:37:00   11 2020-05-01 03:02:00        
# 11     11 2020-05-01 02:44:00   19 2020-05-01 03:09:00        
# 12     12 2020-05-01 02:45:00   17 2020-05-01 03:10:00        

Comments

[aws]

ご回答ありがとうございます。
2020.05.01 03:45 => 02:45になります。
処理の所参考になります。