How to interpret this JSON file?

Question

I'm trying to interpret this JSON file but I couldn't figure it out.

{
    "results": [
        {
            "fsq_id": "4dc586fbcc3ff3b3045e2ef3",
            "categories": [
                {
                    "id": 17056,
                    "name": "Flower Store",
                    "icon": {
                        "prefix": "https://ss3.4sqi.net/img/categories_v2/shops/flowershop_",
                        "suffix": ".png"
                    }
                }
            ],
            "chains": [],
            "distance": 121,
            "geocodes": {
                "main": {
                    "latitude": 60.24277,
                    "longitude": 24.92642
                }
            },
            "location": {
                "address": "Pakilantie 69",
                "country": "FI",
                "locality": "Helsinki",
                "postcode": "00660",
                "region": "Uusimaa"
            },
            "name": "Pakilan Kukkatalo",
            "related_places": {},
            "timezone": "Europe/Helsinki"
        },
        {
            "fsq_id": "4d972d612bd6f04de0524850",
            "categories": [
                {
                    "id": 18064,
                    "name": "Soccer Field",
                    "icon": {
                        "prefix": "https://ss3.4sqi.net/img/categories_v2/arts_entertainment/stadium_soccer_",
                        "suffix": ".png"
                    }
                }
            ],
            "chains": [],
            "distance": 149,
            "geocodes": {
                "main": {
                    "latitude": 60.24221613213154,
                    "longitude": 24.929252754506127
                }
            },
            "location": {
                "address": "Pakila",
                "country": "FI",
                "cross_street": "",
                "locality": "Helsinki",
                "postcode": "",
                "region": "Uusimaa"
            },
            "name": "HPS-halli",
            "related_places": {},
            "timezone": "Europe/Helsinki"
        },
        {
            "fsq_id": "4f88508ae4b00a6260d51c09",
            "categories": [
                {
                    "id": 13016,
                    "name": "Lounge",
                    "icon": {
                        "prefix": "https://ss3.4sqi.net/img/categories_v2/nightlife/default_",
                        "suffix": ".png"
                    }
                }
            ],
            "chains": [],
            "distance": 177,
            "geocodes": {
                "main": {
                    "latitude": 60.24322,
                    "longitude": 24.925877
                }
            },
            "location": {
                "country": "FI",
                "cross_street": "",
                "postcode": ""
            },
            "name": "Vuokrasauna.fi",
            "related_places": {},
            "timezone": "Europe/Helsinki"
        },
        {
            "fsq_id": "4c097ac53c70b713fdd3275b",
            "categories": [
                {
                    "id": 17069,
                    "name": "Grocery Store / Supermarket",
                    "icon": {
                        "prefix": "https://ss3.4sqi.net/img/categories_v2/shops/food_grocery_",
                        "suffix": ".png"
                    }
                }
            ],
            "chains": [],
            "distance": 280,
            "geocodes": {
                "main": {
                    "latitude": 60.239423847583105,
                    "longitude": 24.929011346258985
                }
            },
            "location": {
                "address": "Pakilantie 61",
                "country": "FI",
                "locality": "Helsinki",
                "postcode": "00660",
                "region": "Uusimaa"
            },
            "name": "S-market Pakila",
            "related_places": {
                "children": []
            },
            "timezone": "Europe/Helsinki"
        },
        {
            "fsq_id": "5177bebce4b036dbba157074",
            "categories": [
                {
                    "id": 17082,
                    "name": "Furniture and Home Store",
                    "icon": {
                        "prefix": "https://ss3.4sqi.net/img/categories_v2/shops/furniture_",
                        "suffix": ".png"
                    }
                }
            ],
            "chains": [],
            "distance": 282,
            "geocodes": {
                "main": {
                    "latitude": 60.24412692651818,
                    "longitude": 24.92534364400254
                }
            },
            "location": {
                "address": "Elontie 71",
                "country": "FI",
                "cross_street": "",
                "locality": "Helsinki",
                "postcode": "00660",
                "region": "Uusimaa"
            },
            "name": "Parkettilattiat Oy",
            "related_places": {},
            "timezone": "Europe/Helsinki"
        },
        {
            "fsq_id": "4e95b46e9adfced1a955a6e1",
            "categories": [
                {
                    "id": 17035,
                    "name": "Drugstore",
                    "icon": {
                        "prefix": "https://ss3.4sqi.net/img/categories_v2/shops/pharmacy_",
                        "suffix": ".png"
                    }
                }
            ],
            "chains": [],
            "distance": 388,
            "geocodes": {
                "main": {
                    "latitude": 60.23825881820312,
                    "longitude": 24.928175247924628
                }
            },
            "location": {
                "address": "Pakilantie 56",
                "country": "FI",
                "cross_street": "",
                "locality": "Helsinki",
                "postcode": "00660",
                "region": "Uusimaa"
            },
            "name": "Pakilan Apteekki",
            "related_places": {},
            "timezone": "Europe/Helsinki"
        },
        {
            "fsq_id": "5184e1f3498ef45e57b9a417",
            "categories": [
                {
                    "id": 13034,
                    "name": "Caf\u00e9",
                    "icon": {
                        "prefix": "https://ss3.4sqi.net/img/categories_v2/food/cafe_",
                        "suffix": ".png"
                    }
                },
                {
                    "id": 13065,
                    "name": "Restaurant",
                    "icon": {
                        "prefix": "https://ss3.4sqi.net/img/categories_v2/food/default_",
                        "suffix": ".png"
                    }
                }
            ],
            "chains": [],
            "distance": 438,
            "geocodes": {
                "main": {
                    "latitude": 60.237879766432634,
                    "longitude": 24.928877151037955
                }
            },
            "location": {
                "address": "Pakilantie 55",
                "country": "FI",
                "cross_street": "Osuuskunnantie",
                "locality": "Helsinki",
                "postcode": "00660",
                "region": "Uusimaa"
            },
            "name": "Kahvila Helmi",
            "related_places": {},
            "timezone": "Europe/Helsinki"
        },
        {
            "fsq_id": "58ab1e4826a4075f5fa3a891",
            "categories": [
                {
                    "id": 13012,
                    "name": "Hookah Bar",
                    "icon": {
                        "prefix": "https://ss3.4sqi.net/img/categories_v2/nightlife/hookahbar_",
                        "suffix": ".png"
                    }
                }
            ],
            "chains": [],
            "distance": 366,
            "geocodes": {
                "main": {
                    "latitude": 60.239296,
                    "longitude": 24.922197
                }
            },
            "location": {
                "country": "FI",
                "cross_street": "",
                "postcode": ""
            },
            "name": "Noora's",
            "related_places": {},
            "timezone": "Europe/Helsinki"
        },
        {
            "fsq_id": "58296da35cc1d73f7580d214",
            "categories": [
                {
                    "id": 13334,
                    "name": "Sandwich Restaurant",
                    "icon": {
                        "prefix": "https://ss3.4sqi.net/img/categories_v2/food/deli_",
                        "suffix": ".png"
                    }
                }
            ],
            "chains": [],
            "distance": 369,
            "geocodes": {
                "main": {
                    "latitude": 60.238425,
                    "longitude": 24.928056
                }
            },
            "location": {
                "address": "Pakilantie 56",
                "country": "FI",
                "cross_street": "",
                "locality": "Helsinki",
                "postcode": "00660",
                "region": "Uusimaa"
            },
            "name": "House of Sandwiches",
            "related_places": {},
            "timezone": "Europe/Helsinki"
        },
        {
            "fsq_id": "4c52d3d9989eb7137441b226",
            "categories": [
                {
                    "id": 17069,
                    "name": "Grocery Store / Supermarket",
                    "icon": {
                        "prefix": "https://ss3.4sqi.net/img/categories_v2/shops/food_grocery_",
                        "suffix": ".png"
                    }
                }
            ],
            "chains": [
                {
                    "id": "e5a2fd48-9989-4012-a8e4-45404edc24e6",
                    "name": "R-Kioski"
                }
            ],
            "distance": 385,
            "geocodes": {
                "main": {
                    "latitude": 60.23828070026192,
                    "longitude": 24.928105045660168
                }
            },
            "location": {
                "address": "Pakilantie 56",
                "country": "FI",
                "locality": "Helsinki",
                "postcode": "00660",
                "region": "Uusimaa"
            },
            "name": "R-Kioski",
            "related_places": {},
            "timezone": "Europe/Helsinki"
        },
        {
            "fsq_id": "4cce9331ba0a548127874959",
            "categories": [
                {
                    "id": 13064,
                    "name": "Pizzeria",
                    "icon": {
                        "prefix": "https://ss3.4sqi.net/img/categories_v2/food/pizza_",
                        "suffix": ".png"
                    }
                },
                {
                    "id": 13145,
                    "name": "Fast Food Restaurant",
                    "icon": {
                        "prefix": "https://ss3.4sqi.net/img/categories_v2/food/fastfood_",
                        "suffix": ".png"
                    }
                }
            ],
            "chains": [],
            "distance": 465,
            "geocodes": {
                "main": {
                    "latitude": 60.24571426909511,
                    "longitude": 24.924457646189502
                }
            },
            "location": {
                "address": "Pakilantie 82",
                "country": "FI",
                "cross_street": "Ripusuontie",
                "locality": "Helsinki",
                "postcode": "00660",
                "region": "Uusimaa"
            },
            "name": "Pakilan Pizza-Kebab",
            "related_places": {},
            "timezone": "Europe/Helsinki"
        }
    ],
    "context": {
        "geo_bounds": {
            "circle": {
                "center": {
                    "latitude": 60.241687,
                    "longitude": 24.9267659
                },
                "radius": 500
            }
        }
    }
}

