How to read Json data with unbalanced array length in Python-CodePudding

I have been trying to fetch Json data from an API using Python so that I can transfer that data to sqlite3 database. The issue is that the data is unbalanced. My end goal is to transfer this json data to a .db file in sqlite3. Here is what I did:

import pandas as pd
url = "https://baseballsavant.mlb.com/gf?game_pk=635886"
df = pd.read_json(url)
print(df)

This is the error I am getting:

raise ValueError("All arrays must be of the same length")
ValueError: All arrays must be of the same length

CodePudding user response：

It's not obvious what you want your final DataFrame to look like, but appending "orient='index'" avoids the problem in this case.

import pandas as pd
url = "https://baseballsavant.mlb.com/gf?game_pk=635886"
df = pd.read_json(url, orient='index')
print(df)

You could also request the data with, for example, the requests module and prepare it before loading it into a DataFrame

import requests
url = "https://baseballsavant.mlb.com/gf?game_pk=635886"
response = requests.get(url)

data = response.json()

"""
Do data transformations here
"""

df = pd.DataFrame.from_dict(data)