如何用Python将Excel多级表格转换为指定结构的嵌套字典？

需求说明 #

需要将如下Excel表格转换为指定结构的嵌套字典：

期望输出结构 #

dictionary = {
    "chapter1": {
        "subtopic1": {"detail1": "description1"},
        "subtopic2": {"detail2": "description2"}
    },
    "chapter2": {
        "subtopic1": {"detail1": "description1"}
    },
    "chapter3": {
        "subtopic1": {
            "detail1": "description1",
            "detail2": "description2"
        }
    },
    "chapter4": {
        "subtopic1": {"detail1": "description1"}
    }
}

用户尝试的代码（无法得到期望结果） #

import pandas as pd

datapath = "EMDrugs.xlsx"

data = pd.read_excel(datapath)
data = data.fillna(0)

chap = data.Chapter
topi = data.Topic
drug = data.Drug
dose = data.Dose

x = 0

chap = chap.to_dict()
topi = topi.to_dict()
drug = drug.to_dict()
dose = dose.to_dict()

def mergeDictionary(dict_1, dict_2, dict_3, dict_4):
   dict_5 = {**dict_1, **dict_2, **dict_3, **dict_4}
   for key, value in dict_5.items():
       if key in dict_1 and key in dict_2 and key in dict_3 and key in dict_4:
               dict_5[key] = {value , dict_1[key]}
   return dict_5

#dicti = dict(zip(chap, topi, drug, dose))

print(mergeDictionary(chap, topi, drug, dose))

解决方案 #

核心思路 #

先通过向前填充空值补全表格中缺失的章节（A列）和子主题（B列），再逐层遍历数据构建嵌套字典。

完整实现代码 #

import pandas as pd
import pprint

datapath = "EMDrugs.xlsx"
# 读取Excel数据
df = pd.read_excel(datapath)
# 向前填充空值，补全每行的章节和子主题
df[['A', 'B']] = df[['A', 'B']].ffill()

result = {}

# 遍历每行数据构建嵌套字典
for _, row in df.iterrows():
    chapter = row['A']
    subtopic = row['B']
    detail = row['C']
    desc = row['D']
    
    # 初始化章节层级
    if chapter not in result:
        result[chapter] = {}
    # 初始化子主题层级
    if subtopic not in result[chapter]:
        result[chapter][subtopic] = {}
    # 添加详情与描述的键值对
    result[chapter][subtopic][detail] = desc

# 格式化打印结果
pprint.pprint(result)

输出结果验证 #

运行代码后将得到与期望结构完全一致的嵌套字典：

{'chapter1': {'subtopic1': {'detail1': 'description1'},
              'subtopic2': {'detail2': 'description2'}},
 'chapter2': {'subtopic1': {'detail1': 'description1'}},
 'chapter3': {'subtopic1': {'detail1': 'description1',
                            'detail2': 'description2'}},
 'chapter4': {'subtopic1': {'detail1': 'description1'}}}

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