在给定另一个数据帧中两列的值约束的情况下，在一个数据帧的列中查找最大值

一种选择是通过 pyjanitor 的conditional_join来模拟大于和小于条件，然后再进行分组和聚合：

# pip install pyjanitor
import pandas as pd
import janitor

(df1.conditional_join(
            df2, 
            ('ST', 'Time', '<='), 
            ('ET', 'Time', '>='))
    .groupby(['ST', 'ET'], as_index = False)
    .stock
    .max()
)
                   ST                  ET  stock
0 2021-07-11 01:00:00 2021-07-11 02:00:00     48
1 2021-07-11 02:00:00 2021-07-11 03:00:00     48
2 2021-07-11 03:00:00 2021-07-11 04:00:00     80
3 2021-07-11 04:00:00 2021-07-11 05:00:00     81
4 2021-07-11 05:00:00 2021-07-11 06:00:00     83
5 2021-07-11 06:00:00 2021-07-11 07:00:00     84
6 2021-07-11 07:00:00 2021-07-11 08:00:00     65
7 2021-07-11 08:00:00 2021-07-11 09:00:00     80
8 2021-07-11 09:00:00 2021-07-11 10:00:00     80

之后可以使用笛卡尔联接和过滤器（对于大型数据帧，这可能是内存效率低下）：

(df1.merge(df2, how='cross')
    .query('ST <=Time <= ET')
    .groupby(['ST', 'ET'], as_index = False)
    .stock
    .max()
)
Out[113]:
                   ST                  ET  stock
0 2021-07-11 01:00:00 2021-07-11 02:00:00     48
1 2021-07-11 02:00:00 2021-07-11 03:00:00     48
2 2021-07-11 03:00:00 2021-07-11 04:00:00     80
3 2021-07-11 04:00:00 2021-07-11 05:00:00     81
4 2021-07-11 05:00:00 2021-07-11 06:00:00     83
5 2021-07-11 06:00:00 2021-07-11 07:00:00     84
6 2021-07-11 07:00:00 2021-07-11 08:00:00     65
7 2021-07-11 08:00:00 2021-07-11 09:00:00     80
8 2021-07-11 09:00:00 2021-07-11 10:00:00     80

另一种选择是使用区间索引（此处的过程较长，因为生成的区间具有重叠值）：

box = pd.IntervalIndex.from_arrays(df1.ST, df1.ET, closed='both')
df1.index = box

# create temporary Series
temp = (df2.Time
           .apply(lambda x: box[box.get_loc(x)])
           .explode(ignore_index = False)
         )
temp.name = 'interval'

# lump back to main dataframe (df2)
temp = pd.concat([df2, temp], axis = 1)

# aggregate:

temp = temp.groupby('interval').stock.max()

# join back to df1 to get final output

df1.join(temp).reset_index(drop=True)

                   ST                  ET  stock
0 2021-07-11 01:00:00 2021-07-11 02:00:00     48
1 2021-07-11 02:00:00 2021-07-11 03:00:00     48
2 2021-07-11 03:00:00 2021-07-11 04:00:00     80
3 2021-07-11 04:00:00 2021-07-11 05:00:00     81
4 2021-07-11 05:00:00 2021-07-11 06:00:00     83
5 2021-07-11 06:00:00 2021-07-11 07:00:00     84
6 2021-07-11 07:00:00 2021-07-11 08:00:00     65
7 2021-07-11 08:00:00 2021-07-11 09:00:00     80
8 2021-07-11 09:00:00 2021-07-11 10:00:00     80