如何将双层嵌套字典转换为指定列的DataFrame表格

要实现这个需求，我们可以用Pandas库来处理，核心思路是逐层提取嵌套字典里的数据，把需要的字段整合成结构化的列表，再转成DataFrame。以下是具体代码：

import pandas as pd

data = {"sitesEnergy": {"timeUnit": "DAY",
  "unit": "Wh",
  "count": 5,
  "siteEnergyList": [{"siteId": 2248407,
    "energyValues": {"measuredBy": "METER",
     "values": 
      [
      {"date": "2022-08-01 00:00:00", "value": 1084070.0},
      {"date": "2022-08-02 00:00:00", "value": 1420093.0},
      {"date": "2022-08-03 00:00:00", "value": 1757618.0},
      {"date": "2022-08-04 00:00:00", "value": 1685625.0},
      {"date": "2022-08-05 00:00:00", "value": 1043790.0},
      {"date": "2022-08-06 00:00:00", "value": 1340688.0},
      {"date": "2022-08-07 00:00:00", "value": 1555515.0},
      {"date": "2022-08-08 00:00:00", "value": 1573906.0}]}},
   {"siteId": 1485192,
    "energyValues": {"measuredBy": "METER",
     "values": 
      [
      {"date": "2022-08-01 00:00:00", "value": 230484.0},
      {"date": "2022-08-02 00:00:00", "value": 272969.0},
      {"date": "2022-08-03 00:00:00", "value": 302500.0},
      {"date": "2022-08-04 00:00:00", "value": 300594.0},
      {"date": "2022-08-05 00:00:00", "value": 220641.0},
      {"date": "2022-08-06 00:00:00", "value": 255484.0},
      {"date": "2022-08-07 00:00:00", "value": 244516.0},
      {"date": "2022-08-08 00:00:00", "value": 266532.0}]}}]}}

# 初始化空列表存储数据
result_list = []
# 提取全局的unit值
unit = data["sitesEnergy"]["unit"]

# 遍历每个站点
for site in data["sitesEnergy"]["siteEnergyList"]:
    site_id = site["siteId"]
    # 遍历该站点下的所有日期-值记录
    for record in site["energyValues"]["values"]:
        result_list.append({
            "unit": unit,
            "siteId": site_id,
            "date": record["date"],
            "value": record["value"]
        })

# 转换为DataFrame
df = pd.DataFrame(result_list)
print(df.head())

代码说明： #

• 先从顶层字典里取出全局的unit值，这个值对所有记录都有效
• 外层循环遍历每个站点，拿到每个站点的siteId
• 内层循环遍历该站点下的所有日期-值对，把四个字段打包成字典，添加到结果列表里
• 最后用pd.DataFrame()把列表转成结构化的表格

运行后得到的DataFrame就会包含unit、siteId、date、value这四列，完全符合需求。

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