python - 使用直接位于其下方的元素查询/替换 DataFrame 中的元素

Question

我有一个数据框，我需要在其中查询 0.00s 并用其正下方的值替换如果满足某些条件。我已经查找过有关此类行为的文档，但无法找到有效的 Pythonic 解决方案。

逻辑如下:

如果 [交易品种] = 'VIX' 且 [QuoteDateTime] 包含 '09:31:00' 且 [收盘价] = '0.00'

那么我想将 [Close] 值替换为其正下方的 [Close] 值。

+----+--------+---------------------+---------+
|    | Symbol |    QuoteDateTime    |  Close  |
+----+--------+---------------------+---------+
|  0 | VIX    | 2019-04-11 09:31:00 |    0.00 |
|  1 | VIX    | 2019-04-11 09:32:00 |   14.24 |
|  2 | VIX    | 2019-04-11 09:33:00 |   14.40 |
|  3 | SPX    | 2019-04-11 09:31:00 | 2911.09 |
|  4 | SPX    | 2019-04-11 09:32:00 | 2911.55 |
|  5 | SPX    | 2019-04-11 09:33:00 | 2915.22 |
|  6 | VIX    | 2019-04-12 09:31:00 |    0.00 |
|  7 | VIX    | 2019-04-12 09:32:00 |   15.64 |
|  8 | VIX    | 2019-04-12 09:33:00 |   15.80 |
|  9 | SPX    | 2019-04-12 09:31:00 | 2901.09 |
| 10 | SPX    | 2019-04-12 09:32:00 | 2901.55 |
| 11 | SPX    | 2019-04-12 09:33:00 | 2905.22 |
+----+--------+---------------------+---------+

预期输出为指数 0 [收盘价] 为 14.24，指数 6 [收盘价] 为 15.64。其他一切都保持不变。

+----+--------+---------------------+---------+
|    | Symbol |    QuoteDateTime    |  Close  |
+----+--------+---------------------+---------+
|  0 | VIX    | 2019-04-11 09:31:00 |   14.24 |
|  1 | VIX    | 2019-04-11 09:32:00 |   14.24 |
|  2 | VIX    | 2019-04-11 09:33:00 |   14.40 |
|  3 | SPX    | 2019-04-11 09:31:00 | 2911.09 |
|  4 | SPX    | 2019-04-11 09:32:00 | 2911.55 |
|  5 | SPX    | 2019-04-11 09:33:00 | 2915.22 |
|  6 | VIX    | 2019-04-12 09:31:00 |   15.64 |
|  7 | VIX    | 2019-04-12 09:32:00 |   15.64 |
|  8 | VIX    | 2019-04-12 09:33:00 |   15.80 |
|  9 | SPX    | 2019-04-12 09:31:00 | 2901.09 |
| 10 | SPX    | 2019-04-12 09:32:00 | 2901.55 |
| 11 | SPX    | 2019-04-12 09:33:00 | 2905.22 |
+----+--------+---------------------+---------+

Answer 1

通过 Series.eq 创建 bool 掩码对于 ==，Series.dt.strftime对于来自 datetimes 的字符串并通过 Series.mask 设置新值与 Series.shift :

#convert to datetimes if necessary
df['QuoteDateTime'] = pd.to_datetime(df['QuoteDateTime'])

mask = (df['Symbol'].eq('VIX') & 
        df['QuoteDateTime'].dt.strftime('%H:%M:%S').eq('09:31:00') &
        df['Close'].eq(0))

df['Close'] = df['Close'].mask(mask, df['Close'].shift(-1))
#alternative1
#df.loc[mask, 'Close'] = df['Close'].shift(-1)
#alternative2
#df['Close'] = np.where(mask, df['Close'].shift(-1), df['Close'])
print (df)

   Symbol       QuoteDateTime    Close
0     VIX 2019-04-11 09:31:00    14.24
1     VIX 2019-04-11 09:32:00    14.24
2     VIX 2019-04-11 09:33:00    14.40
3     SPX 2019-04-11 09:31:00  2911.09
4     SPX 2019-04-11 09:32:00  2911.55
5     SPX 2019-04-11 09:33:00  2915.22
6     VIX 2019-04-12 09:31:00    15.64
7     VIX 2019-04-12 09:32:00    15.64
8     VIX 2019-04-12 09:33:00    15.80
9     SPX 2019-04-12 09:31:00  2901.09
10    SPX 2019-04-12 09:32:00  2901.55
11    SPX 2019-04-12 09:33:00  2905.22