最近整理 pandas 数据分析的系列内容时总有人问我一个问题“groupby 我会了astype(category) 也见过但透视表、交叉表、Excel 处理跟它们到底什么关系”其实这些功能并不是孤立的它们是一条完整的分析链路先用 astype(category) 把类别字段统一成最适合分组的类型接着用时间操作把日期字段标准化再通过分组聚合或透视表输出统计结果最后还要能顺畅地和 Excel 打交道。这篇文章就以“分类数据 分组聚合 透视表/交叉表 Excel 处理”为主线把这一整套流程讲透适合刚学完 pandas 基础、正准备接触真实业务数据的读者也能给已经写了不少分析脚本的人查漏补缺。1. 整体思路分类、分组、透视表和交叉表为什么是一套组合拳很多教程喜欢把 astype(category)、groupby、pivot_table、crosstab 拆成几节课单独讲学的时候都明白一落到真实数据上就不知道怎么组合。我自己的经验是这些操作本质上都是在回答同一个业务问题“某个维度下面另一个维度表现得怎么样”。比如“每个城市每个月的销售额是多少”城市和月份就是维度销售额就是被聚合的指标。分组聚合计算量不大但输出结果往往是一长串数据业务同事看起来费劲透视表和交叉表就是把同样的统计结果改写成行列交叉的二维报表观感完全不同。再说 Excel企业里的大部分数据源头都是 Excel 表格分析结果的交付也经常要回到 Excel。如果只在 pandas 里算得爽最后导入导出时被编码、格式、多 Sheet 的问题卡住前面做得再好都没用。所以这篇文章不是单纯列 API而是按真实工作的处理顺序读入 Excel → 转换类型 → 处理时间 → 分组统计 → 透视/交叉输出 → 再写回 Excel。一条线走下来你会感觉这些零散的知识点突然串起来了。我遇到过一个做运营分析的同事他每天的工作就是拉一张订单明细表按区域和品类做汇总再把结果做成日报。之前他都是先在 Excel 里拼命做透视表数据一多就卡死。后来我把这套 pandas 流程给他他学会之后效率提升非常明显。这也是我想把这几个主题放在一篇里讲的原因它们不是并列的知识点而是同一件事的四个环节。2. 按类别单独分类astype(category)怎么用、凭什么好用2.1 先从一个最简单的转换看起先造一个特别简单的 DataFrame看看 astype(category) 到底做了什么。import pandas as pd df pd.DataFrame({ 城市: [华北, 华东, 华南, 华东, 华北], 销售额: [100, 200, 150, 180, 120] }) df[城市] df[城市].astype(category) print(df.dtypes)输出结果里城市这一列会从 object 类型变成 category 类型。表面上看数据还是那几个中文词但 pandas 内部已经把相同的值归纳成一组类别并且给每个类别分配了一个整数编码。它不再把“华北”“华东”“华南”当成普通字符串逐个存储而是记录一份类别列表再按整数索引去引用。很多初学者看到这一步会疑惑类型变来变去到底有什么用你可以把 category 理解为“枚举类型”它限定了这一列只能取哪些值。对于业务数据里的“地区”“品类”“门店等级”这类低基数文本列用 category 是再合适不过的。真正的好处要到分组和排序时才会完全体现出来。顺便说一句如果你用的是 pandas 2.0 以上的版本转换后会看到一个新的 PyarrowDtype 相关知识但传统 category 仍然是日常分析最稳妥的选择。只要业务场景是列值重复度高、分类有限用它就不会有坏处。2.2 category类型的三个隐藏优势内存、顺序、分组首先说内存。假设一个订单表有十万行其中“渠道来源”只有五个值如果以字符串存储每一行都要保存一段文本转成 category 后文本只存一份剩余全部用整数引用内存占用会明显下降。当然如果一列几乎每个值都不重复比如订单号转 category 反而会增加开销因为既要维护类别表又要存整数编码。其次是排序。普通字符串排序是按拼音或 Unicode 码位而 category 可以自定义逻辑顺序。最典型的是订单状态“待付款”“已付款”“已发货”“已完成”。如果用字符串默认排序可能完全不是你想要的转成 category 并指定 categories 顺序后sort_values 和 groupby 都会按业务顺序展示。status_order [待付款, 已付款, 已发货, 已完成] df[订单状态] pd.Categorical( df[订单状态], categoriesstatus_order, orderedTrue )第三就是和分组聚合的配合。分组时如果被分组的列是 categorygroupby 之后的输出顺序会基于类别顺序而不是数据出现顺序这在自动生成日报时能保证结果稳定。对比一下普通字符串分组输出顺序受数据原始顺序影响换一批数据可能就变了。这也是我在项目实战里最爱用 category 的原因。分析脚本写完之后最怕的就是换数据源后结果顺序不一致category 可以帮你把这种不确定性降到最低。2.3 使用category时容易踩的坑category 看着好用但有几个坑必须提前知道。第一个坑是“它不是字符串”。转成 category 后原来的 str.contains() 或 str.strip() 这类方法不能直接用了。如果需要对类别文本做清洗要么先把该列转为字符串再操作要么对 cat.categories 处理后再重新赋值。第二个坑是缺失值。category 列可以包含 NaN这不影响分组但如果你在读取数据时用 dtype{地区: category}当 Excel 读到空值时会把空值也当作一个类别。之后输出结果会出现一个带空值的分组有时候会让人莫名其妙可以用 dropnaFalse 或 fillna 提前处理。第三个坑是分组时的空类别。以前 pandas 在用 category 列做 groupby 时会默认把没有数据的空类别也展示出来很多老教程里会出现一行全是 NaN 的分组结果。新版本已经开始提醒要让用户显式设置 observed 参数我的建议是分组时直接写清楚df.groupby(地区, observedTrue)[销售额].sum()这样代码明确、结果可控也不会被版本升级影响。3. 时间操作把字符串时间变成可分析的时间维度3.1 时间列读取之后先做一次标准化从 Excel 或数据库读出来的时间形态千差万别。有的是“2024/1/5”有的是“20240105”还有的是真正的 datetime 类型。如果不统一后面按月份汇总就会出错。标准做法是用 pd.to_datetime 做一次转换。df[订单日期] pd.to_datetime(df[订单日期])这一步看起来简单但要注意两个细节。一是格式识别失败时会报错如果数据里面有类似“2024年1月”这种字符串建议加上 format 参数。二是混合了日期和时间的数据to_datetime 也能处理但如果你只关心日期后面可以用 dt.date 或直接设置索引。如果你只是在“月份”粒度上做统计也可以直接从时间列提取月份df[月份] df[订单日期].dt.month这样得到的月份是 1 到 12 的整数后续分组非常方便。如果想更完整一点可以先构造一个“年月”列例如 df[订单日期].dt.to_period(M)在跨年份分析时能避免 1 月和去年 1 月混在一起。3.2 resample时间维度上的分组利器当数据已经按时间排序并且你希望按周、月、季度、年汇总时resample 比 groupby(dt.month) 更标准。resample 需要先把时间列设置为索引。df df.set_index(订单日期) df.resample(M)[销售额].sum()这里的 M 和 MS 经常有人搞混。M 表示日历月月末MS 表示月初。单纯做聚合求和两种结果对数值没有影响但如果你要结合月末业务节奏做判断两者就不同了。按周的话W 默认是周日结束W-MON 则以周一分组。建议在实际业务里先确认团队的周起始日是哪一天。resample 对索引有要求所以如果连续做多个分析最好先把订单日期放回普通列或者用 reset_index() 把结果转回 DataFrame。否则后续再用地区分组时日期索引会一直跟着处理起来别扭。3.3 时间字段和category类型怎么配合时间字段虽然可以用 dt.month 提取整数但有些业务场景把月份当成分类维度更合适。比如连续 12 个月的报表如果把月份转成 category并指定 1 到 12 的顺序即使某个月份没有数据分组结果也会稳定展示。更常见的场景是把连续数值分段成类别。举个例子分析“客单价区间”时可以先用 cut 把销售额分成几个区间再转成 category之后做交叉表就非常直观。df[销售额区间] pd.cut( df[销售额], bins[0, 100, 500, 1000, 5000], labels[低, 中, 高, 极高] )这样切出来的列本质是 category有明确顺序。后续无论做透视表还是画图都会按照低、中、高、极高的顺序展示不会乱跳。时间分析和分类数据从来不是对立的很多报表里的“月份”是时间维度“销售额区间”是类别维度两者经常同时出现在一张透视表里。4. 分组聚合操作groupby的完整打开方式4.1 单列分组最朴素但最常用的写法groupby 的基本语法很简洁指定分组键再指定聚合列和聚合函数。比如一个月度订单表里想算各城市的总销售额常见写法是result df.groupby(城市, observedTrue)[销售额].sum() print(result)返回的是一个 Series索引是城市值是销售额。很多人踩的坑是忘记 reset_index()导致后续想继续用城市列做关联时每次都得 reset_index()很烦。我的习惯是如果做中间处理就保留 Series如果要交付结果或者继续和其他表合并就顺手 reset_index()。有个细节值得注意如果分组列里出现 NaN默认分组结果会把它排除掉。如果想让缺失值单独成组可以加 dropnaFalse。这个参数在真实数据里非常实用尤其当你分析的数据是从多张表拼接过来、本来就有缺失值的时候。4.2 多列分组加多指标聚合agg的正确姿势真实分析很少只算一个指标。最常见的是按“城市 月份”分组同时求销售额总和、订单数、客单价均值。这种需求用 agg 一次搞定result ( df.groupby([城市, 月份], observedTrue) .agg( 销售额合计(销售额, sum), 订单数(订单号, count), 平均客单价(销售额, mean) ) .reset_index() )这种写法比传字典更清晰而且列名是中文也方便直接输出给业务。注意订单数这里用的是 count不是 nunique因为同一笔订单如果有多个商品订单号会有重复如果要求的是“订单张数”应该用 nunique。agg 能接收的聚合函数包括常见的 sum、mean、max、min、count、nunique、std 等。字符串形式就够了也可以传 numpy 函数。如果你有自定义聚合逻辑比如想算“销售额超过一千的订单占比”可以用 lambda但要小心性能数据量大时建议先用 filter 或 transform 拆分处理。4.3 transform和filter不用聚合也能分组groupby 不只是用来做汇总。transform 可以把分组计算结果映射回原数据每一行常用于计算占比、去均值、标准化。df[城市平均销售额] df.groupby(城市, observedTrue)[销售额].transform(mean)这样每一行都会带着自己所在城市的平均值你可以直接算单店销售额和城市均值的差距。filter 则用来筛选满足条件的分组比如只保留总销售额大于 1000 的城市df.groupby(城市, observedTrue).filter(lambda x: x[销售额].sum() 1000)filter 和 transform 最大的意义是保持数据行数不变或按组删除而不是像 agg 那样把多行压成一行。遇到“给每个订单打上所属分组的均值标签”之类的需求时用 transform 会比先聚合再合并更高效代码也更短。5. 透视表把聚合结果变成一张可读的二维报表5.1 pivot_table最关键的那几个参数分组聚合结果通常是长表一行一个分组。可业务上更常看到的是“行是城市列是月份”的宽表这时候就用到了 pivot_table。pivot pd.pivot_table( df, values销售额, index城市, columns月份, aggfuncsum, fill_value0 )values 是要聚合的数值列index 是放在行索引上的维度columns 是展开成横向列标题的维度aggfunc 决定如何聚合。实际使用中可以传多个列名和多个聚合函数。比如同时算销售额合计和订单数pivot pd.pivot_table( df, values[销售额, 订单号], index城市, columns月份, aggfunc{销售额: sum, 订单号: count} )传字典的时候它会自动给不同列配不同聚合方式比较符合真实需求。生成的列名是二级的看起来稍微有点乱但输出到 Excel 后稍微调整一下表头就行。5.2 margins和fill_value让报表更像业务报表两个参数对交付质量影响很大一个是 fill_value一个是 margins。fill_value 可以把空值填成 0。如果没有它某城市某月没有订单时透视表里就是 NaNExcel 里显得很脏。margins 会在透视表底部和右侧增加“汇总”行列计算的其实是分组维度的总计。pivot pd.pivot_table( df, values销售额, index城市, columns月份, aggfuncsum, fill_value0, marginsTrue )这样输出后业务同事一眼就能看到总体销售额和每个城市的边际合计。不过要注意marginsTrue 时汇总行名默认是“All”看不懂的话可以改 margins_name总计。如果数据量很大输出前可以用 astype/int 处理数据类型避免出现 1.234567e09 这种显示问题。5.3 pivot_table和groupby怎么选在决定用 groupby 还是 pivot_table 时我一般按输出形态来判断。如果只是中间计算后续还要继续 join 或处理用 groupby 更灵活如果是要交给人的报表用 pivot_table 更直观。其实两者内部计算有很大重叠pivot_table 也就是先分组聚合再把结果重塑成宽表。所以没有必要纠结“谁更高级”真正决定选择的是你下一步要干什么。如果你有多次复杂聚合groupby 更方便如果你需要把行、月份、列维度组成一张二维矩阵pivot_table 是更快的路径。6. 交叉表频数统计的专属武器6.1 crosstab最基本的用法交叉表的名字听着有点专业其实就是“两个或多个分类变量的频数统计”。比如统计“每个城市各品类的订单数量”写出来非常简洁freq pd.crosstab(df[城市], df[品类])输出结果里行是城市列是品类单元格里的数字是频数。如果 df 本身有几十万行但城市和品类都是低基数这个结果很适合直接做成报表。它的优点是不需要先构造中间 DataFrame直接传两列就能用。还可以传三个维度变成三维表但日常业务中通常用两个或三个维度就够了太多维度反而不利于阅读。6.2 normalize参数从频数到占比交叉表核心是频数但很多时候百分比比绝对数量更有业务意义。normalize 参数支持按不同方向做归一化。pd.crosstab(df[城市], df[品类], normalizeindex)normalizeindex 表示每行总和为 1看的是“在该城市下各品类的占比”normalizecolumns 表示每列总和为 1看的是“在该品类下各城市的占比分布”normalizeall 则是看全局占比。实际工作时看“某城市某品类占该城市的比例”比看“占整体订单比例”更有指向性。建议在做交叉表时把原始频数和归一化结果放在两个列里方便对比。比如先算频数再算行占比最后再算一个总体占比如下info pd.crosstab(df[城市], df[品类], normalizeindex)预览过后导出到 Excel 的时候可以把数字改成百分比格式这一步用 openpyxl 处理稍后会提到。6.3 crosstab与pivot_table的边界虽然 crosstab 好像只能数个数但它同样支持 values 和 aggfunc。比如想在交叉表里求每组销售额总和pd.crosstab( df[城市], df[品类], valuesdf[销售额], aggfuncsum )这样它就和 pivot_table 很像了。区别在于 crosstab 默认只聚焦“对两个分类列做频数统计”所以可以少写几个参数代码更短。如果你只是想看两个维度里的订单占比或人数分布优先用 crosstab如果想算销售额、均值、方差这类数值聚合用 pivot_table 更顺手。从经验上说crosstab 非常适合快速做数据探索。拿到一份新数据先拿几个分类列组合一下数个数、看分布能很快发现异常值。比如某个品类的订单数突然变成零交叉表一眼就能看出来。7. Excel表的数据处理从数据入口到交付出口7.1 read_excel常用参数pandas 读写 Excel 最常见的组合是 read_excel 和 to_excel。很多人在真实项目里一上来就写df pd.read_excel(订单明细.xlsx)但如果字段里有日期、有数字、还有全角字符这个极简写法会给你埋下不少坑。先看几个关键参数。sheet_name 用于指定工作表可以传工作表名也可以传 0、1 这样的位置索引还可以传 None 把全部 Sheet 读成字典。header 用来指定表头所在行如果 Excel 里前两行是标题和副标题就要用 header1。dtype 可以在读取时直接把某些列指定为 category 或 str避免 pandas 自动推断类型造成额外开销。usecols 可以只读取需要的列df pd.read_excel( 订单明细.xlsx, sheet_name明细, dtype{城市: category, 品类: category}, usecols[日期, 城市, 品类, 销售额] )在读取阶段把类型定好后面就省得再用 astype 做一次转换。7.2 多Sheet和脏数据公司里的 Excel 往往不止一个 Sheet。比如一份文件里既有“订单明细”又有“门店表”这时可以用 sheet_nameNone 把整个文件读进一个字典。all_sheets pd.read_excel(业务数据.xlsx, sheet_nameNone) detail all_sheets[订单明细] store all_sheets[门店表]字典的键是 Sheet 名值是 DataFrame操作起来非常舒服。还有一个常见问题Excel 里会有“合计行”“空白行”“单位行”。其实这些都不是解析错误而是 Excel 本身的生产习惯。如果读取后发现数据头部或尾部有多余内容可以用 skiprows 跳过用 dropna 清理。建议在项目开始前先统一约定一份 Excel 模板表头固定在第 1 行不要合并单元格不要写“单位万元”这种行日期列统一格式。模板定得好后面所有脚本都能稳定复用。7.3 输出Excel的常见坑写回 Excel 时最容易被骂的是“你给我的这个表怎么不能透视”。一般原因是 indexTrue把分组生成的索引也写进表格了列名对不上。写回时统一 indexFalseresult.to_excel(结果.xlsx, sheet_name汇总, indexFalse)如果要写多个 DataFrame 到一个 Excel 文件用 ExcelWriterwith pd.ExcelWriter(统计结果.xlsx, engineopenpyxl) as writer: result1.to_excel(writer, sheet_name月度汇总, indexFalse) pivot.to_excel(writer, sheet_name透视表)另一个坑就是格式。pandas 默认写出去的是纯数据没有边框、没有列宽、没有居中业务同事拿到后往往会嫌“格式难看”。要美化格式可以用 openpyxl 在写入之后调整列宽、字体、数字格式。通常我是先把数据写进 Excel然后用 openpyxl 加载并设置。from openpyxl import load_workbook from openpyxl.styles import Font wb load_workbook(统计结果.xlsx) ws wb[透视表] for col in ws.columns: ws.column_dimensions[col[0].column_letter].width 14 ws[A1].font Font(boldTrue) wb.save(统计结果.xlsx)如果数据量不大这一步成本很低如果数据量很大格式设置反而会导致性能问题建议只对报表结果的 Sheet 做美化。8. 完整实操从Excel读取到分类分组透视的一条龙8.1 搭建一个模拟场景假设手上有一份零售订单明细字段包括订单号、门店、品类、订单日期、销售额。日常需求有三个统计每个门店每个月的销售额生成一张门店与品类的销售额透视表统计门店与品类之间的订单频数最后把所有结果输出到同一个 Excel 工作簿。为了演示方便直接在代码里构造数据真实场景里把第一行 read_excel 替换成自己的文件路径就行。8.2 代码逐段说明import pandas as pd # 模拟数据 df pd.DataFrame({ 订单号: [1001, 1002, 1003, 1004, 1005], 门店: [华北一店, 华东一店, 华北二店, 华东一店, 华南一店], 品类: [饮料, 食品, 饮料, 日化, 食品], 订单日期: [2024-01-05, 2024-01-12, 2024-02-01, 2024-02-03, 2024-01-20], 销售额: [120, 300, 180, 200, 150] }) # 第一步类型处理 df[门店] df[门店].astype(category) df[品类] df[品类].astype(category) # 第二步时间标准化 df[订单日期] pd.to_datetime(df[订单日期]) df[月份] df[订单日期].dt.month # 第三步分组聚合 monthly ( df.groupby([门店, 月份], observedTrue) .agg(销售额合计(销售额, sum)) .reset_index() ) # 第四步透视表 pivot pd.pivot_table( df, values销售额, index门店, columns月份, aggfuncsum, fill_value0, marginsTrue, margins_name总计 ) # 第五步交叉表 freq pd.crosstab(df[门店], df[品类]) # 第六步输出 with pd.ExcelWriter(零售统计结果.xlsx, engineopenpyxl) as writer: monthly.to_excel(writer, sheet_name月度汇总, indexFalse) pivot.to_excel(writer, sheet_name门店月份透视) freq.to_excel(writer, sheet_name门店品类频数)这段代码的逻辑很直白先转换类型和日期再按门店 月份分组之后分别生成透视表和交叉表最后写进一个 Excel 文件。如果把 read_excel 和真实文件路径补上这套脚本可以直接应用到日常月报里。8.3 跑完之后你会得到一个什么样的结果月度汇总表是一个普通的长表三列门店、月份、销售额合计。透视表则是一张宽表行是门店列是月份每个交叉点是该门店该月的总销售额最右边和最下面还有“总计”。交叉表是门店和品类的频数矩阵每个格子代表该门店下该品类的订单数。如果你需要对数字做进一步加工比如求环比可以在月度汇总表里再做一次 pivot 或者使用 shift但大多数情况下这三张表已经能回答门店的销售表现、品类的结构性分布、时段变化趋势等基础业务问题。8.4 实操中的几条总结性经验写分析脚本时我最常犯的错误是把所有步骤塞在一个长函数里出错了不好排查。实际操作中建议分三步走先读数并打印字段类型确认类型是否正确再做中间计算频繁用 head() 或 shape 检查行数是否异常最后才是生成最终报表。不要跳过类型转换直接分组那样通常在排序和输出上会有一堆奇怪问题。还有一个小经验groupby、pivot_table、crosstab 这些操作虽然也可以在一个链式表达式里写到底但代码可读性会大幅下降。真实项目中我一般会为每个步骤分配一个明确变量名再用 reset_index() 把中间结果转成干净的 DataFrame。后续接手代码的人会感谢你的。最后提醒一点如果你的数据每天都在更新最好把文件路径、Sheet 名、分组字段、日期格式都抽出来作为配置项这样月底换数据时只需要改配置不需要改代码逻辑。个人经验里这种“半自动化”的数据处理脚本要比一次性但难以复用的脚本价值高太多。把这一点做好你以后写任何数据处理脚本都能做到打开即用、换数不慌省下来的时间可以投入到真正需要判断力的业务分析上。