python - 在 Python 3.9 中使用 Pandas 将 Excel 转换为 JSON

Question

这是我在这里的第一篇文章，所以放轻松! :) 我正在尝试使用 Python Pandas 库将数据从 Excel 转换为 JSON。

我在 Excel 中有如下表所示的数据，详细列为“未命名:x”的列是空白的，我使用这些标题是因为它们在转换为 JSON 时是这样输出的。大约有 20 个测试格式如下所示:

<表类="s-表"><头>未命名:1未命名:2未命名:3未命名:4<正文>测试 1菜单设置值菜单 1设置 1Value1测试 2ABC123

我想将它们放入 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)

Answer 1

您的 Excel 工作表基本上由几个不同的子表组成(每个测试一个)。我在 pandas 中处理它们的方法是使用 groupby 然后将每个组作为一个表处理。 DataFrame.to_dict 将成为您输出支持 JSON 的对象的 friend 。

首先，这里是一些与您提供的内容相似的示例数据:

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()

最后，输出等于:

{
  "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)