各项工具大pk，分组聚合哪家强？

矫情吗；* 2023-01-06 15:59 230阅读 0赞

![format_png][]

> 小小明:「凹凸数据」专栏作者，Pandas数据处理专家，致力于帮助无数数据从业者解决数据处理难题。

凹凸们，大家好

先看一个小需求，其实是很常见的分组聚合问题。

![format_png 1][]

今天我将带大家分别使用`MySQL`、`Excel`、`Pandas`、`VBA`和`Python`来实现这个需求。

这么齐全的应该算是全网首发吧！当然也是为了让大家对分组聚合代码层面的实现能够更加熟悉。

![format_png 2][]

## MySQL实现分组统计 ##

SQL语句：

SELECT 
      deal_date,
      SUM(IF(AREA= 'A区', 1, 0)) 'A区',
      SUM(IF(AREA= 'B区', 1, 0)) 'B区',
      SUM(IF(AREA= 'C区', 1, 0)) 'C区' 
    FROM
      order_info 
    GROUP BY deal_date ;

结果：

![format_png 3][]

## Excel实现分组统计 ##

首先创建数据透视表：

![format_png 4][]

然后将对应的字段拖动到正确的位置：

![format_png 5][]

然后打开透视表选项取消这两项勾选即可：

![format_png 6][]

## Pandas进行分组统计 ##

读取数据：

import pandas as pd
    
    df = pd.read_csv("data.csv", encoding="gb18030")
    df

结果：

<table> 
 <thead> 
  <tr> 
   <th><br></th> 
   <th>order_id</th> 
   <th>price</th> 
   <th>deal_date</th> 
   <th>area</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>0</td> 
   <td>S001</td> 
   <td>10</td> 
   <td>2019/1/1</td> 
   <td>A区</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>S002</td> 
   <td>20</td> 
   <td>2019/1/1</td> 
   <td>B区</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>S003</td> 
   <td>30</td> 
   <td>2019/1/1</td> 
   <td>C区</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>S004</td> 
   <td>40</td> 
   <td>2019/1/2</td> 
   <td>A区</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>S005</td> 
   <td>10</td> 
   <td>2019/1/2</td> 
   <td>B区</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>S006</td> 
   <td>20</td> 
   <td>2019/1/2</td> 
   <td>C区</td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td>S007</td> 
   <td>30</td> 
   <td>2019/1/3</td> 
   <td>A区</td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td>S008</td> 
   <td>40</td> 
   <td>2019/1/3</td> 
   <td>C区</td> 
  </tr> 
 </tbody> 
</table>

使用数据透视表操作：

df.pivot_table(values="order_id", index="deal_date",
                   columns="area", aggfunc="count", fill_value=0)

上述代码相当于groupby操作：

df.groupby(["deal_date", "area"])["order_id"].count().unstack(1, fill_value=0)

但我一般会这样写：

df.groupby(["deal_date", "area"]).size().unstack(1, fill_value=0)

结果均为：

![format_png 7][]

## VBA实现分组统计 ##

经过近1小时的痛苦的尝试，终于编写出了下面这段VBA代码，它模拟实现了分组计数的过程：

Option Explicit
    Function is_exists(name As String)
    Dim sht As Worksheet
    For Each sht In Worksheets
       If sht.name = name Then
          is_exists = True
          Exit Function
       End If
    Next
    is_exists = False
    End Function
    
    Sub 分组统计()
        Dim LastRow, LastCol As Long
        Dim Sh As Worksheet
        'Sh指代当前活动页
        Set Sh = Sheets("data")
        '当前活动页的最后一行
        LastRow = Sh.Cells(Rows.Count, 1).End(xlUp).row
        '当前活动页的最后一列
        LastCol = Sh.Cells(1, Columns.Count).End(xlToLeft).Column
        '定义D为字典
        Dim D As Object
        Set D = CreateObject("Scripting.Dictionary")
        Dim row, i As Integer
        Dim key, value As String
        
        For i = 2 To LastRow
            key = Sh.Cells(i, 3).value
            value = Sh.Cells(i, 4).value
            '如果在字典里
            If Not D.exists(key) Then
                D.Add key, Array(0, 0, 0)
            End If
            row = D(key)
            If value = "A区" Then
                row(0) = row(0) + 1
            ElseIf value = "B区" Then
                row(1) = row(1) + 1
            ElseIf value = "C区" Then
                row(2) = row(2) + 1
            End If
            D(key) = row
        Next
        '调试输出字典存储的内容
        For Each key In D.keys()
            Debug.Print key & "," & Join(D(key), ",")
        Next
        
        Dim sht As Worksheet
        If is_exists("result") Then
            Sheets("result").Delete
        End If
        
        '在最后的位置增加一个sheet作为结果表
        Sheets.Add After:=Sheets(Sheets.Count)
        Set sht = Sheets(Sheets.Count)
        sht.name = "result"
        
        '屏幕刷新=false
        Application.ScreenUpdating = False
        '下面写出数据到结果表中，首先写出标题行
        sht.Range("A1").Resize(1, 4) = Application.Transpose(Array("deal_date", "A区", "B区", "C区"))
        sht.Range("A2").Resize(D.Count, 1) = Application.Transpose(D.keys)
        i = 2
        For Each row In D.items()
            sht.Cells(i, 2).Resize(1, 3) = row
            i = i + 1
        Next
        Application.ScreenUpdating = True
        
    End Sub

