如何解析Excel单单元格ERP表单响应生成结构化pandas DataFrame

import pandas as pd

# 辅助函数：将单条反馈字符串转为问答字典，同时返回当前行的问题顺序
def parse_response(resp_str):
    # 按--分割问答对，过滤空值并去除首尾空白符
    qa_pairs = [p.strip() for p in resp_str.split("--") if p.strip()]
    qa_dict = {}
    question_order = []
    for pair in qa_pairs:
        # 仅按第一个冒号分割，避免答案包含冒号时拆分错误
        q, a = pair.split(":", 1)
        clean_q = q.strip()
        qa_dict[clean_q] = a.strip()
        question_order.append(clean_q)
    return qa_dict, question_order

# ---------------------- 第一步：生成固定顺序的表头 ----------------------
# 存储每个问题出现的最小索引，保证同位置的动态问题排在一起
question_min_idx = {}
for resp in raw_df["Form response in one cell"]:
    _, q_order = parse_response(resp)
    for idx, q in enumerate(q_order):
        # 仅记录问题首次出现的最小位置
        if q not in question_min_idx or idx < question_min_idx[q]:
            question_min_idx[q] = idx

# 按最小索引从小到大排序，保证顺序符合填写逻辑：地区→对应国家问题→通用问题
all_questions = sorted(question_min_idx.keys(), key=lambda x: question_min_idx[x])
final_columns = ["PO", "Vendor"] + all_questions

# ---------------------- 第二步：生成结果表 ----------------------
result_rows = []
for _, row in raw_df.iterrows():
    # 基础字段
    row_data = {"PO": row["PO"], "Vendor": row["Vendor"]}
    # 合并当前行的问答数据
    qa_dict, _ = parse_response(row["Form response in one cell"])
    row_data.update(qa_dict)
    result_rows.append(row_data)

# 生成DataFrame时指定列顺序，无答案的字段自动填充为空
result_df = pd.DataFrame(result_rows, columns=final_columns)

# 导出Excel
result_df.to_excel("ERP表单反馈整理结果.xlsx", index=False)

功能说明 #

1. 字符串转字典实现 #

解决你原来嵌套列表的问题，直接返回可直接查询的问答字典，同时做了边界兼容：分割冒号时只拆分第一个出现的冒号，避免答案本身包含冒号时拆分错误，所有字段自动去除首尾空白字符。

2. 去重且保留问题顺序实现 #

通过记录每个问题首次出现的最小索引，排序后生成的表头完全符合填写逻辑：同一位置出现的动态问题（比如不同地区对应的国家问题）会排在对应位置，不会追加到通用问题后面，完全满足你要求的「地区→对应国家→通用问题」的顺序要求。

3. 数据填充逻辑 #

不需要手动遍历单元格赋值，直接合并每行的基础信息和问答字典，pandas自动对齐列，无答案的问题自动填充为空值。

Python风格优化建议 #

• 遍历可迭代对象直接遍历元素即可，不需要额外声明counter计数器，避免冗余代码
• 简单逻辑优先用列表推导式实现，代码更简洁易读
• 处理字符串分割时优先考虑边界场景，指定分割次数避免内容包含分隔符时出错
• 优先用Python原生的字典、列表和pandas内置方法处理数据，比手动循环单元格执行效率高很多，也不容易出错

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