使用索引取消拼合 JSON 对象/将 n 元组反序列化为分层 Excel

Question

我已经解析了来自Screaming Frog工具的JSON+LD(结构化(数据。此工具导出数据的格式不可行，因为父/子关系(交叉引用(不在 Excel 中的一行上。编辑：这种序列化格式称为n-三元组。下面是一个示例输出，其中索引关系以颜色编码(抱歉，尚不允许发布图像(：

https://i.stack.imgur.com/7Zzp3.jpg

Subject     Predicate                           Object
subject27   schema.org/aggregateRating          subject28
subject27   schema.org/offers                   subject29
subject27   schema.org/operatingSystem          ANDROID
subject27   type                                schema.org/SoftwareApplication
subject28   schema.org/ratingCount              15559
subject28   schema.org/ratingValue              3.597853422
subject28   type                                schema.org/AggregateRating
subject29   schema.org/price                    0
subject29   type                                schema.org/Offer

下面是所需的最终输出示例，其中所有嵌套级别都位于其自己的列中。每个嵌套级别(最多 4 个深度(都应映射到其自己的列中，重复父路径信息。

Predicate L1                Object L1                       Predicate L2            Object L2
type                        schema.org/SoftwareApplication      
schema.org/operatingSystem  ANDROID     
schema.org/aggregateRating  subject28                       schema.org/ratingCount  15559
schema.org/aggregateRating  subject28                       schema.org/ratingValue  3.597853422
schema.org/aggregateRating  subject28                       type                    schema.org/AggregateRating
schema.org/offers           subject29                       schema.org/price        0
schema.org/offers           subject29                       type                    schema.org/Offer

我寻找了现有的未平展解决方案，但这些解决方案要么使用存储在单个列中的路径信息(每个"最低级别值"都有自己的"行"(，要么不基于索引重建原始数据。

我希望将 for 循环与 SQL JOIN 结合使用来做到这一点，但我觉得必须有一个更优雅的解决方案。这可能是在Python，PHP，JS或SQL或组合中，甚至将每个"主题"添加到MongoDB文档中，然后对此应用合并操作？

编辑：更新标题以优化本文的SEO。我正在使用的这个RDF和JSON+LD数据的序列化格式称为N-三元组。在此处阅读更多内容： https://medium.com/wallscope/understanding-linked-data-formats-rdf-xml-vs-turtle-vs-n-triples-eb931dbe9827

Answer 1

这可能是各种丑陋的，而且肯定在很多方面都是非pythonic的，但它可以在您的示例数据上完成工作：

import re
def group_items(items, prop):
group = {}
for item in items:
key = item[prop]
if key not in group:
group[key] = []
group[key].append(item)
return group
with open('input.txt', encoding='utf8') as f:
# analyze column widths on the example of the header row
# this allows for flexible column withds in the input data
header_row = next(f)
columns = re.findall('S+s*', header_row.rstrip('n'))
i = 0
cols = []
headers = []
for c in columns:
headers.append( c.strip() )
cols.append( [i, i + len(c)] )
i += len(c)
cols[-1][1] = 100000   # generous data length for last column
# extract one item per line, using those column widths
items = []
for line in f:
item = {}
for c, col in enumerate(cols):
item[headers[c]] = line[col[0]:col[1]].strip()
items.append(item)
# group items to figure out which ones are at the root
items_by_subject = group_items(items, 'Subject')
items_by_object = group_items(items, 'Object')
# root keys are those that are not anyone else's subject
root_keys = set(items_by_subject.keys()) - set(items_by_object.keys())
root_items = [items_by_subject[k] for k in root_keys]
# recursive function to walk the tree and determine the leafs
leafs = []
def unflatten(items, parent=None, level=1):
for item in items:
item['Parent'] = parent
item['Level'] = level
key = item['Object']
if key in items_by_subject:
unflatten(items_by_subject[key], item, level+1)
else:
leafs.append(item)
# ...which needs to be called for each group of root items
for group in root_items:
unflatten(group)
# this is not limited to 4 levels
max_level = max(item['Level'] for item in leafs)
# recursive function to fill in parent data
def fill_data(item, output={}):
parent = item['Parent']
if parent is not None:
fill_data(parent, output)
output['Predicate L%s' % item['Level']] = item['Predicate']
output['Object L%s' % item['Level']] = item['Object']
# ...which needs to be called once per leaf
result = []
for leaf in reversed(leafs):
output = {}
for l in range(1, max_level + 1):
output['Predicate L%s' % l] = None
output['Object L%s' % l] = None
fill_data(leaf, output)
result.append(output)
# output result
for item in result:
print(item)

给定您的示例输入为input.txt，输出如下：

{'Predicate L1': 'type', 'Object L1': 'schema.org/SoftwareApplication', 'Predicate L2': None, 'Object L2': None}
{'Predicate L1': 'schema.org/operatingSystem', 'Object L1': 'ANDROID', 'Predicate L2': None, 'Object L2': None}
{'Predicate L1': 'schema.org/offers', 'Object L1': 'subject29', 'Predicate L2': 'type', 'Object L2': 'schema.org/Offer'}
{'Predicate L1': 'schema.org/offers', 'Object L1': 'subject29', 'Predicate L2': 'schema.org/price', 'Object L2': '0'}
{'Predicate L1': 'schema.org/aggregateRating', 'Object L1': 'subject28', 'Predicate L2': 'type', 'Object L2': 'schema.org/AggregateRating'}
{'Predicate L1': 'schema.org/aggregateRating', 'Object L1': 'subject28', 'Predicate L2': 'schema.org/ratingValue', 'Object L2': '3.597853422'}
{'Predicate L1': 'schema.org/aggregateRating', 'Object L1': 'subject28', 'Predicate L2': 'schema.org/ratingCount', 'Object L2': '15559'}

我将把它放在某种文件中作为练习。