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

In the current code, I try to normalize it:

from pandas import json_normalize
df = json_normalize(list_of_dicts, 'counts')

But I think I am going in the wrong direction. Also, if I do a simple df = pd.DataFrame(list_of_dicts) , it results in each list of dicts being a single row value, which is not desired.

score 3 · Accepted Answer · answered Jan 22 '21 at 17:10

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

nice solution, i have to keep studying these nice pandas methods :) — blah, Jan 22 '21 at 17:26
@blah glad this worked for you. Yes, there are methods for transforming dataframes. — Trenton McKinney, Jan 22 '21 at 17:28

score 2 · Answer 2 · answered Jan 22 '21 at 17:12

Try json_normalize with melt:

(pd.json_normalize(list_of_dicts, record_path='counts', meta='name')
   .melt('name', var_name='Animal', value_name='Frequency')
   .dropna()
)

Output:

   name    Animal  Frequency
0     a       dog        2.0
7     b       cat        1.0
11    c       cat        1.0
14    b  capibara        5.0
21    b     whale       10.0
28    c     horse        1.0

score 1 · Answer 3 · answered Jan 22 '21 at 17:12

1

Try this?

>>> pd.json_normalize(list_of_dicts, 'counts').melt().dropna()

answered Jan 22 '21 at 17:12

Danail Petrov

1,860
9
11

1

this won't have the `'name'` column – Trenton McKinney Jan 22 '21 at 17:13
2

Ah, good shout :-) I forgot about it ... I see others have already answered it anyway – Danail Petrov Jan 22 '21 at 17:13

Mayank Porwal · Answer 4 · 2021-01-22T17:25:48.963

You can also use df.explode with df.apply:

In [50]: df = pd.DataFrame(list_of_dicts).explode('counts')
In [74]: df.counts = df.counts.apply(lambda x: list(x.items())[0])

In [77]: df[['Animal', 'Frequency']] = pd.DataFrame(df['counts'].tolist(), index=df.index)

In [79]: df.drop('counts', 1, inplace=True)

In [80]: df
Out[80]: 
  name    Animal  Frequency
0    a       dog          2
1    b       cat          1
1    b  capibara          5
1    b     whale         10
2    c     horse          1
2    c       cat          1

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

4 Answers4

Linked