专栏首页小数志SQL、Pandas和Spark:如何实现数据透视表?

SQL、Pandas和Spark:如何实现数据透视表?

导读

数据透视表是一个很重要的数据统计操作,最有代表性的当属在Excel中实现(甚至说提及Excel,个人认为其最有用的当属三类:好用的数学函数、便捷的图表制作以及强大的数据透视表功能)。所以,今天本文就围绕数据透视表,介绍一下其在SQL、Pandas和Spark中的基本操作与使用,这也是沿承这一系列的文章之一。

01 数据透视表简介

数据透视表,顾名思义,就是通过对数据执行一定的"透视",完成对复杂数据的分析统计功能,常常伴随降维的效果。例如在Excel工具栏数据透视表选项卡中通过悬浮鼠标可以看到这样的描述:

在上述简介中,有两个关键词值得注意:排列汇总,其中汇总意味着要产生聚合统计,即groupby操作;排列则实际上隐含着使汇总后的结果有序。当然,如果说只实现这两个需求还不能完全表达出数据透视表与常规的groupby有何区别,所以不妨首先看个例子:

给定经典的titanic数据集,我们需要统计不同性别下的生还人数,则可以进行如下设置:

而后,即可得到如下结果:

通过上表,明显可以看出女性中约有2/3的人得以生还,而男性中则仅有不到20%的人得以生还。当然,这是数据透视表的最基本操作,大家应该也都熟悉,本文不做过多展开。

值得补充的是:实际上为了完成不同性别下的生还人数,我们完全可以使用groupby(sex, survived)这两个字段+count实现这一需求,而数据透视表则仅仅是在此基础上进一步完成行转列的pivot操作而已。理解了数据透视表的这一核心功能,对于我们下面介绍数据透视表在三大工具中的适用将非常有帮助!

02 Pandas实现数据透视表

在三大工具中,Pandas实现数据透视表可能是最为简单且又最能支持自定义操作的工具。首先,给出一个自定义的dataframe如下,仅构造name,sex,survived三个字段,示例数据如下:

基于上述数据集实现不同性别下的生还人数统计,运用pandas十分容易。这里给出Pandas中数据透视表的API介绍:

可以明显注意到该函数的4个主要参数:

  • values:对哪一列进行汇总统计,在此需求中即为name字段;
  • index:汇总后以哪一列作为行,在此需求中即为sex字段;
  • columns:汇总后以哪一列作为列,在此需求中即为survived;
  • aggfunc:执行什么聚合函数,在此需求中即为count,该参数的默认参数为mean,但只适用于数值字段。

而后,分别传入相应参数,得到数据透视表结果如下:

上述需求很简单,需要注意以下两点:

  • pandas中的pivot_table还支持其他多个参数,包括对空值的操作方式等;
  • 上述数据透视表的结果中,无论是行中的两个key("F"和"M")还是列中的两个key(0和1),都是按照字典序排序的结果,这也呼应了Excel中关于数据透视表的介绍。

03 Spark实现数据透视表

Spark作为分布式的数据分析工具,其中spark.sql组件在功能上与Pandas极为相近,在某种程度上个人一直将其视为Pandas在大数据中的实现。在Spark中实现数据透视表的操作也相对容易,只是不如pandas中的自定义参数来得强大。

首先仍然给出在Spark中的构造数据:

而后,前面已分析过数据透视表的本质其实就是groupby操作+pivot,所以spark中刚好也就是运用这两个算子协同完成数据透视表的操作,最后再配合agg完成相应的聚合统计。完整的实现数据透视表及其结果如下:

当然,注意到这里仍然是保持了数据透视表结果中行key和列key的有序。

04 SQL中实现数据透视表

这一系列的文章中,一般都是将SQL排在首位进行介绍,但本文在介绍数据透视表时有意将其在SQL中的操作放在最后,这是因为在SQL中实现数据透视表是相对最为复杂的。实际上,SQL中原生并不支持数据透视表功能,只能通过衍生操作来曲线达成需求。

上述在分析数据透视表中,将其定性为groupby操作+行转列的pivot操作,那么在SQL中实现数据透视表就将需要groupby和行转列两项操作,所幸的是二者均可独立实现,简单组合即可。

仍然是在SQL中构造临时数据表,如下:

而后我们采取逐步拆解的方式尝试数据透视表的实现:

1. 利用groupby实现分组聚合统计,这一操作非常简单:

2.对上述结果执行行转列,实现数据透视表。这里,SQL中实现行转列一般要配合case when,简单的也可以直接使用if else实现。由于这里要转的列字段只有0和1两种取值,所以直接使用if函数即可:

上述SQL语句中,仅对sex字段进行groupby操作,而后在执行count(name)聚合统计时,由直接count聚合调整为两个count条件聚合,即:

  • 如果survived字段=0,则对name计数,否则不计数(此处设置为null,因为count计数时会忽略null值),得到的结果记为survived=0的个数;
  • 如果survived字段=1,则对name计数,否则不计数,此时得到的结果记为survived=1的个数。

这样,得到的结果就是最终要实现的数据透视表需求。

值得指出,这里通过if条件函数来对name列是否有实际取值+count计数实现聚合,实际上还可以通过if条件函数衍生1或0+sum求和聚合实现,例如:

当然,二者的结果是一样的。

