我有一个参差不齐(意思是不规则的频率)、时间索引的 DataFrame,我想对其执行时间加权滚动平均值,以维护 DataFrame 的原始索引。假设记录的值在被另一个值取代之前一直有效。实现此目的的一种方法是将参差不齐的 DataFrame 上采样到统一频率,然后进行滚动平均值:
import pandas as pd
import numpy as np
def time_weighted_average_using_upsampling(df: pd.DataFrame, avg_window: str) -> pd.DataFrame:
# Leads to high memory usage
original_index = df.index.copy()
avg = (
df.resample("1s")
.ffill()
.rolling(avg_window, closed="left", min_periods=int(avg_window[0])))
.mean()
.reindex(original_index)
)
return avg
if __name__ == "__main__":
df = pd.DataFrame(
{"A": [0, 1, 2, 3, 4, 5]},
index=[
pd.Timestamp("20130101 09:00:00"),
pd.Timestamp("20130101 09:00:02"),
pd.Timestamp("20130101 09:00:03"),
pd.Timestamp("20130101 09:00:05"),
pd.Timestamp("20130101 09:00:06"),
pd.Timestamp("20130101 09:00:10"),
],
)
expected_avg = pd.DataFrame(
{"A": [np.nan, np.nan, 1 / 3, 5 / 3, 7 / 3, 4]},
index=[
pd.Timestamp("20130101 09:00:00"),
pd.Timestamp("20130101 09:00:02"),
pd.Timestamp("20130101 09:00:03"),
pd.Timestamp("20130101 09:00:05"),
pd.Timestamp("20130101 09:00:06"),
pd.Timestamp("20130101 09:00:10"),
],
)
pd.testing.assert_frame_equal(
time_weighted_average_using_upsampling(df=df, avg_window="3s"), expected_avg
)
问题在于,上采样违背了不规则 df 提供的稀疏表示的目的。稀疏表示具有内存效率,而上采样版本则不然。这就引出了一个问题:如何实现上面所示的结果without必须对整个 df 进行上采样吗?