用Python清洗Excel脏数据,10分钟干完一天的活

用Python清洗Excel脏数据，10分钟干完一天的活

作者：几行代码

你有没有经历过这种绝望——

老板发来一个Excel表格，说”整理一下”。打开一看：

有的日期写成”2024/1/5″，有的写成”2024年1月5日”，还有”Jan 5, 2024″
手机号有的带”-“，有的带空格，还有多了一个前导0
“金额”列里混进了”暂无””待定””N/A”
同一个人出现3次，名字分别是”张三”” 张三 “”张　三”

你对着这坨数据沉默了5秒钟，然后打开了Excel开始一个个手动改……

停。今天教你怎么用Python把这些脏数据收拾得干干净净。

一、准备工作

只需要两个库：pandas 和 openpyxl。

python

pip install pandas openpyxl

先创建一个模拟的脏数据文件，方便你跟着练：

python

import pandas as pd
import numpy as np

# 模拟真实的脏数据
data = {
    "姓名": ["张三", " 李四 ", "张三", "王　五", "赵六", "李四", " 孙七 ", "张三", "周八", "张三"],
    "手机号": ["13812345678", "138-1234-5679", "13912345670", "139 1234 5671", "13812345672", "13812345679", "13912345673", "13812345678", "13912345674", "13812345678"],
    "日期": ["2024/1/5", "2024年3月12日", "2024-06-15", "Jan 20, 2024", "2024.8.30", "2024年3月12日", "2024/11/03", "2024/1/5", "2024-06-15", "2024/1/5"],
    "金额": [1500, "暂无", 2300, "N/A", 890, "待定", 3200, 1500, "N/A", 1500],
    "城市": ["北京", "上海", "广州", "北京", "shanghai", "深圳", "Shenzhen", "北京", "广州", "北京"],
    "邮箱": ["zhangsan@qq.com", "lisi@163.com", "wangwu@qq.com", "INVALID", "zhaoliu@gmail.com", "lisi@163.com", "sunqi@qq.com", "zhangsan@qq.com", "zhouba@126.com", "zhangsan@qq.com"]
}

df = pd.DataFrame(data)
df.to_excel("dirty_data.xlsx", index=False)
print("脏数据文件已生成: dirty_data.xlsx")

跑一下，你会得到一个充满”惊喜”的Excel文件。

二、统一去除空格和不可见字符

这是最基本的操作，但很多人不知道：

python

import pandas as pd

df = pd.read_excel("dirty_data.xlsx")

# 去除所有字符串列的前后空格
str_cols = df.select_dtypes(include="object").columns
for col in str_cols:
    df[col] = df[col].str.strip()

# 去除不可见字符（全角空格、不间断空格等）
for col in str_cols:
    df[col] = df[col].str.replace("\u3000", "")   # 全角空格
    df[col] = df[col].str.replace("\xa0", "")     # 不间断空格
    df[col] = df[col].str.replace("\t", "")        # 制表符

print(df["姓名"].tolist())
# 清洗前: ['张三', ' 李四 ', '张三', '王　五', ...]
# 清洗后: ['张三', '李四', '张三', '王五', ...]

三、统一日期格式

日期格式不统一是最头疼的问题之一：

python

import pandas as pd

df = pd.read_excel("dirty_data.xlsx")

def clean_date(val):
    """将各种日期格式统一为 YYYY-MM-DD"""
    if pd.isna(val) or str(val).strip() in ["", "暂无", "N/A"]:
        return None
    
    val = str(val).strip()
    
    # 中文日期格式：2024年3月12日 → 2024-3-12
    if "年" in val:
        val = val.replace("年", "-").replace("月", "-").replace("日", "")
    
    # 格式替换
    val = val.replace("/", "-").replace(".", "-")
    
    try:
        return pd.to_datetime(val).strftime("%Y-%m-%d")
    except:
        return val  # 转换失败保留原值

df["日期"] = df["日期"].apply(clean_date)
print(df["日期"].tolist())
# 结果: ['2024-01-05', '2024-03-12', '2024-06-15', '2024-01-20', '2024-08-30', ...]

四、统一手机号格式

手机号里混杂着各种分隔符，还有的少了前导0：

python

import re

def clean_phone(val):
    """统一手机号格式：纯11位数字"""
    if pd.isna(val):
        return None
    
    val = str(val).strip()
    # 去掉所有非数字字符
    digits = re.sub(r"\D", "", val)
    
    # 补全前导0（如果只有10位，加上0）
    if len(digits) == 10 and digits[0] in "123456789":
        digits = "0" + digits
    
    # 校验是否为有效手机号
    if len(digits) == 11 and digits[0] == "1":
        return digits
    
    return None  # 格式不对，标记为空

