How to unnest elements of a list that are dictionaries into dataframe (using the first values of it as prefixes)

Question

I am currently studying python (using pandas) for dealing with data analysis. I did a few courses on DataCamp and tried to apply what I've learned into a real problem: I wanted to monitor covid-19 cases in Canada.

For that I am getting the data from an Apify API which returns a json that I then create a dataframe from. The dataframe structure can be seen below:


Int64Index: 57 entries, 0 to 56
Data columns (total 9 columns):
infected              57 non-null float64
deceased              57 non-null float64
infectedByRegion      57 non-null object
measureDate           57 non-null object
measureTime           57 non-null object

The infected and deceased columns contains the total numbers for Canada.

Inside the infectedByRegion column, I have a list of dictionaries in each row as follows:

   [{'region': 'Canada', 'infectedCount': '6258', 'deceasedCount': '61'},
 {'region': 'Newfoundland and Labrador',
  'infectedCount': '135',
  'deceasedCount': '0'},
 {'region': 'Prince Edward Island',
  'infectedCount': '11',
  'deceasedCount': '0'},
 {'region': 'Nova Scotia', 'infectedCount': '122', 'deceasedCount': '0'},
 {'region': 'New Brunswick', 'infectedCount': '66', 'deceasedCount': '0'},
 {'region': 'Quebec', 'infectedCount': '2840', 'deceasedCount': '22'},
 {'region': 'Ontario', 'infectedCount': '1355', 'deceasedCount': '19'},
 {'region': 'Manitoba', 'infectedCount': '72', 'deceasedCount': '1'},
 {'region': 'Saskatchewan', 'infectedCount': '134', 'deceasedCount': '0'},
 {'region': 'Alberta', 'infectedCount': '621', 'deceasedCount': '2'},
 {'region': 'British Columbia', 'infectedCount': '884', 'deceasedCount': '17'},
 {'region': 'Yukon', 'infectedCount': '4', 'deceasedCount': '0'},
 {'region': 'Northwest Territories',
  'infectedCount': '1',
  'deceasedCount': '0'},
 {'region': 'Nunavut', 'infectedCount': '0', 'deceasedCount': '0'},
 {'region': 'Repatriated travellers',
  'infectedCount': '13',
  'deceasedCount': '0'}]

I am trying to create columns at the end of the dataframe for each region's infected and deceased count. Example:

... measureTime   Quebec_infectedCount   Quebec_deceasedCount   Ontario_infectedCount  ...
... 22:30:15      2840                   22                     1355                   ...

I've tried using json_normalize function, but it threw me an error:

AttributeError: 'list' object has no attribute 'values'

Then I tried to look here at stackoverflow, and I found this link:

Python: json_normalize a pandas series gives TypeError

Which it didn't work for me, as it only created a single column called region that contained only 'Canada' as a value in each row at the end of the data frame

... measureDate     measureTime     region
... 2020-03-29      22:30:15        Canada
... 2020-03-30      22:30:15        Canada

Can someone help or point me to an appropriate post here as stackoverflow that help me solve my problem? As I am still a beginner I tried to search for a couple of hours, but I think I don't even know how to frame my question precisely but really want to learn how to handle this type of situation.

Thanks in advance!

user_7758804 · Answer

Given the following dataframe where one column (infectedByRegion) is a list of dictionaries

List of dicts for `infectedByRegion`

data =  [{'region': 'Canada', 'infectedCount': '6258', 'deceasedCount': '61'},
         {'region': 'Newfoundland and Labrador', 'infectedCount': '135', 'deceasedCount': '0'},
         {'region': 'Prince Edward Island', 'infectedCount': '11', 'deceasedCount': '0'},
         {'region': 'Nova Scotia', 'infectedCount': '122', 'deceasedCount': '0'},
         {'region': 'New Brunswick', 'infectedCount': '66', 'deceasedCount': '0'},
         {'region': 'Quebec', 'infectedCount': '2840', 'deceasedCount': '22'},
         {'region': 'Ontario', 'infectedCount': '1355', 'deceasedCount': '19'},
         {'region': 'Manitoba', 'infectedCount': '72', 'deceasedCount': '1'},
         {'region': 'Saskatchewan', 'infectedCount': '134', 'deceasedCount': '0'},
         {'region': 'Alberta', 'infectedCount': '621', 'deceasedCount': '2'},
         {'region': 'British Columbia', 'infectedCount': '884', 'deceasedCount': '17'},
         {'region': 'Yukon', 'infectedCount': '4', 'deceasedCount': '0'},
         {'region': 'Northwest Territories', 'infectedCount': '1', 'deceasedCount': '0'},
         {'region': 'Nunavut', 'infectedCount': '0', 'deceasedCount': '0'},
         {'region': 'Repatriated travellers', 'infectedCount': '13', 'deceasedCount': '0'}]

