Python with very nested JSON to CSV file


Keywords:python 


Question: 

I have changed the script following Stev's answer. The error no longer applies.

#import pandas as pd
import requests
import json
import csv

outfile = open('D:\\test.csv','w')
url = ''
resp = requests.get(url)
data = json.loads(resp.content.decode('UTF8'))
for d in data['data']:
    for r in d['rows']:
       for sd in (d['rows']):
           for td in (sd['data']):
               dparsed = sd['data']

w = csv.DictWriter(outfile, dparsed.keys())
w.writeheader()
w.writerow(dparsed) 

I ran the script and it created the csv file, but it is showing 0 KB and is saying it is locked by another user so I don't know exactly what I have goofed up this time. This is clearly not a duplicate question, so thanks for flagging it as such... /s

I ran the above script and after about 3 hours of waiting I killed spyder to see what happened with the excel file. It kind of worked, but it only managed to spit out some of the data into columns and about like 3 rows. Not sure where I fell down yet.


1 Answer: 

This is more of a comment because it doesn't give you the answer, but I am not sure you json file is formatted properly in order to use pd.json_normalize. You might have to loop through your json file, using something like the following:

import pandas as pd
import requests
import json

url = ''
resp = requests.get(url)
data = json.loads(resp.content.decode('UTF8'))

df1 = pd.DataFrame()
df2 = pd.DataFrame()

for d in data['data']:
    # print(d['identifier'])
    for r in d['rows']:
        # print(r['contractIdentifier'])
        # print(r['data'])
        df1 = df1.append(json_normalize(r['data']))
        df2 = df2.append(pd.DataFrame([r['contractIdentifier']]))
        # print(r)

df = pd.concat([df1,df2], axis=1)
df.to_csv('my_file.txt')