批量聚合同结构Excel调研文件生成指定格式DataFrame的方法
提问内容
我此前开展了一项调研,要求受访者填写指定的Excel文件,目前已收到上百个结构完全一致、但填写内容各不相同的Excel文件,以受访者B的文件为例,其结构可参考附图。
每个Excel文件均以对应受访者的标识命名,例如recipient_a_survey.xlsx、recipient_b_survey.xlsx等。我希望最终生成如下格式的DataFrame:
Name birth_place birth_date domicile recipient_a London 21/04/1965 London recipient_b Manchester 19/02/1985 London recipient_c Glasgow 14/08/1991 Edinburgh ...
请问是否有简便的实现方案可完成该批量数据聚合需求?且该方案需要能够兼容处理Excel中的列表类内容。
实现方案
使用Python的pandas库批量处理即可,全程无需手动操作Excel,兼容结构固定的表单式/表头式Excel,也支持列表类多值内容处理。
前期准备
先安装所需依赖:
pip install pandas openpyxl
openpyxl用于读取xlsx格式文件,如需读取旧版xls格式可额外安装xlrd。
批量处理代码
import pandas as pd import glob import os # 替换为你的调研Excel存放文件夹路径 excel_folder = "./survey_files/" # 匹配所有符合命名规则的文件 target_files = glob.glob(os.path.join(excel_folder, "recipient_*_survey.xlsx")) # 初始化结果存储列表 result_list = [] # 遍历处理每个文件 for file_path in target_files: # 从文件名提取受访者标识作为Name列值 file_name = os.path.basename(file_path) name = file_name.split("_survey.xlsx")[0] # 读取单个Excel,表单式(无表头)设置header=None,表头式则去掉header参数 single_df = pd.read_excel(file_path, engine="openpyxl", header=None) # 【根据实际Excel结构调整取值位置】 # 示例为表单式取值:对应单元格位置如B2为iloc[1,1](行、列均从0开始计数) # 表头式Excel可直接按列名取值,如single_df["出生地"].iloc[0] birth_place = single_df.iloc[1, 1] birth_date = single_df.iloc[2, 1] domicile = single_df.iloc[3, 1] # 兼容列表类多值内容处理:自动处理换行分隔/多选列表内容 def process_multi_value(val): if pd.isna(val): return None # 处理字符串类型的多值(单元格内换行分隔) if isinstance(val, str): return val.replace("\n", "; ").strip() # 处理列表类型的多值(Excel多选返回的列表) if isinstance(val, list): return "; ".join([str(v) for v in val]) return val # 应用多值处理逻辑 birth_place = process_multi_value(birth_place) birth_date = process_multi_value(birth_date) domicile = process_multi_value(domicile) # 追加当前受访者结果 result_list.append({ "Name": name, "birth_place": birth_place, "birth_date": birth_date, "domicile": domicile }) # 生成最终聚合DataFrame final_df = pd.DataFrame(result_list) # 可导出为CSV或新的Excel文件 final_df.to_csv("./survey_aggregated_result.csv", index=False, encoding="utf-8-sig")
注意事项
- 代码中
iloc的行列索引需替换为你实际Excel里对应答案的单元格位置,如果是表头式结构化Excel,直接按列名读取即可。 - 如需保留列表类型的字段而非拼接为字符串,可删除
process_multi_value中的拼接逻辑,pandas原生支持列表类型的字段存储。 - 合并单元格的内容会被
pandas自动识别填充,无需额外处理。
内容的提问来源于stack exchange,提问作者Raschid Rasko
相关产品推荐
相关产品推荐

