Python实现Excel转JSON时合并同名重复数据的方法

现有包含Name、Question、Answer列的Excel文件，部分行存在Name相同且Answer一致的情况，需要转换为指定格式的JSON——将相同Name对应的Question合并到exampleSentences数组中。但当前Python代码会生成重复的Name条目，无法实现合并效果，需修改代码。

Excel示例内容 #

期望生成的JSON格式 #

[
  {
    "name":"N1",
    "exampleSentences": ["Q1"],
    "defaultReply": {
        "text": ["a1"],
        "type": "text"
    }
  },
  {
    "name":"N2",
    "exampleSentences": ["Q2"],
    "defaultReply": {
        "text": ["a2"],
        "type": "text"
    }
  },
  {
    "name":"N3",
    "exampleSentences": ["Q3","Q5"],
    "defaultReply": {
        "text": ["a3"],
        "type": "text"
    }
  },
  {
    "name":"N4",
    "exampleSentences": ["Q4"],
    "defaultReply": {
        "text": ["a4"],
        "type": "text"
    }
  }
]

用户原代码 #

# Import the required python modules
import pandas as pd
import math
import json
import csv

# Define the name of the Excel file
fileName = "FAQ_eng"

# Read the Excel file
df = pd.read_excel("{}.xlsx".format(fileName))

intents = []
intentNames = df["Name"]

# Loop through the list of Names and create a new intent for each row
for index, name in enumerate(intentNames):

    if name is not None:
        exampleSentences = []
        defaultReplies = []

        if df["Question"][index] is not None and df["Question"][index] is not float:

            try:
                exampleSentences = df["Question"][index]
                exampleSentences = [exampleSentences]
           
                defaultReplies = df["Answer"][index]
                defaultReplies = [defaultReplies]
                
            except:
                continue
      

        intents.append({
            "name": name,
            "exampleSentences": exampleSentences,
            "defaultReply": {
                "text": defaultReplies,
                "type": "text"
            }
        })

# Write the list of created intents into a JSON file
with open("{}.json".format(fileName), "w", encoding="utf-8") as outputFile:
    json.dump(intents, outputFile, ensure_ascii=False)

修改后的代码 #

核心思路是用字典按Name分组，先收集相同Name的所有Question，再转换为目标格式：

import pandas as pd
import json

fileName = "FAQ_eng"
df = pd.read_excel(f"{fileName}.xlsx")

# 用字典存储分组结果，键为Name，值为对应的结构
intent_dict = {}

for _, row in df.iterrows():
    name = row["Name"]
    question = row["Question"]
    answer = row["Answer"]
    
    # 跳过空值
    if pd.isna(name) or pd.isna(question) or pd.isna(answer):
        continue
    
    # 如果Name已存在，追加Question到数组
    if name in intent_dict:
        intent_dict[name]["exampleSentences"].append(question)
    # 否则新建条目
    else:
        intent_dict[name] = {
            "name": name,
            "exampleSentences": [question],
            "defaultReply": {
                "text": [answer],
                "type": "text"
            }
        }

# 将字典的值转换为列表，得到最终结构
intents = list(intent_dict.values())

# 写入JSON文件
with open(f"{fileName}.json", "w", encoding="utf-8") as outputFile:
    json.dump(intents, outputFile, ensure_ascii=False, indent=2)

修改说明 #

1. 替换原有的enumerate遍历为df.iterrows()，直接获取整行数据，代码更简洁
2. 使用字典intent_dict按Name分组，避免重复创建条目
3. 对空值的判断改用pandas的pd.isna()，更准确处理Excel中的空单元格
4. 当遇到已存在的Name时，仅追加Question到exampleSentences数组，复用已有的Answer结构
5. 最后将字典的值转为列表，符合目标JSON的数组格式

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