I want to get 'name', "latitude", "longitude", and 'categories'.

I tried this code but it didn't work:

def getNearbyVenues(names, latitudes, longitudes, radius=500):
    headers = {
        "Accept": "application/json",
        "Authorization": "fsq363333333###JtJOrcBK3WguBrzyryQuTrO3hhhsFMzZ3CJo="
    }

    URL = "https://api.foursquare.com/v3/places/search?ll={},{}&radius={}&limit={}"

    venues_list = []
    for name, lat, lng in zip(names, latitudes, longitudes):
        print(name)

        url = URL.format(lat, lng, radius, LIMIT)
        results = requests.request("GET", url, headers=headers).json()['results']['main'][0]['location']
        venues_list.append([(
            name,
            lat,
            lng,
            v['venue']['name'],
            v['venue']['location']['lat'],
            v['venue']['location']['lng'],
            v['venue']['categories'][0]['name']) for v in results])

        nearby_venues = pd.DataFrame([item for venue_list in venues_list for item in venue_list])
        nearby_venues.columns = ['Neighborhood',
                      'Neighborhood Latitude',
                      'Neighborhood Longitude',
                      'Venue',
                      'Venue Latitude',
                      'Venue Longitude',
                      'Venue Category']

    return(nearby_venues)

What should I edit so it works?

