如何基于df2时间列计算df1各时间区间对应的最大库存值

我有一个DataFrame df1，包含两列分别代表任务的开始时间ST和结束时间ET。另有一个DataFrame df2，包含两列分别代表时间点Time和对应时间的可用库存stock。我需要在df1中新增名为max_stock的列，取值为df1每行ST和ET对应的时间区间内，df2的stock列的最大值。例如第一个任务的开始时间为7/11/2021 1:00，结束时间为7/11/2021 2:00，对应的max_stock值就是df2中7/11/2021 1:00的10、7/11/2021 1:30的26、7/11/2021 2:00的48三个值的最大值。

示例数据 #

df1 #

ST              ET
7/11/2021 1:00  7/11/2021 2:00
7/11/2021 2:00  7/11/2021 3:00
7/11/2021 3:00  7/11/2021 4:00
7/11/2021 4:00  7/11/2021 5:00
7/11/2021 5:00  7/11/2021 6:00
7/11/2021 6:00  7/11/2021 7:00
7/11/2021 7:00  7/11/2021 8:00
7/11/2021 8:00  7/11/2021 9:00
7/11/2021 9:00  7/11/2021 10:00

df2 #

Time            stock
7/11/2021 1:00  10
7/11/2021 1:30  26
7/11/2021 2:00  48
7/11/2021 2:30  35
7/11/2021 3:00  32
7/11/2021 3:30  80
7/11/2021 4:00  31
7/11/2021 4:30  81
7/11/2021 5:00  65
7/11/2021 5:30  83
7/11/2021 6:00  40
7/11/2021 6:30  84
7/11/2021 7:00  41
7/11/2021 7:30  15
7/11/2021 8:00  65
7/11/2021 8:30  18
7/11/2021 9:00  80
7/11/2021 9:30  12
7/11/2021 10:00 5

期望输出 #

ST              ET              max_stock
7/11/2021 1:00  7/11/2021 2:00  48.00
7/11/2021 2:00  7/11/2021 3:00  48.00
7/11/2021 3:00  7/11/2021 4:00  80.00
7/11/2021 4:00  7/11/2021 5:00  81.00
7/11/2021 5:00  7/11/2021 6:00  83.00
7/11/2021 6:00  7/11/2021 7:00  84.00
7/11/2021 7:00  7/11/2021 8:00  65.00
7/11/2021 8:00  7/11/2021 9:00  80.00
7/11/2021 9:00  7/11/2021 10:00 80.00

解决方法 #

首先将所有时间列转换为pandas datetime类型，避免字符串比较出现误差：

import pandas as pd

# 统一转换时间格式
df1['ST'] = pd.to_datetime(df1['ST'])
df1['ET'] = pd.to_datetime(df1['ET'])
df2['Time'] = pd.to_datetime(df2['Time'])

普通数据量下直接用apply逐行计算即可，逻辑清晰易读：

def get_interval_max(row):
    # 筛选落在当前行时间区间内的库存数据
    match_data = df2[(df2['Time'] >= row['ST']) & (df2['Time'] <= row['ET'])]
    return match_data['stock'].max()

df1['max_stock'] = df1.apply(get_interval_max, axis=1).round(2)

如果数据量较大，可以将df2设置时间索引提升查询性能：

df2 = df2.set_index('Time').sort_index()
df1['max_stock'] = df1.apply(
    lambda x: df2.loc[x['ST']:x['ET'], 'stock'].max(),
    axis=1
).round(2)

内容的提问来源于stack exchange，提问作者vp_050