df["手机号"] = df["手机号"].apply(clean_phone)
print(df["手机号"].tolist())
# 结果: ['13812345678', '13812345679', '13912345670', '13912345671', '13812345672', ...]

五、清洗金额列——把非数字值变成数字

金额列里混着”暂无””待定””N/A”，统计的时候直接报错：

python

def clean_amount(val):
    """将金额列统一为数字，非数字标记为空"""
    if pd.isna(val):
        return None
    
    val = str(val).strip()
    
    # 常见的非数字标记
    invalid_marks = ["暂无", "待定", "N/A", "n/a", "无", "-", "--", "—", "未知"]
    if val in invalid_marks or val == "":
        return None
    
    try:
        # 处理带逗号的数字，如 "1,500" → 1500
        val = val.replace(",", "").replace("，", "")
        return float(val)
    except ValueError:
        return None

df["金额"] = df["金额"].apply(clean_amount)
print(df["金额"].tolist())
# 结果: [1500.0, None, 2300.0, None, 890.0, None, 3200.0, 1500.0, None, 1500.0]

六、统一城市名称（大小写+别名）

“北京”和”beijing”是同一个城市，”shanghai”和”上海”也是：

python

city_mapping = {
    "北京": "北京", "beijing": "北京", "bj": "北京",
    "上海": "上海", "shanghai": "上海", "sh": "上海",
    "广州": "广州", "guangzhou": "广州", "gz": "广州",
    "深圳": "深圳", "shenzhen": "深圳", "sz": "深圳",
    "杭州": "杭州", "hangzhou": "杭州", "hz": "杭州",
    "成都": "成都", "chengdu": "成都", "cd": "成都",
}

def clean_city(val):
    """统一城市名称"""
    if pd.isna(val):
        return None
    val = str(val).strip().lower()
    return city_mapping.get(val, str(val).strip().title())

df["城市"] = df["城市"].apply(clean_city)
print(df["城市"].tolist())
# 结果: ['北京', '上海', '广州', '北京', '上海', '深圳', '深圳', '北京', '广州', '北京']

七、邮箱格式校验

简单校验邮箱是否合法：

python

import re

def clean_email(val):
    """校验邮箱格式，不合法的标记为空"""
    if pd.isna(val):
        return None
    
    val = str(val).strip().lower()
    pattern = r"^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}%%CODEBLOCK_7%%quot;
    
    if re.match(pattern, val):
        return val
    return None

df["邮箱"] = df["邮箱"].apply(clean_email)
print(df["邮箱"].tolist())
# 结果: ['zhangsan@qq.com', 'lisi@163.com', 'wangwu@qq.com', None, ...]

八、去除重复数据

清洗完各列之后，最重要的一步——去重：

python

print(f"清洗前: {len(df)} 条记录")

# 按姓名+手机号去重，保留第一条
df_dedup = df.drop_duplicates(subset=["姓名", "手机号"], keep="first")
print(f"去重后: {len(df_dedup)} 条记录")
# 张三出现了3次 → 只保留1条
# 李四出现了2次 → 只保留1条

如果需要查看被去掉的重复记录：

python

# 找出重复的行
duplicates = df[df.duplicated(subset=["姓名", "手机号"], keep=False)]
print("重复记录：")
print(duplicates)

九、导出清洗后的数据

python

# 导出为新的Excel文件
df_dedup.to_excel("clean_data.xlsx", index=False)
print(f"清洗完成！共 {len(df_dedup)} 条有效数据，已保存至 clean_data.xlsx")

# 同时生成一份清洗报告
print("\n" + "="*40)
print("清洗报告")
print("="*40)
print(f"原始数据: {len(df)} 条")
print(f"重复数据: {len(df) - len(df_dedup)} 条")
print(f"有效数据: {len(df_dedup)} 条")
for col in ["手机号", "日期", "金额", "邮箱"]:
    null_count = df_dedup[col].isna().sum() if col in df_dedup.columns else 0
    print(f"  {col}列空值: {null_count} 条")

输出示例：

清洗报告
========================================
原始数据: 10 条
重复数据: 4 条
有效数据: 6 条
  手机号列空值: 0 条
  日期列空值: 0 条
  金额列空值: 3 条
  邮箱列空值: 1 条

一键完整版

把上面所有清洗步骤封装成一个完整脚本，直接拿去用：

python

import pandas as pd
import numpy as np
import re
import os

