3万行脏数据30分钟洗白：四个工具组合替代逐行手工改

一、四类数据问题，对号入座选工具

数据脏乱差的表现形式五花八门，但归类下来就四种。每种有最适合的工具组合，混用反而效率低。

二、Excel Power Query：表格数据整理的首选，零代码

很多人不知道Excel自带了非常强大的数据整理引擎Power Query（Excel 2016及以上内置，2013需装插件）。它的核心逻辑是：把清洗步骤记录下来，下次新数据扔进来自动执行相同的清洗流程。

Power Query常用M函数速查

// 去除首尾空格= Table.TransformColumns(源, {{"姓名", Text.Trim}})// 按分隔符分列（把"张三,13800138000"拆成两列）= Table.SplitColumn(源, "混合列", Splitter.SplitTextByDelimiter(","), {"姓名", "电话"})// 提取文本中的数字= Table.AddColumn(源, "金额", each Text.Select([备注], {"0".."9", "."}))// 统一日期格式= Table.TransformColumnTypes(源, {{"日期", type date}})// 删除所有空行= Table.SelectRows(源, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))

三、Python Pandas：数据整理的瑞士军刀

当Power Query处理不过来（数据量大、逻辑复杂、需要正则、需要自动化定时跑），Pandas是下一个台阶。十几行代码能完成的事情，在Excel里可能要操作十几分钟。

数据清洗常用操作速查

import pandas as pdimport redf = pd.read_csv("messy_data.csv", encoding="utf-8")# ═══ 1. 列操作 ═══# 重命名列df.rename(columns={"客户姓名 ": "姓名", "电话 " : "电话"}, inplace=True)# 删除不需要的列df.drop(columns=["备注", "来源"], inplace=True)# ═══ 2. 字符串清洗 ═══# 去除所有字符串列的首尾空格str_cols = df.select_dtypes(include="object").columnsdf[str_cols] = df[str_cols].apply(lambda col: col.str.strip())# 统一大小写df["城市"] = df["城市"].str.upper()   # 全部大写df["姓名"] = df["姓名"].str.title()   # 首字母大写# 正则清洗：移除电话号码中的特殊字符df["电话"] = df["电话"].str.replace(r'[\s\-\(\)\+]', '', regex=True)# ═══ 3. 分列（把一列拆成多列） ═══# 按逗号分列："张三,13800138000" → 姓名、电话两列df[["姓名", "电话"]] = df["混合信息"].str.split(",", expand=True)# 正则提取：从地址中提取省份和城市df["省份"] = df["地址"].str.extract(r'([\u4e00-\u9fa5]{2,3}省)')df["城市"] = df["地址"].str.extract(r'([\u4e00-\u9fa5]{2,3}市)')# ═══ 4. 空值处理 ═══# 查看每列空值数量print(df.isnull().sum())# 删除任何包含空值的行df.dropna(inplace=True)# 用指定值填充空值df["城市"].fillna("未知", inplace=True)df["金额"].fillna(df["金额"].median(), inplace=True)  # 用中位数填充# ═══ 5. 数据类型转换 ═══df["日期"] = pd.to_datetime(df["日期"], errors="coerce")df["金额"] = pd.to_numeric(df["金额"], errors="coerce")df["ID"] = df["ID"].astype("int64")# ═══ 6. 去重 ═══# 完全重复的行df.drop_duplicates(inplace=True)# 按电话去重，保留第一条df.drop_duplicates(subset=["电话"], keep="first", inplace=True)# ═══ 7. 筛选 ═══# 筛选金额大于1000的记录df = df[df["金额"] > 1000]# 多个条件筛选df = df[(df["城市"].isin(["北京", "上海", "深圳"])) & (df["金额"] > 500)]# ═══ 8. 导出 ═══df.to_csv("clean_data.csv", index=False, encoding="utf-8-sig")df.to_excel("clean_data.xlsx", index=False)

多表合并：把十几个Excel文件合成一个

