Python语法日报 第14期|Excel自动化开篇——数据清洗实战.脏数据别扔,三下五除二给它洗干净

上期把几十个Excel揉成一张总表了，看着挺爽。但打开一看，有的名字带空格，有的部门大小写乱写，有的工资是空的，还有重复的行。今天一起洗数据，把脏玩意儿整干净。

一、先看今天要洗啥

拿上期合并完的“汇总结果.xlsx”开刀，典型的脏数据长这样：

毛病一堆：张三重复、部门名不统一、王五工资空的、名字前后有空格。一个一个手改？那不得改到后半夜。上代码。

二、去重——重复的行直接噶掉

from openpyxl import load_workbookwb = load_workbook('汇总结果.xlsx')ws = wb.activeseen = set()          # 记录见过的行rows_to_delete = []for row in range(2, ws.max_row + 1):# 把整行数据拼成个元组当指纹    key = (ws.cell(row, 1).value, ws.cell(row, 2).value, ws.cell(row, 3).value)if key in seen:        rows_to_delete.append(row)    # 重复的记下来else:        seen.add(key)# 倒着删，不然删一行行号全乱for row in reversed(rows_to_delete):    ws.delete_rows(row)wb.save('汇总结果_去重.xlsx')print(f'干掉{len(rows_to_delete)}行重复的')

关键点：删行必须倒着删。正着删的话，删完第3行，原来的第4行变成第3行，再删第4行就删错了。

三、填空——空的地方给它补上

空工资的填0，空部门的填“未知”。

wb = load_workbook('汇总结果_去重.xlsx')ws = wb.activefor row in range(2, ws.max_row + 1):# 部门空了填“未知”    dept = ws.cell(row, 2).valueif not dept or str(dept).strip() == '':        ws.cell(row, 2).value = '未知'# 工资空了填0    salary = ws.cell(row, 3).valueif not salary:        ws.cell(row, 3).value = 0wb.save('汇总结果_填空.xlsx')

坑：not dept能同时抓None和空字符串，但抓不了全是空格的" "。所以后面再加个str(dept).strip() == ''兜底。

四、去空格——名字前后别留白

wb = load_workbook('汇总结果_填空.xlsx')ws = wb.activefor row in range(2, ws.max_row + 1):for col in range(1, ws.max_column + 1):        cell = ws.cell(row, col)if isinstance(cell.value, str):            cell.value = cell.value.strip()   # 去掉前后空格wb.save('汇总结果_去空格.xlsx')

strip()把字符串前后的空格、换行、制表符全薅掉。中间的空格不动，别把“张 三”改成“张三”。

五、统一部门名——大小写和别名都归一

有的写“销售”，有的写“销售部”，有的写“销售部门”。全给它统一成“销售部”。

wb = load_workbook('汇总结果_去空格.xlsx')ws = wb.active# 别名映射表，左边是乱的，右边是标准的dept_map = {'销售': '销售部','销售部门': '销售部','技术': '技术部','技术部门': '技术部','行政': '行政部','行政部': '行政部'}for row in range(2, ws.max_row + 1):    dept = ws.cell(row, 2).valueif dept in dept_map:        ws.cell(row, 2).value = dept_map[dept]wb.save('汇总结果_统一部门.xlsx')

映射表想加就加，以后碰到新别名往里塞一行就行。

六、把上面几步串成一个清洗函数

from openpyxl import load_workbookdef clean_excel(input_file, output_file):    wb = load_workbook(input_file)    ws = wb.active# 1. 去重    seen = set()    del_rows = []for row in range(2, ws.max_row + 1):        key = (ws.cell(row,1).value, ws.cell(row,2).value, ws.cell(row,3).value)if key in seen:            del_rows.append(row)else:            seen.add(key)for row in reversed(del_rows):        ws.delete_rows(row)# 2. 去空格 + 填空 + 统一部门    dept_map = {'销售':'销售部', '技术':'技术部', '行政':'行政部'}for row in range(2, ws.max_row + 1):for col in range(1, ws.max_column + 1):            cell = ws.cell(row, col)if isinstance(cell.value, str):                cell.value = cell.value.strip()        dept = ws.cell(row, 2).valueif dept in dept_map:            ws.cell(row, 2).value = dept_map[dept]elif not dept:            ws.cell(row, 2).value = '未知'if not ws.cell(row, 3).value:            ws.cell(row, 3).value = 0    wb.save(output_file)print(f'清洗完毕：{output_file}')clean_excel('汇总结果.xlsx', '汇总结果_干净版.xlsx')

以后拿到脏数据，调这个函数就完事。

七、今儿个知识点