How to extract list from nested Json into a CSV?

Question

I make use of an API which outputs me adresses. However the adresses are nested as such:

{
   "totalItemCount":55,
   "pageCount":1,
   "size":100,
   "_links":{
      "self":{
         "href":"\/bag?filters[postcode]=1011PL&ovio-api-key=KEY"
      }
   },
   "_embedded":{
      "adres":[
         {
            "huisnummer":"7",
            "huisletter":"",
            "postcode":"1011PL",
            "huisnummertoevoeging":"",
            "openbareruimte":"Nieuwe Amstelstraat",
            "slug":"1011pl-nieuwe-amstelstraat-7",
            "woonplaats":"Amsterdam",
            "_links":{
               "self":{
                  "href":"\/bag\/1011pl-nieuwe-amstelstraat-7"
               }
            }
         },
         {
            "huisnummer":"25",
            "huisletter":"",
            "postcode":"1011PL",
            "huisnummertoevoeging":"",
            "openbareruimte":"Nieuwe Amstelstraat",
            "slug":"1011pl-nieuwe-amstelstraat-25",
            "woonplaats":"Amsterdam",
            "_links":{
               "self":{
                  "href":"\/bag\/1011pl-nieuwe-amstelstraat-25"
               }
            }
         },

My current script:

## Convert Output JSON to CSV
f = open("output.json", "r+")
x = json.loads(f.read())
f.close()
# print(x['_embedded']['adres'][0]['openbareruimte'])

f = csv.writer(open("test.csv", "w"))
f.writerow(["straat","huisnummer","postcode","stad"])
for y in x:
    f.writerow([x["_embedded"]["adres"][0]["openbareruimte"],
                x["_embedded"]["adres"][0]["huisnummer"],
                x["_embedded"]["adres"][0]["postcode"],
                x["_embedded"]["adres"][0]["woonplaats"]])

I want to output all of the streets, numbers, postal codes and cities to CSV, but it only outputs the first adress. I have tried using split and format but I'm too unfamiliar with that. If anyone knows how to make use of the nested data, it would be appreciated. I could not find any tutorial in regards.

Try to upload code that I can easily copy-paste and run (and short). The sample JSON you posted above is partial. I don't need to see all 55 addresses but it would be nice if the the JSON is valid. — Guy
– Guy, Commented Apr 4, 2020 at 18:38
@guyarad I have checked the json and it is valid, I currently got it working :) — Èlom Nathaniël Kwami Akaba
– Èlom Nathaniël Kwami Akaba, Commented Apr 4, 2020 at 21:34
the JSON you posted in your question is definitely not valid. — Guy
– Guy, Commented Apr 5, 2020 at 11:44

Guy · Accepted Answer · 2020-04-05 11:41:26Z

1

First, x seems to be a dictionary. So doing for y in x: ... will iterate over the keys. In this case it seems to be "totalItemsCount", "pageCount" etc. That's obviously not what you want because you aren't even using y.

The embedded field, as you've used it yourself, is x["_embedded"]["adres"]. As you have identified, it's an array of addresses. All you need to is go over it:

addresses = x["_embedded"]["adres"]
for address in addresses:
    f.writerow([address["openbareruimte"],
        address["huisnummer"],
        address["postcode"],
        address["woonplaats"]])

Few more comments about your code:

When opening a file, you should always use it as a context manager so it will be closed: with open(...) as f: ... (the reason is that if an exception is raised during json loading, the file isn't closed properly).
json can load directly from a file: json.load(f)

Considering the above two comments, the correct way to load the json is:

with open("output.json", "r+") as f:
    x = json.load(f)
# no need to call "f.close()"

with open("test.csv", "w") as f:
    writer = csv.writer(f)
    writer.writerow(["straat","huisnummer","postcode","stad"])
    addresses = x["_embedded"]["adres"]
    for address in addresses:
        f.writerow([address["openbareruimte"],
            address["huisnummer"],
            address["postcode"],
            address["woonplaats"]])

edited Apr 5, 2020 at 11:41

answered Apr 4, 2020 at 18:48

Guy

1,3701 gold badge14 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Èlom Nathaniël Kwami Akaba Over a year ago

Thanks for the reaction. What you mentioned is exactly the part that I did not understand and could not get clear in regards to nested context. Your solution works perfect! I only needed to add [] for the writerow to make it 1 argument. In regards to the open(...), I don't fully understand what you mean. Would you mind elaborating? For now I outputted the results from a .json as I have limited requests to the API. I will update it to json.load(r.get(...))

Guy Over a year ago

@ÈlomNathaniëlKwamiAkaba fixed the missing []. added example on opening the file as a context manager, which saves you the need for calling close and will also close the file if exception is raised.

Thomas · Accepted Answer · 2020-04-04 18:44:31Z

1

You want to loop over items in x["_embedded"]["adres"] and write items for each y

for y in x["_embedded"]["adres"]:
    f.writerow(y["openbareruimte"],
               y["huisnummer"],
               y["postcode"],
               y["woonplaats"])

answered Apr 4, 2020 at 18:44

Thomas

1,0669 silver badges19 bronze badges

2 Comments

Èlom Nathaniël Kwami Akaba Over a year ago

Is there a clear explanation or guide in regards to looping? As this is my issue which I couldn't figure out.

Thomas Over a year ago

For loops need a list or iterator as input (on the right of in). For each loop, the next item in the list will be assigned to your variable (on the left of in - in the above example y). You can then use the item inside the loop statement and do something with it. In your case you wanted to loop over all addresses in your JSON object. The addresses are located at x["_embedded"]["adres"] and already come as a list. You then loop over every address item and extract the fields within.

martineau · Accepted Answer · 2020-04-06 21:40:46Z

1

You need to iterate through the list in the loaded JSON data.

## Convert Output JSON to CSV

import csv, json

with open("output.json", "r") as f:
    x = json.load(f)

with open("subtract_test.csv", "w", newline="") as outp:
    f = csv.writer(outp)
    f.writerow(["straat","huisnummer","postcode","stad"]) # Header.

    for adres in x["_embedded"]["adres"]:
        f.writerow([adres["openbareruimte"],
                    adres["huisnummer"],
                    adres["postcode"],
                    adres["woonplaats"]])

print("Done")

edited Apr 6, 2020 at 21:40

answered Apr 4, 2020 at 18:56

martineau

124k29 gold badges181 silver badges319 bronze badges

1 Comment

Guy Over a year ago

instead of json.loads(f.read()) you should do json.load(f)

Collectives™ on Stack Overflow

How to extract list from nested Json into a CSV?

3 Answers 3

2 Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related