运行前：

![format_png 8][]

点击按钮运行后：

![format_png 9][]

立即窗口和工作表都看到了正确的结果输出，立即窗口看到重复2次的输出是因为我连续运行了两次。

## Python实现分组计数 ##

实现代码：

import csv
    from collections import namedtuple
    
    result = {}
    columns = ["A区", "B区", "C区"]
    areas_map = dict(zip(columns, range(len(columns))))
    with open("data.csv", encoding="gb18030") as f:
        f_csv = csv.reader(f)
        headers = next(f_csv)
        resultSet = namedtuple("resultSet", headers)
        for r in f_csv:
            row = resultSet(*r)
            areas = result.setdefault(row.deal_date, [0, 0, 0])
            areas[areas_map[row.area]] += 1
    result

结果：

{'2019/1/1': [1, 1, 1], '2019/1/2': [1, 1, 1], '2019/1/3': [1, 0, 1]}

借助Pandas转换为表结构方便查看：

pd.DataFrame.from_dict(result, 'index', columns=["A区", "B区", "C区"])

结果：

下面用Python模拟一下Pandas数据透视表实现分组统计的过程：

import csv
    from collections import namedtuple, Counter
    
    result = Counter()
    with open("data.csv", encoding="gb18030") as f:
        f_csv = csv.reader(f)
        headers = next(f_csv)
        resultSet = namedtuple("resultSet", headers)
        for r in f_csv:
            row = resultSet(*r)
            result[(row.deal_date, row.area)] += 1
    result

结果：

Counter({('2019/1/1', 'A区'): 1,
             ('2019/1/1', 'B区'): 1,
             ('2019/1/1', 'C区'): 1,
             ('2019/1/2', 'A区'): 1,
             ('2019/1/2', 'B区'): 1,
             ('2019/1/2', 'C区'): 1,
             ('2019/1/3', 'A区'): 1,
             ('2019/1/3', 'C区'): 1})

第二步Pandas还需再对这个结果进行重塑才得到最终所需要的结果，具体重塑的过程实际实现较为复杂，但可以借助category的Series模拟实现一下：

indexs = result.keys()
    index = pd.Series(map(lambda x: x[0], indexs), dtype='category')
    columns = pd.Series(map(lambda x: x[1], indexs), dtype='category')
    values = result.values()
    
    data = np.zeros((len(index.cat.categories), len(columns.cat.categories)))
    for x, y, v in zip(index.cat.codes, columns.cat.codes, values):
        data[x, y] = v
    result = pd.DataFrame(data, index=index.cat.categories,
                          columns=columns.cat.categories, dtype='int8')
    result

结果：

## 总结 ##

其实不管用什么语言和工具，分组聚合统计的核心原理都是：

![format_png 10][]

今天我给大家同时演示了MySQL、Excel、Pandas、VBA和Python实现分组聚合，通过对比，或许大家能自己总结出各项工具的优劣和适用场景。

欢迎你在下方评论区留言，发表你的看法，给大家分享和互动。

![format_png 11][]

????《**数据结构和算法基础Python语言实现**》60个好玩的实例代码+300幅图表+模块化编程，从找出并简化核心问题到估算数据规模和算法复杂度，再到编写代码，帮你掌握数据结构和算法这一核心技能！点击可看详情/购买！????

