将字典列表解析为表/csv

Question

我有一个字典，其中包含嵌套的字典列表，我想将这些字典展平并解压缩为csv文件。初始字典的键（称为“读物”）是物理位置标识符的列表（例如111、222等）

list_of_attributes = readings.keys()
print(list_of_attributes)

结果：

dict_keys(['000111', '000967', '000073' ...

对于每个键，都有一个嵌套的字典列表，其中包含类似时间序列的数据：

list(readings.values())[0]

[{'Approval': {'ApprovalLevel': 650, 'LevelDescription': 'Working'},
  'FieldVisitIdentifier': '2a328f51-aac7-494d-9f08-69716a06b179',
  'Value': {'Unit': 'ft', 'Numeric': -1.53},
  'DatumConvertedValues': [{'TargetDatum': 'NAVD88',
    'Unit': 'ft',
    'Numeric': 4.196010479999999},
   {'TargetDatum': 'NGVD29', 'Unit': 'ft', 'Numeric': 4.779999999999999},
   {'TargetDatum': 'Local Assumed Datum', 'Unit': 'ft', 'Numeric': -0.28}],
  'Parameter': 'GWL',
  'MonitoringMethod': 'Steel Tape',
  'Time': '2021-05-25T18:51:00.0000000+00:00',
  'Comments': 'MP = hole in top of SS = +1.25 ft LSD',
  'Publish': True,
  'ReadingType': 'Routine',
  'ReferencePointUniqueId': '13208d3d0fe846c08ba7d6777b040433',
  'UseLocationDatumAsReference': False},

...

我使用Amir Ziai的Flatte_json包成功地将嵌套字典列表展平并解压为csv，如下所示：

data = {(k, i): flatten(item) for k, v in readings.items() for i, item in enumerate(v)}
df = pd.DataFrame.from_dict(data, orient='index')
df.to_csv(r"C:\test.csv", index=False)

结果csv:

这正是我想要的，但我还需要以某种方式将初始键（'000111'，'000967'，'000073'）映射到每个相关记录的一列。看起来像这样

更新：来自端点的json示例

Response Body
{
  "FieldVisitReadings": [
    {
      "Approval": {
        "ApprovalLevel": 650,
        "LevelDescription": "Working"
      },
      "FieldVisitIdentifier": "2a328f51-aac7-494d-9f08-69716a06b179",
      "Value": {
        "Unit": "ft",
        "Numeric": -1.53
      },
      "DatumConvertedValues": [
        {
          "TargetDatum": "NAVD88",
          "Unit": "ft",
          "Numeric": 4.196010479999999
        },
        {
          "TargetDatum": "NGVD29",
          "Unit": "ft",
          "Numeric": 4.779999999999999
        },
        {
          "TargetDatum": "Local Assumed Datum",
          "Unit": "ft",
          "Numeric": -0.28
        }
      ],
      "Parameter": "GWL",
      "MonitoringMethod": "Steel Tape",
      "Time": "2021-05-25T18:51:00.0000000+00:00",
      "Comments": "MP = hole in top of SS = +1.25 ft LSD",
      "Publish": true,
      "ReadingType": "Routine",
      "ReferencePointUniqueId": "13208d3d0fe846c08ba7d6777b040433",
      "UseLocationDatumAsReference": false
    },

更新2：

# Step 1: Fetch the list of locations
locationDescriptions = client.publish.get('/GetLocationist')['LocationDescriptions']



#Pass list of identifiders to 2nd endpoint  and create dictionary.   Keys are list of locations from Step 1
readings = dict(
    (loc['Identifier'],
     client.publish.get('/GetReadingsByLocation', params={
        'LocationIdentifier': loc['Identifier'],
        'Parameters': ['GWL'],
        'ApplyDatumConversion': 'true'
     })['FieldVisitReadings']
    ) for loc in locationDescriptions)

Answer 1

您可以使用DataFrame.drop_level()+DataFrame.reset_index()获取您的列：

import pandas as pd
from flatten_json import flatten

readings = {
    "000111": [
        {
            "Approval": {"ApprovalLevel": 650, "LevelDescription": "Working"},
            "FieldVisitIdentifier": "2a328f51-aac7-494d-9f08-69716a06b179",
            "Value": {"Unit": "ft", "Numeric": -1.53},
            "DatumConvertedValues": [
                {
                    "TargetDatum": "NAVD88",
                    "Unit": "ft",
                    "Numeric": 4.196010479999999,
                },
                {
                    "TargetDatum": "NGVD29",
                    "Unit": "ft",
                    "Numeric": 4.779999999999999,
                },
                {
                    "TargetDatum": "Local Assumed Datum",
                    "Unit": "ft",
                    "Numeric": -0.28,
                },
            ],
            "Parameter": "GWL",
            "MonitoringMethod": "Steel Tape",
            "Time": "2021-05-25T18:51:00.0000000+00:00",
            "Comments": "MP = hole in top of SS = +1.25 ft LSD",
            "Publish": True,
            "ReadingType": "Routine",
            "ReferencePointUniqueId": "13208d3d0fe846c08ba7d6777b040433",
            "UseLocationDatumAsReference": False,
        },
        {
            "Approval": {"ApprovalLevel": 1200, "LevelDescription": "Working"},
            "FieldVisitIdentifier": "xxx",
            "Value": {"Unit": "ft", "Numeric": -1.53},
            "DatumConvertedValues": [
                {
                    "TargetDatum": "NAVD88",
                    "Unit": "ft",
                    "Numeric": 4.196010479999999,
                },
                {
                    "TargetDatum": "NGVD29",
                    "Unit": "ft",
                    "Numeric": 4.779999999999999,
                },
                {
                    "TargetDatum": "Local Assumed Datum",
                    "Unit": "ft",
                    "Numeric": -0.28,
                },
            ],
            "Parameter": "GWL",
            "MonitoringMethod": "Steel Tape",
            "Time": "2021-05-25T18:51:00.0000000+00:00",
            "Comments": "MP = hole in top of SS = +1.25 ft LSD",
            "Publish": True,
            "ReadingType": "Routine",
            "ReferencePointUniqueId": "13208d3d0fe846c08ba7d6777b040433",
            "UseLocationDatumAsReference": False,
        },
    ]
}

data = {
    (k, i): flatten(item)
    for k, v in readings.items()
    for i, item in enumerate(v)
}
df = pd.DataFrame.from_dict(data, orient="index")
df = df.droplevel(level=1).reset_index().rename(columns={"index": "Keys"})
print(df)
df.to_csv("data.csv", index=False)

印刷品：

     Keys  Approval_ApprovalLevel Approval_LevelDescription                  FieldVisitIdentifier Value_Unit  Value_Numeric DatumConvertedValues_0_TargetDatum DatumConvertedValues_0_Unit  DatumConvertedValues_0_Numeric DatumConvertedValues_1_TargetDatum DatumConvertedValues_1_Unit  DatumConvertedValues_1_Numeric DatumConvertedValues_2_TargetDatum DatumConvertedValues_2_Unit  DatumConvertedValues_2_Numeric Parameter MonitoringMethod                               Time                               Comments  Publish ReadingType            ReferencePointUniqueId  UseLocationDatumAsReference
0  000111                     650                   Working  2a328f51-aac7-494d-9f08-69716a06b179         ft          -1.53                             NAVD88                          ft                         4.19601                             NGVD29                          ft                            4.78                Local Assumed Datum                          ft                           -0.28       GWL       Steel Tape  2021-05-25T18:51:00.0000000+00:00  MP = hole in top of SS = +1.25 ft LSD     True     Routine  13208d3d0fe846c08ba7d6777b040433                        False
1  000111                    1200                   Working                                   xxx         ft          -1.53                             NAVD88                          ft                         4.19601                             NGVD29                          ft                            4.78                Local Assumed Datum                          ft                           -0.28       GWL       Steel Tape  2021-05-25T18:51:00.0000000+00:00  MP = hole in top of SS = +1.25 ft LSD     True     Routine  13208d3d0fe846c08ba7d6777b040433                        False

并保存data.csv：