Pandas：根据索引将每行除以另一行

Question

我有一个数据表，里面记录了每天从早上9点半到下午4点的各种证券的每分钟价格。它的样子是这样的：

                  Date  TUA COMDTY  FVA COMDTY  TYA COMDTY  USA COMDTY  \
0  2014-03-14 09:30:00  109.898438  119.523438  123.796875   131.34375   
1  2014-03-14 09:31:00  109.898438  119.523438  123.796875   131.34375   
2  2014-03-14 09:32:00  109.898438  119.523438  123.796875   131.34375   
3  2014-03-14 09:33:00  109.898438  119.523438  123.796875   131.34375   
4  2014-03-14 09:34:00  109.898438  119.523438  123.796875   131.34375   
5  2014-03-14 09:35:00  109.898438  119.523438  123.796875   131.34375   
6  2014-03-14 09:36:00  109.898438  119.523438  123.796875   131.34375   
7  2014-03-14 09:37:00  109.898438  119.523438  123.796875   131.34375   
8  2014-03-14 09:38:00  109.898438  119.523438  123.796875   131.34375   
9  2014-03-14 09:39:00  109.898438  119.523438  123.796875   131.34375   
10 2014-03-14 09:40:00  109.898438  119.523438  123.796875   131.34375   
11 2014-03-14 09:41:00  109.898438  119.523438  123.796875   131.34375   
12 2014-03-14 09:42:00  109.898438  119.523438  123.796875   131.34375   
13 2014-03-14 09:43:00  109.898438  119.523438  123.796875   131.34375   
14 2014-03-14 09:44:00  109.898438  119.523438  123.796875   131.34375   
15 2014-03-14 09:45:00  109.898438  119.523438  123.796875   131.34375   
16 2014-03-14 09:46:00  109.898438  119.523438  123.796875   131.34375   
17 2014-03-14 09:47:00  109.898438  119.523438  123.796875   131.34375   
18 2014-03-14 09:48:00  109.898438  119.523438  123.796875   131.34375   
19 2014-03-14 09:49:00  109.898438  119.523438  123.796875   131.34375   
20 2014-03-14 09:50:00  109.898438  119.523438  123.796875   131.34375   
21 2014-03-14 09:51:00  109.898438  119.523438  123.796875   131.34375   
22 2014-03-14 09:52:00  109.898438  119.523438  123.796875   131.34375   
23 2014-03-14 09:53:00  109.898438  119.523438  123.796875   131.34375   
24 2014-03-14 09:54:00  109.898438  119.523438  123.796875   131.34375   
25 2014-03-14 09:55:00  109.898438  119.523438  123.796875   131.34375   
26 2014-03-14 09:56:00  109.898438  119.523438  123.796875   131.34375   
27 2014-03-14 09:57:00  109.898438  119.523438  123.796875   131.34375   
28 2014-03-14 09:58:00  109.898438  119.523438  123.796875   131.34375   
29 2014-03-14 09:59:00  109.898438  119.523438  123.796875   131.34375   
30 2014-03-14 10:00:00  109.898438  119.523438  123.796875   131.34375   
31 2014-03-14 10:01:00  109.898438  119.523438  123.796875   131.34375   
32 2014-03-14 10:02:00  109.898438  119.523438  123.796875   131.34375   
33 2014-03-14 10:03:00  109.898438  119.523438  123.796875   131.34375   
34 2014-03-14 10:04:00  109.898438  119.523438  123.796875   131.34375   
35 2014-03-14 10:05:00  109.898438  119.523438  123.796875   131.34375   
36 2014-03-14 10:06:00  109.898438  119.523438  123.796875   131.34375   
37 2014-03-14 10:07:00  109.898438  119.523438  123.796875   131.34375   
38 2014-03-14 10:08:00  109.898438  119.523438  123.796875   131.34375   
39 2014-03-14 10:09:00  109.898438  119.523438  123.796875   131.34375   
40 2014-03-14 10:10:00  109.898438  119.523438  123.796875   131.34375   
41 2014-03-14 10:11:00  109.898438  119.523438  123.796875   131.34375   
42 2014-03-14 10:12:00  109.898438  119.523438  123.796875   131.34375   
43 2014-03-14 10:13:00  109.898438  119.523438  123.796875   131.34375   
44 2014-03-14 10:14:00  109.898438  119.523438  123.796875   131.34375   
45 2014-03-14 10:15:00  109.898438  119.523438  123.796875   131.34375   
46 2014-03-14 10:16:00  109.898438  119.523438  123.796875   131.34375   
47 2014-03-14 10:17:00  109.898438  119.523438  123.796875   131.34375   
48 2014-03-14 10:18:00  109.898438  119.523438  123.796875   131.34375   
49 2014-03-14 10:19:00  109.898438  119.523438  123.796875   131.34375   
50 2014-03-14 10:20:00  109.898438  119.523438  123.796875   131.34375   
51 2014-03-14 10:21:00  109.898438  119.523438  123.796875   131.34375   
52 2014-03-14 10:22:00  109.898438  119.523438  123.796875   131.34375   
53 2014-03-14 10:23:00  109.898438  119.523438  123.796875   131.34375   
54 2014-03-14 10:24:00  109.898438  119.523438  123.796875   131.34375   
55 2014-03-14 10:25:00  109.898438  119.523438  123.796875   131.34375   
56 2014-03-14 10:26:00  109.898438  119.523438  123.796875   131.34375   
57 2014-03-14 10:27:00  109.898438  119.523438  123.796875   131.34375   
58 2014-03-14 10:28:00  109.898438  119.523438  123.796875   131.34375   
59 2014-03-14 10:29:00  109.898438  119.523438  123.796875   131.34375   
                   ...         ...         ...         ...         ...   

我想得到一个新的数据表，这个表能显示出和前一天相比价格的变化。现在我是在一个循环里处理每一行数据，获取前一天4点的价格，然后用这个价格去除以当前行的价格。 目前我每天在4点的时候进行重采样，把新的数据和原来的数据通过向前填充的方式对齐。这样新数据表的大小和原来的数据表是一样的，里面包含了我需要用来计算的价格。然后我就把这两个数据表的值进行相除。 我在想有没有更简单的方法，比如用apply()函数？这个函数的作用有点像Excel里的vlookup。问题是我需要每一行的索引（日期），但在apply()里没有这个信息。 你们有什么想法吗？谢谢！

Answer 1

我猜你只是想在关闭的时候返回数据。

我有个小技巧可以实现这个目的。我把数组重新采样成每日的数据，然后把它和我原来的数据框合并在一起。接着，我把收盘价向前填充。

这样做怎么样呢？

df_daily = df[df["Date"].hour == 16].copy().shift(1)
df = df.join(df_daily, rsuffix="closing_").fillna(method="ffill")