专栏首页凹凸玩数据各项工具大pk,分组聚合哪家强?

各项工具大pk,分组聚合哪家强?

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

凹凸们,大家好

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

今天我将带大家分别使用MySQLExcelPandasVBAPython来实现这个需求。

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

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 ;

结果:

Excel实现分组统计

首先创建数据透视表:

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

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

Pandas进行分组统计

读取数据:

import pandas as pd

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

结果:

order_id

price

deal_date

area

0

S001

10

2019/1/1

A区

1

S002

20

2019/1/1

B区

2

S003

30

2019/1/1

C区

3

S004

40

2019/1/2

A区

4

S005

10

2019/1/2

B区

5

S006

20

2019/1/2

C区

6

S007

30

2019/1/3

A区

7

S008

40

2019/1/3

C区

使用数据透视表操作:

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)

结果均为:

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

运行前:

点击按钮运行后:

立即窗口和工作表都看到了正确的结果输出,立即窗口看到重复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区"])

结果:

A区

B区

C区

2019/1/1

1

1

1

2019/1/2

1

1

1

2019/1/3

1

0

1

下面用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

结果:

A区

B区

C区

2019/1/1

1

1

1

2019/1/2

1

1

1

2019/1/3

1

0

1

总结

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

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

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

本文分享自微信公众号 - 凹凸数据(alltodata),作者:小小明

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2021-01-14

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 太强了!这个 Jupyter notebook 离线工具可以用一辈子!

    我是 Jupyter notebook 的老忠实用户了,直到现在一直用。但在使用过程中,一直觉得有个地方不是很方便。

    朱小五
  • Jupyter Notebook最强指南,没有之一

    Python语言是一种强大而简洁的编程语言。据IEEE Spectrum消息,Python在2020年继续蝉联最受欢迎的编程语言第一名。对于刚接触Python的...

    朱小五
  • 爬取3万景点,分析十一哪里人从众从人?

    首先,我们来明确一下我们想要爬取的数据是哪些,这里为了方便起见,我们先以目前国内最热门的城市——杭州为例:

    朱小五
  • Visor - 把你的终端变成Quake式的下拉形式

    如果你经常使用终端程序,这个小程序一定可以帮到你。他能将终端窗口变成类似雷神或CS那样的下拉窗口,非常酷。

    EltonZheng
  • 既然有 HTTP 请求,为什么还要用 RPC 调用?

    HTTP协议,以其中的Restful规范为代表,其优势很大。它可读性好,且可以得到防火墙的支持、跨语言的支持。而且,在去年的报告中,Restful大有超过RPC...

    week
  • VUE+WebPack游戏设计:实现盒子爆破效果和界面美化

    望月从良
  • 那些年下过的大雨

    想了解一下用纯CSS和JS怎么实现一段下雨的动画,于是去CodePen上面搜了一下,发现了很多很有意思的东西。有空可以常去上面逛逛,在对技术产生敬畏的同时也能学...

    糊糊糊糊糊了
  • Flutter基础widgets教程-CupertinoSwitch篇

    青年码农
  • Flutter基础widgets教程-SnackBar篇

    青年码农
  • 构建ASP.NET MVC4+EF5+EasyUI+Unity2.x注入的后台管理系统(13)-系统日志和异常的处理③

    上一节我们讲了如何捕获异常和记录日志,这一节我们讲,没有捕获的或者忘记捕获的异常包括404错误等,我们统一处理这个异常。 这一讲是利用 Application_...

    用户1149182

扫码关注云+社区

领取腾讯云代金券

,,