Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Extract key, value pair from nested json file

I am trying to extract the name, longitude and latitude from a json file and convert it into a new .csv file with the columns cityname | longitude | latitude

from this json: https://www.submarinecablemap.com/api/v3/landing-point/landing-point-geo.json


def cables():
    # print(page.text)
    # print(page2.text)
    # print(page3.text)

    json_file = 'exitpoints.json'

    with open(json_file) as json_data:
        data = json.load(json_data)

    # print(data)

    print("City: ", data['features.name'])
    #print("Longitude:  ", data['0'])
    #print("Latitude:  ", data['1'])

i tried all kinds of combinations data[features].data[name] etc…
how do i get this?

Tags:

5

Answers


  1. 0

    In order for you to get the lat and long, you need to first iterate the features list and then unpack the coordinates

    for i in data['features']:
    long = i['geometry']['coordinates'][0]
    lat = i['geometry']['coordinates'][1]
    Login or Signup to reply.
  2. 0

    You can do it like this, the traversal of the json is working like this, where you can get the name, longitude and latitude as well.

    for feature in data['features']:
            name = feature['properties']['name']
            longitude, latitude = feature['geometry']['coordinates']
data = json.loads(json_data)

    Full example

    csv_file_path = "output.csv"

with open(csv_file_path, mode="w", newline='') as csv_file:
    fieldnames = ['name', 'longitude', 'latitude']
    writer = csv.DictWriter(csv_file, fieldnames=fieldnames)
    
    # Write header
    writer.writeheader()

    # traverse data
    for feature in data['features']:
        name = feature['properties']['name']
        longitude, latitude = feature['geometry']['coordinates']
        writer.writerow({'name': name, 'longitude': longitude, 
        'latitude': latitude})
    Login or Signup to reply.
  3. 0

    you can do it like this for example.

    It assumes that you know that your data of interest in the ‘features’ list.

    import requests
import json

res = requests.get(r'https://www.submarinecablemap.com/api/v3/landing-point/landing-point-geo.json')
data = res.content()
data = json.loads(data)

csv_list = [f"{ele['properties']['name']};{';'.join(map(str, ele['geometry']['coordinates']))}" for ele in data['features']]

    Now each row in csv_list contains the name, lon and lat as string. Delimiter is ; since , is used in the name.

    csv_list[0] 
>>> 'Changi South, Singapore;103.98701228871843;1.389044778364536'
    Login or Signup to reply.
  4. 0

    You can also use SimpleNamespace

    from types import SimpleNamespace

import requests

url = "https://www.submarinecablemap.com/api/v3/landing-point/landing-point-geo.json"


def cables():
    response = requests.get(url)
    parsed = response.json(object_hook=lambda d: SimpleNamespace(**d))

    for f in parsed.features:
        print(f.properties.name, f.geometry.coordinates)


if __name__ == "__main__":
    cables()
    Login or Signup to reply.
  5. 0

    Here you go friend, I’ve written the soluton in form of a script for you, but you can put it in a function too.

    It helps that you pretify your JSON so you can understand the structure of it. There are several online prettifiers, but I often use this one: https://jsonformatter.curiousconcept.com/

    When you pretify the JSON you’ve provided it is instantly evident that all of the desired cities with the coordinates are in the features array.

    Next you need to open a json file for reading and a csv file for writing. Then you load the json like you already did, but you also prepare the csv dictionary writer and write a header to it.

    Then you go through each item in the json and take what you need and write it into the csv file. In the end you close the files, you don’t have to, but it’s a good practice.

    #!/usr/bin/env python3

import csv
import json

json_file = 'exitpoints.json'
csv_file = 'exitpoints.csv'

with open(json_file, 'r') as infile, open(csv_file, 'w', newline='') as outfile:
    data = json.load(infile)
    writer = csv.DictWriter(outfile, ['cityname', 'longitude', 'latitude'])
    writer.writeheader()
    for row in data['features']:
        cityname = row['properties']['name']
        longitude = row['geometry']['coordinates'][0]
        latitude = row['geometry']['coordinates'][1]
        
        writer.writerow({'cityname': cityname, 'longitude': longitude, 'latitude': latitude})

infile.close()
outfile.close()

    This script will create the CSV file named exitpoints.csv with the colums you’ve requested cityname|longitude|latitude.

    To run it, don’t forget to chmod +x make it executable if you are using a Unix based system.

    Cheers! 🍻

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search