做数据分析的谁还没被“一列数据里塞了姓名、电话、城市”这种烂摊子恶心过。平时工作里只要用到Excel十有八九碰到需要数据分列一列拆成多列的需求把地址拆成省份城市、把日期拆成年月日、把标签拆成一个个关键词。这个操作在Excel里有个按钮能点但遇到几十个文件、上百万行数据、格式还不统一的时候手动点“分列”简直是在浪费生命。所以我才写了这篇Python处理Excel数据分列的内容把从读取、拆分、清洗到写回的全流程讲明白保证你看完能直接抄作业。这篇文章适合谁一类是天天跟Excel打交道、被重复分列折腾到怀疑人生的办公族另一类是刚学Python、想用pandas处理真实数据的新手。我会把原理、代码、坑都放在一起讲就算你只懂一点Python基础语法跟着操作也能跑通整个流程。1. 先想清楚为什么不直接点Excel自带的“分列”按钮1.1 这个需求的真实样子我们先还原一下场景。某个业务部门发来一张表里面“客户信息”这列长这样杭州-王小明-13800138000-已成交领导要求你把城市、姓名、电话、成交状态分成四列。这种需求太常见了甚至每天都在发生。Excel里确实自带“分列”功能入口在“数据 → 分列”提供按分隔符和固定宽度两种方式处理一次性的小表完全没问题。但问题在于真实工作里的分列需求往往是今天拆A表明天拆B表后天还要把上个月的表重新拆一遍。你每次都要重复点开向导选分隔符点完成再把表头重新整理一遍。如果原始数据稍微变个格式比如有人用了全角逗号有人用了半角竖线分列结果就会乱七八糟。关键是不可重复、不可追溯哪天领导问你这列数据是怎么拆出来的你什么都说不出来。Python的方案解决的就是这个痛点写一次脚本以后任何同类表格丢进来双击运行就能得到格式统一的结果。而且pandas处理几十万行数据毫无压力还能在拆列的同时做清洗、补空值、校验格式这是Excel按钮做不到的。1.2 三种方案的取舍别一上来就选最重的有些朋友可能会说Excel里还有Power Query也能做拆分而且界面化刷新就能重复执行。这个说法没错我自己也会用Power Query做简单的分列它的“拆分列 → 按分隔符”比传统向导好用得多。但Power Query对正则表达式的支持很弱遇到需要按模式提取比如从一段文本里抽出11位手机号就非常吃力。我平时选型的逻辑是这样的场景推荐方案原因一次性的小表几百行格式规整Excel自带分列或Power Query上手快点几下就好表会经常更新需要重复处理Power Query刷新即重跑不写代码数据量大格式乱需要清洗Python pandas正则、批量、可控需要接入定时任务或自动流程Python pandas脚本可以无缝对接所以如果你只想解决眼前一次拆分用Excel没毛病但如果你想根治“隔三差五就要分一次列”的问题用Python是值得投入时间的。而且Python这条路学会了后面做数据清洗、报表自动化的回报率非常高。2. 准备工作环境、依赖和表格读取2.1 装对库pandas openpyxl开始写代码之前先把环境准备好。用Python读Excel最核心的库是pandas它负责数据结构和各种拆分操作另外还需要一个操作Excel文件的底层引擎。现在主流的.xlsx文件用openpyxl老版本的.xls文件用xlrd。安装命令很简单pip install pandas openpyxl如果你要处理的是老版.xls文件再加一个xlrdpip install xlrd注意xlrd 2.0之后的版本只支持.xls不支持.xlsx所以别指望用xlrd读新格式。而且别用openpyxl去读.xls它读不了。现在多数公司都用.xlsx所以pandas加openpyxl这个组合基本够用。2.2 读取Excel的几个关键参数很多人一上来就写pd.read_excel(文件.xlsx)也不管有没有报错读到一堆奇怪数据类型就开拆。这里我建议先养成一个习惯读取的时候就把参数设置好。import pandas as pd df pd.read_excel( 客户信息表.xlsx, sheet_nameSheet1, dtype{客户编号: str, 原始信息: str}, # 关键以字符串读入 engineopenpyxl )重点说下dtype。Excel里的手机号、订单号经常被识别成数字读进来就会变成1.38001e10拆分后你得到的是“13800138000.0”这种鬼东西。把需要拆分的列显式指定为str能避免掉一大半后续清洗的麻烦。另外sheet_name可以传数字0、表名、甚至表名列表一次读多个Sheet在批量处理时很好用。2.3 动手前先花十秒钟看一眼数据拿到数据之后别急着拆列。我见过太多人直接对着一列数据写split结果拆出来了发现这一列里有空值、有特殊符号、有跟别人格式完全不一样的记录。先看一眼再动手能省很多返工时间。print(df.shape) # 行列数 print(df.head(10)) # 前10行肉眼看格式 print(df[原始信息].dtype) # 确认是不是字符串 print(df[原始信息].isna().sum()) # 看有没有空值这些小检查看起来啰嗦但在真实数据处理里非常关键。数据量大到几万行的时候你不可能逐行去看靠head()和info()快速摸底是性价比最高的方式。分列的本质是对字符串做切分如果某一行的值是NaNPython没法对NaN做字符串操作后面就会有各种预想不到的结果。所以这个步骤也是为后面清洗做铺垫。3. 一列拆多列的核心写法五种场景全覆盖这一节是整个内容的重头戏。我按实际工作中最常见的五类情况把pandas里拆分列的方法讲透。你不用全背下来用到的时候知道去哪找就行但建议先通读一遍遇到对应场景能想起来有这么个写法。3.1 按固定分隔符分列最简单的split假设“原始信息”列长这样原始信息杭州-王小明-13800138000-已成交上海-李丽-13900139000-未成交需求是拆成城市、姓名、电话、成交状态四列。pandas里最直接的方法就是str.split注意一定要加expandTruesplit_df df[原始信息].str.split(-, expandTrue) split_df.columns [城市, 姓名, 电话, 状态]expandTrue的意思是把切分结果展开成DataFrame每一段变成一列。如果不加这个参数返回的是Series每个元素一个列表还得自己想办法转成多列非常别扭。所以我的习惯是只要是想拆成多列就一定带上expandTrue。这里还有一个隐藏参数n值得讲。如果你的字符串中有多个分隔符但只拆第一个比如2024-05-01-促销活动你只想要日期和后面的事件描述就可以写split_df df[活动时间].str.split(-, n1, expandTrue)n1表示只按第一个横杠切一刀后面再出现横杠不会被切。这样列表长度就是2适合切分时不想破坏后面内容的情况。3.2 遇到不定长空白或多种分隔符真实表格比上面这个例子要脏得多。我遇到过用Tab键对齐的数据也遇到过中文逗号、英文逗号、竖线混用的表格。比如某列数据是北京 朝阳区,望京SOHO | 140平米里面有连续空格、有中文逗号、有竖线。如果只用单分隔符str.split(,)空格和竖线后面夹着的分割就没法处理干净。正确的姿势是结合正则表达式用str.split加上正则模式split_df df[房源信息].str.split(r[,\s|、], expandTrue)这里[,\s|、]表示匹配逗号、空白字符、竖线、中文逗号、顿号中的任意组合加号是让连续字符匹配成一整个分隔符。用这个写法上面的字符串会被拆成北京、朝阳区、望京SOHO、140平米四段干净利落。在使用正则的时候我建议先在小样本上测试一下确认切分结果符合预期再应用到全量数据。因为正则模式写错的情况下最容易出现的现象是“没报错但拆出来的列数不对”或者“第一列前面多了个空串”。这种不报错的结果错误是最坑的。3.3 按固定宽度拆适合日期、编码拆字段有些数据不是用分隔符隔开的而是固定宽度。典型例子是日期字符串20240115你要拆成2024、01、15或者订单编号HW20240501001要拆出前面的机构编码、中间的日期、后面的序列号。固定宽度拆分在pandas里有两种做法。第一种是用字符串切片简单粗暴但很直接df[年份] df[日期].str[:4] df[月份] df[日期].str[4:6] df[日] df[日期].str[6:8]第二种是正则提取用分组的方式把各段取出来date_df df[日期].str.extract(r(\d{4})(\d{2})(\d{2})) date_df.columns [年份, 月份, 日]我自己更喜欢正则有名字的分组因为可读性强尤其是字段一多看着代码就知道每段是什么。比如code_df df[订单编号].str.extract(r(?P机构\w{2})(?P日期\d{8})(?P序号\d{3}))(?P名字...)是命名分组生成的新列会直接用这个名字当列名。这招在拆复杂文本时非常好用后面单独讲。3.4 用正则提取处理不规则文本正则提取是处理“脏数据”分列的大杀器。前面的方法都要求数据有比较规整的分隔符或宽度但真实数据往往是一整段描述文本夹杂着各种信息。比如原始信息是王小明联系电话13800138000城市杭州状态已成交信息都在但顺序和格式没有固定规律。这时候split不一定好用用str.extract配合正则模式去抓每一段最靠谱extract_df df[原始信息].str.extract( r(?P姓名[\u4e00-\u9fa5]{2,4}) r.*?(?P电话1\d{10}) r.*?(?P城市(?:北京|上海|杭州|广州)) r.*?(?P状态成交|未成交) )这个模式看起来复杂拆开看就很好懂[\u4e00-\u9fa5]{2,4}匹配2到4个汉字理解为常见中文姓名1\d{10}匹配以1开头的11位手机号。用.*?做非贪婪匹配跳过中间无关内容。各段用命名分组包住提取结果会自动生成对应的列名。正则的好处是能容忍格式一定程度的混乱只要关键信息存在就能抓出来。坏处是模式写不好会漏数据而且排查起来比普通代码费劲。所以用正则处理的时候提取完一定要统计一下提取失败的行数看有没有信息被漏掉。3.5 把一列标签拆成多行用explode还有一种“分列”跟前面都不一样。有时候我们遇到的是标签类数据比如一列里存了多个标签用逗号分隔但你需要的不是分成多列而是分成多行让每条记录都能单独统计。比如订单号标签A001手机,数码,优惠你想拆成三行每行一个标签。这就要用到str.split加explode组合df_exploded df.assign(标签df[标签].str.split(,)).explode(标签)原理是先用split把字符串变成列表再用explode把列表展开成多行同时其他列的值会自动复制。这个操作在打标签、做词频统计时特别实用算是分列需求的横向变体。比如电商运营经常要把“标签”那列拆开做类目汇总一行变多行后用value_counts直接统计各个标签数量一条龙很舒服。4. 完整案例把“客户信息表”一列拆成四列并写回Excel4.1 数据结构和目标为了让你能直接照着练我设计一个完整的实战案例。假设你手上有一张客户信息表.xlsxSheet1有两列客户编号原始信息C001杭州-王小明-13800138000-已成交C002上海-李丽-13900139000-未成交C003广州-赵强-13700137000-已成交目标很简单拆成五列——客户编号、城市、姓名、电话、状态并生成一个新的Excel文件。这个案例看起来简单但我会尽量把真实处理中的细节都放进去空值、多余空格、写回Excel的注意事项等。4.2 分步实现与代码注释直接上完整代码每一步我都写了注释建议你跑一遍之后再根据自己的数据调整。import pandas as pd # 1. 读取数据指定关键列以字符串读入 df pd.read_excel( 客户信息表.xlsx, sheet_nameSheet1, dtype{客户编号: str, 原始信息: str}, engineopenpyxl ) # 2. 对原始信息列去除首尾空格避免拆出来带空白 df[原始信息] df[原始信息].str.strip() # 3. 按横杠拆分expandTrue 展开成多列 split_df df[原始信息].str.split(-, expandTrue) # 4. 给拆分后的列命名 split_df.columns [城市, 姓名, 电话, 状态] # 5. 与原表合并保留客户编号 result pd.concat([df[客户编号], split_df], axis1) # 6. 结果写入新Excel去掉索引列 result.to_excel(客户信息表_分列后.xlsx, indexFalse, engineopenpyxl) print(result.head())这段代码跑完生成的Excel就会变成规整的五行五列。“indexFalse”是关键点不写的话Excel里会多出一列0、1、2的数字索引看着特别不专业别人拿到表还得手动删这个坑我早期踩过好几次。4.3 结果校验与常见变体代码跑完别急着交差我一般会做两步校验第一步看拆分前后行数是否一致确认没有因为某行拆不动而丢数据第二步抽样看几行拆分结果核对各列内容是否对得上。如果原始数据存在分隔符数量不一致的情况比如有的行是四段有的行是五段直接expandTrue会导致列数不齐多出来的数据散落到其他行下面变成乱序。这种情况我常用的办法是先拆成列表再采用自定义函数补齐长度。举个简单思路def safe_split(s, sep-, n4): parts str(s).split(sep) parts [] * (n - len(parts)) # 缺失位置补空字符串 return pd.Series(parts[:n], index[城市, 姓名, 电话, 状态]) split_df df[原始信息].apply(safe_split) result pd.concat([df[客户编号], split_df], axis1)这样不管每行拆出几段最后都会对齐成四列。缺的字段补空字符串多的字段截断掉也可以再加逻辑把多余字段拼进备注列。真实场景里这个变体用得非常频繁毕竟数据格式不可能永远像测试数据一样规矩。4.4 批量处理多个Excel文件分列的最终形态往往是批量处理。我处理的报表经常是12个月每月一张表结构一模一样。如果每个月都手动复制粘贴、重新拆列那写脚本的意义就减半了。批量处理的思路很简单用glob.glob拿到所有文件名循环处理然后按月份或原文件名重命名输出。这是我最常用的写法之一import glob for path in glob.glob(报表/*.xlsx): df pd.read_excel(path, dtype{原始信息: str}, engineopenpyxl) split_df df[原始信息].str.split(-, expandTrue) split_df.columns [城市, 姓名, 电话, 状态] result pd.concat([df[客户编号], split_df], axis1) output_path path.replace(.xlsx, _分列后.xlsx) result.to_excel(output_path, indexFalse, engineopenpyxl)注意这里文件路径不要用中文和空格混合的奇怪路径Windows下容易出现编码问题。另外如果源文件里有多余的Sheet读取时候最好指定sheet_name避免读到汇总页或说明页。我见过因为没指定Sheet导致程序报错的情况排查了半天才发现读的是第二个Sheet。5. 常见问题与排查技巧实录5.1 报错与异常速查表平时在技术社区里被问得最多的问题我整理成了一张速查表基本覆盖了分列场景里出现频率最高的报错和异常情况。报错或异常原因解决办法AttributeError: float object has no attribute split列里存在NaN或数字没有按字符串读入读取时指定dtypestr或者先fillna()再拆分拆出来列数不对分隔符数量各不一致或部分行缺失字段先探查split().str.len()分布或用safe_split方式补列拆完列名是0、1、2忘了给拆分结果设置列名手动指定columns或者用命名正则一次性生成表头输出Excel多出一列编号写文件时没加indexFalse写入时加indexFalse手机号显示成1.38001e10Excel自动把长数字转成科学计数法读取时保留原字符串写回时用dtypestr或把列设为文本格式ValueError: Sheet named Sheet1 not found表名不叫Sheet1用pd.ExcelFile先列出所有Sheet名确认这些坑我基本都踩过一遍。尤其是第一个AttributeError后来的解决方案大多是用字符串读入并及时填充空值就从根上避免了这个麻烦。5.2 容易踩的隐性坑不报错但结果错程序不报错不等于结果正确这是数据清洗工作里最危险的地方。以下几个坑是我真实遇到的明明代码正常运行输出却完全不能用。最典型的一个是“列错位”。如果某一行的原始信息是上海-王芳-未成交少了个电话直接split加expandTrue结果会变成“上海”、“王芳”、“未成交”三列后面的状态信息被挤到了电话列。这种错位不检查根本看不出来。所以拆完一定要做字段内容校验比如判断电话列是不是1\d{10}格式城市列是不是在已知城市名单里。这也是为什么我强烈建议拆分后不要直接用先抽样验证。另一个坑是比较隐蔽的“不可见字符”。从别的系统导出的Excel经常在字符串里混入换行符、制表符甚至全角空格。表面看是“上海-王小明”实际可能是“上海 -\n王小明”。这种情况下用-切分某列会带着换行符打印出来不明显写到Excel里就会把单元格撑开。我现在的习惯是拆分前统一做一次str.replace(r\s, , regexTrue)清理空白字符或者显式把所有换行符、制表符清掉。还有一个跟Excel显示逻辑有关的坑即使你用pandas读的时候指定了dtypestr把拆分结果写到新Excel后如果这一列是纯数字的文本Excel打开时还是可能自动变成科学计数法。解决方法是写回前给列加上不可见前缀或者用openpyxl写成文本格式单元格。如果只是交差用的报表我会在拆分结果里对电话列做一次astype(str).str.replace(r\.0$, )先清理掉可能存在的科学计数法尾巴再写Excel。6. 从“会用”到“用得顺”的经验心得讲完代码和坑最后分享一点我在实际工作中的经验。我的习惯是任何一个分列操作第一次用新数据跑完一定会生成一个类似结果检查_说明.txt的校验文件记录原始行数、拆分后行数、每列非空数量、电话格式异常数量等。这样过两个月再跑这批数据对比一下检查文件就能快速发现数据源哪里变了。哪怕不做自动化检查这种记录也能帮你建立对数据的敏感度。再一个建议是把常用的分列逻辑封装成函数参数只保留文件路径、拆分列名、分隔符和目标列名。比如def split_column(df, column, sep, new_cols): split_df df[column].str.split(sep, expandTrue) split_df.columns new_cols return pd.concat([df.drop(columns[column]), split_df], axis1)别看这个函数只有几行实际项目里特别顶用。不同报表之间总是有一些细微差别但核心拆分逻辑是高度重复的封装成函数以后新增一张表就只要一行调用。时间久了这个函数会变成你自己的脚本库效率比每次打开Excel手动分列不知道高到哪里去了。最后再分享一个小技巧如果某个分列场景涉及到特别复杂的规则比如不同分隔符、不同字段顺序、部分信息可能缺失先在Excel里手动找三五条典型数据把实际拆分后的目标写出来然后再调代码。我很少直接闭着眼睛对着十万行写正则因为你不确定真实数据长什么样。拿几条样本先把规则磨透再放到全量数据上跑这是全流程最稳的做法。分列这件事听起来很小但做扎实了算是Excel数据清洗里性价比最高的一项技能。