如何在Pandas中基于另一DataFrame的日期索引分组/平均数据？

核心思路 #

以价格数据的日期为分界点划分时间区间，将每日汇率数据映射到对应区间后计算平均值，最终合并到价格数据中。

步骤实现 #

1. 数据预处理

先确保两个DataFrame的索引为datetime类型，同时将逗号分隔的数值转换为浮点数：

import pandas as pd

# 构造价格数据
prices_data = {"Prices": ["0,56", "0,1", "0,22", "0,19", "0,29", "0,13"]}
prices_index = pd.to_datetime(["10.01.2022", "14.01.2022", "20.01.2022", "28.01.2022", "04.02.2022", "11.02.2022"], dayfirst=True)
df_prices = pd.DataFrame(prices_data, index=prices_index)
df_prices["Prices"] = df_prices["Prices"].str.replace(",", ".").astype(float)

# 构造汇率数据
fx_data = {
    "FX": ["11,71", "11,74", "11,80", "11,89", "11,98", "11,80", "11,80", "11,73", "11,76", 
           "11,99", "12,01", "11,96", "11,88", "11,80", "11,74", "11,73"]
}
fx_index = pd.to_datetime(["03.01.2022", "04.01.2022", "05.01.2022", "06.01.2022", "07.01.2022", 
                          "10.01.2022", "11.01.2022", "12.01.2022", "13.01.2022", "03.02.2022", 
                          "04.02.2022", "07.02.2022", "08.02.2022", "09.02.2022", "10.02.2022", "11.02.2022"], 
                         dayfirst=True)
df_fx = pd.DataFrame(fx_data, index=fx_index)
df_fx["FX"] = df_fx["FX"].str.replace(",", ".").astype(float)

2. 创建日期区间

以价格数据的日期为分界点，结合汇率数据的最早日期生成右闭合区间：

# 获取价格日期列表，插入汇率最早日期作为起始点
price_dates = df_prices.index.tolist()
price_dates.insert(0, df_fx.index.min())

# 创建区间索引
intervals = pd.IntervalIndex.from_arrays(price_dates[:-1], price_dates[1:], closed='right')

3. 映射汇率到区间并计算平均值

将每条汇率数据分配到对应区间，按区间分组计算平均汇率：

# 为汇率数据标记所属区间
df_fx["interval"] = pd.cut(df_fx.index, bins=intervals)

# 计算每个区间的汇率平均值
fx_avg = df_fx.groupby("interval")["FX"].mean()

4. 合并结果并格式化

将平均值匹配到价格数据中，设置第一个日期的平均值为n/a，并还原逗号分隔的数值格式：

# 合并平均值到价格数据
df_prices["FX avr"] = fx_avg.reindex(intervals).values

# 第一个价格日期无前置区间，设为n/a
df_prices.iloc[0, df_prices.columns.get_loc("FX avr")] = pd.NA

# 格式化数值为逗号分隔格式
df_prices["Prices"] = df_prices["Prices"].apply(lambda x: f"{x:.2f}".replace(".", ","))
df_prices["FX avr"] = df_prices["FX avr"].apply(lambda x: f"{x:.2f}".replace(".", ",") if pd.notna(x) else "n/a")

最终输出

执行代码后，df_prices将输出与期望一致的结果：

Prices FX avr
2022-01-10  0,56   n/a
2022-01-14  0,10   11,80
2022-01-20  0,22   ...
...

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