[split] Print JSON Dictionary to Excel?

[venukommu]

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

# fake json data data = {     "features": [         {             "attributes": {                 "color": "green",                 "size": "5",                 "can_rotate": "no"             }         },         {             "attributes": {                 "can_rotate": "yes",                 "color": "black"             }         },         {             "attributes": {                 "color": "red",                 "size": "9"             }         }     ] }

The above sample json file helped me alot, i would like to pass the same kind of data as json file i.e https://www.dallasopendata.com/resource/are8-xahz.json

I could not read the data from above using the given sample python program which Posted by nilamo - Aug-17-2017, 05:11 PM

Can anyone help me to read the above json to read and export data into CSV or Excel ?

Problem is that above JSON file is having no some of the columns in each record. I'm not expert in the Python, appreciate if any help.

---

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

import csv     #JSON Data data = {     "features": [{"attributes": {"incident_number":"19-2084137","division":"Northeast","nature_of_call":"40/01 - Other","priority":"2","date_time":"2019-11-04T11:09:00.000","unit_number":"B299","block":"8100","location":"Park Ln","beat":"211","reporting_area":"1062","status":"At Scene"}}, {"attributes": {"incident_number":"19-2084045","division":"Northeast","nature_of_call":"40/01 - Other","priority":"2","date_time":"2019-11-04T11:08:00.000","unit_number":"B245","location":"WALNUT HILL LN / N CENTRAL SERV NB","beat":"211","reporting_area":"1062","status":"At Scene"}}, {"attributes": {"incident_number":"19-2084045","division":"Northeast","nature_of_call":"40/01 - Other","priority":"2","date_time":"2019-11-04T11:08:00.000","unit_number":"B239","location":"WALNUT HILL LN / N CENTRAL SERV NB","beat":"211","reporting_area":"1062","status":"At Scene"}}, {"attributes": {"incident_number":"19-2084126","division":"Northeast","nature_of_call":"37F - Freeway Blockage","priority":"2","date_time":"2019-11-04T11:07:00.000","unit_number":"B256","location":"N Central Expy Nb / Walnut Hill Ln","beat":"211","reporting_area":"1062","status":"At Scene"}}, {"attributes": {"incident_number":"19-2084146","division":"Southeast","nature_of_call":"37 - Street Blockage","priority":"4","date_time":"2019-11-04T11:05:00.000","unit_number":"T412","block":"7500","location":"Scyene Rd","beat":"321","reporting_area":"1235","status":"At Scene"}}, {"attributes": {"incident_number":"19-2075873","division":"Southeast","nature_of_call":"7X - Major Accident","priority":"1","date_time":"2019-11-03T01:30:00.000","unit_number":"T245","location":"Elam Rd / N Prairie Creek Rd","beat":"334","reporting_area":"2211","status":"At Scene"}}] }    columns = [] all_rows = [] for feature in data["features"]:     row = ["" for col in columns]     for key, value in feature["attributes"].items():         try:             index = columns.index(key)         except ValueError:             # this column hasn't been seen before             columns.append(key)             row.append("")             index = len(columns) - 1            row[index] = value     all_rows.append(row)    with open("JSON_Data_To_CSV.csv", "w") as csvfile:     writer = csv.writer(csvfile)     # first row is the headers     writer.writerow(columns)     # then, the rows     writer.writerows(all_rows)

I'm using this code to convert JSON data into CSV/Excel, i would like to use json file as input i.e https://www.dallasopendata.com/resource/are8-xahz.json instead of data in the python file.

I have formatted JSON data little bit to provide in the required format instead of actual JSON. If i provide json file as input then we are getting following error.

Error:
Traceback (most recent call last):
  File "JSON_Data_To_CSV.py", line 72, in <module>
    for key, value in feature["attributes"].items():
KeyError: 'attributes'

I'm newbie in the Python, appreciate if any help.
Thank You

[micseydel]

Split from https://python-forum.io/Thread-Print-JSO...y-to-Excel

I ran your code and couldn't reproduce the error, here's what I got as output:

Output:
incident_number,division,nature_of_call,priority,date_time,unit_number,block,location,beat,reporting_area,status
19-2084137,Northeast,40/01 - Other,2,2019-11-04T11:09:00.000,B299,8100,Park Ln,211,1062,At Scene
19-2084045,Northeast,40/01 - Other,2,2019-11-04T11:08:00.000,B245,,WALNUT HILL LN / N CENTRAL SERV NB,211,1062,At Scene
19-2084045,Northeast,40/01 - Other,2,2019-11-04T11:08:00.000,B239,,WALNUT HILL LN / N CENTRAL SERV NB,211,1062,At Scene
19-2084126,Northeast,37F - Freeway Blockage,2,2019-11-04T11:07:00.000,B256,,N Central Expy Nb / Walnut Hill Ln,211,1062,At Scene
19-2084146,Southeast,37 - Street Blockage,4,2019-11-04T11:05:00.000,T412,7500,Scyene Rd,321,1235,At Scene
19-2075873,Southeast,7X - Major Accident,1,2019-11-03T01:30:00.000,T245,,Elam Rd / N Prairie Creek Rd,334,2211,At Scene