from pathlib import Pathimport pandas as pddef merge_excel_files(folder, output):"""把一个文件夹里所有Excel/CSV合并成一个文件，自动去重"""all_data = []for f in Path(folder).glob("*"):if f.suffix in (".xlsx", ".xls"):df = pd.read_excel(f)elif f.suffix == ".csv":try:df = pd.read_csv(f, encoding="utf-8")except:df = pd.read_csv(f, encoding="gbk")  # 兼容中文编码else:continuedf["来源文件"] = f.name  # 标记数据来源all_data.append(df)merged = pd.concat(all_data, ignore_index=True)merged.drop_duplicates(inplace=True)# 去除所有字符串列的首尾空格for col in merged.select_dtypes("object").columns:merged[col] = merged[col].str.strip()merged.to_excel(output, index=False)print(f"合并完成: {len(all_data)} 个文件 → {len(merged)} 行（去重后）")return merged# 使用merge_excel_files("./销售报表/", "合并结果.xlsx")

模糊匹配去重：同一公司注册了六个名字怎么办

精确去重处理不了"腾讯科技"和"腾讯科技有限公司"这种问题，需要模糊匹配：

from difflib import SequenceMatcherdef fuzzy_dedupe(df, column, threshold=0.85):"""模糊去重：相似度超过阈值的视为重复，保留第一条"""names = df[column].dropna().unique()duplicates = set()for i, name1 in enumerate(names):if name1 in duplicates:continuefor name2 in names[i+1:]:if name2 in duplicates:continueratio = SequenceMatcher(None, name1, name2).ratio()if ratio >= threshold:duplicates.add(name2)print(f"模糊匹配: {name1} ≈ {name2} (相似度: {ratio:.2%})")return df[~df[column].isin(duplicates)]# 使用：对"公司名称"列做模糊去重，相似度85%以上视为重复clean_df = fuzzy_dedupe(df, "公司名称", threshold=0.85)

四、jq：JSON数据的命令行手术刀

API返回的JSON数据动辄嵌套四五层，直接用眼睛看能看瞎。jq是处理JSON数据的最强命令行工具，一条命令就能把复杂JSON拍平成干净的表格。

# 安装jq（Windows用choco或scoop，Mac用brew，Linux用apt/yum）# choco install jq# 美化JSON输出cat data.json | jq '.'# 提取嵌套字段：取出所有用户的姓名和邮箱cat users.json | jq '.[] | {name: .profile.name, email: .contact.email}'# 拍平嵌套数组为CSV格式cat orders.json | jq -r '.[] | [.id, .customer.name, .total, .items | length] | @csv'# 筛选：只保留金额大于1000的订单cat orders.json | jq '.[] | select(.total > 1000)'# 分组统计：按城市统计用户数量cat users.json | jq 'group_by(.city) | map({city: .[0].city, count: length})'# 从多层嵌套中提取数据并输出为CSVcat api_response.json | jq -r '.data.items[] | [.sku, .name, .price.amount, .stock.available] | @csv' > products.csv

Python json_normalize：把嵌套JSON展平成DataFrame

import pandas as pdimport json# 加载深层嵌套JSONwith open("nested_data.json") as f:data = json.load(f)# json_normalize 自动展平嵌套结构# {"user": {"name": "张三", "address": {"city": "北京"}}} → user.name, user.address.citydf = pd.json_normalize(data["items"])# 展平嵌套列表（每个订单的items数组展开成多行）df = pd.json_normalize(data["orders"],record_path=["items"],        # 要展开的列表字段meta=["order_id", "customer"]  # 父级字段保留)# 展平后导出为Exceldf.to_excel("flattened_data.xlsx", index=False)

五、命令行文本处理：日志和纯文本的结构化提取

不是所有数据都规规矩矩待在Excel或JSON里。服务器日志、爬虫输出、邮件导出、聊天记录——这些非结构化文本里也藏着结构化数据。awk + sed + grep 的组合拳在这里最管用。

# ═══ 日志提取 ═══# 从Nginx日志中提取每个IP的请求次数（前20名）awk '{print $1}' access.log | sort | uniq -c | sort -rn | head -20# 提取指定时间段内的请求grep "26/Jul/2026:1[4-5]:" access.log > afternoon_requests.log# 提取返回码为500的错误请求awk '$9 == 500 {print $0}' access.log > errors_500.log# ═══ 文本清洗 ═══# 删除文件中的所有空行sed '/^$/d' messy.txt > clean.txt# 删除行首行尾空白sed 's/^[[:space:]]*//;s/[[:space:]]*$//' messy.txt > clean.txt# 把所有连续多个空格替换成单个制表符（转TSV）sed 's/[[:space:]]\{2,\}/\t/g' data.txt > data.tsv# ═══ 数据提取 ═══# 提取文件中所有手机号（中国大陆11位）grep -oP '1[3-9]\d{9}' messy_data.txt | sort -u > phones.txt# 提取所有邮箱地址grep -oP '[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}' data.txt | sort -u# 提取所有金额（含小数）grep -oP '\d+\.?\d*' report.txt

