stream 多个字段分组_Python Pandas对Excel数据的分组聚合和数据透视

      使用Excel进行商业数据分析的时候，最重要的就是两个手段就是vlookup函数和数据透视表。本章就讲解一下与数据透视功能相关的分组聚合和数据透视。其实分组聚合和数据透视两者基本是等价的，但由于使用的函数不一样，一个是groupby，一个是pivot_table，所以讲解的时候还是把两者分开讲解。

一、分组聚合分组聚合的核心函数就是groupby。

DataFrame.groupby(by=None, axis=0, level=None, as_index=True, sort=True, group_keys=True, squeeze=False, **kwargs)

• by :接收映射、函数、标签或标签列表；用于确定聚合的组。

• axis : 接收 0/1；用于表示沿行(0)或列(1)分割。

• level : 接收int、级别名称或序列，默认为None；如果轴是一个多索引(层次化)，则按一个或多个特定级别分组。

• as_index：接收布尔值，默认Ture；Ture则返回以组标签为索引的对象，False则不以组标签为索引。

       首先我们导入需要分析的Excel数据，有A、B、C三家公司，款项分为主营业务收入、应交税费、其他应付款三类，后面就是上半年和下半年的款项金额(万)。

import pandas as pdimport numpy as np df = pd.read_excel(r'C:\Users\56331\Desktop\test.xlsx')df

1、聚合计算

统计3个公司上半年的总营业额(三个款项加起来)

# 首先先按照公司名称和上半年进行分组ret = df.groupby(['公司名称'])['上半年']# 分好组之后再进行相关的计算ret.sum()>>>公司名称A公司    46327B公司    46864C公司    47497Name: 上半年, dtype: int64

也可以用另外一种形式，结果一致

ret = df['上半年'].groupby(df['公司名称'])ret.sum()>>>公司名称A公司    46327B公司    46864C公司    47497Name: 上半年, dtype: int64

由此可见，其他的数学计算函数max、min、std、mean、median等等也都是可以使用的

2、apply 函数       对于groupby后的apply，以分组后的子DataFrame作为参数传入指定函数的，基本操作单位是DataFrame，而之前介绍的apply的基本操作单位是Series。       还以最开始的数据为例，求三家公司上半年的款项次数、总金额、平均数(手动保留两位小数)、最小值。

def analysis(x):    return x.count(),x.sum(),np.round(x.mean()),x.min()ret = df.groupby(['公司名称'])['上半年']ret.apply(analysis)>>>公司名称A公司    (29, 46327, 1597.0, 1025)B公司    (30, 46864, 1562.0, 1000)C公司    (32, 47497, 1484.0, 1033)Name: 上半年, dtype: object

当需要根据两个维度来进行聚合的时候，如下所示

g = df.groupby(['公司名称','款项类型'])g.mean()

       这个时候会出现一个问题，就是上半年和下半年这两个索引会上移，如果需要与其他表匹配的时候，这个格式就有些麻烦，所以我们需要做一些调整，将 as_index 改为 False ，默认是 Ture 。

g = df.groupby(['公司名称','款项类型'], as_index=False)g.mean()

3、agg 函数

agg聚合可以对多个列做相同的聚合，也可以针对不同列做不同的聚合示例1：对多个列做相同的聚合，如对上半年和下半年都计算均值

g = df.groupby(['公司名称','款项类型'])g.agg('mean')

示例2：对多个列做不同的聚合，如对上半年计算和，对下半年计算均值

g = df.groupby(['公司名称','款项类型'])g.agg({'上半年' : 'sum', '下半年' : 'mean'})

4、transform 函数

      对transform而言，则会对每一条数据求得相应的结果，同一组内的样本会有相同的值，组内求完均值后会按照原索引的顺序返回结果       以原数据为例，各公司以下半年为考核标准，新增一列，那一列每个公司都有相同的下半年均值。

df['下半年均值'] = df.groupby('公司名称')['下半年'].transform('mean')df

二、数据透视       数据透视表是我们使用Excel对数据进行处理分析最重要的手段之一，它是一种可以对数据动态排布并且分类汇总的表格格式，而在pandas中它被称作pivot_table。

pivot_table(data, values=None, index=None, columns=None,aggfunc='mean', fill_value=None, margins=False, dropna=True, margins_name='All')

      pivot_table有四个最重要的参数index、values、columns、aggfunc，本文以这四个参数为中心讲解pivot操作是如何进行。

       还是以前面导入的数据为例

df

1、index参数       

       每个pivot_table必须拥有一个index，Index就是层次字段，要通过透视表获取什么信息就按照相应的顺序设置字段，如果想查看每个公司的均值(默认)，首先我们将公司名称设置为index：

df.pivot_table(index = '公司名称')

如果想按照两个维度，比如公司名称和款项类型进行透视，类型为求和。

df.pivot_table(index = ['公司名称', '款项类型'], aggfunc = 'sum')

2、values 参数       

       Values可以对需要的计算数据进行筛选。如我们只对上半年进行透视，计算其均值。

df.pivot_table(index = ['公司名称', '款项类型'], values = '上半年')

3、aggfunc 参数       

       aggfunc参数可以设置对数据聚合时进行的函数操作，默认为mean。可以设置为其他各种类型。如只求上半年各款项的中位数。

df.pivot_table(index = ['公司名称', '款项类型'], values = '上半年', aggfunc = 'median')

4、columns 参数       

       columns类似Index可以设置列层次字段，它不是一个必要参数，作为一种分割数据的可选方式。如将公司作为index，将款项类型作为columns，进行求和。

df.pivot_table(index = ['公司名称'], columns = [ '款项类型'], values = ['上半年', '下半年'], aggfunc = 'sum')

三、索引重置及查询

1、reset_index       

       从上面的很多聚合和透视可以看出，会出现列索引上移的现象，如果需要与其他表匹配的时候，这个格式就有些麻烦，所以我们需要做一些调整，上面也介绍了一个，对于groupby函数中我们可以将 as_index 改为 False ，默认是 Ture，可以解决这个问题 。      有些情况比如使用数据透视表的时候就没有这样的参数，所以我们需要使用reset_index来重新设置索引，如下所示：

df.pivot_table(index = ['公司名称', '款项类型'], aggfunc = 'sum')

使用reset_index重置索引

df.pivot_table(index = ['公司名称', '款项类型'], aggfunc = 'sum').reset_index()

2、query     

       对于数据透视出来的数据，我们想进行查询某个索引对应值的时候，可以使用query函数。比如对于这个数据透视，查询其中A公司的数据：

table = df.pivot_table(index = ['公司名称', '款项类型'], aggfunc = 'sum')table.query('公司名称 == ["A公司"]')

总结：

pd.pivot_table(df,index=[字段1],values=[字段2],aggfunc=[函数],fill_value=0)
df.groupby([字段1])[字段2].agg(函数).fillna(0)

        对于groupby和pivot_table这两个函数的区别，基本上是等价的，其中pivot_table加入了columns与margin等参数，使用起来比groupby更加灵活。