Representative dataframe

import pandas as pd
from ast import literal_eval

df = pd.DataFrame({'measureDate': ['2020-03-29', '2020-03-30', '2020-03-31'], 'measureTime': ['22:30:15', '21:30:16', '20:56:29'],
                   'infectedByRegion': [data, data, data], 'infected': [12516, 13000, 14000], 'deceased': [122, 133, 143]})


  measureDate measureTime  infected  deceased                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           infectedByRegion
0  2020-03-29    22:30:15     12516       122  [{'region': 'Canada', 'infectedCount': '6258', 'deceasedCount': '61'}, {'region': 'Newfoundland and Labrador', 'infectedCount': '135', 'deceasedCount': '0'}, {'region': 'Prince Edward Island', 'infectedCount': '11', 'deceasedCount': '0'}, {'region': 'Nova Scotia', 'infectedCount': '122', 'deceasedCount': '0'}, {'region': 'New Brunswick', 'infectedCount': '66', 'deceasedCount': '0'}, {'region': 'Quebec', 'infectedCount': '2840', 'deceasedCount': '22'}, {'region': 'Ontario', 'infectedCount': '1355', 'deceasedCount': '19'}, {'region': 'Manitoba', 'infectedCount': '72', 'deceasedCount': '1'}, {'region': 'Saskatchewan', 'infectedCount': '134', 'deceasedCount': '0'}, {'region': 'Alberta', 'infectedCount': '621', 'deceasedCount': '2'}, {'region': 'British Columbia', 'infectedCount': '884', 'deceasedCount': '17'}, {'region': 'Yukon', 'infectedCount': '4', 'deceasedCount': '0'}, {'region': 'Northwest Territories', 'infectedCount': '1', 'deceasedCount': '0'}, {'region': 'Nunavut', 'infectedCount': '0', 'deceasedCount': '0'}, {'region': 'Repatriated travellers', 'infectedCount': '13', 'deceasedCount': '0'}]
1  2020-03-30    21:30:16     13000       133  [{'region': 'Canada', 'infectedCount': '6258', 'deceasedCount': '61'}, {'region': 'Newfoundland and Labrador', 'infectedCount': '135', 'deceasedCount': '0'}, {'region': 'Prince Edward Island', 'infectedCount': '11', 'deceasedCount': '0'}, {'region': 'Nova Scotia', 'infectedCount': '122', 'deceasedCount': '0'}, {'region': 'New Brunswick', 'infectedCount': '66', 'deceasedCount': '0'}, {'region': 'Quebec', 'infectedCount': '2840', 'deceasedCount': '22'}, {'region': 'Ontario', 'infectedCount': '1355', 'deceasedCount': '19'}, {'region': 'Manitoba', 'infectedCount': '72', 'deceasedCount': '1'}, {'region': 'Saskatchewan', 'infectedCount': '134', 'deceasedCount': '0'}, {'region': 'Alberta', 'infectedCount': '621', 'deceasedCount': '2'}, {'region': 'British Columbia', 'infectedCount': '884', 'deceasedCount': '17'}, {'region': 'Yukon', 'infectedCount': '4', 'deceasedCount': '0'}, {'region': 'Northwest Territories', 'infectedCount': '1', 'deceasedCount': '0'}, {'region': 'Nunavut', 'infectedCount': '0', 'deceasedCount': '0'}, {'region': 'Repatriated travellers', 'infectedCount': '13', 'deceasedCount': '0'}]
2  2020-03-31    20:56:29     14000       143  [{'region': 'Canada', 'infectedCount': '6258', 'deceasedCount': '61'}, {'region': 'Newfoundland and Labrador', 'infectedCount': '135', 'deceasedCount': '0'}, {'region': 'Prince Edward Island', 'infectedCount': '11', 'deceasedCount': '0'}, {'region': 'Nova Scotia', 'infectedCount': '122', 'deceasedCount': '0'}, {'region': 'New Brunswick', 'infectedCount': '66', 'deceasedCount': '0'}, {'region': 'Quebec', 'infectedCount': '2840', 'deceasedCount': '22'}, {'region': 'Ontario', 'infectedCount': '1355', 'deceasedCount': '19'}, {'region': 'Manitoba', 'infectedCount': '72', 'deceasedCount': '1'}, {'region': 'Saskatchewan', 'infectedCount': '134', 'deceasedCount': '0'}, {'region': 'Alberta', 'infectedCount': '621', 'deceasedCount': '2'}, {'region': 'British Columbia', 'infectedCount': '884', 'deceasedCount': '17'}, {'region': 'Yukon', 'infectedCount': '4', 'deceasedCount': '0'}, {'region': 'Northwest Territories', 'infectedCount': '1', 'deceasedCount': '0'}, {'region': 'Nunavut', 'infectedCount': '0', 'deceasedCount': '0'}, {'region': 'Repatriated travellers', 'infectedCount': '13', 'deceasedCount': '0'}]

