Convert Nested JSON into CSV File

Question

I want to create a CSV file with the columns 'Duration' and 'Attention' from a nested JSON file that looks like this:

[
  {
    "Id": {
        "Address": "",
        "Address": "",
        "Name": "Avg"
    },
    "position": 0,
    "sender": [
        false
    ],
    "Atten": 0,
    "Duration": 5,
    "End": 1645,
    "EndRaw": 16040.395,
    "Index": 0,
    "__v": 0
},
{
    "Id": {
        "local": "",
        "Address": "",
        "trafficPairName": "Avg"
    },
    "position": 0,
    "sender": [
        false
    ],
    "Atten": 1,
    "Duration": 5,
    "End": 1652,
    "EndRaw": 166140.3618,
    "Index": 1,
    "__v": 0
  }
]

I am getting an error on the for loop that says "list indices must be integers or slices, not str". How do I properly parse this JSON file and create the CSV file?

Here is my Python code:

import json
import csv

with open('averages.json') as f:
    data = json.load(f)

filename = "data.csv"

with open(filename, "w") as file:
    csv_file = csv.writer(file)
    csv_file.writerow(["Duration", "Attention"])
    for item in data["Id"]:
        csv_file.writerow([])

score 0 · Answer 1 · answered Sep 30 '22 at 09:16

data is list of dicts, and from each dict you want the values for pair of keys.

for item in data["pairId"]:
    csv_file.writerow([])

should be

for item in data:
    csv_file.writerow([item.get("stepDuration"), item.get("stepIndexAtten")])

or in one-line list comprehension

csv_file.writerows(([item.get("stepDuration"), item.get("stepIndexAtten")] for item in data))

score 0 · Answer 2 · answered Sep 30 '22 at 09:23

Not sure why are you taking the data["pairId"] if you need the stepDuration and stepIndexAtten?

This should work for you,

import json
import csv

with open('averageThroughputVsTime-Aggregated.json') as f:
    data = json.load(f)

fname = "output.csv"

with open(fname, "w") as file:
    csv_file = csv.writer(file)
    csv_file.writerow(["stepDuration", "stepIndexAtten"])
    for key, value in enumerate(data):
      csv_file.writerow([key, value["stepDuration"], value["stepIndexAtten"]])

output.csv

stepDuration,stepIndexAtten
0,5,0
1,5,1

Convert Nested JSON into CSV File

2 Answers2