How to convert a json object into a dataframe when arrays are of different lengths?

user032020 0
Viewed 45

I am trying to convert a json format into a dataframe but getting an error saying "All arrays must be of the same length". Below is the code. Any advice are highly appreciated.

I have the following codes

import pandas as pd
import requests
import json
from pandas import json_normalize

resp = requests.get("https://unstats.un.org/SDGAPI/v1/sdg/Indicator/Data?indicator=7.2.1")

resp.json()

pd.DataFrame(resp.json())
ValueError: All arrays must be of the same length
python-requests dataframe pandas python-3.x
1 Answers

Here is how to convert the entire json using Pandas from_dict and transpose (T) methods:

import pandas as pd
import requests

resp = requests.get(
    "https://unstats.un.org/SDGAPI/v1/sdg/Indicator/Data?indicator=7.2.1"
)

df = pd.DataFrame.from_dict(resp.json(), orient="index").T

print(df.info())
# Output
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 1 entries, 0 to 0
Data columns (total 7 columns):
 #   Column         Non-Null Count  Dtype
---  ------         --------------  -----
 0   size           1 non-null      object
 1   totalElements  1 non-null      object
 2   totalPages     1 non-null      object
 3   pageNumber     1 non-null      object
 4   attributes     1 non-null      object
 5   dimensions     1 non-null      object
 6   data           1 non-null      object
dtypes: object(7)
memory usage: 184.0+ bytes

But if you are interested in specific values of your json, which, I presume, are the ones associated with the key data, then simply do:

df = pd.DataFrame(resp.json()["data"])

print(df.info())
# Output
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 25 entries, 0 to 24
Data columns (total 21 columns):
 #   Column             Non-Null Count  Dtype  
---  ------             --------------  -----  
 0   goal               25 non-null     object 
 1   target             25 non-null     object 
 2   indicator          25 non-null     object 
 3   series             25 non-null     object 
 4   seriesDescription  25 non-null     object 
 5   seriesCount        25 non-null     object 
 6   geoAreaCode        25 non-null     object 
 7   geoAreaName        25 non-null     object 
 8   timePeriodStart    25 non-null     float64
 9   value              25 non-null     object 
 10  valueType          25 non-null     object 
 11  time_detail        0 non-null      object 
 12  timeCoverage       0 non-null      object 
 13  upperBound         0 non-null      object 
 14  lowerBound         0 non-null      object 
 15  basePeriod         0 non-null      object 
 16  source             25 non-null     object 
 17  geoInfoUrl         0 non-null      object 
 18  footnotes          25 non-null     object 
 19  attributes         25 non-null     object 
 20  dimensions         25 non-null     object 
dtypes: float64(1), object(20)
memory usage: 4.2+ KB
userCDbL0
Related