`explode` list of dicts into separate rows

It's not clear if the infectedByRegion column is type list or str in the dataframe, so it may need fixing

# convert str to list; may not be required
df.infectedByRegion = df.infectedByRegion.apply(literal_eval)

# combine columns to datetime the drop them
df['DateTime'] = pd.to_datetime(df.measureDate + ' ' + df.measureTime)
df.drop(columns=['measureDate', 'measureTime'], inplace=True)

# explode infectedByRedion; pandas >= 0.25
df = df.explode('infectedByRegion')

|    | infectedByRegion                                                                      |   infected |   deceased | DateTime            |
|---:|:--------------------------------------------------------------------------------------|-----------:|-----------:|:--------------------|
|  0 | {'region': 'Canada', 'infectedCount': '6258', 'deceasedCount': '61'}                  |      12516 |        122 | 2020-03-29 22:30:15 |
|  0 | {'region': 'Newfoundland and Labrador', 'infectedCount': '135', 'deceasedCount': '0'} |      12516 |        122 | 2020-03-29 22:30:15 |
|  0 | {'region': 'Prince Edward Island', 'infectedCount': '11', 'deceasedCount': '0'}       |      12516 |        122 | 2020-03-29 22:30:15 |
|  0 | {'region': 'Nova Scotia', 'infectedCount': '122', 'deceasedCount': '0'}               |      12516 |        122 | 2020-03-29 22:30:15 |
|  0 | {'region': 'New Brunswick', 'infectedCount': '66', 'deceasedCount': '0'}              |      12516 |        122 | 2020-03-29 22:30:15 |
|  0 | {'region': 'Quebec', 'infectedCount': '2840', 'deceasedCount': '22'}                  |      12516 |        122 | 2020-03-29 22:30:15 |
|  0 | {'region': 'Ontario', 'infectedCount': '1355', 'deceasedCount': '19'}                 |      12516 |        122 | 2020-03-29 22:30:15 |
|  0 | {'region': 'Manitoba', 'infectedCount': '72', 'deceasedCount': '1'}                   |      12516 |        122 | 2020-03-29 22:30:15 |
|  0 | {'region': 'Saskatchewan', 'infectedCount': '134', 'deceasedCount': '0'}              |      12516 |        122 | 2020-03-29 22:30:15 |
|  0 | {'region': 'Alberta', 'infectedCount': '621', 'deceasedCount': '2'}                   |      12516 |        122 | 2020-03-29 22:30:15 |
|  0 | {'region': 'British Columbia', 'infectedCount': '884', 'deceasedCount': '17'}         |      12516 |        122 | 2020-03-29 22:30:15 |
|  0 | {'region': 'Yukon', 'infectedCount': '4', 'deceasedCount': '0'}                       |      12516 |        122 | 2020-03-29 22:30:15 |
|  0 | {'region': 'Northwest Territories', 'infectedCount': '1', 'deceasedCount': '0'}       |      12516 |        122 | 2020-03-29 22:30:15 |
|  0 | {'region': 'Nunavut', 'infectedCount': '0', 'deceasedCount': '0'}                     |      12516 |        122 | 2020-03-29 22:30:15 |
|  0 | {'region': 'Repatriated travellers', 'infectedCount': '13', 'deceasedCount': '0'}     |      12516 |        122 | 2020-03-29 22:30:15 |
|  1 | {'region': 'Canada', 'infectedCount': '6258', 'deceasedCount': '61'}                  |      13000 |        133 | 2020-03-30 21:30:16 |
|  1 | {'region': 'Newfoundland and Labrador', 'infectedCount': '135', 'deceasedCount': '0'} |      13000 |        133 | 2020-03-30 21:30:16 |
|  1 | {'region': 'Prince Edward Island', 'infectedCount': '11', 'deceasedCount': '0'}       |      13000 |        133 | 2020-03-30 21:30:16 |
|  1 | {'region': 'Nova Scotia', 'infectedCount': '122', 'deceasedCount': '0'}               |      13000 |        133 | 2020-03-30 21:30:16 |
|  1 | {'region': 'New Brunswick', 'infectedCount': '66', 'deceasedCount': '0'}              |      13000 |        133 | 2020-03-30 21:30:16 |
|  1 | {'region': 'Quebec', 'infectedCount': '2840', 'deceasedCount': '22'}                  |      13000 |        133 | 2020-03-30 21:30:16 |
|  1 | {'region': 'Ontario', 'infectedCount': '1355', 'deceasedCount': '19'}                 |      13000 |        133 | 2020-03-30 21:30:16 |
|  1 | {'region': 'Manitoba', 'infectedCount': '72', 'deceasedCount': '1'}                   |      13000 |        133 | 2020-03-30 21:30:16 |
|  1 | {'region': 'Saskatchewan', 'infectedCount': '134', 'deceasedCount': '0'}              |      13000 |        133 | 2020-03-30 21:30:16 |
|  1 | {'region': 'Alberta', 'infectedCount': '621', 'deceasedCount': '2'}                   |      13000 |        133 | 2020-03-30 21:30:16 |
|  1 | {'region': 'British Columbia', 'infectedCount': '884', 'deceasedCount': '17'}         |      13000 |        133 | 2020-03-30 21:30:16 |
|  1 | {'region': 'Yukon', 'infectedCount': '4', 'deceasedCount': '0'}                       |      13000 |        133 | 2020-03-30 21:30:16 |
|  1 | {'region': 'Northwest Territories', 'infectedCount': '1', 'deceasedCount': '0'}       |      13000 |        133 | 2020-03-30 21:30:16 |
|  1 | {'region': 'Nunavut', 'infectedCount': '0', 'deceasedCount': '0'}                     |      13000 |        133 | 2020-03-30 21:30:16 |
|  1 | {'region': 'Repatriated travellers', 'infectedCount': '13', 'deceasedCount': '0'}     |      13000 |        133 | 2020-03-30 21:30:16 |
|  2 | {'region': 'Canada', 'infectedCount': '6258', 'deceasedCount': '61'}                  |      14000 |        143 | 2020-03-31 20:56:29 |
|  2 | {'region': 'Newfoundland and Labrador', 'infectedCount': '135', 'deceasedCount': '0'} |      14000 |        143 | 2020-03-31 20:56:29 |
|  2 | {'region': 'Prince Edward Island', 'infectedCount': '11', 'deceasedCount': '0'}       |      14000 |        143 | 2020-03-31 20:56:29 |
|  2 | {'region': 'Nova Scotia', 'infectedCount': '122', 'deceasedCount': '0'}               |      14000 |        143 | 2020-03-31 20:56:29 |
|  2 | {'region': 'New Brunswick', 'infectedCount': '66', 'deceasedCount': '0'}              |      14000 |        143 | 2020-03-31 20:56:29 |
|  2 | {'region': 'Quebec', 'infectedCount': '2840', 'deceasedCount': '22'}                  |      14000 |        143 | 2020-03-31 20:56:29 |
|  2 | {'region': 'Ontario', 'infectedCount': '1355', 'deceasedCount': '19'}                 |      14000 |        143 | 2020-03-31 20:56:29 |
|  2 | {'region': 'Manitoba', 'infectedCount': '72', 'deceasedCount': '1'}                   |      14000 |        143 | 2020-03-31 20:56:29 |
|  2 | {'region': 'Saskatchewan', 'infectedCount': '134', 'deceasedCount': '0'}              |      14000 |        143 | 2020-03-31 20:56:29 |
|  2 | {'region': 'Alberta', 'infectedCount': '621', 'deceasedCount': '2'}                   |      14000 |        143 | 2020-03-31 20:56:29 |
|  2 | {'region': 'British Columbia', 'infectedCount': '884', 'deceasedCount': '17'}         |      14000 |        143 | 2020-03-31 20:56:29 |
|  2 | {'region': 'Yukon', 'infectedCount': '4', 'deceasedCount': '0'}                       |      14000 |        143 | 2020-03-31 20:56:29 |
|  2 | {'region': 'Northwest Territories', 'infectedCount': '1', 'deceasedCount': '0'}       |      14000 |        143 | 2020-03-31 20:56:29 |
|  2 | {'region': 'Nunavut', 'infectedCount': '0', 'deceasedCount': '0'}                     |      14000 |        143 | 2020-03-31 20:56:29 |
|  2 | {'region': 'Repatriated travellers', 'infectedCount': '13', 'deceasedCount': '0'}     |      14000 |        143 | 2020-03-31 20:56:29 |