Use triple backticks not single for long lines. also don't post long line of json if possible - look up what does MRE mean. — rv.kvetch, Jan 29 '22 at 17:43

shivankgtm · Answer 1 · 2022-01-29T19:18:34.887

I cut your data into two entries so, you don't get to see a lot of data.you can scroll to bottom to see what code I wrote.

data = {
    "results": [
        {
            "fsq_id": "4dc586fbcc3ff3b3045e2ef3",
            "categories": [
                {
                    "id": 17056,
                    "name": "Flower Store",
                    "icon": {
                        "prefix": "https://ss3.4sqi.net/img/categories_v2/shops/flowershop_",
                        "suffix": ".png",
                    },
                }
            ],
            "chains": [],
            "distance": 121,
            "geocodes": {"main": {"latitude": 60.24277, "longitude": 24.92642}},
            "location": {
                "address": "Pakilantie 69",
                "country": "FI",
                "locality": "Helsinki",
                "postcode": "00660",
                "region": "Uusimaa",
            },
            "name": "Pakilan Kukkatalo",
            "related_places": {},
            "timezone": "Europe/Helsinki",
        },
        {
            "fsq_id": "4d972d612bd6f04de0524850",
            "categories": [
                {
                    "id": 18064,
                    "name": "Soccer Field",
                    "icon": {
                        "prefix": "https://ss3.4sqi.net/img/categories_v2/arts_entertainment/stadium_soccer_",
                        "suffix": ".png",
                    },
                }
            ],
            "chains": [],
            "distance": 149,
            "geocodes": {
                "main": {"latitude": 60.24221613213154, "longitude": 24.929252754506127}
            },
            "location": {
                "address": "Pakila",
                "country": "FI",
                "cross_street": "",
                "locality": "Helsinki",
                "postcode": "",
                "region": "Uusimaa",
            },
            "name": "HPS-halli",
            "related_places": {},
            "timezone": "Europe/Helsinki",
        },S
    ],
    "context": {
        "geo_bounds": {
            "circle": {
                "center": {"latitude": 60.241687, "longitude": 24.9267659},
                "radius": 500,
            }
        }
    },
}

