現(xiàn)Excel數(shù)據(jù)高效比對與清洗)
1. 問題場景與需求分析在日常HR管理或行政工作中我們經(jīng)常需要處理來自不同系統(tǒng)的員工數(shù)據(jù)。比如考勤系統(tǒng)導(dǎo)出的當(dāng)月在職人員名單財(cái)務(wù)系統(tǒng)提供的工資發(fā)放清單部門自行維護(hù)的項(xiàng)目組成員表這些數(shù)據(jù)通常以Excel工作表形式存在但往往存在以下痛點(diǎn)各系統(tǒng)間員工ID格式不統(tǒng)一如有的帶前綴有的純數(shù)字姓名可能存在簡繁體/大小寫差異部分字段可能包含多余空格等隱形字符需要直觀展示比對結(jié)果供非技術(shù)人員查閱實(shí)際案例某公司年終審計(jì)時(shí)發(fā)現(xiàn)考勤系統(tǒng)顯示在職員工比HR系統(tǒng)多出12人經(jīng)查是離職員工未及時(shí)同步導(dǎo)致五險(xiǎn)一金多繳造成直接經(jīng)濟(jì)損失8萬余元。2. 技術(shù)方案選型2.1 為什么選擇Python相比Excel自帶函數(shù)或VBAPython處理該任務(wù)的優(yōu)勢在于處理大文件更高效實(shí)測10萬行數(shù)據(jù)Python比VBA快3倍以上更靈活的數(shù)據(jù)清洗能力正則表達(dá)式、字符串處理等豐富的可視化選項(xiàng)條件格式、差異高亮等可保存為模板腳本重復(fù)使用2.2 核心工具棧import pandas as pd # 數(shù)據(jù)操作 from openpyxl import load_workbook # Excel編輯 from openpyxl.styles import PatternFill # 單元格樣式 import difflib # 模糊匹配3. 完整實(shí)現(xiàn)步驟3.1 數(shù)據(jù)預(yù)處理def clean_data(df): # 統(tǒng)一字符串格式 df df.apply(lambda x: x.str.strip() if x.dtype object else x) # 處理空值 df.fillna(NULL_PLACEHOLDER, inplaceTrue) # 統(tǒng)一ID格式示例去除前綴 df[員工ID] df[員工ID].str.replace(EMP-, ) return df # 讀取兩個(gè)工作表 df1 pd.read_excel(data.xlsx, sheet_nameSheet1) df2 pd.read_excel(data.xlsx, sheet_nameSheet2) # 清洗數(shù)據(jù) df1_clean clean_data(df1) df2_clean clean_data(df2)3.2 關(guān)鍵比對邏輯3.2.1 精確匹配推薦方案# 使用merge進(jìn)行比對 result pd.merge( df1_clean, df2_clean, on[員工ID, 姓名], # 關(guān)鍵字段 howouter, indicatorTrue ) # 分類結(jié)果 matched result[result[_merge] both] only_in_df1 result[result[_merge] left_only] only_in_df2 result[result[_merge] right_only]3.2.2 模糊匹配備選方案當(dāng)姓名可能存在拼寫差異時(shí)def fuzzy_match(row): # 使用difflib計(jì)算相似度 return difflib.SequenceMatcher( None, str(row[姓名_x]), str(row[姓名_y]) ).ratio() # 應(yīng)用模糊匹配 fuzzy_results result.apply(fuzzy_match, axis1) result[相似度] fuzzy_results3.3 可視化輸出def highlight_diff(sheet): # 設(shè)置差異高亮樣式 red_fill PatternFill(start_colorFFEE1111, end_colorFFEE1111, fill_typesolid) green_fill PatternFill(start_colorFF11EE11, end_colorFF11EE11, fill_typesolid) # 遍歷單元格標(biāo)記差異 for row in sheet.iter_rows(): for cell in row: if DIFF_FLAG in str(cell.value): cell.fill red_fill elif NEW_FLAG in str(cell.value): cell.fill green_fill # 保存結(jié)果到新Excel with pd.ExcelWriter(comparison_result.xlsx) as writer: matched.to_excel(writer, sheet_name匹配成功, indexFalse) only_in_df1.to_excel(writer, sheet_name僅表1存在, indexFalse) only_in_df2.to_excel(writer, sheet_name僅表2存在, indexFalse) # 應(yīng)用高亮樣式 wb load_workbook(comparison_result.xlsx) for sheetname in wb.sheetnames: highlight_diff(wb[sheetname]) wb.save(comparison_result_final.xlsx)4. 實(shí)戰(zhàn)經(jīng)驗(yàn)與避坑指南4.1 性能優(yōu)化技巧大文件處理方案# 分塊讀取適用于超大型文件 chunk_size 10000 reader pd.read_excel(large_file.xlsx, chunksizechunk_size) for chunk in reader: process(chunk) # 自定義處理函數(shù)內(nèi)存優(yōu)化參數(shù)pd.read_excel( data.xlsx, dtype{員工ID: string, 部門: category}, # 指定數(shù)據(jù)類型 usecols[員工ID, 姓名, 部門] # 只讀取必要列 )4.2 常見問題排查問題1編碼錯(cuò)誤導(dǎo)致亂碼# 指定編碼格式常見于包含中文的Excel df pd.read_excel(data.xlsx, engineopenpyxl, encodinggbk)問題2日期格式不一致# 統(tǒng)一日期格式 df[入職日期] pd.to_datetime(df[入職日期], errorscoerce).dt.strftime(%Y-%m-%d)問題3隱藏字符干擾# 徹底清洗不可見字符 import re df[姓名] df[姓名].apply(lambda x: re.sub(r[\x00-\x1F\x7F], , str(x)))5. 擴(kuò)展應(yīng)用場景5.1 多表聯(lián)合比對# 比對三個(gè)及以上工作表 from functools import reduce dfs [df1, df2, df3] common_cols list(reduce(lambda x, y: x.intersection(y), [set(df.columns) for df in dfs])) result reduce(lambda left,right: pd.merge(left, right, oncommon_cols, howouter), dfs)5.2 自動(dòng)化郵件報(bào)告import smtplib from email.mime.multipart import MIMEMultipart from email.mime.base import MIMEBase from email import encoders msg MIMEMultipart() msg[Subject] 員工數(shù)據(jù)比對報(bào)告 msg.attach(MIMEBase(application, octet-stream).set_payload(open(comparison_result_final.xlsx, rb).read())) encoders.encode_base64(msg.get_payload(0)) msg.get_payload(0).add_header(Content-Disposition, attachment, filenameresult.xlsx) with smtplib.SMTP(smtp.example.com) as server: server.sendmail(senderexample.com, receiverexample.com, msg.as_string())5.3 數(shù)據(jù)庫集成方案# 從數(shù)據(jù)庫直接讀取比對 import sqlalchemy engine sqlalchemy.create_engine(postgresql://user:passlocalhost:5432/hr_db) df_db pd.read_sql(SELECT * FROM employees, engine) df_excel pd.read_excel(current.xlsx) pd.merge(df_db, df_excel, onemployee_id, howouter) Python實(shí)現(xiàn)Excel數(shù)據(jù)高效比對與清洗
1. 問題場景與需求分析在日常HR管理或行政工作中我們經(jīng)常需要處理來自不同系統(tǒng)的員工數(shù)據(jù)。比如考勤系統(tǒng)導(dǎo)出的當(dāng)月在職人員名單財(cái)務(wù)系統(tǒng)提供的工資發(fā)放清單部門自行維護(hù)的項(xiàng)目組成員表這些數(shù)據(jù)通常以Excel工作表形式存在但往往存在以下痛點(diǎn)各系統(tǒng)間員工ID格式不統(tǒng)一如有的帶前綴有的純數(shù)字姓名可能存在簡繁體/大小寫差異部分字段可能包含多余空格等隱形字符需要直觀展示比對結(jié)果供非技術(shù)人員查閱實(shí)際案例某公司年終審計(jì)時(shí)發(fā)現(xiàn)考勤系統(tǒng)顯示在職員工比HR系統(tǒng)多出12人經(jīng)查是離職員工未及時(shí)同步導(dǎo)致五險(xiǎn)一金多繳造成直接經(jīng)濟(jì)損失8萬余元。2. 技術(shù)方案選型2.1 為什么選擇Python相比Excel自帶函數(shù)或VBAPython處理該任務(wù)的優(yōu)勢在于處理大文件更高效實(shí)測10萬行數(shù)據(jù)Python比VBA快3倍以上更靈活的數(shù)據(jù)清洗能力正則表達(dá)式、字符串處理等豐富的可視化選項(xiàng)條件格式、差異高亮等可保存為模板腳本重復(fù)使用2.2 核心工具棧import pandas as pd # 數(shù)據(jù)操作 from openpyxl import load_workbook # Excel編輯 from openpyxl.styles import PatternFill # 單元格樣式 import difflib # 模糊匹配3. 完整實(shí)現(xiàn)步驟3.1 數(shù)據(jù)預(yù)處理def clean_data(df): # 統(tǒng)一字符串格式 df df.apply(lambda x: x.str.strip() if x.dtype object else x) # 處理空值 df.fillna(NULL_PLACEHOLDER, inplaceTrue) # 統(tǒng)一ID格式示例去除前綴 df[員工ID] df[員工ID].str.replace(EMP-, ) return df # 讀取兩個(gè)工作表 df1 pd.read_excel(data.xlsx, sheet_nameSheet1) df2 pd.read_excel(data.xlsx, sheet_nameSheet2) # 清洗數(shù)據(jù) df1_clean clean_data(df1) df2_clean clean_data(df2)3.2 關(guān)鍵比對邏輯3.2.1 精確匹配推薦方案# 使用merge進(jìn)行比對 result pd.merge( df1_clean, df2_clean, on[員工ID, 姓名], # 關(guān)鍵字段 howouter, indicatorTrue ) # 分類結(jié)果 matched result[result[_merge] both] only_in_df1 result[result[_merge] left_only] only_in_df2 result[result[_merge] right_only]3.2.2 模糊匹配備選方案當(dāng)姓名可能存在拼寫差異時(shí)def fuzzy_match(row): # 使用difflib計(jì)算相似度 return difflib.SequenceMatcher( None, str(row[姓名_x]), str(row[姓名_y]) ).ratio() # 應(yīng)用模糊匹配 fuzzy_results result.apply(fuzzy_match, axis1) result[相似度] fuzzy_results3.3 可視化輸出def highlight_diff(sheet): # 設(shè)置差異高亮樣式 red_fill PatternFill(start_colorFFEE1111, end_colorFFEE1111, fill_typesolid) green_fill PatternFill(start_colorFF11EE11, end_colorFF11EE11, fill_typesolid) # 遍歷單元格標(biāo)記差異 for row in sheet.iter_rows(): for cell in row: if DIFF_FLAG in str(cell.value): cell.fill red_fill elif NEW_FLAG in str(cell.value): cell.fill green_fill # 保存結(jié)果到新Excel with pd.ExcelWriter(comparison_result.xlsx) as writer: matched.to_excel(writer, sheet_name匹配成功, indexFalse) only_in_df1.to_excel(writer, sheet_name僅表1存在, indexFalse) only_in_df2.to_excel(writer, sheet_name僅表2存在, indexFalse) # 應(yīng)用高亮樣式 wb load_workbook(comparison_result.xlsx) for sheetname in wb.sheetnames: highlight_diff(wb[sheetname]) wb.save(comparison_result_final.xlsx)4. 實(shí)戰(zhàn)經(jīng)驗(yàn)與避坑指南4.1 性能優(yōu)化技巧大文件處理方案# 分塊讀取適用于超大型文件 chunk_size 10000 reader pd.read_excel(large_file.xlsx, chunksizechunk_size) for chunk in reader: process(chunk) # 自定義處理函數(shù)內(nèi)存優(yōu)化參數(shù)pd.read_excel( data.xlsx, dtype{員工ID: string, 部門: category}, # 指定數(shù)據(jù)類型 usecols[員工ID, 姓名, 部門] # 只讀取必要列 )4.2 常見問題排查問題1編碼錯(cuò)誤導(dǎo)致亂碼# 指定編碼格式常見于包含中文的Excel df pd.read_excel(data.xlsx, engineopenpyxl, encodinggbk)問題2日期格式不一致# 統(tǒng)一日期格式 df[入職日期] pd.to_datetime(df[入職日期], errorscoerce).dt.strftime(%Y-%m-%d)問題3隱藏字符干擾# 徹底清洗不可見字符 import re df[姓名] df[姓名].apply(lambda x: re.sub(r[\x00-\x1F\x7F], , str(x)))5. 擴(kuò)展應(yīng)用場景5.1 多表聯(lián)合比對# 比對三個(gè)及以上工作表 from functools import reduce dfs [df1, df2, df3] common_cols list(reduce(lambda x, y: x.intersection(y), [set(df.columns) for df in dfs])) result reduce(lambda left,right: pd.merge(left, right, oncommon_cols, howouter), dfs)5.2 自動(dòng)化郵件報(bào)告import smtplib from email.mime.multipart import MIMEMultipart from email.mime.base import MIMEBase from email import encoders msg MIMEMultipart() msg[Subject] 員工數(shù)據(jù)比對報(bào)告 msg.attach(MIMEBase(application, octet-stream).set_payload(open(comparison_result_final.xlsx, rb).read())) encoders.encode_base64(msg.get_payload(0)) msg.get_payload(0).add_header(Content-Disposition, attachment, filenameresult.xlsx) with smtplib.SMTP(smtp.example.com) as server: server.sendmail(senderexample.com, receiverexample.com, msg.as_string())5.3 數(shù)據(jù)庫集成方案# 從數(shù)據(jù)庫直接讀取比對 import sqlalchemy engine sqlalchemy.create_engine(postgresql://user:passlocalhost:5432/hr_db) df_db pd.read_sql(SELECT * FROM employees, engine) df_excel pd.read_excel(current.xlsx) pd.merge(df_db, df_excel, onemployee_id, howouter)