为带MultiIndex的Pandas DataFrame补全缺失日期

原始数据 #

SaleQty  SaleAmount  OrderQty  OrderAmount
NmId    DateUtc                                               
6470000 2022-10-12        0        0.00         1       724.52
        2022-10-16        1      724.52         0         0.00
6470001 2023-06-18        0        0.00         0         0.00
        2023-06-19        0        0.00         0         0.00

期望结果 #

SaleQty  SaleAmount  OrderQty  OrderAmount
NmId    DateUtc                                               
6470000 2022-10-12        0        0.00         1       724.52
        2022-10-13        0        0.00         0         0.00
        2022-10-14        0        0.00         0         0.00
        2022-10-15        0        0.00         0         0.00
        2022-10-16        1      724.52         0         0.00
6470001 2023-06-18        0        0.00         0         0.00
        2023-06-19        0        0.00         0         0.00

尝试过的方法及问题 #

使用笛卡尔积生成全组合索引后重新索引，代码如下：

new_index = pd.MultiIndex.from_product(df.index.levels)
new_df = df.reindex(new_index)

但该方法会生成所有NmId与所有日期的组合，不符合需求，得到错误结果：

SaleQty  SaleAmount  OrderQty  OrderAmount
NmId    DateUtc                                               
6470000 2022-10-12        0        0.00         1       724.52
        2022-10-13        0        0.00         0         0.00
        2022-10-14        0        0.00         0         0.00
        2022-10-15        0        0.00         0         0.00
        2022-10-16        1      724.52         0         0.00
        2023-06-18        0        0.00         0         0.00
        2023-06-19        0        0.00         0         0.00
6470001 2022-10-12        0        0.00         0         0.00
        2022-10-13        0        0.00         0         0.00
        2022-10-14        0        0.00         0         0.00
        2022-10-15        0        0.00         0         0.00
        2022-10-16        0        0.00         0         0.00
        2023-06-18        0        0.00         0         0.00
        2023-06-19        0        0.00         0         0.00

数据字典 #

{'SaleQty': {(6472924, pd.Timestamp('2022-10-12 00:00:00')): 0,
  (6472924, pd.Timestamp('2022-10-21 00:00:00')): 1,
  (6637841, pd.Timestamp('2023-06-18 00:00:00')): 0,
  (6637841, pd.Timestamp('2023-06-19 00:00:00')): 0},
 'SaleAmount': {(6472924, pd.Timestamp('2022-10-12 00:00:00')): 0.0,
  (6472924, pd.Timestamp('2022-10-21 00:00:00')): 724.52,
  (6637841, pd.Timestamp('2023-06-18 00:00:00')): 0.0,
  (6637841, pd.Timestamp('2023-06-19 00:00:00')): 0.0},
 'OrderQty': {(6472924, pd.Timestamp('2022-10-12 00:00:00')): 1,
  (6472924, pd.Timestamp('2022-10-21 00:00:00')): 0,
  (6637841, pd.Timestamp('2023-06-18 00:00:00')): 0,
  (6637841, pd.Timestamp('2023-06-19 00:00:00')): 0},
 'OrderAmount': {(6472924, pd.Timestamp('2022-10-12 00:00:00')): 724.52,
  (6472924, pd.Timestamp('2022-10-21 00:00:00')): 0.0,
  (6637841, pd.Timestamp('2023-06-18 00:00:00')): 0.0,
  (6637841, pd.Timestamp('2023-06-19 00:00:00')): 0.0}}

解决方案 #

方法一：单独处理目标NmId后合并 #

针对指定NmId生成其日期范围内的完整序列，再与其他数据合并：

import pandas as pd

# 目标NmId
target_nmid = 6470000

# 提取目标NmId的数据并重置索引
target_df = df.xs(target_nmid, level='NmId').reset_index()

# 生成该NmId的完整日期序列
min_date = target_df['DateUtc'].min()
max_date = target_df['DateUtc'].max()
full_dates = pd.date_range(start=min_date, end=max_date, freq='D')

# 重新索引并填充缺失值为0，恢复MultiIndex
target_full_df = target_df.set_index('DateUtc').reindex(full_dates).fillna(0).reset_index()
target_full_df['NmId'] = target_nmid
target_full_df = target_full_df.set_index(['NmId', 'DateUtc'])

# 提取其他NmId的数据
other_df = df[df.index.get_level_values('NmId') != target_nmid]

# 合并并排序索引得到最终结果
final_df = pd.concat([target_full_df, other_df]).sort_index()

方法二：分组批量处理（适用于多NmId补全） #

如果需要为多个NmId分别补全各自的日期范围，可使用分组处理：

import pandas as pd

def fill_missing_dates(group):
    # 生成当前组的完整日期索引
    nmid = group.index.get_level_values('NmId')[0]
    min_date = group.index.get_level_values('DateUtc').min()
    max_date = group.index.get_level_values('DateUtc').max()
    full_index = pd.MultiIndex.from_product(
        [[nmid], pd.date_range(min_date, max_date, freq='D')],
        names=['NmId', 'DateUtc']
    )
    return group.reindex(full_index).fillna(0)

# 仅对目标NmId执行补全，其他数据保留原样
target_groups = df[df.index.get_level_values('NmId') == target_nmid].groupby('NmId').apply(fill_missing_dates)
other_df = df[df.index.get_level_values('NmId') != target_nmid]

final_df = pd.concat([target_groups, other_df]).sort_index()

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