Parse JSON to CSV + additional columns

Question

I'm attempting to parse a JSON file with the following syntax into CSV:

{"code":2000,"message":"SUCCESS","data":
{"1":
  {"id":1,
"name":"first_name",
"icon":"url.png",
"attribute1":"value",
"attribute2":"value" ...},
"2":
  {"id":2,
"name":"first_name",
"icon":"url.png",
"attribute1":"value",
"attribute2":"value" ...},
"3":
  {"id":3,
"name":"first_name",
"icon":"url.png",
"attribute1":"value",
"attribute2":"value" ...}, and so forth
}}}

I have found similar questions (e.g. here and here and I am working with the following method:

import requests
import json
import csv
import os

jsonfile = "/path/to.json"
csvfile = "/path/to.csv"

with open(jsonfile) as json_file:
    data=json.load(json_file)

data_file = open(csvfile,'w')

csvwriter = csv.writer(data_file)
csvwriter.writerow(data["data"].keys())

for row in data:
    csvwriter.writerow(row["data"].values())
data_file.close()

but I am missing something. I get this error when I try to run:

TypeError: string indices must be integers

and my csv output is:

1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,96

At the end of the day, I am trying to convert the following function (from PowerShell) to Python. This converted the JSON to CSV and added 3 additional custom columns to the end:

$json = wget $lvl | ConvertFrom-Json
$json.data | %{$_.psobject.properties.value} `
   | select-object *,@{Name='Custom1';Expression={$m}},@{Name='Level';Expression={$l}},@{Name='Custom2';Expression={$a}},@{Name='Custom3';Expression={$r}} `
   | Export-CSV -path $outfile

The output looks like:

"id","name","icon","attribute1","attribute2",..."Custom1","Custom2","Custom3"
"1","first_name","url.png","value","value",..."a","b","c"
"2","first_name","url.png","value","value",..."a","b","c"
"3","first_name","url.png","value","value",..."a","b","c"

Please provide a runnable [mre] including sample input data and entire traceback (not just the single line saying `TypeError`. — martineau, May 06 '21 at 19:37

score 0 · Answer 1 · edited May 07 '21 at 17:55

As suggested by martineau in a now-deleted answer, my key name was incorrect.

I ended up with this:

import json
import csv

jsonfile = "/path/to.json"
csvfile = "/path/to.csv"

with open(jsonfile) as json_file:
    data=json.load(json_file)

data_file = open(csvfile,'w')

csvwriter = csv.writer(data_file)

#get sample keys
header=data["data"]["1"].keys()

#add new fields to dict
keys = list(header)
keys.append("field2")
keys.append("field3")

#write header
csvwriter.writerow(keys)

#for each entry
total = data["data"]

for row in total:
    rowdefault = data["data"][str(row)].values()
    rowdata = list(rowdefault)
    rowdata.append("value1")
    rowdata.append("value2")
    csvwriter.writerow(rowdata)

Here, I'm grabbing each row by its name id via str(row).

Parse JSON to CSV + additional columns

1 Answers1