如何合并不同长度的groupby数据为DataFrame并解决IndexError索引越界

核心问题分析 #

现有代码存在几个关键错误，直接导致了索引越界及运行异常：

• 元组索引错误：qn是(Worker_ID, Project_ID)的元组，你用qn[2]、qn[3]会触发索引越界，正确应该用qn[0]取Worker_ID，qn[1]取Project_ID。
• 循环逻辑错误：直接按未定义的max_length循环，未按Project维度独立处理，且默认question_1/question_2/question_3的索引一一对应，实际每个Project下的Worker分组是独立的，长度并不匹配。
• 未定义变量：max_length、four_question、merged_df5、merged_df3均未声明，会直接导致运行报错。

正确实现步骤 #

我们需要按Project_ID维度拆分处理，每个Project内将不同Worker的条目按行对齐（短分组补"Blank"），最后合并成目标结构。

代码实现 #

import pandas as pd

# 假设原始数据存储在df1中
final_dfs = []

# 按Project_ID分组，逐个处理每个项目的数据
for project_id, project_df in df1.groupby("Project_ID"):
    # 项目内按Worker_ID拆分，得到每个工人的子数据集
    worker_groups = project_df.groupby("Worker_ID")
    worker_dfs = {wid: df.reset_index(drop=True) for wid, df in worker_groups}
    
    # 获取当前项目下所有工人数据集的最大行数，用于对齐补全
    max_rows = max(len(df) for df in worker_dfs.values()) if worker_dfs else 0
    
    # 补全每个工人的数据集到最大行数，空值填充"Blank"
    filled_dfs = {}
    for wid, df in worker_dfs.items():
        add_rows = max_rows - len(df)
        if add_rows > 0:
            blank_rows = pd.DataFrame(
                {"Question": ["Blank"]*add_rows,
                 "Answer": ["Blank"]*add_rows,
                 "Worker_ID": ["Blank"]*add_rows}
            )
            filled_df = pd.concat([df, blank_rows], ignore_index=True)
        else:
            filled_df = df.copy()
        filled_dfs[wid] = filled_df
    
    # 按Worker对应后缀重命名列并横向合并
    merged = pd.DataFrame()
    
    # 处理Worker X（对应_1后缀）
    if "X" in filled_dfs:
        x_df = filled_dfs["X"].rename(columns={
            "Question": "Question_1", "Answer": "Answer_1", "Worker_ID": "Worker_ID_1"
        })
        merged = pd.concat([merged, x_df], axis=1)
    else:
        blank_x = pd.DataFrame({
            "Question_1": ["Blank"]*max_rows,
            "Answer_1": ["Blank"]*max_rows,
            "Worker_ID_1": ["Blank"]*max_rows
        })
        merged = pd.concat([merged, blank_x], axis=1)
    
    # 处理Worker K（对应_2后缀）
    if "K" in filled_dfs:
        k_df = filled_dfs["K"].rename(columns={
            "Question": "Question_2", "Answer": "Answer_2", "Worker_ID": "Worker_ID_2"
        })
        merged = pd.concat([merged, k_df], axis=1)
    else:
        blank_k = pd.DataFrame({
            "Question_2": ["Blank"]*max_rows,
            "Answer_2": ["Blank"]*max_rows,
            "Worker_ID_2": ["Blank"]*max_rows
        })
        merged = pd.concat([merged, blank_k], axis=1)
    
    # 处理Worker J（对应_3后缀）
    if "J" in filled_dfs:
        j_df = filled_dfs["J"].rename(columns={
            "Question": "Question_3", "Answer": "Answer_3", "Worker_ID": "Worker_ID_3"
        })
        merged = pd.concat([merged, j_df], axis=1)
    else:
        blank_j = pd.DataFrame({
            "Question_3": ["Blank"]*max_rows,
            "Answer_3": ["Blank"]*max_rows,
            "Worker_ID_3": ["Blank"]*max_rows
        })
        merged = pd.concat([merged, blank_j], axis=1)
    
    # 添加Project_ID列并调整列顺序
    merged["Project_ID"] = project_id
    merged = merged[[
        "Question_1", "Answer_1", "Worker_ID_1",
        "Question_2", "Answer_2", "Worker_ID_2",
        "Question_3", "Answer_3", "Worker_ID_3",
        "Project_ID"
    ]]
    
    final_dfs.append(merged)

# 合并所有项目的结果
final_df = pd.concat(final_dfs, ignore_index=True)
print(final_df)

代码说明 #

1. 按Project独立处理：避免跨项目的数据混乱，确保每个项目内的工人数据对齐。
2. 空白行补全：以当前项目最长的工人数据集为基准，给短数据集补全"Blank"行，解决长度不一致问题。
3. 缺失Worker处理：如果项目中没有某个工人的记录，直接生成全"Blank"的对应列，保证结构统一。
4. 列顺序对齐：最终调整列顺序与目标结构完全匹配。

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