在Python中将嵌套的Json文件转换为CSV文件

Question

我有一个嵌套的Json文件，我正试图将其转换为CSV文件

Json数据如下所示：

{
  "businessUnitId": 999999,
  "lastPollTime": "2021-04-12T18:30:48.703Z",
  "totalRecords": 2,
  "dispositions": [
    {
      "dispositionId": 1000,
      "dispositionName": "Reservation_Test",
      "isActive": true,
      "skills": [
        {
          "skillId": 1,
          "mediaTypeId": 3,
          "mediaTypeName": "Chat"
        },
        {
          "skillId": 2,
          "mediaTypeId": 3,
          "mediaTypeName": "Chat"
        },
        {
          "skillId": 3,
          "mediaTypeId": 3,
          "mediaTypeName": "Chat"
        }
      ]
    },
    {
      "dispositionId": 1001,
      "dispositionName": "Cancel Reservation",
      "isActive": true,
      "skills": [
        {
          "skillId": 4,
          "mediaTypeId": 3,
          "mediaTypeName": "Chat"
        },
        {
          "skillId": 5,
          "mediaTypeId": 3,
          "mediaTypeName": "Chat"
        },
        {
          "skillId": 6,
          "mediaTypeId": 3,
          "mediaTypeName": "Chat"
        },
        {
          "skillId": 7,
          "mediaTypeId": 3,
          "mediaTypeName": "Chat"
        },
        {
          "skillId": 8,
          "mediaTypeId": 4,
          "mediaTypeName": "Phone Call"
        },
        {
          "skillId": 9,
          "mediaTypeId": 4,
          "mediaTypeName": "Phone Call"
        },
        {
          "skillId": 10,
          "mediaTypeId": 4,
          "mediaTypeName": "Phone Call"
        },
        {
          "skillId": 11,
          "mediaTypeId": 4,
          "mediaTypeName": "Phone Call"
        }
   
    }
  ]
}

这是我的密码：

import json 
import csv 
  
  
# Opening JSON file and loading the data 
# into the variable data 
with open('File.json') as json_file: 
    data = json.load(json_file) 
  
dispositions = data['dispositions'] 
  
# now we will open a file for writing 
data_file = open('File.csv', 'w') 
  
# create the csv writer object 
csv_writer = csv.writer(data_file) 
  
# Counter variable used for writing  
# headers to the CSV file 
count = 0
  
for emp in dispositions: 
    if count == 0: 
  
        # Writing headers of CSV file 
        header = emp.keys() 
        csv_writer.writerow(header) 
        count += 1
  
    # Writing data of CSV file 
    csv_writer.writerow(emp.values()) 
  
data_file.close() 

当我运行代码时，我得到的是：

性格是积极的技能 保留\u测试TRUE[{'skillId'：1，'mediaTypeId'：3，'mediaTypeName'：'Chat'}，{'skillId'：2，'mediaTypeId'：3，'mediaTypeName'：'Chat'}，{'skillId'：3，'mediaTypeName'：'Chat'}] 取消保留TRUE[{'skillId'：4，'mediaTypeId'：3，'mediaTypeName'：'Chat'}，{'skillId'：5，'mediaTypeId'：3，'mediaTypeId'：'Chat'}，{'skillId'：7，'mediaTypeId'：3，'mediaTypeName'：'Chat'}，{'skillId'：8，'mediaTypeId'：4，'mediaTypeName'：'Phone Call}，{'skillId'：10647567，'mediaTypeId'：4，'mediaTypeName'：'Phone Call'}，{'skillId'：10647574，'mediaTypeId'：4，'mediaTypeName'：'Phone Call'}，{'skillId'：10647599，'mediaTypeId'：4，'mediaTypeName'：'Phone Call'}]

这就是我想要的：

dispositionId   dispositionName isActive    SkillId mediaTypeId mediaTypeName
1000    Reservation_Test    TRUE    1   3   Chat
1000    Reservation_Test    TRUE    2   3   Chat
1000    Reservation_Test    TRUE    3   3   Chat
1001    Cancel Reservation  TRUE    4   3   Chat
1001    Cancel Reservation  TRUE    5   3   Chat
1001    Cancel Reservation  TRUE    6   3   Chat
1001    Cancel Reservation  TRUE    7   3   Chat
1001    Cancel Reservation  TRUE    8   4   Phone Call
1001    Cancel Reservation  TRUE    9   4   Phone Call
1001    Cancel Reservation  TRUE    10  4   Phone Call
1001    Cancel Reservation  TRUE    11  4   Phone Call

我只是不确定如何处理嵌套的JSON数据

谢谢

Answer 1

对于重复的内容，需要有一个嵌套的循环。我还建议使用the built-in ^{}而不是常规的csv.writer类

data_file = open('File.csv', 'w') 

# Use the first obj to determine the header row
header = [x for x in dispositions[0].keys() if x != "skills"]
header.extend(dispositions[0]["skills"][0].keys())

csv_writer = csv.DictWriter(data_file, fieldnames=header)
csv_writer.write(header)
  
for emp in dispositions:
    # These key/values are common to each nested row
    common_fields = {
        k: v for k, v in emp.items() if k != "skills"
    }

    for skill in emp["skills"]:
        # Combine the common fields with this skill's key/values
        row = {**common_fields, **skill}
        csv_writer.write(row)
  
data_file.close()