python - 使用另一列的偏移值比较 Pandas 数据框列的值

Question

我有一个数据框:

Time    InvInstance
5          5
8          4
9          3
19         2
20         1
3          3
8          2
13         1

Time 变量已排序，InvInstance 变量表示到 Time block 末尾的行数。我想创建另一个列来显示 Time 列中是否满足交叉条件。我可以用这样的 for 循环来完成:

import pandas as pd
import numpy as np

df = pd.read_csv("test.csv")

df["10mMark"] = 0
for i in range(1,len(df)):
    r = int(df.InvInstance.iloc[i])
    rprev = int(df.InvInstance.iloc[i-1])
    m = df['Time'].iloc[i+r-1] - df['Time'].iloc[i]
    mprev = df['Time'].iloc[i-1+rprev-1] - df['Time'].iloc[i-1]
    df["10mMark"].iloc[i] = np.where((m < 10) & (mprev >= 10),1,0)

所需的输出是:

Time  InvInstance  10mMark
 5            5        0
 8            4        0
 9            3        0
19            2        1
20            1        0
 3            3        0
 8            2        1
13            1        0

更具体地说；在 Time 列中有 2 个排序的时间 block ，逐行我们知道到每个 block 末尾的距离(以行为单位)由 InvInstance 的值决定。问题是一行与 block 末尾的时间差是否小于10分钟，而前一行大于10。是否可以在没有循环的情况下执行此操作，例如 shift() 等，以便它运行得更快？

Answer 1

我不知道/不知道如何使用内部矢量化 Pandas/Numpy 方法通过非标量/矢量 步骤移动系列/数组，但我们可以使用 Numba这里:

from numba import jit

@jit
def dyn_shift(s, step):
    assert len(s) == len(step), "[s] and [step] should have the same length"
    assert isinstance(s, np.ndarray), "[s] should have [numpy.ndarray] dtype"
    assert isinstance(step, np.ndarray), "[step] should have [numpy.ndarray] dtype"
    N = len(s)
    res = np.empty(N, dtype=s.dtype)
    for i in range(N):
        res[i] = s[i+step[i]-1]
    return res

mask1 = dyn_shift(df.Time.values, df.InvInstance.values) - df.Time < 10
mask2 = (dyn_shift(df.Time.values, df.InvInstance.values) - df.Time).shift() >= 10
df['10mMark'] = np.where(mask1 & mask2,1,0)

结果:

In [6]: df
Out[6]:
   Time  InvInstance  10mMark
0     5            5        0
1     8            4        0
2     9            3        0
3    19            2        1
4    20            1        0
5     3            3        0
6     8            2        1
7    13            1        0

8.000 行 DF 的时间:

In [13]: df = pd.concat([df] * 10**3, ignore_index=True)

In [14]: df.shape
Out[14]: (8000, 3)

In [15]: %%timeit
    ...: df["10mMark"] = 0
    ...: for i in range(1,len(df)):
    ...:     r = int(df.InvInstance.iloc[i])
    ...:     rprev = int(df.InvInstance.iloc[i-1])
    ...:     m = df['Time'].iloc[i+r-1] - df['Time'].iloc[i]
    ...:     mprev = df['Time'].iloc[i-1+rprev-1] - df['Time'].iloc[i-1]
    ...:     df["10mMark"].iloc[i] = np.where((m < 10) & (mprev >= 10),1,0)
    ...:
3.06 s ± 109 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

In [16]: %%timeit
    ...: mask1 = dyn_shift(df.Time.values, df.InvInstance.values) - df.Time < 10
    ...: mask2 = (dyn_shift(df.Time.values, df.InvInstance.values) - df.Time).shift() >= 10
    ...: df['10mMark'] = np.where(mask1 & mask2,1,0)
    ...:
1.02 ms ± 21.4 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

加速因子:

In [17]: 3.06 * 1000 / 1.02
Out[17]: 3000.0