电信数据清洗实战：从脏数据到可信指标的完整方案

做电信数据的人都知道跑数之前最怕的不是SQL写得差而是表里的数据脏到没法用。3.2这一节讲的是电信数据清洗说白了就是教你怎么把这些又脏又乱的数据收拾到能算、敢算、算得准的状态。我这些年经手过的电信数据集从小到几万条的信令样本到大到几亿条的通话详单几乎没有哪一批是能直接拿去跑指标的多多少少都得洗一遍。这篇文章就把我在实际项目中沉淀下来的清洗思路、操作步骤和踩过的坑一次性讲清楚。1. 先搞清楚电信数据的“脏”在哪里1.1 电信数据的源头与格式差异电信行业的数据不是来自一张表而是来自一堆系统每个系统的脾气都不一样。业务支撑系统BSS里存的是用户资料、套餐信息、账单记录这部分数据相对规范但字段更新不及时是常事运营支撑系统OSS里是网络资源、工单记录文本字段多口径五花八门网络管理系统NMS产生的是性能指标和告警日志时间戳格式经常不统一信令监测系统的数据最复杂字段多、量大而且经常有截断和乱码。这些系统之间的数据要关联起来用麻烦就来了。同一个用户ID在不同系统里可能一个是字符串、一个是数字同一个手机号码在BSS里带国家码、在信令里不带同一个基站的编号在OSS和NMS里用的是完全不同的编码规则。更要命的是时间格式有的用yyyyMMddHHmmss有的用Unix时间戳有的干脆就是一个文本。所以做电信数据清洗的第一步不是急着写去重脚本而是先摸清楚你要处理的这批数据是从哪几个系统汇过来的每个系统各自是什么格式、什么粒度。我遇到过最典型的一种情况是两个系统对“通话时长”的定义都不一样一个按秒计、一个按分钟计而且分钟那个还做了四舍五入。如果你不加处理直接把两张表join起来算平均通话时长算出来的结果偏差会大到让你怀疑人生。这种源头上的口径差异不是靠清洗脚本能完全兜住的但清洗阶段至少要做到“识别差异、统一口径、做好标记”否则后面所有统计都是白做。1.2 常见的数据质量缺陷类型把电信数据的脏病归纳起来基本逃不出下面这几类。第一是缺失值用户资料表里身份证号缺失、通话详单里IMSI国际移动用户识别码为空都是家常便饭。第二是重复记录同一个通话在一次采集任务里被上报了两次或者由于系统重传机制导致详单里有完全一样的行。第三是格式不一致手机号有的带86前缀、有的不带套餐代码在A系统里是数字、在B系统里变成了带字母的编码。第四是逻辑矛盾比如通话开始时间晚于结束时间或者上网流量超过套餐上限几个数量级。第五是异常值比如基站工参里的经纬度飘到了海里或者信令里出现一个根本不存在的IMEI国际移动设备识别码。有意思的是这五类问题在电信数据里出现的频率并不均匀。按我的经验格式不一致和逻辑矛盾是出现率最高的两类缺失和重复反而相对可控。为什么因为电信系统的采集链路长中间经过计数器、汇总、传输、落地好几道工序每道工序都可能对数据做一次“改编”改到最后字段就变得面目全非。比如同一个话单在采集端会打上采集时间戳在计费端又会打上计费时间戳两边的时区还不一样这类问题靠肉眼根本发现不了。所以电信数据清洗本质上是一场“数据审计”你要逐字段去核对这些数据是谁产生的、在哪个环节被改过、当前值是否在合理范围内。这要比单纯写几个正则表达式去替换字符串复杂得多但也正是这份复杂构成了电信数据清洗工作的核心价值。2. 数据探查动手清洗前的第一道工序2.1 先定义“干净”的标准再动手很多新手拿到数据就开始写清洗脚本结果洗到一半发现漏了某种脏数据又回头改脚本来回折腾。正确的做法恰恰相反动手清洗之前先定义清楚“干净”到底是什么意思。在电信数据这个语境下我一般把清洗目标拆成五个维度完整性、唯一性、准确性、一致性、及时性。完整性的意思是关键字段不能为空唯一性是同一业务事实不能被重复记录准确性是字段值在合理范围内一致性是同一实体在不同表里的口径一致及时性是数据的业务时间在有效窗口内。这五个维度不是拍脑袋定的每一个都对应着后续业务计算的具体风险。比如完整性影响的是用户基数的统计唯一性影响的是通话量的汇总准确性影响的是资费和流量的计算一致性影响的是跨系统关联的join结果及时性影响的是日报、周报的指标是否可信。把“干净”拆成这五条之后清洗就不再做无头苍蝇因为每一步操作都能回答“我到底在为哪个维度服务”这个问题。实践中我会把五个维度的验收规则写成一张检查表每条规则都配一个SQL或Python的校验逻辑。比如完整性检查就写成“核心字段的null值占比不超过1%”唯一性检查就写成“按业务主键去重后的记录数与去重前相差不超过0.5%”。有了这样的规则清洗工作才算得上可验收、可复盘而不是一句“我洗过了”就交差。再强调一点你定义的“干净”标准一定要跟业务方确认过。我曾经在处理某省运营商数据集市的数据时按我对“一个用户一条主档记录”的理解去清洗结果把业务方故意保留的历史归档数据也删了差点导致一个遗留用户无法计费。从那以后我养成了一个习惯每定义一个清洗规则都要标注它的业务动机并且找业务方白纸黑字确认一遍。2.2 探查工具与方法探查阶段的目标是量化问题而不是解决问题。你要回答的问题只有三个数据里有哪些脏数据占比是多少都集中在哪些字段常用的工具无非是SQL、Python和Spark。如果数据量在几百万级用Pandas就够如果到了亿级就得用Spark或者基于SQL的探查方式。我用得最多的探查方式是这样对每张表的每个字段拉出五项统计指标——非空率、唯一值数量、值域范围、格式非法条数、TopN分布情况。这五项指标一出来哪里脏基本一目了然。比如一个手机号字段的非空率只有90%那这个字段就有排查的必要一个套餐字段的唯一值数量有三百多个但业务上套餐总共才十几种那基本可以断定存在编码不一致的问题一个信令字段的TopN分布里某个值占了99%那这个字段很可能已经退化成常量了。探查脚本写完之后我会把结果整理成一张数据质量探查报告每一行是一个字段每一列是一类问题指标。这张报告不只是给自己看的也是后来跟业务方沟通清洗方案的依据。我习惯把探查报告命名为“数据体检单”因为它的作用真的就跟体检单一样告诉你各项指标是否正常、哪些需要复查、哪些必须马上干预。这里有一个特别容易被忽略的细节探查数据时要区分“存量数据”和“增量数据”。某省运营商每个月月底会集中做一次历史数据迁移新老系统数据格式不兼容导致月底那几天的数据质量会明显恶化。如果你只用平日里质量不错的数据做探查很容易漏掉这类周期性脏数据。我后来的做法是探查时专门留一个窗口期把月初、月中、月末的数据各抽一批来做对比这样才不会被好数据蒙混过关。3. 清洗规则设计与核心处理环节3.1 缺失值的处理策略电信数据里的缺失值处理策略不是一刀切的。我一般把字段分成三类。第一类是主键和关联键比如用户ID、手机号、IMSI、IMEI这些字段一旦缺失记录就失去了关联价值最简单的办法是直接删除记录或者退回给源系统补采。第二类是核心业务字段比如通话时长、流量、费用这些字段缺失会直接影响指标计算处理策略是优先用关联表回填回填不了的用同用户历史均值填充。第三类是描述性字段比如用户性别、年龄、职业这类字段缺失对核心指标影响不大可以用默认值或者众数填充。具体操作上要特别注意填充缺失值不等于造假每一步填充都要记录。我会在清洗后的数据集里保留一个填充标记字段表示这个值原本是空的被我用什么策略填成了什么。比如用户年龄缺失、我用该地区用户平均年龄填了那就在标记字段里写“avg_age_imputed”。这样做的好处是后续建模或者统计时数据分析师可以自行决定这些填充值要不要参与计算。我踩过一个很深的坑处理某省用户上网日志时把流量字段的缺失值全部用0填充了。结果当月该省人均流量的统计指标直接被拉低了将近20%。原因很简单上网日志里流量为空不代表用户没上网而是采集链路出了故障用0填充相当于把“采集失败”偷换成了“没有流量”。后来我改成了用该用户前7天的平均流量做填充同时给这些记录打上标记统计时额外看一眼填充比例数据才恢复了可信度。3.2 重复记录的识别与去重去重看起来简单实际上是最容易出问题的一个环节。关键在于“重复”的定义不同业务场景下完全不一样。比如通话详单CDR的去重一般用“主叫号码 被叫号码 通话开始时间 通话时长”作为业务主键这四者一致才判定为重复。但如果你只看主叫号码和通话开始时间很可能把同一用户在同一秒发起的两个不同呼叫误判成重复。所以去重第一步就是跟业务方确认到底哪些字段组合能唯一标识一条业务事实。确定好业务主键之后再细分去重策略。如果只是完全重复直接drop_duplicates就行棘手的是“半重复”——比如同一条话单一条记录的通话时长是120秒另一条是121秒其他字段都一样。这种多半是网络时延或者四舍五入导致的需要按置信度保留特定来源的记录比如优先保留计费系统的记录把网管系统的记录标记为疑似重复丢掉。在处理某跨平台系统的信令数据时我还遇到过更复杂的情况同一个位置更新事件在MSC移动交换中心和SGSN服务GPRS支持节点两个系统里各记录了一条时间戳相差不到1秒但其他字段的编码方式完全不同。这种跨系统重复靠字段值比对是查不出来的只能靠业务语义去判断。当时我们的处理方式是给每条记录生成一个“业务指纹”将关键字段做归一化后拼在一起取哈希值再用哈希值去重。这个方案效果不错成了后面处理多源重复数据的标准做法。3.3 业务规则校验与异常值清洗缺失和重复处理完之后剩下的硬骨头是逻辑校验和异常值。电信数据的业务规则多且细比如通话开始时间必须早于结束时间、通话时长必须大于等于零、IMSI必须是15位数字、IMEI必须是15位且满足Luhn校验、手机号码段必须存在于运营商已分配的号码段列表中。这些规则每一条都对应一种或多种脏数据形态我建议把它们整理成一张规则清单逐条写校验逻辑。规则校验里最难的是“区间不合理的值”。比如某用户的月累计流量是1.2TB这在个人用户层面几乎不可能但在基站工参里又可能存在因为它们承载的是海量用户的总流量。所以同一个校验规则不能全表统一套用要按数据粒度区分。我在实操中会把数据先按主键分组在每个组内做Z-Score或者四分位距异常检测把明显偏离组内分布的值挑出来。比如以用户ID分组后计算每月的流量某个月突然是其他月份平均值的50倍这基本可以判定为异常。异常值不一定要删除更多时候要先定位原因。我处理过一批信令数据发现某些用户的位置区编号LAC总是不变但基站编号却在天上飞。排查下来是采集程序对LAC字段做了硬编码导致上报数据不真实。这种情况下清洗能做的是把这些字段标记为“不可信”而不是强行修正一个错误的值。清洗只是数据链路中的一环不能替源系统背锅发现问题后及时反馈给采集方修复才是正解。4. 实操过程一个完整的电信数据清洗流水线4.1 场景设定与数据准备为了把这套流程说明白我构造一个模拟项目X来演示。假设某跨平台系统采集了某地市一周的通话详单数据总计350万条每天约50万条。字段包括通话ID、主叫号码、被叫号码、通话开始时间、通话时长、通话类型、IMEI、IMSI、位置区编号LAC、小区编号CELL_ID、套餐编码、流量使用量。我们往这批数据里人工注入四类典型脏数据5%的主叫号码为空、2%的完全重复记录、3%的通话时长为负值、以及少数时区格式不一致的时间字段。第一步先做数据探查跑一遍统计把每张表的字段级健康状况拉出来。我习惯用Python的pandas-profiling概要报告加自定义的字段级统计脚本一起做前者看整体分布后者看具体的脏数据形态。这一步的目标是确认哪些字段需要清洗、需要按什么规则清洗、清洗后的预期影响是什么。第二步是确定清洗流程的顺序。我的原则是先去掉完全不可信的数据再统一格式再处理重复再填充缺失最后做业务规则校验。顺序不能乱比如如果你先填充缺失再统一格式等于用旧的格式逻辑去填充新格式的数据后面还得返工。定好顺序之后第三件事才是写清洗脚本。4.2 核心清洗代码与执行下面是这个场景里我用到的关键清洗代码片段。我会按第2节定义的“业务主键”先做去重再用统一逻辑处理号码格式和时间字段最后做业务规则校验和异常值标注。import pandas as pd import numpy as np from datetime import datetime # 读取原始数据 df pd.read_csv(call_detail_records.csv, dtype{主叫号码: str, 被叫号码: str}) # 1. 去除重复以主叫被叫开始时间时长为业务主键 dedup_cols [主叫号码, 被叫号码, 通话开始时间, 通话时长] df[业务去重键] df[dedup_cols].apply(lambda x: |.join(str(v) for v in x), axis1) df df.drop_duplicates(subset[业务去重键], keepfirst).drop(columns[业务去重键]) # 2. 手机号格式化去掉86前缀统一为11位 def format_mobile(num): if pd.isna(num): return num num str(num).strip() if num.startswith(86) and len(num) 13: num num[2:] if len(num) ! 11 or not num.isdigit(): return np.nan return num df[主叫号码] df[主叫号码].apply(format_mobile) df[被叫号码] df[被叫号码].apply(format_mobile) # 3. 时间字段统一把多种时间格式都转成标准datetime df[通话开始时间_std] pd.to_datetime(df[通话开始时间], formatmixed, errorscoerce) # 4. 缺失号码记录直接剔除 df df.dropna(subset[主叫号码, 被叫号码]) # 5. 通话时长负数按异常值处理拷贝到异常标记字段 df.loc[df[通话时长] 0, 异常标记] 负数时长 df.loc[df[通话时长] 0, 通话时长] np.nan df[通话时长] df.groupby(主叫号码)[通话时长].transform( lambda x: x.fillna(x.median()) ) # 6. 业务规则校验开始时间必须在统计周期内 valid_range (datetime(2024, 6, 1), datetime(2024, 6, 7, 23, 59, 59)) df df[(df[通话开始时间_std] valid_range[0]) (df[通话开始时间_std] valid_range[1])] print(df.shape) print(df[异常标记].value_counts())每段代码我拆开讲一下。去重部分用的是“业务去重键”这个方法好处是明确可追溯你可以随时拿这个键去对账。号码格式化用了自定义函数而不是正则替换因为在号码上直接做字符串替换很容易误伤其他字段。时间字段用formatmixed可以自动识别多种格式但要注意pandas对时间格式的解析性能较差在亿级数据上建议换成Spark或者提前在源系统层面做统一。最后的业务规则校验我在这里把负通话时长的记录打上了“负数时长”标记再用组内中位数做了回填。这一步其实就是我在3.1里强调的“填充留痕”异常标记字段会跟随数据一起进入后续流程业务方如果发现某个群体指标偏低可以很方便地回溯到这些被修正过的记录。4.3 清洗效果验证清洗不是跑完脚本就算完必须验证“洗得对不对、洗得干不干净”。我的验证方法分三层。第一层是数量验证清洗前350万条清洗后剩多少条每一步删了多少、填充了多少要有记录。第二层是规则验证把清洗前定义的规则清单逐条跑一遍确认每条规则的违规率降到了目标阈值以下。第三层是抽样验证人工抽取清洗前后的数据各100条用肉眼核对一遍时间格式、号码格式和业务逻辑确认脚本没有把不该改的也改了。在模拟项目X里清洗后的数据规模是350万条变成约321万条删除了18万条重复记录和近11万条缺失记录。但要注意删除比例不是越小越好也不是越大越好关键在于删除的原因是否在业务上站得住脚。如果一批数据的删除率超过10%我建议停下来先做原因分析而不是继续往下洗因为大概率是源系统出了系统性故障你得反馈回去修复源头。我还习惯做一次“稳定性抽查”把清洗后的数据按天拆分对比每天的记录数、平均通话时长、平均流量这几个关键指标的波动趋势。如果某一天的指标明显偏离其他天说明清洗规则在那天可能误伤了一批数据。这个习惯帮我抓出过不止一次因为时区转换导致的日期错位问题强烈推荐你也试试。5. 常见问题与排查技巧实录5.1 高频问题速查表过程中遇到的问题千奇百怪但高频的也就那么几类我整理成了一张速查表。注意这张表不只是记录“怎么修”更重要的是记录“怎么排查”因为修只是表象根因往往在源系统。问题现象可能原因排查思路解决办法手机号全是11位但前三位不在号段列表携号转网或虚拟运营商号段未更新拉取最新号段表比对更新号段字典将未匹配号码标记待核查通话时长为0且通话类型为“主叫”采集系统未等到释放消息就结束了记录抽查原始码流或采集器日志对这类记录打标剔除不计入KPI计算同一用户ID关联出两个不同姓名用户换套餐时资料被新建按用户ID和时间戳查历史记录保留最新一条历史记录归档备份某些基站CELL_ID完全对不上新站入网后邻区表未同步更新对照OSS工参表字段更新日期同步工参表旧数据标记为存疑流量值比套餐上限高几个数量级采集计数器翻表导致数值累加错误查看计数器编码规则按计数器最大值做模运算修正这张表可以作为一个模板你在自己的项目里遇到新问题不用紧张就按照“现象—原因—排查—解决”四段式往里填。填得多了你就有了一本属于自己的故障字典排查速度会快很多。5.2 沉淀下来的几条经验第一条经验清洗脚本必须做到“可配置、可重跑、可对比”。所谓可配置是指你的清洗规则不能写死在代码里而是放在配置文件或参数表里这样业务规则变了不用改代码可重跑是指清洗脚本要幂等跑两次和跑一次结果一样可对比是指每次清洗后都要生成一份质量报告和上次做对比观察质量趋势。这三点缺一个清洗任务一多准乱套。第二条经验不要把清洗的结果一把梭直接覆盖原表。我总是会把原始表、清洗过程中的中间表、清洗后的结果表分开存储每一类表加上不同的前缀。原始表是重中之重宁可磁盘多占一点也别因为删了原始数据导致后面无法溯源。我见过不止一次因为清洗脚本出bug、把原始表也update坏了的案例那真是欲哭无泪。所以重要数据洗之前先做快照是我铁打的规矩。第三条经验清洗工作和业务方的确认节奏不能省。每一轮新规则上线前我都至少给业务方发一次“规则变更说明”包含规则名、生效范围、预期影响、回滚方案。与其事后被质疑不如事前对齐。有一次我们上线了一个去掉信令重复记录的规则结果导致某个重点区域的指标环比下降不少业务方差点以为是市场活动出了问题。好在清洗规则有配置和记录一查就能定位避免了误判。第四条经验尽量在数据接入阶段就做清洗而不是等数据进入数据仓库之后再洗。清洗关口越靠前脏数据对下游的影响就越小。如果数据采集端实在改不了也要在贴源层的入口做一道强校验把明显非法的数据先拦下来。现在很多团队在做实时数据管道时会在Kafka或者流处理任务里内嵌清洗规则道理是一样的清洗不要等事后补救尽量在源头就解决。再说说自动化。清洗规则的维护本身也是一份工作量大到容易被低估的事尤其是电信数据局方系统升级后字段可能说变就变。我建议每个清洗任务都要配一套“数据质量监控”的告警比如某一字段的非空率突然下降、某张表的重复率上升、某天的记录数较前7日均值差距超过30%等等出现这些情况就要第一时间通知人工介入。清洗是个持续动作不是一次性工程别指望跑一次脚本就一劳永逸。结语这条路上的一些心里话数据清洗在很多人眼里是脏活累活但在我眼里它是数据工作的地基。电信数据尤其如此它量大、链路长、来源杂业务方又急着拿数据出指标夹在中间的你既要保证速度又要保证准度。做了这么多年我和这句话最有共鸣数据清洗不是为了直接在表里删除、填补、改动那些值而是为了在这些值的背后重建一份可被信任的事实。每一张干净的表后面都有一个或一群数据工程师在默默收拾那些系统留下的烂摊子这份工作值得被看见。最后再分享一个小习惯每次洗完一批数据我都会在表注释里写下这批数据的时间范围、清洗规则版本、主要问题记录和操作人。半年后如果有业务方问我“这张表的这个字段当时为什么这么处理”我能翻出当时的记录说清楚来龙去脉。数据会变规则会变但记录下来的处理逻辑不会骗人。这个习惯救了我很多次也推荐你不妨试试。