以上就是数据透视表在SQL、Pandas和Spark中的基本操作,应该讲都还是比较方便的,仅仅是在SQL中需要稍加使用个小技巧。希望能对大家有所帮助,如果觉得有用不妨点个在看!

本文分享自微信公众号 - 小数志(Datazhi),作者:luanhz

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

原始发表时间:2021-05-22

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

我来说两句

0 条评论
登录 后参与评论

相关文章

  • SQL、Pandas和Spark:常用数据查询操作对比

    当今信息时代,数据堪称是最宝贵的资源。沿承系列文章,本文对SQL、Pandas和Spark这3个常用的数据处理工具进行对比,主要围绕数据查询的主要操作展开。

    luanhz
  • EMPS:个人做数据分析处理的4重境界

    自从事数据科学行业以来,便每天在与各种数据处理打交道,当然这里的数据处理是多方面的:既有数据采集和读写,也有数据清洗与变换,当然还有数据分析和挖掘。从主用工具的...

    luanhz
  • 使用Spark轻松做数据透视(Pivot)

    spark从1.6开始引入,到现在2.4版本,pivot算子有了进一步增强,这使得后续无论是交给pandas继续做处理,还是交给R继续分析,都简化了不少。大家无...

    麒思妙想
  • 写在 Spark3.0 发布之后的一篇随笔

    Spark3.0 从2019年开始就说要准备发布了,然后就一直期待这个版本,毕竟对于 Spark 而言,这是一个大版本的跨越,从 2.4 直接到了 3.0,而之...

    哒呵呵
  • Sql 实现数据透视表功能

    要讲怎么在 Sql 中做透视表,我们还是先看看什么是透视表,其实透视表的核心就是按照行列同时分组,然后对分组后的值进行某种汇总运算。

    张俊红
  • SQL、Pandas和Spark:这个库,实现了三大数据分析工具的大一统

    看过近期推文的读者,想必应该知道笔者最近在开一个数据分析常用工具对比的系列,主要是围绕SQL、Pandas和Spark三大个人常用数据分析工具,目前已完成了基本...

    luanhz
  • PySpark SQL——SQL和pd.DataFrame的结合体

    昨日推文PySpark环境搭建和简介,今天开始介绍PySpark中的第一个重要组件SQL/DataFrame,实际上从名字便可看出这是关系型数据库SQL和pan...

    luanhz
  • 用于ETL的Python数据转换工具详解

    做 数据仓库系统,ETL是关键的一环。说大了,ETL是数据整合解决方案,说小了,就是倒数据的工具。回忆一下工作这么些年来,处理数据迁移、转换的工作倒 还真的不...

    砸漏
  • Spark 2.3.0 重要特性介绍

    为了继续实现 Spark 更快,更轻松,更智能的目标,Spark 2.3 在许多模块都做了重要的更新,比如 Structured Streaming 引入了低延...

    smartsi
  • Hudi实践 | Apache Hudi在Hopsworks机器学习的应用

    Hopsworks特征存储库统一了在线和批处理应用程序的特征访问而屏蔽了双数据库系统的复杂性。我们构建了一个可靠且高性能的服务,以将特征物化到在线特征存储库,不...

    大数据技术架构
  • 【学习】50+数据科学与机器学习速查表

    关于Python、R和Numpy、Scipy以及Pandas的速查表 有了这些和R语言、python、Django、MySQL、SQL、Hadoop、Apach...

    小莹莹
  • Apache Spark 3.0.0重磅发布 —— 重要特性全面解析

    2020年6月18日,开发了近两年(自2018年10月份至今)的Apache Spark 3.0.0正式发布!

    大数据学习与分享
  • Apache Spark 3.0.0重磅发布 —— 重要特性全面解析

    2020年6月18日,开发了近两年(自2018年10月份至今)的Apache SparkTM 3.0.0正式发布!

    大数据学习与分享
  • 【数据科学】50+数据科学与机器学习速查表

    关于Python、R和Numpy、Scipy以及Pandas的速查表 有了这些和R语言、python、Django、MySQL、SQL、Hadoop、Apach...

    陆勤_数据人网
  • SQL、Pandas、Spark:窗口函数的3种实现

    窗口函数是数据库查询中的一个经典场景,在解决某些特定问题时甚至是必须的。个人认为,在单纯的数据库查询语句层面【即不考虑DML、SQL调优、索引等进阶】,窗口函数...

    luanhz
  • Apache Hudi在Hopsworks机器学习的应用

    Hopsworks特征存储库统一了在线和批处理应用程序的特征访问而屏蔽了双数据库系统的复杂性。我们构建了一个可靠且高性能的服务,以将特征物化到在线特征存储库,不...

    ApacheHudi
  • Spark vs Dask Python生态下的计算引擎

    对于 Python 环境下开发的数据科学团队,Dask 为分布式分析指出了非常明确的道路,但是事实上大家都选择了 Spark 来达成相同的目的。Dask 是一个...

    Ewdager
  • 数据流编程教程:R语言与DataFrame

    DataFrame DataFrame 是一个表格或者类似二维数组的结构,它的各行表示一个实例,各列表示一个变量。 一. DataFrame数据流编程 ? 二....

    小莹莹
  • 浅谈pandas,pyspark 的大数据ETL实践经验

    本文主要以基于AWS 搭建的EMR spark 托管集群,使用pandas pyspark 对合作单位的业务数据进行ETL ---- EXTRACT(抽取)、T...

    流川疯

扫码关注云+社区

领取腾讯云代金券