使用Python 3.9中的Pandas将Excel转换为JSON

Question

这是我在这里发的第一篇帖子，所以请轻松点！ :) 我试图使用Python Pandas库将Excel中的数据转换为JSON。

我有一个类似下表的Excel数据，列详细信息为“未命名：x”为空白，我使用这些标题是因为在转换为JSON时它们的输出方式就是这样的。大约有20个测试形式如下面的示例：

未命名：1	未命名：2	未命名：3	未命名：4
测试1	菜单	设置	值
	菜单1	设置1	值1
测试2	A	B	C
	1	2	3

我想将它们转换为JSON，看起来像这样：

{
    "Test 1": [ 
    "Menu":"Menu1",
    "Setting":"Setting1",
    "Value":"Value1",
    ]
}

等等，我可以将当前代码转换为JSON（但不是上面详细说明的格式），并且我一直在尝试使用Python创建不同的Pandas数据帧。目前，我得到的JSON数据大致如下：

"3":[
        {
            "Unnamed: 0":"Test1",
            "Unnamed: 1":"Menu",
            "Unnamed: 2":"Setting",
            "Unnamed: 2":"Value"
        }
"4":[
        {
            "Unnamed: 1":"Menu1",
            "Unnamed: 2":"Setting1",
            "Unnamed: 2":"Value1"
        }

我正在进行一些手动操作（复制和粘贴），以便按照所需格式进行设置。

这是我的当前代码：

import pandas

# Pointing to file location and specifying the sheet name to convert
excel_data_fragment = pandas.read_excel('C:\\Users\\user_name\\tests\\data.xls', sheet_name='Tests')

# Converting to data frame
df = pandas.DataFrame(excel_data_fragment)

# This will get the values in Column A and removes empty values
test_titles = df['Unnamed: 0'].dropna(how="all")

# This is the first set of test values
columnB = df['Unnamed: 1'].dropna(how="all")

# Saving original data in df and removing rows which contain all NaN values to mod_df
mod_df = df.dropna(how="all")

# Converting data frame with NaN values removed to json
df_json = mod_df.apply(lambda x: [x.dropna()], axis=1).to_json()

print(mod_df)

- pcal

---

你能给一个你想要实现的输出示例吗？ - Linden

感谢您的回复，Linden。我想要实现的输出格式是JSON：{ "Test 1": [ "Menu":"菜单1", "设置":"设置1", "值":"值1", ] } - pcal

Answer 1

您的Excel表格基本上由几个不同的子表格组成（每个测试一个表格）。我会使用pandas中的groupby对它们进行处理，然后将每个组作为一个表格进行处理。DataFrame.to_dict在此处将是您的好帮手，用于输出可转换为JSON的对象。

首先，这里是一些类似于您提供的示例数据：

import pandas as pd


rows = [
    [],
    [],
    ["Test 1", "Menu", "Setting", "Value"],
    [None, "Menu1", "Setting1", "Value1"],
    [None, "Menu2", "Setting2", "Value2"],
    [],
    [],
    ["Test 2", "A", "B", "C"],
    [None, 1, 2, 3],
    [None, 4, 5, 6],
]

df = pd.DataFrame(rows, columns=[f"Unnamed: {i}" for i in range(1, 5)])

df 的样子如下：

  Unnamed: 1 Unnamed: 2 Unnamed: 3 Unnamed: 4
0       None       None       None       None
1       None       None       None       None
2     Test 1       Menu    Setting      Value
3       None      Menu1   Setting1     Value1
4       None      Menu2   Setting2     Value2
5       None       None       None       None
6       None       None       None       None
7     Test 2          A          B          C
8       None          1          2          3
9       None          4          5          6

接下来使用以下代码片段，该代码清除 df 中的所有缺失值，并将每个子表转换为一个 dict。

# Remove entirely empty rows
df = df.dropna(how="all")

# Fill missing values in column 1
df["Unnamed: 1"] = df["Unnamed: 1"].fillna(method="ffill")


def process_group(g):
    # Drop first column
    g = g.drop("Unnamed: 1", axis=1)
    # Use first row as column names
    g = g.rename(columns=g.iloc[0])
    # Drop first row
    g = g.drop(g.index[0])
    # Convert to dict
    return g.to_dict(orient="records")


output = df.groupby("Unnamed: 1").apply(process_group).to_dict()

最终，output等于：

{
  "Test 1": [
    {
      "Menu": "Menu1",
      "Setting": "Setting1",
      "Value": "Value1"
    },
    {
      "Menu": "Menu2",
      "Setting": "Setting2",
      "Value": "Value2"
    }
  ],
  "Test 2": [
    {
      "A": 1,
      "B": 2,
      "C": 3
    },
    {
      "A": 4,
      "B": 5,
      "C": 6
    }
  ]
}

您可以通过简单地使用以下方法获得JSON字符串：

import json

output_str = json.dumps(output)

- Roméo Després