CODEWITHSUNDEEP
pythonjsonpandaslist
mohit kumar
mohit kumar

Reputation: 13

Converting List of JSON to Pandas Dataframe

actualsTimeseries column contains list of json. I want to make a separate column for each object so that i can extract the date column from that.

This is the code for to fetch the Covid_data

 import urllib.request, json 
with urllib.request.urlopen("https://api.covidactnow.org/v2/counties.timeseries.json?apiKey=2bd2fb77d0f7415f8313b45e5d260206") as url:
    data = json.loads(url.read().decode())
covid_data=pd.DataFrame(data)
covid_data.head()

enter image description here

I need state,county, testPositivityRatio and date column from Actual timeseries

scd=covid_data[['state','county']]
positivity_ratio=pd.json_normalize(covid_data.metrics[0:])[['testPositivityRatio']]

I am not able to Normalize the actualsTimeseries column. Please help, I tried lot. But could not able to do

Upvotes: 0

Views: 106

Answers (1)

NYC Coder
NYC Coder

Reputation: 7594

Try this to get the columns inside metricsTimeseries and state and county from the top level:

covid_data = pd.json_normalize(data, record_path=['metricsTimeseries'], meta=['state', 'county'])
print(covid_data)

Output:

testPositivityRatio  caseDensity contactTracerCapacityRatio infectionRate infectionRateCI90 icuHeadroomRatio icuHeadroomDetails        date state                  county
0                 None          0.0                       None          None              None             None               None  2020-01-21    AK  Aleutians East Borough
1                 None          0.0                       None          None              None             None               None  2020-01-22    AK  Aleutians East Borough
2                 None          0.0                       None          None              None             None               None  2020-01-23    AK  Aleutians East Borough
3                 None          0.0                       None          None              None             None               None  2020-01-24    AK  Aleutians East Borough
4                 None          0.0                       None          None              None             None               None  2020-01-25    AK  Aleutians East Borough
5                 None          0.0                       None          None              None             None               None  2020-01-26    AK  Aleutians East Borough
6                 None          0.0                       None          None              None             None               None  2020-01-27    AK  Aleutians East Borough
7                 None          0.0                       None          None              None             None               None  2020-01-28    AK  Aleutians East Borough
8                 None          0.0                       None          None              None             None               None  2020-01-29    AK  Aleutians East Borough
9                 None          0.0                       None          None              None             None               None  2020-01-30    AK  Aleutians East Borough
10                None          0.0                       None          None              None             None               None  2020-01-31    AK  Aleutians East Borough

Upvotes: 1

Related Questions