如何按名称分组，基于重叠时间区间计算DataFrame均值

现有两个Pandas DataFrame：

• df1包含name、time_start、time_end列，记录不同名称对应的时间区间
• df2包含name、time、values列，记录带时间戳的数值数据

需求：按name分组，计算df2中time落在df1对应名称的时间区间内的values均值，最终输出需保留df1的所有列，并添加计算得到的均值列。

数据示例 #

df1 代码定义 #

import pandas as pd

df1 = (pd.DataFrame({'name': ['a', 'a', 'a', 'a', 'b'],
              'time_start': ['2000-01-01 00:01:12',
                            '2000-01-01 00:02:12',
                            '2000-01-01 00:03:12',
                            '2000-01-01 00:04:12',
                            '2000-01-01 00:05:12'],
              'time_end': ['2000-01-01 00:01:18',
                            '2000-01-01 00:02:22',
                            '2000-01-01 00:03:24',
                            '2000-01-01 00:04:40',
                            '2000-01-01 00:05:14']})
 .assign(time_start = lambda x: pd.to_datetime(x['time_start']),
        time_end = lambda x: pd.to_datetime(x['time_end'])))

df2 代码定义 #

df2 = (pd.DataFrame({'name': ['a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'b'],
              'time': ['2000-01-01 00:01:12',
                            '2000-01-01 00:01:13',
                            '2000-01-01 00:01:14',
                            '2000-01-01 00:02:13',
                            '2000-01-01 00:02:20',
                            '2000-01-01 00:03:12',
                            '2000-01-01 00:04:12',
                            '2000-01-01 00:04:30',
                            '2000-01-01 00:05:12'],
             'values': [10,20, 30, 40,50,60,70,80, 90]})
 .assign(time = lambda x: pd.to_datetime(x['time'])))

预期输出 #

name           time_start             time_end  values
0     a 2000-01-01 00:01:12 2000-01-01 00:01:18      20
1     a 2000-01-01 00:02:12 2000-01-01 00:02:22      45
2     a 2000-01-01 00:03:12 2000-01-01 00:03:24      60
3     a 2000-01-01 00:04:12 2000-01-01 00:04:40      75
4     b 2000-01-01 00:05:12 2000-01-01 00:05:14      90

解决方案 #

方法1：合并筛选后分组计算 #

逻辑直观，适合数据量不大的场景：

# 按name合并两个DataFrame
merged = pd.merge(df1, df2, on='name')
# 筛选时间落在对应区间内的记录
mask = (merged['time'] >= merged['time_start']) & (merged['time'] <= merged['time_end'])
filtered = merged[mask]
# 按df1的原始索引分组，计算每个区间的values均值
mean_values = filtered.groupby(df1.index)['values'].mean().reset_index()
# 将均值合并回df1，得到最终结果
final_result = df1.join(mean_values.rename(columns={'values': 'values'}))
print(final_result)

方法2：使用merge_asof高效匹配区间 #

merge_asof是Pandas专门用于时间区间匹配的高效方法，适合大数据量场景：

# 对两个DataFrame按name和时间列排序（merge_asof要求排序）
df2_sorted = df2.sort_values(['name', 'time'])
df1_sorted = df1.sort_values(['name', 'time_start'])

# 按name匹配，将df2的时间映射到df1对应的区间
matched = pd.merge_asof(
    df2_sorted,
    df1_sorted,
    on='time',
    by='name',
    direction='backward',
    allow_exact_matches=True
)

# 按df1的区间分组计算均值，并恢复原df1的顺序
result = matched.groupby(['name', 'time_start', 'time_end'])['values'].mean().reset_index()
final_result = result.reindex(df1.index)
print(final_result)

方法3：分组后逐区间计算 #

通过分组apply，对每个name下的区间单独计算均值：

def compute_interval_mean(df1_group):
    name = df1_group['name'].iloc[0]
    # 取出当前name对应的df2数据
    df2_sub = df2[df2['name'] == name]
    # 对每个区间计算values均值
    df1_group['values'] = df1_group.apply(
        lambda row: df2_sub[(df2_sub['time'] >= row['time_start']) & 
                            (df2_sub['time'] <= row['time_end'])]['values'].mean(),
        axis=1
    )
    return df1_group

# 按name分组处理，得到最终结果
final_result = df1.groupby('name', group_keys=False).apply(compute_interval_mean)
print(final_result)

以上三种方法均可得到与预期一致的输出，可根据数据规模选择合适的方案。

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