python - 如何从字典中提取一些键和值并使用 Pandas 放入表中

Question

以下是示例字典

sample = {'took': 728, 'timed_out': False, '_shards': {'total': 1, 'successful': 1, 'skipped': 0, 'failed': 0}, 'hits': {'total': {'value': 111, 'relation': 'eq'}, 'max_score': 1.0, 'hits': [{'_index': 'movie_data_01_03', '_type': '_doc', '_id': '0', '_score': 1.0, '_source': {'id': 0, 'Title': 'The Land Girls', 'US Gross': 146083, 'Worldwide Gross': 146083, 'US DVD Sales': None, 'Production Budget': 8000000, 'Release Date': 'Jun 12 1998', 'MPAA Rating': 'R', 'Running Time min': None, 'Distributor': 'Gramercy', 'Source': None, 'Major Genre': None, 'Creative Type': None, 'Director': None, 'Rotten Tomatoes Rating': None, 'IMDB Rating': 6.1, 'IMDB Votes': 1071}}, {'_index': 'movie_data_01_03', '_type': '_doc', '_id': '1', '_score': 1.0, '_source': {'id': 1, 'Title': 'First Love, Last Rites', 'US Gross': 10876, 'Worldwide Gross': 10876, 'US DVD Sales': None, 'Production Budget': 300000, 'Release Date': 'Aug 07 1998', 'MPAA Rating': 'R', 'Running Time min': None, 'Distributor': 'Strand', 'Source': None, 'Major Genre': 'Drama', 'Creative Type': None, 'Director': None, 'Rotten Tomatoes Rating': None, 'IMDB Rating': 6.9, 'IMDB Votes': 207}}, {'_index': 'movie_data_01_03', '_type': '_doc', '_id': '2', '_score': 1.0, '_source': {'id': 2, 'Title': 'I Married a Strange Person', 'US Gross': 203134, 'Worldwide Gross': 203134, 'US DVD Sales': None, 'Production Budget': 250000, 'Release Date': 'Aug 28 1998', 'MPAA Rating': None, 'Running Time min': None, 'Distributor': 'Lionsgate', 'Source': None, 'Major Genre': 'Comedy', 'Creative Type': None, 'Director': None, 'Rotten Tomatoes Rating': None, 'IMDB Rating': 6.8, 'IMDB Votes': 865}}]}}

熊猫输出将有 id,Title,_score

• 字典从sample['hits']['hits']

代码如下

import pandas as pd
df = pd.DataFrame(sample['hits']['hits'])
df

我的输出如下

• 从 _source 标头我需要提取 id,Title,_score并创建一个新的数据框

标签： pythonpandas

Answer 1

您可以尝试json_normalize然后提取感兴趣的列：

pd.json_normalize(sample, record_path=['hits','hits'] )[['_id','_score','_source.Title']]

输出：

  _id  _score               _source.Title
0   0     1.0              The Land Girls
1   1     1.0      First Love, Last Rites
2   2     1.0  I Married a Strange Person