df_mapping = []
from unicodedata import category
import pandas as pd
result = data['results']
for entry in result:
    name = entry["name"]
    category = entry["categories"][0]['name']
    geocode_dct = entry['geocodes']['main']
    lat = geocode_dct['latitude']
    lon = geocode_dct['longitude']

    df_mapping.append({
        "name": name,
        "lat": lat,
        "lon": lon,
        "category": category
    })
df = pd.DataFrame(df_mapping)
print(df)

output

                   name        lat        lon                     category
0     Pakilan Kukkatalo  60.242770  24.926420                 Flower Store
1             HPS-halli  60.242216  24.929253                 Soccer Field
2        Vuokrasauna.fi  60.243220  24.925877                       Lounge
3       S-market Pakila  60.239424  24.929011  Grocery Store / Supermarket
4    Parkettilattiat Oy  60.244127  24.925344     Furniture and Home Store
5      Pakilan Apteekki  60.238259  24.928175                    Drugstore
6         Kahvila Helmi  60.237880  24.928877                         Café
7               Noora's  60.239296  24.922197                   Hookah Bar
8   House of Sandwiches  60.238425  24.928056          Sandwich Restaurant
9              R-Kioski  60.238281  24.928105  Grocery Store / Supermarket
10  Pakilan Pizza-Kebab  60.245714  24.924458                     Pizzeria

can you please write the code the gets me all what I want for the json ?https://www.dropbox.com/s/z5x9rfobyc9l5jn/json.txt?dl=0 here is the whole json output , my problem is I don't know how to fill what I want in my code — Kaokor, Jan 29 '22 at 18:00
`url = URL.format(lat, lng, radius, LIMIT) results = requests.request("GET", url, headers=headers).json()['results'] venues_list.append([( name, lat, lng, v['categories']['name'], v['geocodes']['main']['latitude'], v['geocodes']['main']['longitude'], v["name"]) for v in results])` i tried this but didn't work — Kaokor, Jan 29 '22 at 18:11
Hi @Kaokor, I am still not able to understand what exactly you want. can you edit your questions with your desired output and what exactly you are doing now. — shivankgtm, Jan 29 '22 at 18:30
can you please have look at section 2.5 https://github.com/bloonsinthesky/Data-Science-Portfolio/blob/main/The%20Battle%20of%20the%20Neighborhoods/The%20Battle%20of%20the%20Neighborhoods.ipynb , I'm trying to get the same info , but the json file format is different than what is in the link , so I want to edit the code in my question to get the same data , got it ? thank a lot for help — Kaokor, Jan 29 '22 at 18:38
Hi @Kaokor, I went through the link you shared, but I can't see the relation of a json file and hitting api in the 2.5. do you want to say, that you have a json file and you want some data out of that if that is the case, what is the use of url of api you shared. — shivankgtm, Jan 29 '22 at 18:42
the function in the question parse the data to json and then I want add the data to my df as what happened in 2.5 section — Kaokor, Jan 29 '22 at 18:49
I am sorry but that ll require anyone to reproduce your code, and I don't have api key to hit the api and see the desired output. I can tell you how you can put json that into df if you tell me at what specific point your code is not working as expected. Thanks — shivankgtm, Jan 29 '22 at 18:52

How to interpret this JSON file?

1 Answers1

Linked