嵌套For循环的更有效替代方案

Question

我有两个数据帧，其中包含在两个不同频率收集的数据。我想要将df2的标签更新为df1的标签，如果它属于事件持续时间。

我创建了一个嵌套的for循环来做这件事，但这需要相当长的时间。下面是我使用的代码：

for i in np.arange(len(df1)-1):
    for j in np.arange(len(df2)):
        if (df2.timestamp[j] > df1.timestamp[i]) & (df2.timestamp[j] < (df1.timestamp[i] + df1.duration[i])):
            df2.loc[j,"label"] = df1.loc[i,"label"]

有没有更有效的方法来做到这一点？df1大小(367，4) df2大小(342423，9)

简短的示例数据：

import numpy as np
import pandas as pd

data1 = {'timestamp':  [1,2,3,4,5,6,7,8,9],
    'duration': [0.5,0.3,0.8,0.2,0.4,0.5,0.3,0.7,0.5],
     'label': ['inh','exh','inh','exh','inh','exh','inh','exh','inh']
    }
df1 = pd.DataFrame (data1, columns = ['timestamp','duration','label'])

data2 = {'timestamp':  [1,1.5,2,2.5,3,3.5,4,4.5,5,5.5,6,6.5,7,7.5,8,8.5,9,9.5],
         'label': ['plc','plc','plc','plc','plc','plc','plc','plc','plc','plc','plc','plc','plc','plc','plc','plc','plc','plc']
        }
    df2 = pd.DataFrame (data2, columns = ['timestamp','label'])

Answer 1

我将首先使用merge_asof从df1中选择低于df2的时间戳的最高时间戳。接下来，对df2.timestamp和df1.timestamp +df1.uration进行简单的(矢量化)比较，就足以选择匹配的行。

代码可以是：

df1['t2'] = df1['timestamp'].astype('float64') # types of join columns must be the same
temp = pd.merge_asof(df2, df1, left_on='timestamp', right_on='t2')
df2.loc[temp.timestamp_x <= temp.t2 + temp.duration, 'label'] = temp.label_y

它为df2提供了：

    timestamp label
0         1.0   inh
1         1.5   inh
2         2.0   exh
3         2.5   plc
4         3.0   inh
5         3.5   inh
6         4.0   exh
7         4.5   plc
8         5.0   inh
9         5.5   plc
10        6.0   exh
11        6.5   exh
12        7.0   inh
13        7.5   plc
14        8.0   exh
15        8.5   exh
16        9.0   inh
17        9.5   inh