Python with very nested JSON to CSV file

Question

https://www.eex.com/data//view/data/detail/phelix-power-futures/2018/02.27.json

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 = 'https://www.eex.com/data//view/data/detail/phelix-power-futures/2018/02.27.json'
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.

Please put the content of your error, it will help to solve your issue. — hanego, Feb 28 '18 at 08:30
Possible duplicate of [Pandas json\_normalize produces confusing \`KeyError\` message?](https://stackoverflow.com/questions/32291437/pandas-json-normalize-produces-confusing-keyerror-message) — Matthias, Feb 28 '18 at 14:10
Except it's not a duplicate of that. I have a completely different JSON structure and needed to understand how to drill down further into it so that the json_normalize can handle it. I am going to give Stev's stuff a shot. — Shenanigator, Feb 28 '18 at 18:11

Stev · Answer 1 · 2018-03-01T09:31:26.773

0

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 = 'https://www.eex.com/data//view/data/detail/phelix-power-futures/2018/02.27.json'
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')

edited Mar 01 '18 at 09:31

answered Feb 28 '18 at 14:10

Stev

1,100
9
16

Stev, this is super close; I think I need to tell it which contractIdentifier I want and then go loop through that data. going to poke at this with a stick. I agree the JSON format sucks, but I don't have a way to control that. – Shenanigator Feb 28 '18 at 19:29
@Shenanigator, something like this? There are a lot of zeros and NaNs in the file, are you expecting that? This might get you a little further anyway. – Stev Mar 01 '18 at 12:06

Python with very nested JSON to CSV file

1 Answers1