如何高效填充股票停牌期间的缺失交易数据？

问题背景 #

处理股票交易数据时，数据集仅包含实际交易日的记录，股票停牌期间无对应数据，需要将这些缺失日期的收盘价填充为0。

当前数据示例 #

Trading Date | Stock Code | Closing Price
2023-08-01   | Stock A    | 35.00
2023-08-02   | Stock A    | 34.00
2023-08-04   | Stock A    | 35.79
2023-08-07   | Stock A    | 33.26
2023-08-08   | Stock A    | 33.88
2023-08-09   | Stock A    | 33.73
2023-08-10   | Stock A    | 31.00
2023-08-01   | Stock B    | 25.00
2023-08-02   | Stock B    | 24.00
2023-08-03   | Stock B    | 22.00
2023-08-04   | Stock B    | 25.79
2023-08-09   | Stock B    | 21.00
2023-08-10   | Stock B    | 31.00
...          | ...        | ...

缺失情况 #

• Stock A在2023-08-03停牌，无数据
• Stock B在2023-08-07至2023-08-08停牌，无数据

期望结果 #

Trading Date | Stock Code | Closing Price
2023-08-01   | Stock A    | 35.00
2023-08-02   | Stock A    | 34.00
2023-08-03   | Stock A    | 0.00  ← 填充的停牌日数据
2023-08-04   | Stock A    | 35.79
2023-08-07   | Stock A    | 33.26
2023-08-08   | Stock A    | 33.88
2023-08-09   | Stock A    | 33.73
2023-08-10   | Stock A    | 31.00
2023-08-01   | Stock B    | 25.00
2023-08-02   | Stock B    | 24.00
2023-08-03   | Stock B    | 22.00
2023-08-04   | Stock B    | 25.79
2023-08-07   | Stock B    | 0.00  ← 填充的停牌日数据
2023-08-08   | Stock B    | 0.00  ← 填充的停牌日数据
2023-08-09   | Stock B    | 21.00
2023-08-10   | Stock B    | 31.00
...          | ...        | ...

当前实现方案 #

核心步骤 #

1. 提取所有唯一股票代码和交易日期
2. 生成所有日期-股票的笛卡尔积组合
3. 将原始数据与完整组合合并，缺失值填充为0

Python实现代码 #

# 获取所有股票和日期
all_stocks = df['Stock Code'].unique()
all_dates = df['Trading Date'].unique()

# 生成完整组合索引
full_index = pd.MultiIndex.from_product(
    [all_dates, all_stocks],
    names=['Trading Date', 'Stock Code']
)

# 合并并填充缺失值
result = (
    df.set_index(['Trading Date', 'Stock Code'])
    .reindex(full_index, fill_value=0)
    .reset_index()
)

DolphinDB实现代码 #

res = select code, date, nullFill(val, 0) as val 
      from lj(cj(table(distinct(t["code"]) as code), table(marketDay as date)), t, `code`date)

提问 #

针对海量股票场景，是否有更高效的实现方式？

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