Convert the dictionary keys to columns

df_concat = pd.concat([df, df.infectedByRegion.apply(pd.Series)], axis=1).drop('infectedByRegion', axis=1)

|    |   infected |   deceased | DateTime            | region                    |   infectedCount |   deceasedCount |
|---:|-----------:|-----------:|:--------------------|:--------------------------|----------------:|----------------:|
|  0 |      12516 |        122 | 2020-03-29 22:30:15 | Canada                    |            6258 |              61 |
|  0 |      12516 |        122 | 2020-03-29 22:30:15 | Newfoundland and Labrador |             135 |               0 |
|  0 |      12516 |        122 | 2020-03-29 22:30:15 | Prince Edward Island      |              11 |               0 |
|  0 |      12516 |        122 | 2020-03-29 22:30:15 | Nova Scotia               |             122 |               0 |
|  0 |      12516 |        122 | 2020-03-29 22:30:15 | New Brunswick             |              66 |               0 |
|  0 |      12516 |        122 | 2020-03-29 22:30:15 | Quebec                    |            2840 |              22 |
|  0 |      12516 |        122 | 2020-03-29 22:30:15 | Ontario                   |            1355 |              19 |
|  0 |      12516 |        122 | 2020-03-29 22:30:15 | Manitoba                  |              72 |               1 |
|  0 |      12516 |        122 | 2020-03-29 22:30:15 | Saskatchewan              |             134 |               0 |
|  0 |      12516 |        122 | 2020-03-29 22:30:15 | Alberta                   |             621 |               2 |
|  0 |      12516 |        122 | 2020-03-29 22:30:15 | British Columbia          |             884 |              17 |
|  0 |      12516 |        122 | 2020-03-29 22:30:15 | Yukon                     |               4 |               0 |
|  0 |      12516 |        122 | 2020-03-29 22:30:15 | Northwest Territories     |               1 |               0 |
|  0 |      12516 |        122 | 2020-03-29 22:30:15 | Nunavut                   |               0 |               0 |
|  0 |      12516 |        122 | 2020-03-29 22:30:15 | Repatriated travellers    |              13 |               0 |
|  1 |      13000 |        133 | 2020-03-30 21:30:16 | Canada                    |            6258 |              61 |
|  1 |      13000 |        133 | 2020-03-30 21:30:16 | Newfoundland and Labrador |             135 |               0 |
|  1 |      13000 |        133 | 2020-03-30 21:30:16 | Prince Edward Island      |              11 |               0 |
|  1 |      13000 |        133 | 2020-03-30 21:30:16 | Nova Scotia               |             122 |               0 |
|  1 |      13000 |        133 | 2020-03-30 21:30:16 | New Brunswick             |              66 |               0 |
|  1 |      13000 |        133 | 2020-03-30 21:30:16 | Quebec                    |            2840 |              22 |
|  1 |      13000 |        133 | 2020-03-30 21:30:16 | Ontario                   |            1355 |              19 |
|  1 |      13000 |        133 | 2020-03-30 21:30:16 | Manitoba                  |              72 |               1 |
|  1 |      13000 |        133 | 2020-03-30 21:30:16 | Saskatchewan              |             134 |               0 |
|  1 |      13000 |        133 | 2020-03-30 21:30:16 | Alberta                   |             621 |               2 |
|  1 |      13000 |        133 | 2020-03-30 21:30:16 | British Columbia          |             884 |              17 |
|  1 |      13000 |        133 | 2020-03-30 21:30:16 | Yukon                     |               4 |               0 |
|  1 |      13000 |        133 | 2020-03-30 21:30:16 | Northwest Territories     |               1 |               0 |
|  1 |      13000 |        133 | 2020-03-30 21:30:16 | Nunavut                   |               0 |               0 |
|  1 |      13000 |        133 | 2020-03-30 21:30:16 | Repatriated travellers    |              13 |               0 |
|  2 |      14000 |        143 | 2020-03-31 20:56:29 | Canada                    |            6258 |              61 |
|  2 |      14000 |        143 | 2020-03-31 20:56:29 | Newfoundland and Labrador |             135 |               0 |
|  2 |      14000 |        143 | 2020-03-31 20:56:29 | Prince Edward Island      |              11 |               0 |
|  2 |      14000 |        143 | 2020-03-31 20:56:29 | Nova Scotia               |             122 |               0 |
|  2 |      14000 |        143 | 2020-03-31 20:56:29 | New Brunswick             |              66 |               0 |
|  2 |      14000 |        143 | 2020-03-31 20:56:29 | Quebec                    |            2840 |              22 |
|  2 |      14000 |        143 | 2020-03-31 20:56:29 | Ontario                   |            1355 |              19 |
|  2 |      14000 |        143 | 2020-03-31 20:56:29 | Manitoba                  |              72 |               1 |
|  2 |      14000 |        143 | 2020-03-31 20:56:29 | Saskatchewan              |             134 |               0 |
|  2 |      14000 |        143 | 2020-03-31 20:56:29 | Alberta                   |             621 |               2 |
|  2 |      14000 |        143 | 2020-03-31 20:56:29 | British Columbia          |             884 |              17 |
|  2 |      14000 |        143 | 2020-03-31 20:56:29 | Yukon                     |               4 |               0 |
|  2 |      14000 |        143 | 2020-03-31 20:56:29 | Northwest Territories     |               1 |               0 |
|  2 |      14000 |        143 | 2020-03-31 20:56:29 | Nunavut                   |               0 |               0 |
|  2 |      14000 |        143 | 2020-03-31 20:56:29 | Repatriated travellers    |              13 |               0 |

Pivot into desired format

df_pivot = df_concat.pivot(index='DateTime', columns='region', values=['infectedCount', 'deceasedCount'])

# rename multi-index column names
df_pivot.columns = [f'{col[1]}_{col[0]}' for col in df_pivot.columns.values]

# output form
                    Alberta_infectedCount British Columbia_infectedCount Canada_infectedCount Manitoba_infectedCount New Brunswick_infectedCount Newfoundland and Labrador_infectedCount Northwest Territories_infectedCount Nova Scotia_infectedCount Nunavut_infectedCount Ontario_infectedCount Prince Edward Island_infectedCount Quebec_infectedCount Repatriated travellers_infectedCount Saskatchewan_infectedCount Yukon_infectedCount Alberta_deceasedCount British Columbia_deceasedCount Canada_deceasedCount Manitoba_deceasedCount New Brunswick_deceasedCount Newfoundland and Labrador_deceasedCount Northwest Territories_deceasedCount Nova Scotia_deceasedCount Nunavut_deceasedCount Ontario_deceasedCount Prince Edward Island_deceasedCount Quebec_deceasedCount Repatriated travellers_deceasedCount Saskatchewan_deceasedCount Yukon_deceasedCount
DateTime                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
2020-03-29 22:30:15                   621                            884                 6258                     72                          66                                     135                                   1                       122                     0                  1355                                 11                 2840                                   13                        134                   4                     2                             17                   61                      1                           0                                       0                                   0                         0                     0                    19                                  0                   22                                    0                          0                   0
2020-03-30 21:30:16                   621                            884                 6258                     72                          66                                     135                                   1                       122                     0                  1355                                 11                 2840                                   13                        134                   4                     2                             17                   61                      1                           0                                       0                                   0                         0                     0                    19                                  0                   22                                    0                          0                   0
2020-03-31 20:56:29                   621                            884                 6258                     72                          66                                     135                                   1                       122                     0                  1355                                 11                 2840                                   13                        134                   4                     2                             17                   61                      1                           0                                       0                                   0                         0                     0                    19                                  0                   22                                    0                          0                   0

user_10888655 · Answer

You can create a dataframe with the list you have.

df = pd.DataFrame(
    [
        {"region": "Canada", "infectedCount": "6258", "deceasedCount": "61"},
        {
            "region": "Newfoundland and Labrador",
            "infectedCount": "135",
            "deceasedCount": "0",
        },
        {"region": "Prince Edward Island", "infectedCount": "11", "deceasedCount": "0"},
        {"region": "Nova Scotia", "infectedCount": "122", "deceasedCount": "0"},
        {"region": "New Brunswick", "infectedCount": "66", "deceasedCount": "0"},
        {"region": "Quebec", "infectedCount": "2840", "deceasedCount": "22"},
        {"region": "Ontario", "infectedCount": "1355", "deceasedCount": "19"},
        {"region": "Manitoba", "infectedCount": "72", "deceasedCount": "1"},
        {"region": "Saskatchewan", "infectedCount": "134", "deceasedCount": "0"},
        {"region": "Alberta", "infectedCount": "621", "deceasedCount": "2"},
        {"region": "British Columbia", "infectedCount": "884", "deceasedCount": "17"},
        {"region": "Yukon", "infectedCount": "4", "deceasedCount": "0"},
        {"region": "Northwest Territories", "infectedCount": "1", "deceasedCount": "0"},
        {"region": "Nunavut", "infectedCount": "0", "deceasedCount": "0"},
        {
            "region": "Repatriated travellers",
            "infectedCount": "13",
            "deceasedCount": "0",
        },
    ]
)
print(df)
                      region infectedCount deceasedCount
0                      Canada          6258            61
1   Newfoundland and Labrador           135             0
2        Prince Edward Island            11             0
3                 Nova Scotia           122             0
4               New Brunswick            66             0
5                      Quebec          2840            22
6                     Ontario          1355            19
7                    Manitoba            72             1
8                Saskatchewan           134             0
9                     Alberta           621             2
10           British Columbia           884            17
11                      Yukon             4             0
12      Northwest Territories             1             0
13                    Nunavut             0             0
14     Repatriated travellers            13             0

Let's add in your date and time and set the date, time and region to the index.

df["measureDate"] = "2020-03-29"
df["measureTime"] = "22:30:15"

df = df.set_index(["measureDate", "measureTime", "region"])
print(df)
measureDate measureTime region                                               
2020-03-29  22:30:15    Canada                             6258            61
                        Newfoundland and Labrador           135             0
                        Prince Edward Island                 11             0
                        Nova Scotia                         122             0
                        New Brunswick                        66             0
                        Quebec                             2840            22
                        Ontario                            1355            19
                        Manitoba                             72             1
                        Saskatchewan                        134             0
                        Alberta                             621             2
                        British Columbia                    884            17
                        Yukon                                 4             0
                        Northwest Territories                 1             0
                        Nunavut                               0             0
                        Repatriated travellers               13             0

Next we unstack the region level=2 in the index to the columns, swap the levels, and sort the columns.

df = df.unstack(level=2)
df.swaplevel(axis=1).sort_index(axis=1)

This doesn't print well here....

    region                                                Alberta                British Columbia
                                  deceasedCount     infectedCount   deceasedCount   infectedCount
measureDate     measureTime                 
2020-03-29  22:30:15                          2            621                  17                      
884

How to unnest elements of a list that are dictionaries into dataframe (using the first values of it as prefixes)

2 Answers

List of dicts for infectedByRegion

Representative dataframe

explode list of dicts into separate rows

Convert the dictionary keys to columns

Pivot into desired format

List of dicts for `infectedByRegion`

`explode` list of dicts into separate rows