Add JSON from URL to DataFrame in a loop

November 14, 2021

I’m trying to download JSON data and add it to a DataFrame, but I’m limited to 10 results per request.

The Loop and Offset works, but I can’t get it to add the new data to the DataFrame, it replaces it each time.

I’ve tried using a df2 df2 = df.append(df) and appending the df that’s created each loop, but that’s not working either.

offset = 10
while offset < 1000:
    url = f"https://someurl/?limit=10&offset={offset}"
    data = pd.read_json(url)
    df = pd.json_normalize(data['results'])
    offset = offset + 10
    
    df = df.append(df)
    print(df)

>Solution :

See below.
The idea is to collect the df into list and to concat list items.
Note that code cant be tested since the URL is a dummy one

import pandas as pd

offset = 10
df_list = []
while offset < 100:
  data = pd.read_json(f"https://someurl/?limit=10&offset={offset}")
  df_list.append(pd.json_normalize(data['results']))
  offset = offset + 10
merged_df = pd.concat(df_list)
print(merged_df)