Extracting values from json containing list as values to key

user_13604788 0
Viewed 108

i am currently doing a data science project (beginner) and have the following scenario :

  1. I have a dataframe with Pincode , address and city (approx 57000 rows)
  2. I need the geo coordinates of the same

i am trying to use the Bing Map API to get the coordinates in python. But i am stuck at parsing the Json response.

pincodelist=[]

import json

i=0

for i in range(5): #just trying with first 5 rows

    countryRegion = "IN"
    locality = df_all.iloc[13,6] #references the location column
    postalCode =df_all.iloc[13,12] #references the pincode column
    addressLine = df_all.iloc[13,0] #references the address
    BingMapsKey = 'my api key'

    url="http://dev.virtualearth.net/REST/v1/Locations?countryRegion="+str(countryRegion)+"&locality="+str(locality)+"&postalCode=" + str(postalCode)+"&addressLine="+str(addressLine)+"&key="+str(BingMapsKey)
     # make the GET request
    results = requests.get(url).json()

    pincodelist.append([
                    addressLine, 
                    postalCode,
                    results(['resourceSets']['resources']['bbox'])])
print(pincodelist)

I get the following error:

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-198-396207c04bc6> in <module>
     20                     addressLine,
     21                     postalCode,
---> 22                     results(['resourceSets']['resources']['bbox'])])
     23 print(pincodelist)
     24 

TypeError: list indices must be integers or slices, not str

can somebody please help me how to parse this json response? the info i need is "bbox" which contains the coordinates.


    {
       "authenticationResultCode":"ValidCredentials",
       "brandLogoUri":"http://dev.virtualearth.net/Branding/logo_powered_by.png",
       "copyright":"Copyright © 2020 Microsoft and its suppliers. All rights reserved. This API cannot be accessed and the content and any results may not be used, reproduced or transmitted in any manner without express written permission from Microsoft Corporation.",
       "resourceSets":[
          {
             "estimatedTotal":1,
             "resources":[
                {
                   "__type":"Location:http://schemas.microsoft.com/search/local/ws/rest/v1",
                   "bbox":[
                      12.91842713075696,
                      77.56459359208381,
                      12.926152565898313,
                      77.57516165693963
                   ],
                   "name":"Banashankari, India",
                   "point":{
                      "type":"Point",
                      "coordinates":[
                         12.922289848327637,
                         77.56987762451172
                      ]
                   },
                   "address":{
                      "adminDistrict":"KA",
                      "adminDistrict2":"Bengaluru",
                      "countryRegion":"India",
                      "formattedAddress":"Banashankari, India",
                      "locality":"Bengaluru"
                   },
                   "confidence":"High",
                   "entityType":"Neighborhood",
                   "geocodePoints":[
                      {
                         "type":"Point",
                         "coordinates":[
                            12.922289848327637,
                            77.56987762451172
                         ],
                         "calculationMethod":"Rooftop",
                         "usageTypes":[
                            "Display"
                         ]
                      }
                   ],
                   "matchCodes":[
                      "Good"
                   ]
                }
             ]
          }
       ],
       "statusCode":200,
       "statusDescription":"OK",
       "traceId":"4e23d3d9bef84411846539f3113cc06b|DU00000D7F|0.0.0.1|Ref A: F8AB7E576A9B47B1A86B3DE04F1058A9 Ref B: DB3EDGE1616 Ref C: 2020-05-24T11:30:41Z"
    }

Also would be helpful if you can refer any other location data service considering the numbers of rows to query. As a student a paid service is not feasible for me.

rest python json
2 Answers

resourceSets is a list of objects, as can be seen by the square brackets that follow the key, so you need to specify a numeric index to get an element out of it. The same goes for the resources key.

{'resourceSets': [{'estimatedTotal': 1, 'resources': [{'__type': ...

In you example, there is only one resourceSet, so we can just get the first element:

# resource sets - a list
resource_sets = results['resourceSets']

# resource set object
resource_set = resource_sets[0]

# resources - a list
resources = resource_set['resources']

# first resource
resource = resources[0]

# bbox - a list with 4 numbers inside
bbox = resource['bbox']

# Or in one line:
results['resourceSets'][0]['resources'][0]['bbox']
user_20290970

Parse the response into json using json.parse.

I tried doing it in javaScript and assigned the json response to a variable t.

was able to extract using this reference, some keys have list as their values that could be the problem you are facing.

t.resourceSets[0].resources[0].bbox
(4) [12.91842713075696, 77.56459359208381, 12.926152565898313, 77.57516165693963]
user_74194570
Related