Normalize array of json records into dataframe

Question

I want to create a dataframe from the owid covid19 json data located here. The json has an array of daily records in the data column, this along with the country index is what I'm trying to make into a dataframe.

{"AFG":{"continent":"Asia","location":"Afghanistan","population":39835428.0,"population_density":54.422,"median_age":18.6,"aged_65_older":2.581,"aged_70_older":1.337,"gdp_per_capita":1803.987,"cardiovasc_death_rate":597.029,"diabetes_prevalence":9.59,"handwashing_facilities":37.746,"hospital_beds_per_thousand":0.5,"life_expectancy":64.83,"human_development_index":0.511,"data":[{"date":"2020-02-24","total_cases":5.0,"new_cases":5.0,"total_cases_per_million":0.126,"new_cases_per_million":0.126,"stringency_index":8.33},{"date":"2020-02-25","total_cases":5.0,"new_cases":0.0,"total_cases_per_million":0.126,"new_cases_per_million":0.0,"stringency_index":8.33},

So far I've been loading the file straight into a dataframe

df = pd.read_json('owid-covid-data.json', orient='index')

and then normalizing the array as such

data = pd.concat([pd.DataFrame(json_normalize(key)) for key in df['data']])

Which works fine apart from dropping the index and thus giving no identifier to join back to the static values.

I also imagine there's a more efficient way of normalizing than what I've used.

Any help greatly appreciated!

user_10260243 · Answer

It's not the most efficient way, but it works:

new_df = pd.DataFrame()
for index, row in df.iterrows():
    tmp = pd.json_normalize(row['data'])
    tmp['country_code'] = index
    new_df = pd.concat([new_df, tmp])

EDIT:

I found a more efficient way, by normalizing all the JSONs at once:

country_codes = []
datas = []
for index, data in zip(df.index, df['data']):
    datas.extend(data)
    country_codes.extend(len(data)*[index])
    
new_df = pd.DataFrame(datas)
new_df['country_code'] = country_codes

Improved from 9.38 s ± 856 ms per loop to 1.37 s ± 12 ms per loop

user_17120692 · Answer

df = pd.read_json("https://covid.ourworldindata.org/data/owid-covid-data.json", orient='index')

# explode records/ lists into new rows, convert to dict, 
# use it to create a new DataFrame, and transpose it
data = pd.DataFrame(df['data'].explode().to_dict()).T

df = df.drop(columns='data').join(data)

Performance

Ignoring the data reading

>>> %%timeit
... data = pd.DataFrame(df['data'].explode().to_dict()).T
... df.drop(columns='data').join(data)

84.4 ms ± 3.03 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

EDIT - DISCLAIMER

The above solution is actually wrong. During the to_dict conversion many records are lost because there are lots of repeated country codes keys (Series index), and keys of a dictionary must be unique. To solve this, first, we need to reset the index to make sure it's unique. Only after the new DataFrame is created, we add the original index.

data = df['data'].explode()
data_df = pd.DataFrame(data.reset_index(drop=True).to_dict()).T
data_df.index = data.index

df = df.drop(columns='data').join(data_df)

This is much slower than the previous solution because there are actually 127314 records, while the previous solution only produces 233 records (unique country codes). Even if we ignore the join part, as Bruno's solution does, it's much slower than Bruno's solution

>>> %%timeit 
... data = df['data'].explode()
... new_df = pd.DataFrame(data.reset_index(drop=True).to_dict()).T
... new_df.index = data.index

17.6 s ± 972 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

# Bruno's solution 
>>> %%timeit
... country_codes = []
... datas = []
... for index, data in zip(df.index, df['data']):
...     datas.extend(data)
...     country_codes.extend(len(data)*[index])
...     
... new_df = pd.DataFrame(datas)
... new_df['country_code'] = country_codes

1.86 s ± 32.2 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

EDIT 2 - There is a better way...

I found a much better and simpler solution. I was definitely overcomplicating. It's basically the same idea as Bruno's solution

data = df['data'].explode()
data_df = pd.DataFrame(data.tolist(), index=data.index)

df = df.drop(columns='data').join(data_df)

It's as fast as Bruno's solution

>>> %%timeit 
... data = df['data'].explode()
... pd.DataFrame(data.tolist(), index=data.index)

1.87 s ± 16.6 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

user_13337635 · Answer

You can use pd.json_normalize() which is must faster(I tried this out on the entire JSON file):

%%timeit
pd.json_normalize(
    data["AFG"],
    record_path=["data"],
    meta=[
        "continent",
        "location",
        "population",
        "population_density",
        "median_age",
        "aged_65_older",
        "aged_70_older",
        "gdp_per_capita",
        "cardiovasc_death_rate",
        "diabetes_prevalence",
        "handwashing_facilities",
        "hospital_beds_per_thousand",
        "life_expectancy",
        "human_development_index",
    ],
)

17.9 ms ± 1.68 ms per loop (mean ± std. dev. of 7 runs, 100 loops each)

Output:

 date  total_cases  new_cases  total_cases_per_million  new_cases_per_million  stringency_index  new_cases_smoothed  ...  gdp_per_capita  cardiovasc_death_rate  diabetes_prevalence  handwashing_facilities  hospital_beds_per_thousand  life_expectancy  human_development_index
0    2020-02-24          5.0        5.0                    0.126                  0.126              8.33                 NaN  ...        1803.987                597.029                 9.59                  37.746                         0.5            64.83                    0.511
1    2020-02-25          5.0        0.0                    0.126                  0.000              8.33                 NaN  ...        1803.987                597.029                 9.59                  37.746                         0.5            64.83                    0.511
2    2020-02-26          5.0        0.0                    0.126                  0.000              8.33                 NaN  ...        1803.987                597.029                 9.59                  37.746                         0.5            64.83                    0.511
3    2020-02-27          5.0        0.0                    0.126                  0.000              8.33                 NaN  ...        1803.987                597.029                 9.59                  37.746                         0.5            64.83                    0.511
4    2020-02-28          5.0        0.0                    0.126                  0.000              8.33                 NaN  ...        1803.987                597.029                 9.59                  37.746                         0.5            64.83                    0.511
..          ...          ...        ...                      ...                    ...               ...                 ...  ...             ...                    ...                  ...                     ...                         ...              ...                      ...
610  2021-10-26     156071.0       31.0                 3917.894                  0.778               NaN              38.571  ...        1803.987                597.029                 9.59                  37.746                         0.5            64.83                    0.511
611  2021-10-27     156124.0       53.0                 3919.225                  1.330               NaN              37.857  ...        1803.987                597.029                 9.59                  37.746                         0.5            64.83                    0.511
612  2021-10-28     156166.0       42.0                 3920.279                  1.054               NaN              39.286  ...        1803.987                597.029                 9.59                  37.746                         0.5            64.83                    0.511
613  2021-10-29     156196.0       30.0                 3921.032                  0.753               NaN              37.857  ...        1803.987                597.029                 9.59                  37.746                         0.5            64.83                    0.511
614  2021-10-30     156210.0       14.0                 3921.384                  0.351               NaN              38.571  ...        1803.987                597.029                 9.59                  37.746                         0.5            64.83                    0.511

[615 rows x 38 columns]

But the fastest solution is just downloading the data in csv format, as they give you the link here.

Normalize array of json records into dataframe

3 Answers