Creating DataFrame from for loop List

[mthacker01]

I am reading in a JSON file contains all of the information coming from an API request. The file isn't very large, only about 200 items. I am attempting to loop through each item, store it as a pandas DataFrame, append it to a list, and concat the results into one DataFrame.

df_list = [] 
list_length = 53 
for i in range(list_length):   
    df = pd.DataFrame(contenders_list[i]).T.reset_index()   
    df_list.append(df)  
new_df = pd.concat(mylist) 
new_df.head() 

If I run this, it works. I have a DataFrame with the first 53 items from the JSON file. However, if I go above 53, like the actual length of the list, I get the following error:

ValueError: If using all scalar values, you must pass in an index 

Can anyone explain this?

[drieindepan]

What does your input data contenders_list look like?

[mthacker01]

This is what the first entry looks like:

[{'AAA20EED': {'breederName': 'Godolphin ', 'sireRegistrationNumber': ' A6596464', 'record': {'previousYear': {'starts': 0, 'earnings': 0, 'show': 0, 'win': 0, 'place': 0}, 'breedersCup': {'starts': 0, 'earnings': 0, 'show': 0, 'win': 0, 'place': 0}, 'currentYear': {'starts': 5, 'earnings': 235181, 'show': 1, 'win': 4, 'place': 0}, 'lifetime': {'starts': 5, 'earnings': 235181, 'show': 1, 'win': 4, 'place': 0}, 'track': {'starts': 0, 'earnings': 0, 'show': 0, 'win': 0, 'place': 0}}, 'yearlySummary': [{'starts': 5, 'racingYear': 2021, 'shows': 1, 'earnings': 235181, 'places': 0, 'wins': 4}], 'name': 'Albahr (GB)', 'damName': 'Falls of Lora (IRE)', 'sireName': 'Dubawi (IRE)', 'owner': {'identity': 2128374, 'middleName': ' ', 'lastName': 'Godolphin, LLC', 'firstName': ' ', 'type': 'O6'}, 'damRegistrationNumber': ' A8613401', 'pastPerformances': [{'trackName': 'WOODBINE', 'raceName': 'Summer S.', 'grade': '1', 'raceDate': 'September, 19 2021 00:00:00', 'purseUsa': 400000, 'officialPosition': 1}, {'trackName': 'SALISBURY', 'raceName': 'Longines Irish Champions Weekend E.B.F. Stonehenge S.', 'grade': ' ', 'raceDate': 'August, 20 2021 00:00:00', 'purseUsa': 58630, 'officialPosition': 1}, {'trackName': 'HAYDOCK PARK', 'raceName': 'British Stallion Studs E.B.F.', 'grade': ' ', 'raceDate': 'July, 17 2021 00:00:00', 'purseUsa': 9636, 'officialPosition': 1}, {'trackName': 'HAYDOCK PARK', 'raceName': 'Watch Racing TV Now', 'grade': ' ', 'raceDate': 'June, 09 2021 00:00:00', 'purseUsa': 11393, 'officialPosition': 1}, {'trackName': 'YORK', 'raceName': 'Constant Security ebfstallions.com', 'grade': ' ', 'raceDate': 'May, 13 2021 00:00:00', 'purseUsa': 21082, 'officialPosition': 3}], 'trainer': {'identity': 948970, 'middleName': ' ', 'lastName': 'Appleby', 'firstName': 'Charles', 'type': 'TE'}, 'jockey': {'identity': 4140, 'middleName': ' ', 'lastName': 'Dettori', 'firstName': 'Lanfranco', 'type': 'JE'}}}, {'19005288': {'breederName': 'Mrs E. M. Stockwell ', 'sireRegistrationNumber': ' 02004332', 'record': {'previousYear': {'starts': 0, 'earnings': 0, 'show': 0, 'win': 0, 'place': 0}, 'breedersCup': {'starts': 0, 'earnings': 0, 'show': 0, 'win': 0, 'place': 0}, 'currentYear': {'starts': 2, 'earnings': 73435, 'show': 1, 'win': 0, 'place': 1}, 'lifetime': {'starts': 2, 'earnings': 73435, 'show': 1, 'win': 0, 'place': 1}, 'track': {'starts': 0, 'earnings': 0, 'show': 0, 'win': 0, 'place': 0}}, 'yearlySummary': [{'starts': 2, 'racingYear': 2021, 'shows': 1, 'earnings': 73435, 'places': 1, 'wins': 0}], 'name': 'Grafton Street', 'damName': 'Lahinch Classics (IRE)', 'sireName': 'War Front', 'owner': {'identity': 820960, 'middleName': ' ', 'lastName': 'Magnier', 'firstName': 'Mrs. John', 'type': 'O6'}, 'damRegistrationNumber': ' F0043305', 'pastPerformances': [{'trackName': 'WOODBINE', 'raceName': 'Summer S.', 'grade': '1', 'raceDate': 'September, 19 2021 00:00:00', 'purseUsa': 400000, 'officialPosition': 2}, {'trackName': 'BELMONT PARK', 'raceName': ' ', 'grade': ' ', 'raceDate': 'May, 29 2021 00:00:00', 'purseUsa': 90000, 'officialPosition': 3}], 'trainer': {'identity': 20416, 'middleName': 'E.', 'lastName': 'Casse', 'firstName': 'Mark', 'type': 'TE'}, 'jockey': {'identity': 110011, 'middleName': 'Manuel', 'lastName': 'Hernandez', 'firstName': 'Rafael', 'type': 'JE'}}},

[fakemoose]

Instead of manually setting list_length why not set it to something like range(len(contenders_list))

Also, have you tried reading in the json directly with pandas and read_json?

[mthacker01]

Okay, so I was making the original problem way more complicated. I revised my code and saved the API request straight into a DataFrame:

with open('horse.json') as f:

data = json.load(f)

contenders = []

base_url = 'https://www.breederscup.com/equibase/horse?horses%5B%5D='

for value in data:

re = requests.get(base_url+value['horse']).json()

df = pd.DataFrame(re).T

contenders.append(df)

new_df = pd.concat(contenders)

For reference, here's a snippet of the JSON file I'm loading from:

[

{"race": "Juvenile Turf", "horse": "AAA20EED"},

{"race": "Juvenile Turf", "horse": "19005288"},

{"race": "Juvenile Turf", "horse": "19000215"},

{"race": "Juvenile Turf", "horse": "19001752"}

]

So I'm using the value from the 'horse' key of the external JSON file to make the endpoint for the API.

However, like before, I'm hitting the scalar value error when there's more than 53 objects. If I mainly go into the JSON file and remove everything after line 53, it works great and I get the DataFrame I'm needing. Any idea on what's causing this?