六、在线工具：不用装任何软件

偶尔处理一次数据，不想装Python也不想学Power Query，这几个在线工具能帮上忙。

七、数据整理常见场景速查表

八、不同数据量级的工具选择

九、一个完整的自动化清洗流水线

如果你每周或每天都要处理同一格式的脏数据，把清洗步骤写成脚本，配置定时任务自动跑。下面是一个完整的数据清洗流水线模板：

import pandas as pdimport refrom pathlib import Pathfrom datetime import datetimeclass DataCleanPipeline:"""通用数据清洗流水线"""def __init__(self, input_path, output_dir="./cleaned"):self.df = self._load(input_path)self.output_dir = Path(output_dir)self.output_dir.mkdir(exist_ok=True)self.log = []def _load(self, path):"""自动识别文件类型加载"""path = Path(path)if path.suffix == ".csv":try:return pd.read_csv(path, encoding="utf-8")except:return pd.read_csv(path, encoding="gbk")elif path.suffix in (".xlsx", ".xls"):return pd.read_excel(path)elif path.suffix == ".json":return pd.read_json(path)else:raise ValueError(f"不支持的文件格式: {path.suffix}")def strip_whitespace(self):"""去除所有字符串列的首尾空格"""before = len(self.df)for col in self.df.select_dtypes("object").columns:self.df[col] = self.df[col].str.strip()self.log.append(f"去除空格完成")return selfdef remove_empty_rows(self):"""删除完全为空的行"""before = len(self.df)self.df.dropna(how="all", inplace=True)self.log.append(f"删除空行: {before - len(self.df)} 行")return selfdef deduplicate(self, subset=None):"""去重"""before = len(self.df)self.df.drop_duplicates(subset=subset, inplace=True)self.log.append(f"去重: 删除 {before - len(self.df)} 行重复")return selfdef normalize_phone(self, col="电话"):"""统一手机号格式：去除非数字字符"""if col in self.df.columns:self.df[col] = self.df[col].astype(str).str.replace(r'\D', '', regex=True)self.log.append("手机号格式统一完成")return selfdef standardize_dates(self, col="日期"):"""统一日期格式"""if col in self.df.columns:self.df[col] = pd.to_datetime(self.df[col], errors="coerce")self.log.append("日期格式统一完成")return selfdef generate_report(self):"""生成数据质量报告"""report = {"总行数": len(self.df),"总列数": len(self.df.columns),"空值统计": self.df.isnull().sum().to_dict(),"各列数据类型": self.df.dtypes.astype(str).to_dict(),"操作日志": self.log}return reportdef save(self, filename=None):"""保存清洗结果"""if filename is None:timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")filename = f"cleaned_{timestamp}.xlsx"output_path = self.output_dir / filenameself.df.to_excel(output_path, index=False)# 同时保存数据质量报告report = self.generate_report()report_path = self.output_dir / f"report_{timestamp}.txt"with open(report_path, "w", encoding="utf-8") as f:for key, value in report.items():f.write(f"{key}: {value}\n")self.log.append(f"结果已保存: {output_path}")print(f"✅ 清洗完成: {output_path}")return self# ─── 使用示例：一行链式调用搞定全部清洗 ───if __name__ == "__main__":DataCleanPipeline("dirty_sales_data.csv") \.strip_whitespace() \.remove_empty_rows() \.deduplicate(subset=["电话"]) \.normalize_phone() \.standardize_dates() \.save()

最后说几句

数据整理这件事，工具不是最重要的，整理流程的标准化才是。拿到一份脏数据，第一反应不是打开Excel开始手工改，而是先观察数据结构、记录所有不规范的格式、设计清洗步骤、然后选工具执行。如果同样的数据源要反复处理，把清洗步骤写成脚本或Power Query模板，下次一键跑通。

工具选择上有个简单的判断逻辑：数据在Excel能打开且不卡→用Power Query录制清洗步骤；数据太大Excel打不开→上Pandas；数据是JSON→jq或json_normalize；数据是非结构化文本→grep+sed+awk组合；偶尔用一次不想装软件→在线工具（敏感数据除外）。