如何对Pandas DataFrame中每个ID按指定日期范围重采样补全测试记录？

要实现每个ID与当周所有测试一一对应，并补全received字段，核心思路是按周生成ID与测试的全量组合，再与原数据匹配填充值，具体步骤如下：

步骤1：预处理日期与全局ID集合 #

首先将date列转为日期类型，并提取所有出现过的ID（确保所有ID在每周都能被覆盖）：

import pandas as pd

# 原数据创建
d = {
    "date": [
        "2023-01-02",
        "2023-01-02",
        "2023-01-02",
        "2023-01-02",
        "2023-01-02",
        "2023-01-09",
        "2023-01-09",
        "2023-01-09",
    ],
    "id": ["a1", "c3", "e5", "b2", "d4", "a1", "b2", "c3"],
    "test": ["a", "a", "a", "b", "b", "c", "c", "c"],
    "received": [1, 1, 1, 1, 1, 1, 1, 1],
}
df = pd.DataFrame(data=d)

# 日期转格式，提取全局ID列表
df['date'] = pd.to_datetime(df['date'])
all_ids = df['id'].unique().tolist()

步骤2：按周生成全量组合并匹配数据 #

按周（date）分组，对每个周生成所有ID × 当周所有测试的笛卡尔积，再与原数据左连接，最后将缺失的received值填充为0：

# 按周分组处理
result_list = []
for week_date, group in df.groupby('date'):
    # 获取当周的所有测试类型
    week_tests = group['test'].unique().tolist()
    # 生成ID与测试的全量组合
    full_combinations = pd.MultiIndex.from_product(
        [all_ids, week_tests],
        names=['id', 'test']
    ).to_frame(index=False)
    # 添加周起始日期并格式化
    full_combinations['week_starting'] = week_date.strftime('%d/%m/%Y')
    # 与原数据匹配received值
    group_sub = group[['id', 'test', 'received']]
    merged = full_combinations.merge(group_sub, on=['id', 'test'], how='left')
    # 填充缺失值为0并转为整数
    merged['received'] = merged['received'].fillna(0).astype(int)
    # 调整列顺序与期望输出一致
    merged = merged[['week_starting', 'id', 'test', 'received']]
    result_list.append(merged)

# 合并所有周的结果
final_df = pd.concat(result_list, ignore_index=True)
print(final_df)

输出结果 #

运行上述代码后，将得到与期望完全一致的输出：

week_starting  id test  received
0     02/01/2023  a1    a         1
1     02/01/2023  a1    b         0
2     02/01/2023  c3    a         1
3     02/01/2023  c3    b         0
4     02/01/2023  e5    a         1
5     02/01/2023  e5    b         0
6     02/01/2023  b2    a         0
7     02/01/2023  b2    b         1
8     02/01/2023  d4    a         0
9     02/01/2023  d4    b         1
10    09/01/2023  a1    c         1
11    09/01/2023  c3    c         1
12    09/01/2023  e5    c         0
13    09/01/2023  b2    c         1
14    09/01/2023  d4    c         0

关键说明 #

• 使用MultiIndex.from_product生成ID与测试的全量组合，确保每个ID都能匹配当周所有测试；
• 通过左连接原数据保留已有的received=1记录，缺失值填充为0实现补全；
• 若你的日期并非周起始日（比如是任意日期），可改用df['date'].dt.to_period('W').dt.start_time来获取每周的起始日期，替换代码中的week_date即可。

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