Excel到嵌套Json，将子元素包含到数组中

Question

我试图使用Python将Excel转换为嵌套的JSON，其中重复的值作为元素数组输入。

例: CSV的结构

Manufacturer,oilType,viscosity
shell,superOil,1ova
shell,superOil,2ova
shell,normalOil,1ova
bp, power, 10bba

应该在JSON (预期输出)中显示为

elements: [
    {
        "Manufacturer": "shell",
        "details": [
            {
                "OilType": "superOil",
                "Viscosity": [
                    "1ova",
                    "2ova"
                ]
            },
            {
                "OilType": "normalOil",
                "Viscosity": [
                    "1ova"
                ]
            }
        ]
    },
    {
        "Manufacturer": "bp",
        "details": [
            {
                "OilType": "power",
                "Viscosity": [
                    "10bba"
                ]
            }
        ]
    }
]

目前，我已经使用openpyxl将CSV转换为JSON，每个标头的值都以类似于(当前输出)的格式显示。

[{Manufacturer: "shell", oilType: "superOil", Viscosity:"1ova"},{...},{...},...]

请帮助获得预期的输出。

Answer 1

你好，欢迎来到StackOverflow。

您的问题实际上与openpyxl无关，因为您不需要保存到Excel文件中。

你可以这样想：

DataFrame

• Group

1. 将csv (或Excel)加载到熊猫中，由制造商和石油类型
2. 转储成您希望
3. 转换为JSON (字符串或文件)的

格式

在实践中，这给出了这样的结果：

import json

import pandas as pd

df = pd.read_csv("oil.csv")  # or read_excel if this is an Excel
oils = df.groupby(["Manufacturer", "oilType"]).aggregate(pd.Series.to_list)

elements = [
    {
        "Manufacturer": manufacturer,
        "Details": [
            {"OilType": o, "Viscosity": v}
            for o, v in data.droplevel(0).viscosity.items()
        ],
    }
    for manufacturer, data in oils.groupby(level="Manufacturer")
]

with open("oil.json", "w") as f:
    json.dump({"elements": elements}, f)

有关信息，oils将如下所示：

                           viscosity
Manufacturer    oilType 
bp              power       [10bba]
shell           normalOil   [1ova]
                superOil    [1ova, 2ova]