def clean_excel(input_file, output_file):
    """完整的Excel数据清洗流程"""
    
    df = pd.read_excel(input_file)
    total_original = len(df)
    
    # 1. 去除空格和不可见字符
    str_cols = df.select_dtypes(include="object").columns
    for col in str_cols:
        df[col] = df[col].str.strip()
        df[col] = df[col].str.replace("\u3000", "", regex=False)
        df[col] = df[col].str.replace("\xa0", "", regex=False)
        df[col] = df[col].str.replace("\t", "", regex=False)
    
    # 2. 清洗日期列（自动识别包含"日期"或"date"的列）
    date_cols = [c for c in df.columns if "日期" in c.lower() or "date" in c.lower()]
    for col in date_cols:
        df[col] = df[col].apply(clean_date)
    
    # 3. 清洗手机号列
    phone_cols = [c for c in df.columns if "手机" in c.lower() or "phone" in c.lower()]
    for col in phone_cols:
        df[col] = df[col].apply(clean_phone)
    
    # 4. 清洗金额列
    amount_cols = [c for c in df.columns if "金额" in c.lower() or "amount" in c.lower() or "价格" in c.lower()]
    for col in amount_cols:
        df[col] = df[col].apply(clean_amount)
    
    # 5. 清洗城市列
    city_cols = [c for c in df.columns if "城市" in c.lower() or "city" in c.lower()]
    for col in city_cols:
        df[col] = df[col].apply(clean_city)
    
    # 6. 清洗邮箱列
    email_cols = [c for c in df.columns if "邮箱" in c.lower() or "email" in c.lower() or "mail" in c.lower()]
    for col in email_cols:
        df[col] = df[col].apply(clean_email)
    
    # 7. 去重
    dedup_cols = phone_cols if phone_cols else str_cols[:2].tolist()
    df = df.drop_duplicates(subset=dedup_cols, keep="first")
    
    # 8. 导出
    df.to_excel(output_file, index=False)
    
    # 打印清洗报告
    print(f"\n清洗完成！")
    print(f"原始数据: {total_original} 条 → 有效数据: {len(df)} 条")
    print(f"去除重复: {total_original - len(df)} 条")
    print(f"结果保存: {output_file}")
    
    return df

def clean_date(val):
    if pd.isna(val) or str(val).strip() in ["", "暂无", "N/A"]:
        return None
    val = str(val).strip()
    if "年" in val:
        val = val.replace("年", "-").replace("月", "-").replace("日", "")
    val = val.replace("/", "-").replace(".", "-")
    try:
        return pd.to_datetime(val).strftime("%Y-%m-%d")
    except:
        return val

def clean_phone(val):
    if pd.isna(val):
        return None
    digits = re.sub(r"\D", "", str(val))
    if len(digits) == 10 and digits[0] in "123456789":
        digits = "0" + digits
    return digits if len(digits) == 11 and digits[0] == "1" else None

def clean_amount(val):
    if pd.isna(val):
        return None
    val = str(val).strip()
    invalid = ["暂无", "待定", "N/A", "n/a", "无", "-", "--", "—", "未知"]
    if val in invalid or val == "":
        return None
    try:
        return float(val.replace(",", "").replace("，", ""))
    except:
        return None

def clean_city(val):
    mapping = {
        "北京": "北京", "beijing": "北京", "bj": "北京",
        "上海": "上海", "shanghai": "上海", "sh": "上海",
        "广州": "广州", "guangzhou": "广州", "gz": "广州",
        "深圳": "深圳", "shenzhen": "深圳", "sz": "深圳",
        "杭州": "杭州", "hangzhou": "杭州", "hz": "杭州",
        "成都": "成都", "chengdu": "成都", "cd": "成都",
    }
    if pd.isna(val):
        return None
    val_lower = str(val).strip().lower()
    return mapping.get(val_lower, str(val).strip().title())

def clean_email(val):
    if pd.isna(val):
        return None
    val = str(val).strip().lower()
    if re.match(r"^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}%%CODEBLOCK_12%%quot;, val):
        return val
    return None

# 使用方法：一行搞定
clean_excel("dirty_data.xlsx", "clean_data.xlsx")

这个脚本的妙处在于自动识别列名——只要你的表头包含”日期””手机””金额””城市””邮箱”这些关键词，就会自动匹配对应的清洗逻辑。

不需要改代码，换个Excel文件直接跑。

总结

清洗步骤	解决的问题	难度
去除空格和不可见字符	“张三” vs ” 张三 ” vs “张　三”	⭐
统一日期格式	2024/1/5 vs 2024年1月5日 vs Jan 5, 2024	⭐⭐
统一手机号格式	138-1234-5678 vs 138 1234 5678	⭐
清洗金额列	1500 vs “暂无” vs “N/A”	⭐
统一城市名称	“北京” vs “beijing” vs “BJ”	⭐⭐
邮箱格式校验	“INVALID” 等非法值	⭐
去除重复数据	同一个人出现多次	⭐

数据清洗的核心就一句话：找到规则，写好映射，批量处理。

与其花一天时间在Excel里手动Ctrl+H，不如花10分钟写个脚本，以后所有脏数据一劳永逸。

如果觉得有用，点个关注，后续更多实战技巧持续分享。

——几行代码