How to structure a looping API call to make the JSON result usable?

Question

I have an API I am making calls to that limits 50 results per request as well as 180 requests per minute. However, an object could have between 10 and 50,000 results in total. I need to structure the resulting JSON in a usable format or convert it to something I can more easily access like pandas, I'm just not sure which is the better option. The plan is to iterate through each of the object id's that I need data from. My current API call looks like this:

import requests, time
import json, pandas as pd

#object id's to iterate through
# object_id = ['13','16','95','93','77']
object_id = ['13']
#this block finds the count of entries
for obj in object_id:
  data = []
  info_url = 'https://api.ontraport.com/1/objects/getInfo?objectID={}'.format(obj)
  headers = {
    'Api-Appid': 'XXXXXXXXX',
    'Api-Key': 'XXXXXXXXX'
  }
  response = requests.get(info_url, headers=headers).json()
  obj_count = int(response['data']['count'])
#this block ensures all entries are pulled as it iterates in groups of 50
  start = 1
  while start / obj_count <= 1:
    url = 'https://api.ontraport.com/1/objects?objectID={}&start={}'.format(obj, start)
    headers = {
      'Api-Appid': 'XXXXXXXXX',
      'Api-Key': 'XXXXXXXXX'
    }
    response = requests.get(url, headers=headers).json()
    data.append(response)
    start += 50
    time.sleep(0.34)
  with open('C:/Desktop/data{}.txt'.format(obj), 'w') as outfile:
    json.dump(data, outfile)

The API produces a result of:

{
   "code": 0,
   "data": [{
              "id":"111",
              "date":"1441326063"
           },
           {
              "id":"132",
              "date":"1441526112"
           }],
   "account_id": 0
}

I only care about the part inside the "data" tag. However when I append the results together, it becomes a large JSON array of JSON objects for each pull and within each object "data" is another array. Can it be structured to append only the data results and then formatted into a pandas dataframe?

Answer 1

if there is only 1 data block you can simply save it like so, using json dictionary format.

data.extend(response['Data'])

So for example:

import pandas as pd

Data = {
"code": 0,
"data": [{
            "id":"111",
            "date":"1441326063"
        },
        {
            "id":"132",
            "date":"1441526112"
        }],
"account_id": 0
}

Data2 = {
"code": 0,
"data": [{
            "id":"134311",
            "date":"1441326063"
        },
        {
            "id":"133432",
            "date":"1441526112"
        }],
"account_id": 0
}

data = []

data.extend(Data['data'])
data.extend(Data2['data'])

print(data)
>>> [{'id': '111', 'date': '1441326063'}, {'id': '132', 'date': '1441526112'}, {'id': '134311', 'date': '1441326063'}, {'id': '133432', 'date': '1441526112'}]

df = pd.DataFrame(data)
df.to_csv('example.csv', index=False)
print(df)

>>>          date      id
     0  1441326063     111
     1  1441526112     132
     2  1441326063  134311
     3  1441526112  133432

Answer 2

I hope this work, for further information take a look at List comprehension

import pandas as pd
import json

json_str = """[
{
   "code": 0,
   "data": [{
              "id":"111",
              "date":"1441326063"
           },
           {
              "id":"132",
              "date":"1441526112"
           }],
   "account_id": 0
},
{
   "code": 0,
   "data": [{
              "id":"111",
              "date":"1441326063"
           },
           {
              "id":"132",
              "date":"1441526112"
           }],
   "account_id": 0
}
]"""

json_dict = json.loads(json_str)

df = pd.DataFrame([d for y in json_dict for d in y['data']])