![format_png 12][]

感谢北京大学出版社支持！

**后台回复关键词「****进群****」，即刻加入读者交流群~**

![format_png 13][]

“**在看**”和“**点赞**”是对文章最好的支持** ![format_png 14][]**

[format_png]: /images/20221119/fb599d038c12437098a5d7f28d06aa19.png
[format_png 1]: /images/20221119/205a18b0c7c5408a833e111393b83174.png
[format_png 2]: /images/20221119/c95825f9c7a34b82b8cfdcef9c9a3213.png
[format_png 3]: /images/20221119/b4f0e6e1d9cc40e1b40a053938a88235.png
[format_png 4]: /images/20221119/65fc304b9213439cb7cdd4dba05d2c40.png
[format_png 5]: /images/20221119/c2602a49cf834453896e0010e89be934.png
[format_png 6]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy90WFlpY3Q0MHhmTGc2TkNpY0NidzNEc2NSNjBYQmtnVGljOWFzME9pYlU3SjhQMXM5a1FxQ2F4NlozdzBNTVQ5VWxBa2libzJhZWljYWljWWg2TmRvN1VNaWI4blp3LzY0MA?x-oss-process=image/format,png
[format_png 7]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy90WFlpY3Q0MHhmTGc2TkNpY0NidzNEc2NSNjBYQmtnVGljOTREandtRGU2ZXZ2M1VQcVF1YmpLdU5RdWZ4NHlCbXBIRmdINEs0Q0swbjRRb0FZeWtQVVphUS82NDA?x-oss-process=image/format,png
[format_png 8]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy90WFlpY3Q0MHhmTGc2TkNpY0NidzNEc2NSNjBYQmtnVGljOXIxSXlJak1LQmU4aWNBMmJQcmJ0M3B1RnloaWNlckVuR3BCcEJ2RkNPRXNTUGVPQkxkRGliZ2huQS82NDA?x-oss-process=image/format,png
[format_png 9]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy90WFlpY3Q0MHhmTGc2TkNpY0NidzNEc2NSNjBYQmtnVGljOTJ5VUE5ZjNwSTIzZWU0UlgzM25LZHVwbGdPbWtiYnpWTFlreEZCN0MyNEZMNzlpYjB0aWJKaWJSQS82NDA?x-oss-process=image/format,png
[format_png 10]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy90WFlpY3Q0MHhmTGc2TkNpY0NidzNEc2NSNjBYQmtnVGljOXZxMXd3OXVaaWIyUFNUMmtaWkc1cFZaem1QSFNpY3ZTQTBQbDFVTjF6WXlSdWppY3QxaWFGdmp5dUEvNjQw?x-oss-process=image/format,png
[format_png 11]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9iOTZDaWJDdDcwaWFiaWFuSUhpYzFoQkFBcDg0aHBNQ2R6NVZpYWFqR3Q2QzJsWGFBWDNpYTExbXR5bnVlQndDckMyRHd1M0FGRmRJd1J3bjM0czJSTUdTMmtWQS82NDA?x-oss-process=image/format,png
[format_png 12]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2pwZy90WFlpY3Q0MHhmTGhPRDZtT1AzZDhaakpmUnYzcHgxT3hUWTFQaWJFRGljdWVnaWFPY3p4bWpGeTA2dzVpYWw1N1czY2RtbWlhYTBGOE9pY0hIdU52R05OTDRWS1EvNjQw?x-oss-process=image/format,png
[format_png 13]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2dpZi90WFlpY3Q0MHhmTGdpYWZHRm1XdFZWSEZWSzdpYlJ0eUVKT1hNczV2VjJBdVZIOFhVOTJKSlA0Y3dxbHRVSzBTTndDS01hZkNsMm9ZV3B4NjN1RXkyWDE5dy82NDA?x-oss-process=image/format,png
[format_png 14]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2dpZi9tbUYwclRqTW1tS1pJVUlMWFRJdEVldDZpYk05MGxJV2hLaWJIR25OZHlRaWN3NXpIQ2ptakl0dUltVUhIZ0Iyb05KNWNxNDFwOXhvbHYzY0QzcHVKTlo1dy82NDA?x-oss-process=image/format,png