Python Forum
[split] Print JSON Dictionary to Excel?
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
[split] Print JSON Dictionary to Excel?
#1
# 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
Reply


Messages In This Thread
[split] Print JSON Dictionary to Excel? - by venukommu - Nov-04-2019, 07:14 PM

Possibly Related Threads…
Thread Author Replies Views Last Post
  [split] Parse Nested JSON String in Python mmm07 4 1,413 Mar-28-2023, 06:07 PM
Last Post: snippsat
  Python Split json into separate json based on node value CzarR 1 5,471 Jul-08-2022, 07:55 PM
Last Post: Larz60+
  Converting cells in excel to JSON format desmondtay 4 1,679 May-23-2022, 10:31 AM
Last Post: Larz60+
Question How do I skipkeys on json file read to python dictionary? BrandonKastning 3 1,829 Mar-08-2022, 09:34 PM
Last Post: BrandonKastning
  [split] Results of this program in an excel file eisamabodian 1 1,543 Feb-11-2022, 03:18 PM
Last Post: snippsat
Question How to print each possible permutation in a dictionary that has arrays as values? noahverner1995 2 1,695 Dec-27-2021, 03:43 AM
Last Post: noahverner1995
  excel - json jmabrito 23 6,285 Mar-02-2021, 10:11 PM
Last Post: jmabrito
  saving a dictionary as json file vinay_py 6 3,031 Jun-06-2020, 05:07 PM
Last Post: vinay_py
  [split] script: remove all "carriage return" from my json variable pete 2 2,742 May-05-2020, 03:22 PM
Last Post: deanhystad
  iterate and print json datas enigma619 1 1,890 Apr-01-2020, 11:48 AM
Last Post: buran

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020