Python嵌套字典构建及多级列DataFrame创建技术求助

原始数据 #

Buildings = ['nr1','nr2','n3']
offices = [1,3,2]
area=[23,[67,77,94],[78,79]]
price=[45,[43,89,56],[54,53]]
employees=[56,[45,54,78],[56,89]]

一、构建目标嵌套字典 #

注意：你给出的目标字典存在语法错误（外层不能是多个字典的集合），正确结构应为单个外层字典，每个建筑名称作为键，对应值是该建筑下所有办公室的子字典。以下是实现代码：

# 初始化结果字典
building_dict = {}

# 遍历每个建筑的索引
for idx, build in enumerate(Buildings):
    build_key = f"build {build}"
    building_dict[build_key] = {}
    
    # 获取当前建筑的办公室数量
    office_count = offices[idx]
    
    # 将单值数据转为列表，统一遍历逻辑
    current_area = area[idx] if isinstance(area[idx], list) else [area[idx]]
    current_price = price[idx] if isinstance(price[idx], list) else [price[idx]]
    current_employees = employees[idx] if isinstance(employees[idx], list) else [employees[idx]]
    
    # 为每个办公室生成子字典
    for office_num in range(1, office_count + 1):
        office_key = f"office {office_num}"
        building_dict[build_key][office_key] = {
            "area": f"{current_area[office_num-1]} kvm",
            "price": current_price[office_num-1],
            "employees": current_employees[office_num-1]
        }

# 打印结果
print(building_dict)

运行后得到的字典结构符合需求，示例片段：

{
    "build nr1": {
        "office 1": {"area": "23 kvm", "price": 45, "employees": 56}
    },
    "build nr2": {
        "office 1": {"area": "67 kvm", "price": 43, "employees": 45},
        "office 2": {"area": "77 kvm", "price": 89, "employees": 54},
        "office 3": {"area": "94 kvm", "price": 56, "employees": 78}
    },
    ...
}

二、生成带多级列的DataFrame #

目标是生成以Buildings nr为索引，一级列为area/price/employees、二级列为办公室编号的DataFrame，提供两种实现方式：

方式1：基于嵌套字典转换 #

import pandas as pd

# 整理数据为多级列格式
data = {}
for build_key, offices_data in building_dict.items():
    build_nr = build_key.split()[1]  # 提取建筑编号（如"nr1"）
    for office_key, metrics in offices_data.items():
        office_nr = int(office_key.split()[1])  # 提取办公室编号（如1）
        for metric, value in metrics.items():
            # 处理area的数值，去掉"kvm"转为数字
            if metric == "area":
                value = int(value.split()[0])
            # 构建多级列的键：(指标名, 办公室编号)
            col_key = (metric, office_nr)
            if col_key not in data:
                data[col_key] = {}
            data[col_key][build_nr] = value

# 生成DataFrame并调整结构
df = pd.DataFrame(data).T.unstack().unstack(level=0)
df.index.name = "Buildings nr"
# 确保列的顺序为area、price、employees
df = df[["area", "price", "employees"]]

print(df)

方式2：直接从原始数据生成（更简洁） #

import pandas as pd

# 整理每行数据
rows = []
for idx, build in enumerate(Buildings):
    office_count = offices[idx]
    current_area = area[idx] if isinstance(area[idx], list) else [area[idx]]
    current_price = price[idx] if isinstance(price[idx], list) else [price[idx]]
    current_employees = employees[idx] if isinstance(employees[idx], list) else [employees[idx]]
    
    for office_num in range(1, office_count + 1):
        rows.append({
            "Buildings nr": build,
            "office": office_num,
            "area": current_area[office_num-1],
            "price": current_price[office_num-1],
            "employees": current_employees[office_num-1]
        })

# 透视生成多级列DataFrame
df = pd.DataFrame(rows).pivot(index="Buildings nr", columns="office")
# 调整一级列的顺序
df = df.reindex(columns=["area", "price", "employees"], level=0)

print(df)

运行后得到的DataFrame结构示例：

area        price        employees       
office          1     2     3    1   2   3       1   2   3
Buildings nr                                              
nr1            23   NaN   NaN   45 NaN NaN      56 NaN NaN
nr2            67    77    94   43  89  56      45  54  78
n3             78    79   NaN   54  53 NaN      56  89 NaN

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