python - 检查数据帧值中有条件的第一次出现

Question

我有一个示例数据框 (df)，如下所示:

              Date_Time      Open      High       Low     Close   UOD  VWB
20  2020-07-01 10:30:00  10298.85  10299.90  10287.85  10299.90    UP    3
21  2020-07-01 10:35:00  10301.40  10310.00  10299.15  10305.75    UP    3
22  2020-07-01 10:40:00  10305.75  10305.75  10285.50  10290.00  DOWN    3
24  2020-07-01 10:45:00  10290.00  10291.20  10277.65  10282.65  DOWN    0
25  2020-07-01 10:50:00  10282.30  10289.80  10278.00  10282.00  DOWN    3
26  2020-07-01 10:55:00  10280.10  10295.00  10279.80  10291.50    UP    3
27  2020-07-01 11:00:00  10290.00  10299.95  10287.30  10297.55    UP    3
28  2020-07-01 11:05:00  10296.70  10306.30  10294.50  10299.40    UP    3
29  2020-07-01 11:10:00  10299.95  10301.10  10291.50  10292.00  DOWN    0
30  2020-07-01 11:15:00  10293.05  10298.70  10286.00  10291.55  DOWN    3
31  2020-07-01 11:20:00  10292.00  10298.70  10286.00  10351.45  DOWN    1

我有以下条件:

1. Check for df['VWB'] == 0 & df['UOD'] == "DOWN" & get the corresponding Open value (= 10290.00 in my example)
2. Then Find the first occurrence of Close value greater than this Open value (10290.00) after that row.

我想要我想要的输出，如下所示，带有有效列

              Date_Time      Open      High       Low     Close   UOD  VWB  Valid
20  2020-07-01 10:30:00  10298.85  10299.90  10287.85  10299.90    UP    3      0
21  2020-07-01 10:35:00  10301.40  10310.00  10299.15  10305.75    UP    3      0
22  2020-07-01 10:40:00  10305.75  10305.75  10285.50  10290.00  DOWN    3      0
23  2020-07-01 10:45:00  10290.00  10291.20  10277.65  10282.65  DOWN    0      0
25  2020-07-01 10:50:00  10282.30  10289.80  10278.00  10282.00  DOWN    3      0
26  2020-07-01 10:55:00  10280.10  10295.00  10279.80  10291.50    UP    3      1 <<= first occurrence
27  2020-07-01 11:00:00  10290.00  10299.95  10287.30  10297.55    UP    3      0
28  2020-07-01 11:05:00  10296.70  10306.30  10294.50  10299.40    UP    3      0
29  2020-07-01 11:10:00  10299.95  10301.10  10291.50  10292.00  DOWN    0      0
30  2020-07-01 11:15:00  10293.05  10298.70  10286.00  10291.55  DOWN    3      0
31  2020-07-01 11:20:00  10292.00  10298.70  10286.00  10351.45  DOWN    1      1 <<= first occurrence

Answer 1

这有点棘手，因为我假设以下 bool 可能有多个值。

df.loc[(df["VWB"] == 0) & (df["UOD"] == "DOWN")]

我们可以创建一个伪键来通过矢量化操作捕获每个组。

我已经编辑了您的示例，因此我们有 2 个值可以等同于上述 bool 值的 True。

print(df)

            Date_Time      Open      High       Low     Close   UOD  VWB
0 2020-07-01 10:30:00  10298.85  10299.90  10287.85  10299.90    UP    3
1 2020-07-01 10:35:00  10301.40  10310.00  10299.15  10305.75    UP    3
2 2020-07-01 10:40:00  10305.75  10305.75  10285.50  10290.00  DOWN    3
3 2020-07-01 10:45:00  10290.00  10291.20  10277.65  10282.65  DOWN    0
4 2020-07-01 10:50:00  10282.30  10289.80  10278.00  10282.00  DOWN    3
5 2020-07-01 10:55:00  10280.10  10295.00  10279.80  10291.50    UP    3
6 2020-07-01 11:00:00  10290.00  10299.95  10287.30  10297.55    UP    3
7 2020-07-01 11:05:00  10296.70  10306.30  10294.50  10299.40    UP    3
8 2020-07-01 11:10:00  10299.95  10301.10  10291.50  10292.00  DOWN    0
9 2020-07-01 11:15:00  10293.05  10298.70  10286.00  10595.55  DOWN    3

---

s = df.loc[(df["VWB"] == 0) & (df["UOD"] == "DOWN"), "Open"]

df1 = df.assign(key=df.index.isin(s.index).cumsum())
# we will filter out the 0 key.


print(df1)

            Date_Time      Open      High       Low     Close   UOD  VWB  key
0 2020-07-01 10:30:00  10298.85  10299.90  10287.85  10299.90    UP    3    0
1 2020-07-01 10:35:00  10301.40  10310.00  10299.15  10305.75    UP    3    0
2 2020-07-01 10:40:00  10305.75  10305.75  10285.50  10290.00  DOWN    3    0
3 2020-07-01 10:45:00  10290.00  10291.20  10277.65  10282.65  DOWN    0    1
4 2020-07-01 10:50:00  10282.30  10289.80  10278.00  10282.00  DOWN    3    1
5 2020-07-01 10:55:00  10280.10  10295.00  10279.80  10291.50    UP    3    1
6 2020-07-01 11:00:00  10290.00  10299.95  10287.30  10297.55    UP    3    1
7 2020-07-01 11:05:00  10296.70  10306.30  10294.50  10299.40    UP    3    1
8 2020-07-01 11:10:00  10299.95  10301.10  10291.50  10292.00  DOWN    0    2
9 2020-07-01 11:15:00  10293.05  10298.70  10286.00  10595.55  DOWN    3    2

---

现在对于每个组，我们需要比较 Open 的第一个实例，看看哪里的 Close 更大。

idx = df1.assign(tempOpen=df1.groupby("key")["Open"].transform("first")).query(
    "Close > tempOpen"
).groupby("key", as_index=False)["key"].idxmin()


df['valid'] = np.where(df1.index.isin(idx) & df1.key.ne(0),1,0)

print(df[['Open','Close','valid']])

       Open     Close  valid
0  10298.85  10299.90      0
1  10301.40  10305.75      0
2  10305.75  10290.00      0
3  10290.00  10282.65      0
4  10282.30  10282.00      0
5  10280.10  10291.50      1
6  10290.00  10297.55      0
7  10296.70  10299.40      0
8  10299.95  10292.00      0
9  10293.05  10595.55      1