why i cant load json file to pandas data frame-CodePudding

My code:

import json
import requests
responseGBP=requests.get("https://public.opendatasoft.com/api/records/1.0/search/?dataset=euro-exchange-rates&sort=date&facet=currency&rows=30&facet=date&q=date:[2020-12-01 TO 2020-12-31]&refine.currency=GBP")
response_jGBP=responseGBP.content.decode("utf-8")
df = pd.read_json(response_jGBP)
df

I'm getting this error:

All arrays must be of the same length

I want to get the currency data , but i cant covert the json file to pandas dataframe. I'm getting "All arrays must be of the same length" Error

CodePudding user response：

Because you didn't analyze dictionary structure properly.

You have to understand that for all columns in pandas rows will be of same count, and you can also load specific part of response if you want.

import pandas as pd
res = requests.get("https://public.opendatasoft.com/api/records/1.0/search/?dataset=euro-exchange-rates&sort=date&facet=currency&rows=30&facet=date&q=date:[2020-12-01 TO 2020-12-31]&refine.currency=GBP")
df = pd.DataFrame(res.json()["records"])

Above code will load the data but not sure if that is you wanted, but you got the idea you have to look at first JSON structure. If needed, you have to modify it as well to load as data frame.

CodePudding user response：

Add lines=True to your pd.read_json function.

import json
import requests
import pandas as pd

responseGBP=requests.get("https://public.opendatasoft.com/api/records/1.0/search/?dataset=euro-exchange-rates&sort=date&facet=currency&rows=30&facet=date&q=date:[2020-12-01 TO 2020-12-31]&refine.currency=GBP")
response_jGBP=responseGBP.content.decode("utf-8")
df = pd.read_json(response_jGBP, lines=True)

print(df['records'][0][0]['fields'])

Output:

{'currency': 'GBP', 'rate': 0.8990300000000001, 'date': '2020-12-31'}