Python解析嵌套分组Excel数据至字典：数据错位问题求助

数据结构说明 #

原始Excel表格的层级结构如下：

目标输出结构 #

需要转换为嵌套字典结构，键为(建筑名, 单元名)，对应值为房间-产品数量的字典：

{
    ('Building 16', 'Unit 1A'): {
        'Bedroom 1 WIC': {'item': 2},
        'Bedroom 2 RIC': {'item': 1},
        ...
    },
    ...
}

现有代码及问题 #

编写的collect_data函数存在数据错位问题，第一个房间的产品数量未被正确关联，后续房间数据对应错误：

def collect_data(df, products, room_wall_codes):
    data_collection = {}
    current_building = None
    current_unit = None
    room_data = []
    current_room_data = initialize_product_dict(products)

    for _, row in df.iterrows():
        # Detect a new unit
        if "Bld" in row['Group'] or "Building" in row['Group']:
            # Save the previous unit's data if available
            if current_building and current_unit:
                data_collection[(current_building, current_unit)] = room_data
                room_data = []
            current_building, current_unit = extract_building_and_unit(row['Group'])
            continue

        # Detect a new room
        if pd.isna(row['Item Name']):
            # Save the previous room's data
            if current_room_data:
                room_data.append(current_room_data)
                current_room_data = initialize_product_dict(products)
            continue

        # Collect product data for the current room
        if row['Item Name'] in current_room_data:
            current_room_data[row['Item Name']] += row['QTY']

    # Save the last room's data
    if current_room_data:
        room_data.append(current_room_data)

    # Save the last unit's data
    if current_building and current_unit:
        data_collection[(current_building, current_unit)] = room_data

    return data_collection

已尝试的排查步骤 #

• 初始方案：通过遍历DataFrame，依据Group列判断层级，用临时变量跟踪状态，出现第一个房间数据被跳过、错位问题。
• 调试：添加打印语句验证层级顺序和键的正确性，发现新单元检测逻辑存在缺陷。
• 修正逻辑：调整单元和房间检测逻辑，重置计数器，问题仍存在。
• 进一步调试：重新审视数据保存逻辑，确认输入数据顺序，发现新单元数据保存逻辑仍有缺陷。
• 再次修正保存逻辑：调整新单元检测后的保存时机，问题依旧。

问题定位与修复方案 #

核心问题分析 #

1. 房间检测逻辑错误：用pd.isna(row['Item Name'])判断新房间，但未区分"房间行"（Group列有值）和空行，且在检测到新房间时，错误地将初始空字典当作上一个房间数据保存，导致后续产品数据被关联到下一个房间。
2. 数据结构不匹配：room_data被定义为列表，但目标结构需要字典（键为房间名），无法正确映射房间与产品数据。
3. 缺少房间名称跟踪：未记录当前房间的名称，无法将产品数据正确关联到对应房间。

修复后的代码 #

def collect_data(df, products, room_wall_codes):
    data_collection = {}
    current_building = None
    current_unit = None
    room_data = {}  # 改为字典，匹配目标结构
    current_room_name = None
    current_room_data = initialize_product_dict(products)

    for _, row in df.iterrows():
        group_val = row['Group']
        # 检测新建筑单元
        if pd.notna(group_val) and ("Bld" in group_val or "Building" in group_val):
            # 保存上一个单元的数据
            if current_building and current_unit and room_data:
                data_collection[(current_building, current_unit)] = room_data
                room_data = {}  # 重置单元的房间数据
            # 提取建筑和单元名称
            current_building, current_unit = extract_building_and_unit(group_val)
            # 重置房间相关变量
            current_room_name = None
            current_room_data = initialize_product_dict(products)
            continue

        # 检测新房间行（Group列有值，且不是建筑单元行）
        if pd.notna(group_val) and group_val.strip() != "":
            # 保存上一个房间的数据（如果存在）
            if current_room_name is not None and current_room_data:
                # 处理房间名称，比如去掉WALL A/B后缀，提取核心房间名
                cleaned_room_name = group_val.strip().split(" WALL")[0]
                room_data[cleaned_room_name] = current_room_data
            # 记录当前房间名称
            current_room_name = group_val.strip()
            # 重置当前房间的产品数据
            current_room_data = initialize_product_dict(products)
            continue

        # 收集当前房间的产品数据
        if pd.notna(row['Item Name']) and row['Item Name'] in current_room_data:
            current_room_data[row['Item Name']] += row['QTY']

    # 保存最后一个房间的数据
    if current_room_name is not None and current_room_data:
        cleaned_room_name = current_room_name.strip().split(" WALL")[0]
        room_data[cleaned_room_name] = current_room_data

    # 保存最后一个单元的数据
    if current_building and current_unit and room_data:
        data_collection[(current_building, current_unit)] = room_data

    return data_collection

关键修改点 #

1. 调整room_data类型：从列表改为字典，直接用房间名作为键，匹配目标输出结构。
2. 新增房间名称跟踪：用current_room_name记录当前房间的原始名称，处理后作为room_data的键。
3. 修正房间检测逻辑：通过pd.notna(group_val)判断房间行（Group列非空且不是建筑单元），确保只有真正的房间标题行才触发房间切换。
4. 完善数据保存时机：在切换房间/单元时，先保存上一个层级的数据，再重置当前层级的变量，避免数据错位。
5. 房间名称清洗：根据实际规则（如去掉WALL A/B后缀）提取统一的房间名称，确保同一房间的不同墙体数据合并到同一键下。

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