复杂嵌套JSON转带多级索引列Excel求助（含多组同键字典列表）

问题背景 #

手上有个嵌套层级较深的JSON文件，包含大量{"key":"value"}格式的字典列表（比如events、cookies、headers字段）。现有Python脚本仅完成部分转换，未正确展平events和cookies，还生成大量冗余行，需要一套能完整展平结构并生成带多级索引列的规整Excel方案。

JSON示例 #

{"logFormatVersion": "log_security_v3", "data": [
{
"logAlertUid": "a2fee3b7e2824c",
"request": {
"body": "",
"cookies": [
{"key": "info_1", "value": "info_2"},
{"key": "info_3", "value": "info_4"},
{"key": "info_5", "value": "info_6"}
],
"headers": [
{"key": "Host", "value": "ip_address"},
{"key": "Accept-Charset", "value": "iso-8859-1,utf-8;q=0.9,*;q=0.1"},
{"key": "Accept-Language", "value": "info_7"},
{"key": "Connection", "value": "Keep-Alive"},
{"key": "Referer", "value": "info_8"}
],
"hostname": "FQDN",
"ipDst": "Y.Y.Y.Y",
"ipSrc": "X.X.X.X",
"method": "GET",
"path": "/xampp/cgi.cgi",
"portDst": 443,
"protocol": "HTTP/1.1",
"query": "",
"requestUid": "info_9"
},
"websocket": [],
"context": {
"tags": "",
"geoipCode": "",
"geoipName": "",
"applianceName": "name_device",
"applianceUid": "18539",
"backendHost": "ip_address",
"backendPort": 80,
"reverseProxyName": "FQDN",
"reverseProxyUid": "info_10",
"tunnelName": "info_11",
"tunnelUid": "6d531c",
"workflowName": "name-workflow",
"workflowUid": "77802"
},
"events": [
{
"eventUid": "e62d8b",
"tokens": {
"date": "time",
"matchingParts": [
{
"part": "info_17",
"partKey": "info_18",
"partKeyOperator": "info_19",
"partKeyPattern": "info_20",
"partKeyMatch": "info_21",
"partValue": "info_21",
"partValueOperator": "info_22",
"partValuePatternUid": "info_23",
"partValuePatternName": "info_24",
"partValuePatternVersion": "00614",
"partValueMatch": "info_25",
"attackFamily": "info_26",
"riskLevel": 80,
"riskLevelOWASP": 8,
"cwe": "CWE-name"
}
],
"reason": "info_27",
"securityExceptionConfigurationUids": ["info_28"],
"securityExceptionMatchedRuleUids": ["info_24"]
}
},
{
"eventUid": "e62d8b",
"tokens": {
"date": "time",
"matchingParts": [
{
"part": "info_17",
"partKey": "info_18",
"partKeyOperator": "info_19",
"partKeyPattern": "info_20",
"partKeyMatch": "info_21",
"partValue": "info_21",
"partValueOperator": "info_22",
"partValuePatternUid": "info_23",
"partValuePatternName": "info_24",
"partValuePatternVersion": "00614",
"partValueMatch": "info_25",
"attackFamily": "info_26",
"riskLevel": 80,
"riskLevelOWASP": 8,
"cwe": "CWE-name"
}
],
"reason": "info_27",
"securityExceptionConfigurationUids": ["info_28"],
"securityExceptionMatchedRuleUids": ["info_24"]
}
}
],
"timestampImport": null,
"timestamp": "info30",
"uid": "AYYm"
}
]}

现有脚本 #

import json
import pandas as pd 

with open("eventlogs.json", "r") as f:
    objectfile = json.load(f)
    data = objectfile["data"]

df =pd.DataFrame(data)
df_request = pd.json_normalize(df["request"])
df_headers = pd.DataFrame(df_request["headers"])
df_headers = df_headers.explode("headers")
df_headers[["header-key", "header-value"]] = df_headers["headers"].apply(pd.Series)
df_headers.drop("headers", axis=1, inplace=True)
df = df.drop("request", axis=1)
new_df = pd.concat([df, df_request, df_headers], axis=1)

new_df.to_excel("all-data.xlsx", index=False)

解决方案 #

核心思路是通过唯一标识关联主数据与嵌套列表，按层级展平所有嵌套字段，最终构建多级列索引的DataFrame，避免冗余行。

完整代码 #

import json
import pandas as pd

# 读取JSON并提取主数据
with open("eventlogs.json", "r") as f:
    raw_data = json.load(f)
    main_data = raw_data["data"]

# 1. 处理主数据：展平context字段，保留唯一标识uid
df_main = pd.json_normalize(main_data, sep="_")

# 2. 处理request下的cookies：展平并关联uid
df_cookies = pd.json_normalize(
    main_data,
    record_path=["request", "cookies"],
    meta=["uid"],
    sep="_"
)
df_cookies.rename(columns={"key": "cookies_key", "value": "cookies_value"}, inplace=True)

# 3. 处理request下的headers：展平并关联uid
df_headers = pd.json_normalize(
    main_data,
    record_path=["request", "headers"],
    meta=["uid"],
    sep="_"
)
df_headers.rename(columns={"key": "headers_key", "value": "headers_value"}, inplace=True)

# 4. 处理events字段：深度展平tokens和matchingParts
df_events = pd.json_normalize(
    main_data,
    record_path=["events", "tokens", "matchingParts"],
    meta=["uid", ["events", "eventUid"], ["events", "tokens", "date"], ["events", "tokens", "reason"]],
    sep="_"
)
# 简化events列名
df_events.columns = [col.replace("events_tokens_", "event_").replace("events_", "") for col in df_events.columns]

# 5. 构建多级索引列
# 主数据转为多级列
df_main.columns = pd.MultiIndex.from_tuples([("主数据", col) for col in df_main.columns])
# cookies转为多级列（key作为二级列）
df_cookies_pivot = df_cookies.pivot(index="uid", columns="cookies_key", values="cookies_value")
df_cookies_pivot.columns = pd.MultiIndex.from_tuples([("Cookies", col) for col in df_cookies_pivot.columns])
# headers转为多级列（key作为二级列）
df_headers_pivot = df_headers.pivot(index="uid", columns="headers_key", values="headers_value")
df_headers_pivot.columns = pd.MultiIndex.from_tuples([("Headers", col) for col in df_headers_pivot.columns])
# events转为多级列
df_events.columns = pd.MultiIndex.from_tuples([("事件", col) for col in df_events.columns])

# 合并所有表，按uid对齐
final_df = df_main.join(df_cookies_pivot, on="主数据_uid")\
                  .join(df_headers_pivot, on="主数据_uid")\
                  .join(df_events, on="主数据_uid")

# 输出到Excel，保留多级索引
with pd.ExcelWriter("规整日志数据.xlsx") as writer:
    final_df.to_excel(writer, merge_cells=False)

print("转换完成，文件已保存为 规整日志数据.xlsx")

效果说明 #

• 全字段展平：所有嵌套字段（包括events内部的tokens、matchingParts）都被展平为可直接阅读的列
• 无冗余行：通过uid关联主数据与嵌套列表，每个主日志条目仅保留一行，多值嵌套字段以独立列展示
• 多级索引结构：Excel列分为一级分类（主数据、Cookies、Headers、事件）和二级字段名，结构清晰，便于筛选分析

内容的提问来源于stack exchange，提问作者OUDRHIRI Mohcine