1. 先想清楚你的数据到底要怎么切上周帮我同事处理一份运营数据原始文件是一个Excel表43万行里面记录了半年内所有门店的销售明细。她要把这个表按门店城市拆成14个文件再分别发给对应城市的分公司负责人。一开始她准备用Excel筛选加复制粘贴忙活了一上午才拆出三个城市中途Excel还卡死了一次。我接手后用Python写了个约80行的脚本把拆分规则放在一张Excel配置文件里半小时调通后跑完整个任务只用了30秒。这就是今天想聊的事用Python对Excel数据集做半自动化切分。如果你平时也在跟几十万行的大表打交道或者经常需要按业务维度把数据切成多个小文件分发给其他同事这篇文章会帮你省下大量时间。不需要你有多深的编程基础只要会写基本的pandas就可以。我会把我踩过的坑、思考过程、以及可以直接抄的脚本都放出来。1.1 四种最常见的切分需求在动手写代码之前一定要先想清楚你的“切分”到底属于哪一种。我做过不少类似任务后发现绝大多数需求都跑不出下面这四类切分类型切分依据典型产出按列值分组根据某一列或某几列的值按城市拆文件、按月份拆表按固定行数批量切只看行数不看内容把50万行拆成10个5万行文件按比例或数量抽样随机取一部分数据训练集、测试集或者抽检样本按自定义键组合多列拼接成唯一键按“城市门店”拆分层级文件多数人第一反应是“按列值分组”因为业务上最常见。但也不要忽略第二种场景Excel单个Sheet最多放1048576行超过了只能切开存。第三种场景在做算法模型训练时特别重要比如把数据清洗好之后拆成70%训练集和30%测试集还要保证分类标签比例一致。实际工作中这几种方式经常混在一起。比如先按城市分组再把每个城市的数据按时间范围切成更小的文件。所以不要把脚本写死成一种逻辑最好设计成“配置驱动”这也是我一直强调半自动化的原因。1.2 为什么是“半自动化”而不是“全自动”很多人一听到“自动化”就想着把逻辑完全写死在代码里以后跑一遍命令就全部搞定。但真实业务里的切分需求变化频率远超你的想象。上周可能是按城市拆这周就变成按渠道拆下周又要求把每个城市再按月份拆。如果每次需求变更都要去改Python代码那跟你手工复制粘贴也没什么本质区别。所以我说的“半自动化”指的是把“切分规则”外置到Excel配置文件里业务同事直接改配置表脚本本身不用动。有人会问那我直接用Power Query或VBA不也行吗当然可以但对比下来各有各的问题。方案优点痛点Excel手工筛选门槛低、直观超过几万行就卡容易漏行、重复行VBA宏与Excel深度绑定能操作单元格样式跨平台差、调试麻烦、处理大数据量时很慢Power Query有可视化界面适合简单拆分复杂规则写M函数也有学习成本批量迭代稍弱Python pandas处理几十万行很轻松逻辑可复用需要一点基础但上手难度比想象中低在数据量超过10万行、规则经常变的时候用Python做“配置驱动”的半自动化是性价比最高的方案。业务同事只需要改Excel里哪一列作为切分依据、文件名前缀是什么、输出到哪个文件夹剩下的事交给脚本。我的建议是别追求一次写一个全自动系统先把“人工改配置、代码跑批”这个半自动半人工流程跑顺比什么都强。2. 读Excel前的准备工作很多人写pandas处理Excel一上来就pd.read_excel()结果读出来一堆奇奇怪怪的问题学号变成了科学计数法、日期变成数字、某列全是NaN、程序还报内存不足。这些坑其实都能在读取阶段提前规避。2.1 安装依赖和版本坑处理Excel文件最常用的组合是pandas加上openpyxl引擎。安装命令很简单pip install pandas openpyxl如果你还需要处理老式的.xls文件就要注意一个特别坑的版本问题xlrd这个库在2.0版本之后不再支持.xlsx文件只支持.xls。而pandas读取.xlsx时默认调用的可能是openpyxl引擎。如果你已经装过xlrd也很容易混淆。我个人的建议是统一要求别人发.xlsx格式尽量避免操作.xls。如果确实遇到旧文件可以先在Excel里另存为.xlsx或者单独安装一个历史版本pip install xlrd1.2.0这样用pandas读.xls也不会报错。另一个容易被忽略的点是如果你只想读取Excel里的数据用openpyxl就够了不需要安装xlsxwriter。但在写入大量数据时xlsxwriter的性能通常比openpyxl更好如果想追求速度可以装pip install xlsxwriter然后在写文件时指定引擎df.to_excel(result.xlsx, enginexlsxwriter, indexFalse)实测下来在几十万行数据写入时xlsxwriter比openpyxl快不少。2.2 读数据前先避免三个坑第一个坑是数字类型变形。Excel里的门店ID、手机号、银行账号这类长数字默认会被pandas识别成int64或者float64写到新Excel里就会变成科学计数法或者超过15位精度直接丢失。我踩过一次客户要求保留完整卡号结果拆完的文件里卡号后几位全是0数据没法用。解决办法很简单读取时强制指定为字符串类型。可以指定全部列也可以只指定关键列import pandas as pd df pd.read_excel( 销售数据.xlsx, sheet_name明细, dtypestr, # 所有列先按文本读避免数字变形 ) print(df.head())如果担心所有列都按字符串读会导致后续数值计算不方便也可以只对敏感列做处理df pd.read_excel( 销售数据.xlsx, sheet_name明细, dtype{门店ID: str, 手机号: str}, )第二个坑是不管三七二十一把整个Excel里所有列都读进来。一个200MB的Excel里面有80列真正要用到的可能只有8列。全量读入不仅浪费内存处理起来也很慢。正确姿势是df pd.read_excel( 销售数据.xlsx, sheet_name明细, usecols[门店ID, 城市, 日期, 销售额], dtype{门店ID: str, 城市: str}, )usecols可以传列名列表也可以传Excel里的列位置。这个习惯一旦养成处理大文件时会舒服很多。第三个坑是表头。很多Excel文件并不规范前面可能有两行标题或者有小计行、合并单元格。读取之前最好先用一个小脚本预览一下df_preview pd.read_excel(销售数据.xlsx, sheet_name明细, nrows5) print(df_preview.columns.tolist()) print(df_preview.head())看到列名和内容之后再正式读取。如果发现有行不对可以调整header参数比如数据表头在第二行就用header1。3. 半自动化的核心用Excel配置文件控制切分逻辑做过重复性数据分析工作的人都懂最怕的不是写脚本而是需求一改就要改代码。半自动化的核心思路就是把“切分规则”单独抽出来放到一个Excel配置表里。脚本每次运行前都去读这张配置表按里面的规则执行。3.1 配置文件怎么设计我常用的配置文件叫split_config.xlsx里面至少有两个Sheet一个叫“任务参数”一个叫“切分规则”。“任务参数”这张表长这样参数名值说明源文件销售数据.xlsx要切分的原始Excel文件源Sheet明细读取哪个Sheet输出目录output/切分后的文件放哪里“切分规则”这张表是核心每一行代表一个切分任务任务名切分方式分组列每行数比例文件名前缀按城市拆column城市城市_按固定行数切rows50000批次_训练集测试集ratio0.7report_这里的“切分方式”我用简单字符串表示column代表按列分组rows代表按行数切ratio代表按比例随机切。这样一来业务同事想加一个新任务不需要碰代码直接在这张Excel表格里加一行就行。有人可能会问为什么不直接写在CSV配置里我试过CSV配置对非技术人员不够友好很容易因为编码问题打不开而且打开后也不会自动对齐。Excel配置表的好处是谁都能打开看谁都能改下拉框、颜色标记都可以往上加。3.2 脚本怎么读配置读取配置表本身也是一次Excel读取操作非常简单import pandas as pd config pd.read_excel(split_config.xlsx, sheet_name切分规则) tasks config.to_dict(records)to_dict(records)会把每行变成一个字典例如{任务名: 按城市拆, 切分方式: column, 分组列: 城市, 每行数: None, 比例: None, 文件名前缀: 城市_}。这样后面遍历就能直接取字段可读性很高。接下来需要一个主函数根据切分方式分发到不同的处理逻辑。我用最直接的方式def run_task(task, master_df): method task[切分方式] if method column: split_by_column(master_df, task[分组列], task[文件名前缀]) elif method rows: split_by_every_n(master_df, int(task[每行数]), task[文件名前缀]) elif method ratio: split_by_ratio(master_df, float(task[比例]), task[文件名前缀]) else: print(f未知切分方式: {method})主流程很清晰读取配置 → 读取源数据 → 逐条执行规则。以后新增切分方式也只需要在这个函数里加一个分支。3.3 用Excel当配置的价值我见过不少人喜欢把参数写在Python文件顶部比如GROUP_COL 城市 OUTPUT_DIR output/这样做不是不行但有一个问题每次需求变化都要打开Python文件去改如果同事不懂代码就只能来找你。而把规则放进Excel业务同学自己就能完成80%的配置修改你只需要在规则异常时帮忙看一眼。还有一点配置表本身也是一份文档。你今天怎么拆的分成几个文件每个文件是什么规则在这张表里一目了然。过了一个月回头查当时的拆法不用去翻聊天记录、不用猜逻辑直接看配置表就行。我现在的做法是把配置文件跟源数据放在同一个目录跑批之前先把配置表复制一份存档这样即使后面被改乱了也能回滚。4. 实操三种高频切分场景的完整实现这节直接上代码每个场景我给一个可以单独运行的函数。你可以把它们组合到同一个脚本里也可以按需复制。4.1 按列值切分把一张大表拆成多个分类文件这是最常用的场景。假设你想把销售数据.xlsx按“城市”这一列拆成多个文件每个城市一个Excel。实现如下import os import re import pandas as pd def split_by_column(df, group_col, prefix, out_diroutput): os.makedirs(out_dir, exist_okTrue) for value, group_df in df.groupby(group_col, dropnaFalse): # 把分组值变成合法文件名 safe_value re.sub(r[\\/:*?|], _, str(value)) safe_value safe_value.replace( , _) if pd.isna(value): safe_value 空值 filename f{prefix}{safe_value}.xlsx filepath os.path.join(out_dir, filename) group_df.to_excel(filepath, indexFalse) print(f已生成: {filename}共 {len(group_df)} 行)有几个细节值得说明。文件名里不能包含\ / : * ? |这些字符所以一定要做一次清洗。分组列里如果存在空值groupby默认会把NaN分到一组但str(NaN)变成nan直接拿来做文件名不好看所以我统一改成“空值”。groupby(dropnaFalse)这个参数容易被忽略。新版pandas对NaN的处理有时候会把它忽略掉导致空值行丢失。如果你希望空值也能单独成一个文件一定要加上dropnaFalse。如果你希望按多个列组合拆分比如“城市门店”只需要把group_col从字符串改成列表然后循环分组键改成元组即可。4.2 按行数批量切分突破Excel单表行数限制Excel单Sheet最多容纳1048576行如果数据量超过这个数保存时就会报错。即使没超过单个文件几十万行打开也费劲。按行数拆分是最稳的方法。实现函数也很简单核心就是用切片def split_by_every_n(df, n, prefix, out_diroutput): os.makedirs(out_dir, exist_okTrue) total_rows len(df) print(f总行数: {total_rows}) for start in range(0, total_rows, n): end min(start n, total_rows) sub_df df.iloc[start:end] seq f{start // n 1:03d} filename f{prefix}{seq}.xlsx filepath os.path.join(out_dir, filename) sub_df.to_excel(filepath, indexFalse) print(f已生成: {filename}行数 {start1}-{end})这里的min(start n, total_rows)就是用来处理尾部数据的。很多人写循环时容易遗漏最后不足一组的行导致数据丢失。用min处理后最后一批实际有多少行就保存多少行。还有一个性能经验如果源文件是几百MB级别to_excel写入很慢。我的处理办法是先把DataFrame转成CSV压缩后再转换。比如sub_df.to_csv(filepath.replace(.xlsx, .csv), indexFalse, encodingutf-8-sig)如果你一定要输出Excel可以试试enginexlsxwriter速度会明显快一些。实测100万行数据拆成20个文件用openpyxl引擎可能要跑两三分钟换掉引擎后经常30秒内跑完。4.3 按比例随机切分训练集/测试集的正确玩法第三种场景是做机器学习时最常见的把清洗好的数据按比例随机切分成训练集和测试集。如果你只是简单用sample抽70%剩下的30%再drop代码是def split_by_ratio(df, frac0.7, prefixsplit, out_diroutput, seed42): os.makedirs(out_dir, exist_okTrue) train df.sample(fracfrac, random_stateseed) test df.drop(train.index) train.to_excel(os.path.join(out_dir, f{prefix}_train.xlsx), indexFalse) test.to_excel(os.path.join(out_dir, f{prefix}_test.xlsx), indexFalse) print(f训练集 {len(train)} 行测试集 {len(test)} 行)random_state42一定要写死。不写的话你每次跑出来的随机结果都不一样别人也没办法复现你的数据划分。数据清洗和模型训练里可复现性非常重要。42只是习惯取值你用别的数字也行只要每次相同。不过这个简单版本有个隐患如果数据里分类标签很不均衡比如1000条数据里只有10条是正样本随机抽样有可能把正样本全部抽到训练集或测试集导致模型训练失效。稳妥做法是按类别分层抽样from sklearn.model_selection import train_test_split def split_by_ratio_stratified(df, label_col, test_size0.3, seed42): train, test train_test_split( df, test_sizetest_size, random_stateseed, stratifydf[label_col], # 按标签列分层 ) return train, teststratify参数会保证训练集和测试集中每个类别的比例基本跟原数据一致。初次接触sklearn也能直接调用这个函数挺稳的。5. 常见问题与排查技巧代码能跑通只是第一步实际项目里最花时间的往往是一堆奇奇怪怪的数据问题。我把经常遇到的几类问题整理成一个速查表希望能帮你少踩坑。5.1 数字变成科学计数法或日期错乱这个在前面提过。我遇到最典型的一个案例是门店ID有18位拆分后打开Excel一看变成了1.23457E17而且后面的数字变成了全零。这就是Excel对超过15位数字的默认处理方式。排查方法很简单读取时设定dtypestr然后抽样打印几行看看。如果是原始Excel本身已经把数字变成了科学计数法那只能在源头让业务方改格式或者在读取前用Excel把该列设为文本格式。日期错乱是另一类问题。某些日期在Excel中看起来是“2024-06-01”读进pandas后变成Timestamp再写回Excel可能变成一串数字。解决办法是统一格式化df[日期] pd.to_datetime(df[日期]).dt.strftime(%Y-%m-%d)这样写入之后的日期就是纯文本格式不会变数字。5.2 合并单元格和重复表头怎么处理Excel里最让人头疼的就是合并单元格。pandas读合并单元格时只有左上角第一个单元格有值其余位置都是NaN。如果你需要按某列分组就会发现很多行被分到“空值”组里。我之前处理过一张部门业绩表表头占了两行还有列合并。我的做法是先保留原文件专门写一段代码检查空值分布再决定怎么处理。最简单的方法是让业务方提供一份“干净版”如果不行就在读取后用前一行填充df[group_col] df[group_col].fillna(methodffill)重复表头的情况通常是表头占了多行pandas默认把第一行当列名。你可以读取时指定header1或者读取后手动重命名列名。df.columns [门店ID, 城市, 日期, 销售额]建议在处理前把列名统一打印出来不要凭印象取列名。我见过太多人因为少打了空格程序白白跑半天最后报KeyError。5.3 内存占用太高Excel直接打不开大数据量环境下内存问题很常见。一个100万行的Excel如果全部按默认类型读入有可能占用好几GB内存直接把Python进程干崩。我的排查思路是先看读取后的df.info()和df.memory_usage(deepTrue)找出占用最大的列。如果是文本列可以考虑把它转为category类型df[城市] df[城市].astype(category)如果是用不到的多余列用usecols删掉。如果数据量实在太大可以分块读取只处理当前块再写出去chunk_iter pd.read_excel(大文件.xlsx, sheet_name明细, chunksize100000, usecolscols)但要注意read_excel的chunksize依赖底层引擎实测openpyxl下可能不太稳定。我更推荐先用to_csv转成CSV再分块读取chunk_iter pd.read_csv(大文件.csv, chunksize100000, dtypestr)5.4 输出文件编码和打开乱码如果你最后生成的不是Excel而是CSV用Excel打开时很容易遇到乱码。原因很简单pandas默认用UTF-8编码写CSV而Excel默认用GBK打开。解决办法是在写的时候指定UTF-8 BOMdf.to_csv(result.csv, indexFalse, encodingutf-8-sig)utf-8-sig会在文件开头加BOMExcel就能正确识别编码。如果你只是用Excel打开不会有这个问题但很多自动化平台默认接收CSV所以这个小技巧挺实用。5.5 别把原文件覆盖了这个错误听起来很低级但很多人跑顺手之后会犯。脚本里输出路径写得不对比如to_excel(销售数据.xlsx)结果直接把原始Excel覆盖掉了数据还没了。我现在的做法是每次运行脚本都自动生成带时间戳的目录from datetime import datetime out_dir foutput_{datetime.now().strftime(%Y%m%d_%H%M%S)} os.makedirs(out_dir, exist_okTrue)这样每次输出到新目录不会覆盖旧结果也更方便追溯。6. 后续扩展从半自动走向更省心半自动化跑通之后你会发现这套思路还能继续往外延伸。我后续做了两个小改动现在每天处理数据的时间压缩了很多。6.1 每次切分后自动生成清单文件以前拆完文件后我还要一个个打开确认行数对不对后来我在脚本末尾加了一段汇总逻辑summary [] # 在每个拆分函数里统一把 {文件名: 行数} 记入 summary # 最后写一个汇总表 pd.DataFrame(summary).to_excel( os.path.join(out_dir, 切分清单.xlsx), indexFalse )清单里每一行代表一个生成的文件包含文件名、行数、生成时间。这样跑完直接看清单就知道有没有漏文件、有没有空文件。业务同事也更容易验收。6.2 把脚本打包成命令行工具如果公司里有人要用又不想打开Python环境可以用PyInstaller把脚本打个包比如生成一个split_data.exe。配置文件和源文件放在同一个目录下双击可执行程序脚本自动运行。这样连代码都不用看真正的“用户界面”就是那张Excel配置表。再往后如果数据每天定时更新你完全可以把脚本挂到计划任务或服务器定时任务上每天固定时间自动跑一次拆分。到那个阶段半自动化也就慢慢进化成全自动化了。但我还是建议保留人工检查配置这一步毕竟数据质量比效率更重要。做数据切分这件事核心从来不是代码多复杂而是你能不能把规则梳理清楚并让规则可以灵活调整。我用这套半自动方法处理过销售明细、用户画像、日志统计每次只需要换一张配置文件剩下的交给脚本就好。希望这套思路也能帮你少加几次班。