Pandas Python搜索JSON列表

Question

我有一个JSON文件结构列表，所以：

[
 {
   "name": "first",
   "points": 0.5,
   "tags": [{
      "key": "Owner",
      "value": "A"
   }]
 },
 {
   "name": "first",
   "points": 1.5,
   "tags": [{
      "key": "Owner",
      "value": "B"
   }]
 },
 {
   "name": "first",
   "points": 24,
   "tags": [{
      "key": "SomeOtherTag",
      "value": "XYZ"
   }]
 }
]

我想加载这个JSON数组，然后过滤出一个特定的key: value标记并计算points。例如，根据Owner: A过滤这个“主”列表，然后计算应该得到0.5结果的点数

我知道如何使用items.points.sum()计算点，但我很难通过标签键/值进行过滤。

这是我到目前为止所知道的：

import pandas as pd

items = pd.read_json('all_items.json')

# Do something to filter down the list
filtered_items = ?????

print(filtered_items.points.sum())

根据Rob的解决方案进行编辑：

#pipenv install pandas
# pandas = 1.1.0

import pandas as pd

items = pd.read_json('all_items.json')

pd.json_normalize(items, record_path="tags", meta=[['name'], ['points']])

提供TypeError: string indices must be integers。

Answer 1

您可以使用json_normalize

df = pd.io.json.json_normalize(s)
print(df)

    name  points                                       tags
0  first     0.5           [{'key': 'Owner', 'value': 'A'}]
1  first     1.5           [{'key': 'Owner', 'value': 'B'}]
2  first    24.0  [{'key': 'SomeOtherTag', 'value': 'XYZ'}]

# to filter
filter_mask = df['tags'].apply(lambda x: x[0]['value'] == 'A')
df.loc[filter_mask, "points"].sum()

Answer 2

json_normalize()为您完成了所有这些工作

js = [{'name': 'first', 'points': 0.5, 'tags': [{'key': 'Owner', 'value': 'A'}]},
 {'name': 'first', 'points': 1.5, 'tags': [{'key': 'Owner', 'value': 'B'}]},
 {'name': 'first',
  'points': 24,
  'tags': [{'key': 'SomeOtherTag', 'value': 'XYZ'}]}]


pd.json_normalize(js, record_path="tags", meta=[['name'], ['points']])

输出

          key value   name points
        Owner     A  first    0.5
        Owner     B  first    1.5
 SomeOtherTag   XYZ  first     24

补充更新

如果从文件读取

import json
with open('all_items.json') as f: items = json.load(f)

pd.json_normalize(items, record_path="tags", meta=[['name'], ['points']])

Answer 3

如果您不严格使用tags，另一种选择是对生成器的理解求和，并假设每行在Pandas列表中只嵌入一个字典：

sum(entry["points"] for entry in data if entry["tags"][0]["value"] == "A")

0.5