如何将包含嵌套列表的字典列表转换为pandas df

Question

我有一个字典列表:

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

我想把它转换成一个pandas数据框架，如下所示:

<表类>名称动物频率tbody><<tr>狗2b猫1bcapibara5b鲸鱼10c马1c猫1

• 必须使用pandas.json_normalize的record_path和meta参数
• 这些列就是动物，它们被堆叠成一个列。

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

尝试json_normalize与melt:

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

输出:

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

试一下?

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

您也可以使用df.explode与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