python - 规范化复杂的嵌套 JSON 文件
问题描述
我试图将下面的 json 文件规范化为 4 个表 - “内容”、“模块”、“图像”和“另一个表中的其他所有内容”
{
"id": "0000050a",
"revision": 1580225050941,
"slot": "product-description",
"type": "E",
"create_date": 1580225050941,
"modified_date": 1580225050941,
"creator": "Auto",
"modifier": "Auto",
"audit_info": {
"date": 1580225050941,
"source": "AutoService",
"username": "Auto"
},
"total_ID": 1,
"name": "Auto_A1AM78C64UM0Y8_B07JCJR5HW",
"content": [{
"ID": ["B01"],
"content_revision": 1580225050941,
"template": {
"module": [{
"id": "module-11",
"text": null,
"header": [{
"id": "title",
"value": null,
"decorators": []
}],
"paragraph": [{
"id": "description",
"value": [],
"decorators": []
}],
"image": [{
"id": "image",
"assetId": "/images/2cdabb786d10.jpg",
"alt": " ",
"viewLarger": null,
"styleCodes": "__CR0,0,970,300_PT0_SX970_V1__",
"src": "image.jpg",
"originalSrc": null
}],
"integer": null,
"chart": null,
"list": null,
"video": null,
"gallery": null,
"composite": null,
"collection": null,
"product": null
}, {
"id": "module-6",
"text": null,
"header": [{
"id": "title1",
"value": "Dest ",
"decorators": []
}, {
"id": "title2",
"value": "cc",
"decorators": []
}, {
"id": "title3",
"value": "Col",
"decorators": []
}, {
"id": "title4",
"value": "C",
"decorators": []
}, {
"id": "caption1",
"value": null,
"decorators": []
}, {
"id": "caption2",
"value": null,
"decorators": []
}, {
"id": "caption3",
"value": null,
"decorators": []
}, {
"id": "caption4",
"value": null,
"decorators": []
}],
"paragraph": [{
"id": "description1",
"value": [" Sport"],
"decorators": [
[]
]
}, {
"id": "description2",
"value": ["elements "],
"decorators": [
[]
]
}, {
"id": "description3",
"value": ["Film "],
"decorators": [
[]
]
}, {
"id": "description4",
"value": ["Our signature "],
"decorators": [
[]
]
}],
"image": [{
"id": "image1",
"assetId": "/images/dbbfc9873e31.jpg",
"alt": " ",
"viewLarger": null,
"styleCodes": "__CR0,0,300,300_PT0_SX300_V1__",
"src": "image2_.jpg",
"originalSrc": null
}, {
"id": "image2",
"assetId": "/images/f577ae005.jpg",
"alt": " ",
"viewLarger": null,
"styleCodes": "__CR0,0,300,300_PT0_SX300_V1__",
"src": "test.jpg",
"originalSrc": null
}, {
"id": "image3",
"assetId": "/images/-0df21c5216d0.jpg",
"alt": " ",
"viewLarger": null,
"styleCodes": "__CR0,0,300,300_PT0_SX300_V1__",
"src": "image.jpg",
"originalSrc": null
}, {
"id": "image4",
"assetId": "/images/78d26b9c-408c-4299-8ea8-e9257f170320.jpg",
"alt": " ",
"viewLarger": null,
"styleCodes": "__CR0,0,300,300_PT0_SX300_V1__",
"src": "image.jpg",
"originalSrc": null
}, {
"id": "thumb1",
"assetId": "/images/-bbbfc9873e31.jpg",
"alt": " ",
"viewLarger": null,
"styleCodes": "__CR0,0,300,300_PT0_SX300_V1__",
"src": "image.jpg",
"originalSrc": null
}, {
"id": "thumb2",
"assetId": "/images/e56f577ae005.jpg",
"alt": " ",
"viewLarger": null,
"styleCodes": "__CR0,0,300,300_PT0_SX300_V1__",
"src": "image_.jpg",
"originalSrc": null
}, {
"id": "thumb3",
"assetId": "/images/0df21c5216d0.jpg",
"alt": " ",
"viewLarger": null,
"styleCodes": "__CR0,0,300,300_PT0_SX300_V1__",
"src": "image_.jpg",
"originalSrc": null
}, {
"id": "thumb4",
"assetId": "/images/-e9257f170320.jpg",
"alt": " ",
"viewLarger": null,
"styleCodes": "__CR0,0,300,300_PT0_SX300_V1__",
"src": "image.jpg",
"originalSrc": null
}],
"integer": null,
"chart": null,
"list": null,
"video": null,
"gallery": null,
"composite": null,
"collection": null,
"product": null
}],
"renderType": "VERTICAL"
},
"locale_data": {
"locale": "en_US",
"identified_by": "MACHINE_DETECT"
}
}],
"badges": []
}
我可以成功地将 JSON 扁平化为一个大数据帧,并将标头作为 JSON 路径。但我想将 JSON 规范化为单独的表。例如,模块表应该有ID
、Text
、Header_ID
等列Header_Value
。Image 表应具有Image_ID
、Assest_ID
等列Src
。谁能帮我将这个 JSON 规范化为 4 个表。
解决方案
您可以使用https://towardsdatascience.com/flattening-json-objects-in-python-f5343c794b10定义的函数,如下所示,然后使用json_normalize
:
import pandas as pd
import json
with open('test.json') as json_file:
data = json.load(json_file)
def flatten_json(y):
out = {}
def flatten(x, name=''):
if type(x) is dict:
for a in x:
flatten(x[a], name + a + '_')
elif type(x) is list:
i = 0
for a in x:
flatten(a, name + str(i) + '_')
i += 1
else:
out[name[:-1]] = x
flatten(y)
return out
module = flatten_json(data["content"][0])
module = pd.json_normalize(module)
然后,您要做的就是根据您描述的四个类别选择列。输出是:
ID_0 content_revision ... locale_data_locale locale_data_identified_by
0 B01 1580225050941 ... en_US MACHINE_DETECT
然后您选择如下,例如为您的模块和图像数据帧:
module = df.loc[:,df.columns.str.contains("module")]
image = df.loc[:,df.columns.str.contains("image")]
例如,您为模块获得的结果是:
template_module_0_id ... template_module_1_product
0 module-11 ... None
然后,我给出模块 DataFrame 转换的示例,您只有两个模块,因此您可以concat
在重命名列后执行:
module1 = module.loc[:,module.columns.str.contains("module_0")]
module1.columns = module1.columns.str.replace("_0","")
module2 = module.loc[:,module.columns.str.contains("module_1")]
module2.columns = module2.columns.str.replace("_1","")
modules = pd.concat([module1,
module2])
你得到:
template_module_id ... template_module_image_7_originalSrc
0 module-11 ... NaN
0 module-6 ... None
如果您有更多元素,另一种选择是直接在您想要的嵌套元素上使用flatten_json
and函数。json_normalize
推荐阅读
- javascript - 在客户端和服务器端格式之间转换数据的正确术语是什么?
- sql - 'like' 子句是否适用于双精度(用于纬度/经度)数据类型?
- treeview - 在 UWP 应用中使用 TreeView 时如何处理命名空间冲突
- python - “一点也不”不在定义的枚举值中
- java - 具有负循环的有向加权图中最短路径的蛮力解决方案
- swift - Swift中的链表声明,手指类型可以透明地插入中间或开始
- python - 预期 conv2d_1_input 有 4 个维度,但得到了形状为 (8, 4) 的数组
- javascript - 如何替换所有出现的字符,除非它包含在使用正则表达式的单词中
- c# - 使用Action时的执行顺序问题
- sql - 运行查询时是否可以跳过列?