Convert JSON to CSV with multiple keys-CodePudding

I writing a code to convert JSON file to CSV. This script works but not with multiple keys in JSON.

I had the error:

Traceback (most recent call last):
  File "test1.py", line 27, in <module>
    for j in data[i]:
KeyError: 0

Could you help me?

import os
import csv
import json,requests
from collections import OrderedDict
from collections import defaultdict
headers = {
  ****
  ****
}
response = requests.get('https://www.exemple.com', headers=headers)
data = response.json()
#print (data)
#print("JSON file loaded")
        # get all keys in json objects
keys = []
for i in range(0,len(data)):
    for j in data[i]:
        if j not in keys:
           keys.append(j)

# map data in each row to key index
converted = []
converted.append(keys)
for i in range(0,len(data)):
    row = []
    for j in range(0,len(keys)):
        if keys[j] in data[i]:
            row.append(data[i][keys[j]])
        else:
            row.append(None)
    converted.append(row)
    
with open("filename.csv", 'w') as outfile:
    writer = csv.writer(outfile)
    writer.writerows(converted)

CodePudding user response：

For exemple and with this complexe json, it doesn't work:

{
"count": 13,
"virtualmachine": [
    {
        "id": "1082e2ed-ff66-40b1-a41b-26061afd4a0b",
        "name": "test-2",
        "displayname": "test-2",
        "securitygroup": [
            {
                "id": "9e649fbc-3e64-4395-9629-5e1215b34e58",
                "name": "test",
                "tags": []
            }
        ],
        "nic": [
            {
                "id": "79568b14-b377-4d4f-b024-87dc22492b8e",
                "networkid": "05c0e278-7ab4-4a6d-aa9c-3158620b6471"
            },
            {
                "id": "3d7f2818-1f19-46e7-aa98-956526c5b1ad",
                "networkid": "b4648cfd-0795-43fc-9e50-6ee9ddefc5bd"
                "traffictype": "Guest"
            }
        ],
        "hypervisor": "KVM",
        "affinitygroup": [],
        "isdynamicallyscalable": false
    }
]

}

CodePudding user response：

You have a bug here:

keys = []
for i in range(0,len(data)): #data is a `dict` 
    for j in data[i]: # <-- error: i is an int, not a key in your data
        if j not in keys:
           keys.append(j)

If you want to get the top-level keys in a dict, the dict can just give it to you:

for key in my_dict:
    print(key)

But it looks like you're trying to get the values from your data. dict can give you this too:

for value in my_dict.values():
    print(value)

Or you can get both at the same time:

for key, value in my_dict.items():
    print(key, value)