Excel数据清洗,我用Python写了套脚本,同事追着要(附完整代码)

pip install pandas openpyxl

import pandas as pdimport numpy as npimport re# dtype=str：全部按字符串读，防止手机号/身份证变成 1.38E+10df = pd.read_excel("员工信息_原始.xlsx", dtype=str)print(f"原始数据：{df.shape[0]} 行，{df.shape[1]} 列")

df.columns = (    df.columns.astype(str)      .str.strip()      .str.replace(r"[\s\u3000]+", "", regex=True)   # 半角+全角空格      .str.replace(r"[（）()]", "", regex=True)      # 中英文括号)

for col in df.select_dtypes(include="object").columns:    df[col] = (        df[col].astype(str)          .str.replace(r"[\u200b\u3000\xa0]", "", regex=True)  # 零宽/全角/不换行空格          .str.strip()    )# 把"伪空值"统一还原成 NaNdf = df.replace({    "nan": np.nan, "NaN": np.nan, "None": np.nan,    "": np.nan, "-": np.nan, "无": np.nan, "null": np.nan})

before = len(df)df = df.drop_duplicates()print(f"删除重复行：{before - len(df)} 条")

def clean_phone(x):    if pd.isna(x):        return np.nan    digits = re.sub(r"\D", "", str(x))       # 只留数字    if digits.startswith("86") and len(digits) == 13:        digits = digits[2:]                   # 去掉 +86 前缀    return digits if re.fullmatch(r"1[3-9]\d{9}", digits) else np.nandf["手机号"] = df["手机号"].apply(clean_phone)

def clean_date(x):    if pd.isna(x):        return pd.NaT    s = str(x).strip()    s = s.replace("年", "-").replace("月", "-").replace("日", "")    s = re.sub(r"[./]", "-", s)    s = re.sub(r"\s+.*$", "", s)              # 去掉 " 09:30:00" 这类时间后缀    if re.fullmatch(r"\d{8}", s):             # 20230512        return pd.to_datetime(s, format="%Y%m%d", errors="coerce")    return pd.to_datetime(s, errors="coerce")df["入职日期"] = df["入职日期"].apply(clean_date)df["入职日期"] = df["入职日期"].dt.date       # 只保留日期部分

def clean_money(x):    if pd.isna(x):        return np.nan    s = str(x).strip().replace(",", "").replace("，", "")    if "万" in s:        num = re.search(r"\d+(\.\d+)?", s)        return float(num.group()) * 10000 if num else np.nan    num = re.search(r"\d+(\.\d+)?", s)    return float(num.group()) if num else np.nandf["月薪"] = df["月薪"].apply(clean_money)

# ① 关键字段缺失 → 直接剔除，并留痕key_cols = ["姓名", "手机号"]bad_rows = df[df[key_cols].isna().any(axis=1)]bad_rows.to_excel("需人工复核.xlsx", index=False)   # 不要静默丢弃！df = df.dropna(subset=key_cols)# ② 分类字段 → 填"未知"df["部门"] = df["部门"].fillna("未知")# ③ 数值字段 → 视业务决定# df["月薪"] = df["月薪"].fillna(df["月薪"].median())

q1, q3 = df["月薪"].quantile([0.25, 0.75])iqr = q3 - q1lower, upper = q1 - 1.5 * iqr, q3 + 1.5 * iqroutliers = df[(df["月薪"] < lower) | (df["月薪"] > upper)]print(f"发现 {len(outliers)} 条薪资异常，建议人工复核")

from openpyxl.styles import Font, Alignment, PatternFilldef clean_pipeline(df: pd.DataFrame) -> pd.DataFrame:    # ... 上面 Step1~8 全部塞进来 ...    return dfdf = pd.read_excel("员工信息_原始.xlsx", dtype=str)df = clean_pipeline(df)with pd.ExcelWriter("员工信息_已清洗.xlsx", engine="openpyxl") as writer:    df.to_excel(writer, index=False, sheet_name="清洗结果")    ws = writer.sheets["清洗结果"]    # 表头样式    for cell in ws[1]:        cell.font = Font(bold=True, color="FFFFFF")        cell.fill = PatternFill("solid", fgColor="4F81BD")        cell.alignment = Alignment(horizontal="center", vertical="center")    # 自适应列宽    for col in ws.columns:        width = max(len(str(c.value)) if c.value else 0 for c in col) + 4        ws.column_dimensions[col[0].column_letter].width = min(width, 30)    ws.freeze_panes = "A2"          # 冻结首行    ws.auto_filter.ref = ws.dimensions  # 自动筛选print(f"✅ 清洗完成，共 {len(df)} 行")

from pathlib import Pathdef batch_clean(input_dir: str, output_dir: str):    Path(output_dir).mkdir(parents=True, exist_ok=True)    for file in Path(input_dir).glob("*.xls*"):        if file.name.startswith("~$"):   # 跳过Excel临时文件            continue        try:            df = pd.read_excel(file, dtype=str)            df = clean_pipeline(df)            out = Path(output_dir) / f"已清洗_{file.stem}.xlsx"            df.to_excel(out, index=False)            print(f"✅ {file.name} → {out.name}（{len(df)}行）")        except Exception as e:            print(f"❌ {file.name} 失败：{e}")batch_clean("./原始报表", "./清洗结果")

pip install python-calamine

df = pd.read_excel("大文件.xlsx", engine="calamine", dtype=str)