python - 如何获取以数据帧格式转换的 json 输出？

Question

我是 python 新手，正在尝试格式化 API 的输出:

输出数据帧是:

**data**
Out[8]: b'[{"date":"2020-01-19","stats":[{"metrics":{"blocks":5,"bounce_drops":6,"bounces":16,"clicks":278,"deferred":8,"delivered":1453,"invalid_emails":6,"opens":2502,"processed":155,"requests":1484,"spam_report_drops":0,"spam_reports":0,"unique_clicks":199,"unique_opens":1013,"unsubscribe_drops":0,"unsubscribes":0}}]}]\n'

我想以表格形式制作它，以便我可以将其导出到 csv:

我尝试过:

import pandas as pd
merge_HOO = {'blocks': [], 'bounce_drops': [], 'bounces': [], 'clicks': []}
for i, restaurant in enumerate(data):
    for item in restaurant['metrics']:
        merge_HOO['blocks'].append(i)
        merge_HOO['bounce_drops'].append(item['bounce_drops'])
        merge_HOO['bounces'].append(item['bounces'])
        merge_HOO['clicks'].append(item['clicks'])


merge_HOO = pd.DataFrame(merge_HOO,
                         columns=['blocks', 'bounce_drops', 'bounces', 'clicks'])
print(merge_HOO)
Traceback (most recent call last):

  File "<ipython-input-9-ad0eecd65eba>", line 4, in <module>
    for item in restaurant['metrics']:

TypeError: 'int' object is not subscriptable

但是我得到了上面的错误。

我希望它在我的 csv 中看起来像下面这样，其中有各自的标题和每个标题下面的统计信息:

blocks bounce_drops  bounces
5       6             16

Answer 1

这里有一个方法:

from pandas.io.json import json_normalize

# lets say d is your list containing dict
f = json_normalize(d)

# reshape the data
cols = ['blocks','bounce_drops','bounces']
df = f['stats'].apply(lambda x: pd.Series(x[0]))['metrics'].apply(pd.Series)[cols]

   blocks  bounce_drops  bounces
0       5             6       16

示例数据

d = [{"date":"2020-01-19","stats":[{"metrics":{"blocks":5,"bounce_drops":6,"bounces":16,"clicks":278,"deferred":8,"delivered":1453,"invalid_emails":6,"opens":2502,"processed":155,"requests":1484,"spam_report_drops":0,"spam_reports":0,"unique_clicks":199,"unique_opens":1013,"unsubscribe_drops":0,"unsubscribes":0}}]}]