python - 获取DataFrame中特定日期范围内的最小值和最大值

Question

我有一个 DataFrame，其中包含列“From”(日期时间)、“To”(日期时间)。表中不同行的范围有些重叠。

这是标准数据框的简化版本(日期范围各不相同且相互重叠):

df1= pd.DataFrame({'From': pd.date_range(start='2020-01-01', end='2020-01-31',freq='2D'), 'To': pd.date_range(start='2020-01-05', end='2020-02-04',freq='2D')})

    From    To
0   2020-01-01  2020-01-05
1   2020-01-03  2020-01-07
2   2020-01-05  2020-01-09
3   2020-01-07  2020-01-11
4   2020-01-09  2020-01-13
5   2020-01-11  2020-01-15
6   2020-01-13  2020-01-17
7   2020-01-15  2020-01-19
8   2020-01-17  2020-01-21
9   2020-01-19  2020-01-23
10  2020-01-21  2020-01-25
11  2020-01-23  2020-01-27
12  2020-01-25  2020-01-29
13  2020-01-27  2020-01-31
14  2020-01-29  2020-02-02
15  2020-01-31  2020-02-04

我有一个数据框，可以像这样保持每日的高值和低值

random.seed(0)
df2= pd.DataFrame({'Date': pd.date_range(start='2020-01-01', end='2020-01-31'), 'High': [random.randint(7,15)+5 for i in range(31)], 'Low': [random.randint(0,7)-1 for i in range(31)]})

    Date    High    Low
0   2020-01-01  18  6
1   2020-01-02  18  6
2   2020-01-03  12  3
3   2020-01-04  16  -1
4   2020-01-05  20  -1
5   2020-01-06  19  0
6   2020-01-07  18  5
7   2020-01-08  16  -1
8   2020-01-09  19  6
9   2020-01-10  17  4
10  2020-01-11  15  2
11  2020-01-12  20  4
12  2020-01-13  14  0
13  2020-01-14  16  2
14  2020-01-15  14  2
15  2020-01-16  13  2
16  2020-01-17  16  1
17  2020-01-18  20  6
18  2020-01-19  14  0
19  2020-01-20  16  0
20  2020-01-21  13  4
21  2020-01-22  13  6
22  2020-01-23  17  0
23  2020-01-24  19  3
24  2020-01-25  20  3
25  2020-01-26  13  0
26  2020-01-27  17  4
27  2020-01-28  18  2
28  2020-01-29  17  3
29  2020-01-30  15  6
30  2020-01-31  20  0

然后我希望根据df1中的From Date和To Date得到最大值和最小值，这是预期的结果:

result = pd.DataFrame({'From': pd.date_range(start='2020-01-01', end='2020-01-31',freq='2D'), 'To': pd.date_range(start='2020-01-05', end='2020-02-04',freq='2D'), 'High':[20,20,20,19,20,20,16,20,20,17,20,20,20,20,20,20], 'Low':[-1,-1,-1,-1,0,0,1,0,0,0,0,0,0,0,0,0]})

    From    To  High    Low
0   2020-01-01  2020-01-05  20  -1
1   2020-01-03  2020-01-07  20  -1
2   2020-01-05  2020-01-09  20  -1
3   2020-01-07  2020-01-11  19  -1
4   2020-01-09  2020-01-13  20  0
5   2020-01-11  2020-01-15  20  0
6   2020-01-13  2020-01-17  16  1
7   2020-01-15  2020-01-19  20  0
8   2020-01-17  2020-01-21  20  0
9   2020-01-19  2020-01-23  17  0
10  2020-01-21  2020-01-25  20  0
11  2020-01-23  2020-01-27  20  0
12  2020-01-25  2020-01-29  20  0
13  2020-01-27  2020-01-31  20  0
14  2020-01-29  2020-02-02  20  0
15  2020-01-31  2020-02-04  20  0

我尝试过使用重采样方法，但似乎不支持自定义日期范围。我正在寻找一种相当有效和优雅的方式来做到这一点。非常感谢。

Answer 1

考虑到数据的大小，我认为您应该考虑另一种方法，这个想法是通过 df1 按 block 矢量化日期与 df2 之间的比较。它比其他解决方案多了很多行，但对于大型数据帧来说会更快。

# this is a parameter you can play with, 
# but if your df1 is in memory, this value should work
nb_split = int((len(df1)*len(df2))//4e6)+1

# work with arrays of flaot
arr1 = df1[['From','To']].astype('int64').to_numpy().astype(float)
arr2 = df2.astype('int64').to_numpy().astype(float)
# create result array
arr_out = np.zeros((len(arr1), 2), dtype=float)
i = 0 #index position
for arr1_sp in np.array_split(arr1, nb_split, axis=0):
    # get length of the chunk
    lft = len(arr1_sp)
    # get the min datetime in From and max in To
    min_from = arr1_sp[:, 0].min()
    max_to = arr1_sp[:, 1].max()

    # select the rows of arr2 tht are within the min and max date of the split
    arr2_sp = arr2[(arr2[:,0]>=min_from)&(arr2[:,0]<=max_to), :]

    # create an bool arraywith True when the date in arr2_sp is above from and below to
    # each row is the reuslt for each row of arr1_sp
    m = np.less_equal.outer(arr1_sp[:,0], arr2_sp[:, 0])\
        &np.greater_equal.outer(arr1_sp[:,1], arr2_sp[:, 0])

    # use this mask to get the values high and low within the range row-wise
    # and replace where the mask was False by np.nan
    arr_high = arr2_sp[:,1]*m
    arr_high[~m] = np.nan
    arr_low = arr2_sp[:,2]*m
    arr_low[~m] = np.nan

    # put the result in the result array
    arr_out[i:i+lft, 0] = np.nanmax(arr_high, axis=1)
    arr_out[i:i+lft, 1] = np.nanmin(arr_low, axis=1)
    i += lft #update first idx position for next loop

# create the columns in df1
df1['High'] = arr_out[:, 0]
df1['Low'] = arr_out[:, 1]

我尝试使用 df1 的 10000 行和 df2 的 5000 行，这个方法大约需要 102 毫秒，而使用 getHighLow2 的方法大约需要 8 秒，所以这种方式快了 80 倍。 Adn 结果相同。