I have looked at related threads here and here but am still having issue when using the methods shown.

If I use the content method

import json
import requests
import pandas as pd

url1 = "https://chronicdata.cdc.gov/views/iw6q-r3ja/rows.json"

content = requests.get(url1)
d = content.json()
print(d.keys())

I get

dict_keys(['meta', 'data'])

but using

pd.DataFrame(d['data'])

or using

url1 = "https://chronicdata.cdc.gov/views/iw6q-r3ja/rows.json"

response = urlopen(url1)
data = json.loads(response.read())
print(type(data))
print(data.keys())
df = pd.DataFrame(data['data'])

I get strange fields, no headers, and so on. I have not used JSONs much and I've honestly found trying to understand what function parameters/arguments I need to use a bit daunting, hence my question here. I'd really like to grasp how I should handle this in the future in order to proceed efficiently. When I paste/open the complete download link in Firefox I get a nice markdown configuration but am unclear if anything here gives me insight as to additional arguments to more easily unpack the JSON.

In some cases for live or regularly updated data JSONs will be the only readily-available format, so I'd like to get a grasp of them. Secondly, I'd like to be more efficient in using them because this has been quite the headache when I can simply use

url2 = "https://data.cdc.gov/api/views/iw6q-r3ja/rows.csv"
df = pd.read_csv(url2)

And have the frame with headers without a lot of head scratching.

How do I best assess a JSON once I've pulled it? Is there a way I can format my code so that headers are properly read and placed and columns appear as they should if the data is later updated on the live url?

CodePudding user response：

To get dataframe with columns and expanded GeoLocation column you can use this example:

import requests
import pandas as pd

url = "https://chronicdata.cdc.gov/views/iw6q-r3ja/rows.json"
data = requests.get(url).json()
columns = [c["name"] for c in data["meta"]["view"]["columns"]]
df = pd.DataFrame(data["data"], columns=columns)

# transform GeoLocation column
geo_columns = [
    "human_address",
    "latitude",
    "longitude",
    "machine_address",
    "needs_recoding",
]
m = df["GeoLocation"].isna()

df.loc[~m, "GeoLocation"] = df.loc[~m, "GeoLocation"].apply(
    lambda x: dict(zip(geo_columns, x))
)
df = pd.concat(
    [df, df.pop("GeoLocation").apply(pd.Series, dtype=object)], axis=1
)

# print some sample info:
print(df.head(10).to_markdown(index=False))

Prints: