python - How to transform a list of dictionaries, containing nested lists into a pandas df

Question

I have a list of dicts:

list_of_dicts = [{'name': 'a', 'counts': [{'dog': 2}]}, 
          {'name': 'b', 'counts': [{'cat': 1}, {'capibara': 5}, {'whale': 10}]}, 
          {'name': 'c', 'counts': [{'horse':1}, {'cat': 1}]]

I would like to transform this into a pandas dataframe like so:

Name	Animal	Frequency
a	dog	2
b	cat	1
b	capibara	5
b	whale	10
c	horse	1
c	cat	1

question from:https://stackoverflow.com/questions/65849528/how-to-transform-a-list-of-dictionaries-containing-nested-lists-into-a-pandas-d

Answer 1

• The record_path and meta parameters of pandas.json_normalize must be used.
• The columns will then be the animals, which are stacked into a single column.

import pandas as pd

# test data
list_of_dicts = [{'name': 'a', 'counts': [{'dog': 2}]}, {'name': 'b', 'counts': [{'cat': 1}, {'capibara': 5}, {'whale': 10}]}, {'name': 'c', 'counts': [{'horse':1}, {'cat': 1}]}]

# load and transform the dataframe
pd.json_normalize(list_of_dicts, 'counts', 'name').set_index('name').stack().reset_index().rename(columns={'level_1': 'Animal', 0: 'Frequency'})

# display(df)
  name    Animal  Frequency
0    a       dog        2.0
1    b       cat        1.0
2    b  capibara        5.0
3    b     whale       10.0
4    c     horse        1.0
5    c       cat        1.0