pandas分类数据与分组聚合：透视表交叉表及Excel处理全流程-尧图手机网站定制

最近整理 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['订单状态'], categories=status_order, ordered=True )

第三就是和分组聚合的配合。分组时如果被分组的列是 category，groupby 之后的输出顺序会基于类别顺序，而不是数据出现顺序，这在自动生成日报时能保证结果稳定。对比一下普通字符串分组，输出顺序受数据原始顺序影响，换一批数据可能就变了。

这也是我在项目实战里最爱用 category 的原因。分析脚本写完之后，最怕的就是换数据源后结果顺序不一致，category 可以帮你把这种不确定性降到最低。

2.3 使用category时容易踩的坑

category 看着好用，但有几个坑必须提前知道。

第一个坑是“它不是字符串”。转成 category 后，原来的 str.contains() 或 str.strip() 这类方法不能直接用了。如果需要对类别文本做清洗，要么先把该列转为字符串再操作，要么对 cat.categories 处理后再重新赋值。

第二个坑是缺失值。category 列可以包含 NaN，这不影响分组，但如果你在读取数据时用 dtype={'地区': 'category'}，当 Excel 读到空值时会把空值也当作一个类别。之后输出结果会出现一个带空值的分组，有时候会让人莫名其妙，可以用 dropna=False 或 fillna 提前处理。

第三个坑是分组时的空类别。以前 pandas 在用 category 列做 groupby 时会默认把没有数据的空类别也展示出来，很多老教程里会出现一行全是 NaN 的分组结果。新版本已经开始提醒要让用户显式设置 observed 参数，我的建议是分组时直接写清楚：

df.groupby('地区', observed=True)['销售额'].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('城市', observed=True)['销售额'].sum() print(result)

返回的是一个 Series，索引是城市，值是销售额。很多人踩的坑是忘记 reset_index()，导致后续想继续用城市列做关联时，每次都得 reset_index()，很烦。我的习惯是，如果做中间处理，就保留 Series；如果要交付结果或者继续和其他表合并，就顺手 reset_index()。

有个细节值得注意：如果分组列里出现 NaN，默认分组结果会把它排除掉。如果想让缺失值单独成组，可以加 dropna=False。这个参数在真实数据里非常实用，尤其当你分析的数据是从多张表拼接过来、本来就有缺失值的时候。

4.2 多列分组加多指标聚合：agg的正确姿势

真实分析很少只算一个指标。最常见的是按“城市 + 月份”分组，同时求销售额总和、订单数、客单价均值。这种需求用 agg 一次搞定：

result = ( df.groupby(['城市', '月份'], observed=True) .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('城市', observed=True)['销售额'].transform('mean')

这样每一行都会带着自己所在城市的平均值，你可以直接算单店销售额和城市均值的差距。filter 则用来筛选满足条件的分组，比如只保留总销售额大于 1000 的城市：

df.groupby('城市', observed=True).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='月份', aggfunc='sum', fill_value=0 )

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。如果没有它，某城市某月没有订单时，透视表里就是 NaN，Excel 里显得很脏。margins 会在透视表底部和右侧增加“汇总”行列，计算的其实是分组维度的总计。

pivot = pd.pivot_table( df, values='销售额', index='城市', columns='月份', aggfunc='sum', fill_value=0, margins=True )

这样输出后，业务同事一眼就能看到总体销售额和每个城市的边际合计。不过要注意，margins=True 时汇总行名默认是“All”，看不懂的话可以改 margins_name='总计'。如果数据量很大，输出前可以用 astype/int 处理数据类型，避免出现 1.234567e+09 这种显示问题。

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['品类'], normalize='index')

normalize='index' 表示每行总和为 1，看的是“在该城市下，各品类的占比”；normalize='columns' 表示每列总和为 1，看的是“在该品类下，各城市的占比分布”；normalize='all' 则是看全局占比。

实际工作时，看“某城市某品类占该城市的比例”比看“占整体订单比例”更有指向性。建议在做交叉表时，把原始频数和归一化结果放在两个列里，方便对比。比如先算频数，再算行占比，最后再算一个总体占比如下：

info = pd.crosstab(df['城市'], df['品类'], normalize='index')

预览过后，导出到 Excel 的时候可以把数字改成百分比格式，这一步用 openpyxl 处理，稍后会提到。

6.3 crosstab与pivot_table的边界

虽然 crosstab 好像只能数个数，但它同样支持 values 和 aggfunc。比如想在交叉表里求每组销售额总和：

pd.crosstab( df['城市'], df['品类'], values=df['销售额'], aggfunc='sum' )

这样它就和 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 里前两行是标题和副标题，就要用 header=1。dtype 可以在读取时直接把某些列指定为 category 或 str，避免 pandas 自动推断类型造成额外开销。usecols 可以只读取需要的列：

df = pd.read_excel( '订单明细.xlsx', sheet_name='明细', dtype={'城市': 'category', '品类': 'category'}, usecols=['日期', '城市', '品类', '销售额'] )

在读取阶段把类型定好，后面就省得再用 astype 做一次转换。

7.2 多Sheet和脏数据

公司里的 Excel 往往不止一个 Sheet。比如一份文件里既有“订单明细”，又有“门店表”，这时可以用 sheet_name=None 把整个文件读进一个字典。

all_sheets = pd.read_excel('业务数据.xlsx', sheet_name=None) detail = all_sheets['订单明细'] store = all_sheets['门店表']

字典的键是 Sheet 名，值是 DataFrame，操作起来非常舒服。还有一个常见问题：Excel 里会有“合计行”“空白行”“单位行”。其实这些都不是解析错误，而是 Excel 本身的生产习惯。如果读取后发现数据头部或尾部有多余内容，可以用 skiprows 跳过，用 dropna 清理。

建议在项目开始前先统一约定一份 Excel 模板，表头固定在第 1 行，不要合并单元格，不要写“单位：万元”这种行，日期列统一格式。模板定得好，后面所有脚本都能稳定复用。

7.3 输出Excel的常见坑

写回 Excel 时，最容易被骂的是“你给我的这个表怎么不能透视”。一般原因是 index=True，把分组生成的索引也写进表格了，列名对不上。写回时统一 index=False：

result.to_excel('结果.xlsx', sheet_name='汇总', index=False)

如果要写多个 DataFrame 到一个 Excel 文件，用 ExcelWriter：

with pd.ExcelWriter('统计结果.xlsx', engine='openpyxl') as writer: result1.to_excel(writer, sheet_name='月度汇总', index=False) 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(bold=True) 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(['门店', '月份'], observed=True) .agg(销售额合计=('销售额', 'sum')) .reset_index() ) # 第四步：透视表 pivot = pd.pivot_table( df, values='销售额', index='门店', columns='月份', aggfunc='sum', fill_value=0, margins=True, margins_name='总计' ) # 第五步：交叉表 freq = pd.crosstab(df['门店'], df['品类']) # 第六步：输出 with pd.ExcelWriter('零售统计结果.xlsx', engine='openpyxl') as writer: monthly.to_excel(writer, sheet_name='月度汇总', index=False) pivot.to_excel(writer, sheet_name='门店月份透视') freq.to_excel(writer, sheet_name='门店品类频数')

这段代码的逻辑很直白：先转换类型和日期，再按门店 + 月份分组，之后分别生成透视表和交叉表，最后写进一个 Excel 文件。如果把 read_excel 和真实文件路径补上，这套脚本可以直接应用到日常月报里。