JSON querying through dataframe

Imagine you have 3 API responses:

test =  df['APIOutput'].apply(lambda url: requests.get(url, verify=False))

0    <Response [200]>
1    <Response [200]>
2    <Response [200]>
Name: APIOutput, dtype: object


type(test[0])

requests.models.Response

Now, I want to query each response on key in the dictionary for each response.

Came up with the following:

pprint.pprint(test.to_json()['data']['items'][0]['addresses'][0]['city'])

However, not working:

TypeError: string indices must be integers

desired output:

test['City']

Amsterdam
Deventer 
Rotterdam

Please help!

2 answers

  • answered 2021-05-03 17:51 hd1

    I have no crystal ball, but I'd posit that you need a dictionary as the 'ApiOutput' column and not the request.get return value. Try changing:

    test = df['APIOutput'].apply(lambda url: requests.get(url, verify=False))

    to:

    test = df['APIOutput'].apply(lambda url: requests.get(url, verify=False).json())

    Let me know if it works.

  • answered 2021-05-03 17:51 mqrtel

    You don't need to use pprint here. I suggest you move the request handling to a function, something like:

    def get_city(url):
      r = requests.get(url, verify=False)
      data = r.json()
      return data['data']['items'][0]['addresses'][0]['city']
    
    df["city"] = df.APIOutput.apply(get_city)