如何用Python将任意结构的JSON完整转换为Excel格式

尝试将JSON文件转换为Excel，但Pandas无法提取所有键。以下是JSON输入示例：

{
    "result": [
        {
            "level": "L3_SW",
            "name": "L23",
            "type": "CM"
        },
        {
            "level": "L3_SW",
            "name": "SOFT",
            "type": "QM"
        }],
   "context": {
        "config": {
            "project_area_name": "XYZ",
            "component_name": "Configuration",
            "config_name": "_WorkOn",
            "bu": "H"
        },
        "meta": {
            "project": {
                "name": "_WorkOn",
                "key": "2023-05-02_96614cc50ac8dc7e121f7090",
                "started_at": "2023-05-02-16-00"
            },
            "task": {
                "started": "2023-05-02-16-00",
                "finished": "2023-05-02-16-00",
                "req_count": 1
            }
        }
    }
}

这只是其中一个JSON文件，无法预知其他JSON输入的结构。使用Pandas库时，需要指定特定键才能提取数据，不符合需求。现有代码如下：

json_file_path = filedialog.askopenfilename(title='Select a JSON file', filetypes=[('JSON files','*.json')])

# Load the JSON file into a pandas dataframe
data = json.load(open(json_file_path))
print(data)
df = pd.DataFrame(data["result"])  #=============> this giving me output of excel column only of result array.

# Ask the user to select a location to save the Excel file
excel_file_path = filedialog.asksaveasfilename(title='Save as Excel',defaultextension='.xlsx')

# Save the dataframe as an Excel file
df.to_excel(excel_file_path, index=False)

需求：无论JSON结构如何，将整个JSON转换为Excel，提取所有键对应的列。

要处理任意结构的JSON并转换为Excel，核心是扁平化嵌套结构，同时处理数组展开（数组中的每个元素都带上上层的上下文数据）。可以通过递归函数实现，再结合Pandas生成DataFrame。

步骤1：编写递归扁平化函数 #

这个函数会遍历JSON的所有层级，将嵌套的键转换为点分隔的键名（比如context.config.project_area_name），同时处理数组：数组中的每个对象都会复制一份上层的所有键值对，再加上自身的键值对。

import json
import pandas as pd
from tkinter import filedialog

def flatten_json(data, parent_key='', sep='.'):
    items = []
    for k, v in data.items():
        new_key = f"{parent_key}{sep}{k}" if parent_key else k
        if isinstance(v, dict):
            items.extend(flatten_json(v, new_key, sep=sep).items())
        elif isinstance(v, list):
            # 处理数组：每个元素展开，带上当前层级的所有键值
            for i, item in enumerate(v):
                if isinstance(item, dict):
                    temp = {k: val for k, val in items if sep + str(i) not in k}
                    temp.update(flatten_json(item, f"{new_key}[{i}]", sep=sep))
                    items.extend(temp.items())
                else:
                    items.append((f"{new_key}[{i}]", v))
        else:
            items.append((new_key, v))
    return dict(items)

def flatten_json_with_arrays(data):
    # 分离顶层数组与非数组数据，处理数组时合并上下文
    flattened = {}
    arrays = {}
    for k, v in data.items():
        if isinstance(v, list):
            arrays[k] = v
        else:
            flattened.update(flatten_json(v))
    # 处理数组：每个元素合并顶层非数组数据后成为单独一行
    if arrays:
        result_list = []
        for arr_key, arr in arrays.items():
            for item in arr:
                item_flat = flatten_json(item)
                item_flat = {f"{arr_key}.{k}": v for k, v in item_flat.items()}
                combined = flattened.copy()
                combined.update(item_flat)
                result_list.append(combined)
        return result_list
    else:
        return [flattened]

步骤2：修改主代码使用扁平化函数 #

替换原来的DataFrame初始化部分，用上面的函数处理任意结构的JSON：

json_file_path = filedialog.askopenfilename(title='Select a JSON file', filetypes=[('JSON files','*.json')])

# 加载JSON数据
with open(json_file_path, 'r', encoding='utf-8') as f:
    data = json.load(f)

# 扁平化JSON，处理嵌套和数组
flattened_data = flatten_json_with_arrays(data)

# 转换为DataFrame
df = pd.DataFrame(flattened_data)

# 保存为Excel
excel_file_path = filedialog.asksaveasfilename(title='Save as Excel', defaultextension='.xlsx')
df.to_excel(excel_file_path, index=False)

关键说明 #

• 嵌套字典处理：键会被转换为点分隔的层级形式，确保所有深层嵌套的键都能成为Excel的列。
• 数组处理：数组中的每个元素会单独成为一行，同时带上所有上层非数组的键值对（比如示例中context下的配置会出现在result数组每个元素对应的行中）。
• 通用性：无需提前知晓JSON结构，可适配任意嵌套深度和数组位置的JSON文件。

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