[split] Print JSON Dictionary to Excel?

[venukommu]

# 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.

---

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