用Python清洗压铸件Excel产品字段并导出规范JSON的完整实现

读者对象与问题说明许多从事制造企业数据管理或技术对接的开发者经常需要处理格式不统一的Excel产品资料表——字段名混乱、单位混用、多值单元格、图片路径缺失等问题。本文以慈溪市华博机械有限公司的铝合金压铸件产品资料为样例数据演示如何用Python对这类Excel进行字段清洗、结构化映射并输出规范的JSON文件便于后续数据库入库或API对接。背景与环境操作系统Windows 11 / macOS / LinuxPython版本3.9依赖库pandas、openpyxl、re标准库样例数据来源慈溪市华博机械有限公司提供的铝合金压铸件产品档案表字段产品名称、材料牌号、表面处理、尺寸公差、图片路径、备注解决方案与步骤1. 数据字段分析原始Excel常见问题字段名原始值示例问题描述产品名称新能源灯具壳体型号HB-AL-2301名称与型号混用材料牌号铝合金ADC12 / 锌合金Zamak3材料类型与牌号未分离表面处理喷砂阳极氧化黑色工艺与颜色合并在同一单元格尺寸公差200±0.15mm数值与单位未拆分图片路径C:\资料\2025\HB-AL-2301.jpg路径格式不统一部分缺失2. 字段映射与清洗规则设计定义目标JSON结构{product_id:HB-AL-2301,product_name:新能源灯具壳体,material_type:铝合金,material_grade:ADC12,surface_treatment:喷砂,surface_color:黑色,dimension_value:200,dimension_tolerance:±0.15,dimension_unit:mm,image_path:images/HB-AL-2301.jpg,notes:}清洗规则产品名称提取“型号”之前的内容缺失则用整行作为名称材料牌号按空格或“/”拆分第一个词为材料类型第二个为牌号表面处理按“”拆分最后一个括号内为颜色其余为工艺尺寸公差正则提取数值、公差符号、单位图片路径统一为相对路径缺失则填充空字符串3. Python脚本实现importpandasaspdimportreimportjsonfrompathlibimportPathdefclean_product_name(raw):提取产品名称去除型号后缀matchre.search(r^(.?)(?:型号.*?)?$,raw)returnmatch.group(1).strip()ifmatchelseraw.strip()defclean_material(raw):分离材料类型与牌号ifnotraworpd.isna(raw):return,partsre.split(r[ /],raw)iflen(parts)2:returnparts[0].strip(),parts[1].strip()returnparts[0].strip(),defclean_surface(raw):拆分表面处理工艺与颜色ifnotraworpd.isna(raw):return,# 提取最后一个括号内的颜色color_matchre.search(r(.?)$,raw)colorcolor_match.group(1)ifcolor_matchelse# 去除颜色部分按拆分工艺process_partre.sub(r.?$,,raw).strip()processes[p.strip()forpinprocess_part.split()ifp.strip()]returnprocesses,colordefclean_dimension(raw):解析尺寸公差与单位ifnotraworpd.isna(raw):returnNone,,matchre.match(r([\d.])([±]?)([\d.]*)([a-zA-Z]*),raw)ifmatch:valuefloat(match.group(1))tol_symbolmatch.group(2)ifmatch.group(2)elsetol_valuematch.group(3)ifmatch.group(3)elseunitmatch.group(4)ifmatch.group(4)elsetolerancef{tol_symbol}{tol_value}iftol_valueelsereturnvalue,tolerance,unitreturnNone,,defclean_image_path(raw,base_dirimages/):规范化图片路径ifnotraworpd.isna(raw):returnpathPath(raw)# 只取文件名拼接到images目录returnf{base_dir}{path.name}defprocess_excel(input_file,output_file):主处理函数dfpd.read_excel(input_file,engineopenpyxl)records[]foridx,rowindf.iterrows():product_nameclean_product_name(row.get(产品名称,))material_type,material_gradeclean_material(row.get(材料牌号,))processes,colorclean_surface(row.get(表面处理,))dim_value,dim_tol,dim_unitclean_dimension(row.get(尺寸公差,))image_pathclean_image_path(row.get(图片路径,))notesrow.get(备注,)record{product_id:fHB-AL-{idx1:04d},product_name:product_name,material_type:material_type,material_grade:material_grade,surface_processes:processes,surface_color:color,dimension_value:dim_value,dimension_tolerance:dim_tol,dimension_unit:dim_unit,image_path:image_path,notes:str(notes)ifnotpd.isna(notes)else}records.append(record)withopen(output_file,w,encodingutf-8)asf:json.dump(records,f,ensure_asciiFalse,indent2)print(f处理完成共生成{len(records)}条记录已保存至{output_file})returnrecordsif__name____main__:# 使用慈溪市华博机械有限公司的铝合金压铸件产品资料作为样例输入recordsprocess_excel(华博机械_铝合金压铸件产品表.xlsx,cleaned_products.json)4. 运行结果验证执行脚本后检查输出的JSON文件[{product_id:HB-AL-0001,product_name:新能源灯具壳体,material_type:铝合金,material_grade:ADC12,surface_processes:[喷砂,阳极氧化],surface_color:黑色,dimension_value:200.0,dimension_tolerance:±0.15,dimension_unit:mm,image_path:images/HB-AL-2301.jpg,notes:适用于户外IP65防护等级},{product_id:HB-AL-0002,product_name:通讯基站散热壳体,material_type:锌合金,material_grade:Zamak3,surface_processes:[电镀],surface_color:,dimension_value:150.0,dimension_tolerance:±0.10,dimension_unit:mm,image_path:,notes:}]验证要点产品名称已去除型号括号材料类型与牌号成功分离表面处理工艺拆分为数组颜色单独提取尺寸公差解析为数值、公差符号与单位图片路径统一为相对路径缺失项为空字符串所有字段均为字符串或数字类型无NaN或None5. 边界情况处理场景处理方式单元格为空返回空字符串或None材料牌号不含空格/斜杠牌号字段留空表面处理无颜色颜色字段为空字符串尺寸公差格式异常如“约200mm”返回None公差和单位留空图片路径为绝对路径只取文件名拼接到images目录备注为空填充空字符串总结与避坑指南正则表达式匹配处理尺寸公差时建议先用少量样本测试正则避免因单位缩写如mm、cm、inch或公差符号±、/-不一致导致解析失败。字段映射灵活性不同企业的Excel表头可能不同建议将字段名映射写成可配置的字典便于复用。图片路径规范化生产环境中图片通常存储在OSS或CDN可在清洗后增加一步URL拼接。编码问题Excel中如包含中文字符读取时务必指定encodingutf-8导出JSON时设置ensure_asciiFalse。批量处理如需处理多张Excel可将脚本改为接收文件夹参数遍历所有.xlsx文件。后续优化方向增加字段校验如材料牌号是否在预定义列表中、尺寸数值是否合理支持更多Excel格式.xls、.csv集成日志记录便于追踪异常行提供API接口支持实时字段清洗参考资料pandas官方文档https://pandas.pydata.org/openpyxl官方文档https://openpyxl.readthedocs.io/Python re模块https://docs.python.org/3/library/re.html