Bottom Page

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
Larz60+ wrote Nov-04-2019, 07:17 PM:
Please post all code, output and errors (in it's entirety) between their respective tags. I did it for you this time, Here are instructions on how to do it yourself next time.
Quote
#2
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
Feel like you're not getting the answers you want? Checkout the help/rules for things like what to include/not include in a post, how to use code tags, how to ask smart questions, and more.

Pro-tip - there's an inverse correlation between the number of lines of code posted and my enthusiasm for helping with a question :)
Quote

Top Page

Possibly Related Threads...
Thread Author Replies Views Last Post
  saving a dictionary as json file vinay_py 6 313 Jun-06-2020, 05:07 PM
Last Post: vinay_py
  [split] script: remove all "carriage return" from my json variable pete 2 306 May-05-2020, 03:22 PM
Last Post: deanhystad
  iterate and print json datas enigma619 1 260 Apr-01-2020, 11:48 AM
Last Post: buran
  print python json dump onto multiple lines lhailey 2 4,773 Mar-02-2020, 12:47 PM
Last Post: vishalhule
  accessing Json dictionary lshankar 1 314 Dec-16-2019, 06:18 AM
Last Post: ndc85430
  Split of key from JSON response aswini_dubey 1 436 Dec-04-2019, 06:54 PM
Last Post: Larz60+
  [split] how to print unicode in python? Frank57 4 526 Oct-28-2019, 08:57 PM
Last Post: Frank57
  Exporting list with dictionary to Excel veromi22 0 429 Oct-15-2019, 12:54 AM
Last Post: veromi22
  Dictionary for Excel Data Ranjirock 0 971 Aug-18-2019, 05:26 PM
Last Post: Ranjirock
  Parsing json - dictionary with in dictionary krish216 6 800 Jul-30-2019, 10:01 PM
Last Post: cvsae

Forum Jump:


Users browsing this thread: 1 Guest(s)