python - pandas json_normalize 所有列都有嵌套字典扁平化

Question

我有一个嵌套字典 (json)，它是从非官方谷歌字典 API 返回的。

看起来像这样:

{'word': 'slack',
 'phonetic': '/slak/',
 'meaning': {'adjective': [{'definition': 'Not taut or held tightly in position; loose.',
    'example': 'a slack rope',
    'synonyms': ['loose',
     'limp',
     'not taut',
     'not tight',
     'hanging',
     'flapping']},
   {'definition': '(of business) characterized by a lack of work or activity; quiet.',
    'example': 'business was rather slack'},
   {'definition': 'Having or showing laziness or negligence.',
    'example': 'slack accounting procedures',
    'synonyms': ['lax',
     'negligent',
     'neglectful',
     'remiss',
     'careless',
     'slapdash',
     'slipshod',
     'lackadaisical',
     'lazy',
     'inefficient',
     'incompetent',
     'inattentive',
     'offhand',
     'casual',
     'disorderly',
     'disorganized']},
   {'definition': '(of a tide) neither ebbing nor flowing.',
    'example': 'soon the water will become slack, and the tide will turn'}],
  'noun': [{'definition': 'The part of a rope or line which is not held taut; the loose or unused part.',
    'example': 'I picked up the rod and wound in the slack',
    'synonyms': ['looseness', 'play', 'give']},
   {'definition': 'Casual trousers.'},
   {'definition': 'A spell of inactivity or laziness.',
    'example': 'he slept deeply, refreshed by a little slack in the daily routine',
    'synonyms': ['lull',
     'pause',
     'respite',
     'spell of inactivity',
     'interval',
     'break',
     'hiatus',
     'breathing space']}],
  'verb': [{'definition': 'Loosen (something, especially a rope).'},
   {'definition': 'Decrease or reduce in intensity, quantity, or speed.',
    'example': 'the flow of blood slacked off',
    'synonyms': ['reduce',
     'lessen',
     'slacken',
     'slow',
     'ease off',
     'ease up']},
   {'definition': 'Work slowly or lazily.',
    'example': 'she reprimanded her girls if they were slacking',
    'synonyms': ['idle',
     'shirk',
     'be inactive',
     'be lazy',
     'be indolent',
     'sit back and do nothing',
     'waste time',
     'lounge about']},
   {'definition': 'Slake (lime).'}],
  'adverb': [{'definition': 'Loosely.',
    'example': 'their heads were hanging slack in attitudes of despair'}]}}

这就是松弛这个词的含义。要获得这个含义，我们可以通过谷歌搜索其含义或简单地使用以下代码:

import numpy as np
import pandas as pd
import json
from pandas.io.json import json_normalize
from io import StringIO
import requests

word = 'slack'
url =  'https://googledictionaryapi.eu-gb.mybluemix.net/?define=' + word
response = requests.get(url)
content = response.content.decode('utf-8') # list of ugly strings
j = json.loads(content) # json list having nested dictionary
j = j[0]
j

现在，字典 j 有三个键。

j.keys() # dict_keys(['word', 'phonetic', 'meaning'])

我主要感兴趣的是它的含义:

j['meaning'].keys() # dict_keys(['adjective', 'noun', 'verb', 'adverb'])

为了获取 pandas 数据框，我使用了以下代码:

json_normalize(data=j['meaning'])

这给出了一个只有 4 列的数据框。

这里，每个词性(形容词、名词等)都必须有“定义”键，“示例”和“同义词”是可选的。

j['meaning']['adjective'][0].keys() # dict_keys(['definition', 'example', 'synonyms'])

如何获取 4 * 3 = 12 列的数据框，列名称如 adjective_definition、adjective_example、....、verb_synonyms?

我尝试从以下链接中获取一些想法:

http://pandas.pydata.org/pandas-docs/version/0.17.0/generated/pandas.io.json.json_normalize.html
https://www.kaggle.com/jboysen/quick-tutorial-flatten-nested-json-in-pandas/notebook
pandas.io.json.json_normalize with very nested json

但是，无法解决问题。我们将不胜感激。

Answer 1

我想使用json_normalize的 record_path 参数将解决您的问题。由于 record_path 旨在成为 json 对象或记录列表的单个路径，因此我必须多次调用 json_normalize，然后将结果连接起来以获得包含所需数据的数据帧。您还可以尝试使用 record_prefix 参数来设置列命名约定。希望这有帮助!

from pandas.io.json import json_normalize
from io import StringIO
import requests

word = 'slack'
url =  'https://googledictionaryapi.eu-gb.mybluemix.net/?define=' + word
response = requests.get(url)
content = response.content.decode('utf-8') # list of ugly strings
j = json.loads(content) # json list having nested dictionary
j = j[0]

df_adj = json_normalize(data=j['meaning'], record_path=["adjective"], record_prefix="adjective.")
df_verb = json_normalize(data=j['meaning'], record_path=["verb"], record_prefix="verb.")
df_adv = json_normalize(data=j['meaning'], record_path=["adverb"], record_prefix="adverb.")
df_noun = json_normalize(data=j['meaning'], record_path=["noun"], record_prefix="noun.")

df = pd.concat([df_adj, df_verb, df_adv, df_noun], axis=1)
print(df.head(3))