数据准备:用Prep Builder清理数据与调整结构(上)

第三章 数据准备:用Prep Builder清理数据与调整结构

——摘自《数据可视化分析:Tableau原理与实践》 喜乐君

关键词:数据清理、数据转置、数据聚合、FIXED LOD

在实际的数据分析过程中,超过一半时间会用在数据的整理和合并上,这不仅让业务人员对数据分析望而却步,也影响了可视化分析的质量。Tableau提供了直观、灵活的准备数据的方法,简单的整理和合并可以在Tableau Desktop(简称Desktop)中直接完成,复杂过程则可以借助于Tableau Prep Builder(简称Prep Builder),并通过Tableau Data Management中的Prep Conductor实现流程自动化。

本章将数据整理分为“数据字段整理”与“数据结构调整”两大类。字段整理包括修改数据类型、拆分字段、清理异常值、筛选数据等多种方法,并能借助“计算字段”来实现更多的整理。数据结构调整主要指数据转置和数据聚合。

作为专门的数据准备工具,本章重点介绍Prep Builder的使用,以及其与Desktop的使用差异。其中,数据聚合部分属于中高级内容,建议在学习全书后重读。

3.1  Prep Builder基础操作

Desktop胜在可视化和业务决策,而Prep Builder却帮助我们节约时间和提高效率。2019年之前,Prep Builder还是本地化的数据整理工具,如今借助Tableau Data Management服务器组件,Prep Builder的流程也可以在服务器端自动化运行并设置计划,进一步提高了中大型企业的处理效率。

打开Prep Builder中自带的超市流程案例,如图3-1所示,Prep Builder主界面由数据连接、流程面板和数据预览面板多个功能区组成。

图3‑1  Prep Builder软件的主要功能区域

其中,上方的“流程面板”是Prep Builder的关键,流程是由节点前后连接组成的。

图3‑2  Prep Builder中各种图标的功能

数据整理通常是多步骤操作,多个节点组成先后相续的流程。根据分析的需要,可以在每个节点的前、后、双节点中间增加节点,非常方便,如图3-3所示。

图3‑3  Prep Builder在任意环节快速添加节点

选择任意一个节点,默认打开下面的配置窗格,配置窗格随着类型的不同而不同,在“连接”节点可以设置连接字段,在“聚合”节点可以设置聚合字段。如图3-4所示,“配置窗格”“数据预览窗格”和“列表窗格”多种显示方式,有助于查看数据结果和针对标题进行二次清理。

图3‑4  Prep Builder多种数据窗格

总结多年使用Prep Builder的经验后,笔者有如下几条建议。

  • 主流程保持在一条水平线上,特别是在多次连接时,以避免混乱;
  • 数据清理优先于数据合并和结构调整,特别是字段筛选、数据筛选、字段重命名、更改数据类型等,有助于减少重复操作,提高数据处理性能;
  • 任意节点都可以用鼠标右击更改节点颜色(见图3-2),通过节点的颜色辨别数据并集、数据连接的字段来源;笔者通常把主流程节点多选统一改为深色。
  • 节点名称简单清晰,在“说明”中补充节点的主要操作内容,有助于数据模型长期可用;
  • 如果数据整理需要自动化运行,则推荐使用Tableau Data Management。

本章主要介绍如何使用Prep Builder完成数据清理和整理,数据转置和聚合,第4章将介绍并集、连接和混合及高级聚合功能(LOD、Rank、Tile等聚合函数)。

3.2  ✅初级字段整理:理解拆分、分组、筛选等皆计算

数据清理是数据准备过程中最烦琐的过程,必须精准地定位问题,然后改正。数据清理有两个基本目标:改正或排除错误数据(典型如清理、筛选)、根据分析需求调整数据(典型如拆分、分组)。

从数据清理的实操中看,清理可以分为两大类:针对数据字段的清理和针对数据内容的整理;前者关注字段标题,后者关注字段数据值。常见操作如图3-5所示。

图3‑5  常见的数据整理类型

针对字段标题和数据值的数据清理,都可以用Desktop或者Prep Builder独立完成,具体使用哪一种工具,取决于清理的复杂性和分析背景。实现的方式有所差异,但基本逻辑完全一致。

比如最常见的“更改字段名称”(重命名)、“复制字段”等功能,如图3-6所示,在Desktop和Prep Builder多个位置都可以通过鼠标右击快速处理。

而点击字段前面的类型标识符(常见Abc字符串)手动则可以快速“更改数据类型”,在Prep Builder中甚至可以借助AI驱动的智能建议快速定位并更改(见图3-7)。此类简单的操作,多加练习,即可游刃有余,本书在介绍这些常规操作的同时,会重点阐述逻辑和思维方法,帮助读者深入地理解Tableau。

图3‑6  修改字段类型和重命名

图3‑7  借助智能推荐快速整理数据

在讲解具体的内容之前,先说明一下Desktop和Prep Builder的不同之处,特别是产品的定位和理念的差异。

(1)Desktop的清理是直接面向可视化分析的,而Prep Builder的清理则完全面向更进一步的整理环节(比如合并、转置、聚合等)。因此,Desktop的数据清理,胜在与可视化分析过程融为一体,有助于保持思维的连贯性;而Prep Builder则胜在专业性,凡是Desktop能完成的数据清理和处理,Prep Builder都能更好地完成,反之不然,多次的数据合并、结构调整必须Prep Builder完成。

打个比方,Desktop的数据清理犹如画家手中的橡皮擦,随检随用,胜在灵活;而Prep Builder则如同容纳各种染料、画笔、灯光的专业工作室,胜在专业,理解了这样的区别,就能理解下面的功能差异,也能更好地理解后面我们即将阐述的“选择Desktop和Prep Builder的使用场景”。

(2)对于字段而言,在Desktop和Prep Builder中都可以“隐藏”“取消隐藏”,而在Prep Builder中还有一个特别的“移除”选项。

Desktop是可视化分析工具,它直接和数据库打交道,自身没有数据库保存。“隐藏”的意思是无须从数据库查询这个字段,但需要查询时,又可以随时“取消隐藏”。如果字段已经使用,则此时就不能被隐藏。笔者通常在仪表板完成后,“隐藏所有未使用的字段”,从而减少数据查询,特别是和数据提取结合使用时更加,因为提取会忽略隐藏的字段。

Prep Builder 从2021.1.4版本开始,借鉴了Desktop的“隐藏”功能,增加了特别好用的“隐藏”新功能,在任何一个节点都可以“隐藏”字段,并在任何环境中“取消隐藏”,但隐藏不影响输出结果,隐藏的字段会在最后输出结果被输出到数据源中。

而在Prep Builder中,更常用的功能是“移除”,在任意节点被“移除”的字段,之后所有的流程节点就不复存在了,想要在后面节点中使用它,必须回到“移除”字段的节点,删除“移除”操作。对比如图3-8所示。

图3‑8  Desktop和Prep Builder在字段上处理的差异(Prep2021.1+增加隐藏功能)

充分理解隐藏和移除的差异后,就可以在Prep Builder中熟练使用了,在不确定字段事后是否使用时可以先“隐藏”,在数据处理完成后,再在节点中“移除”确定不用的字段,这样就可以在敏捷和性能中追求平衡。

可见,隐藏是相对于数据库查询或当前节点而言的;Prep Builder的“移除”是相对于生成的新数据源而言的,被移除的字段在最终结果中不存在,也无法像Desktop一样重新显示。

(3)Desktop不能修改数据内容,而Prep Builder可以直接修改。

这与“隐藏”“移除”类似。Desktop只是从数据库中查询数据,如果能大量更改数据,则需要用“回写数据库”功能,这将引起数据安全风险。但是偶然有这种需求时怎么办?Desktop提供了“别名”,相当于在错误的字段内容上打一个正确的标签。注意,别名仅限于离散的维度,而且多个字段的别名不能重复。如图3-9所示,在Desktop中选择字段后用鼠标右击,在弹出的下拉菜单中选择“别名”,即可在弹出的对话框中设置别名。注意,不要在连续的日期和度量中增加别名。考虑到数据源替换后,别名会自动失效等多个原因,笔者建议谨慎使用这个功能。

图3‑9  Desktop的别名功能

而在Prep Builder中,数据值修改更加安全、稳定。可以更改任意数据,不管是连续维度(如日期),还是连续度量。如图3-10所示,在Prep Builder中选择字段后通过鼠标右击,在弹出的下拉菜单中选择“编辑值”或者直接双击字段,都可以更改错误的数据。如果把“办公用品”改为“家具”,二者就会自动分组。

图3‑10  Prep Builder的数据编辑

Prep Builder通过流程节点分阶段整理数据,每一步都会相对独立,以自身数据引擎压缩、保存了数据,只有这样才能做连续性的复杂整理。从这个角度,Prep Builder虽然是数据整理和准备工具,也可以与Server结合胜任一部分数据仓库的工作——借助于Tableau Data Management,Prep Builder摇身一变就实现了敏捷ETL的流程自动化。

上面是两个软件的关键差异性,一并介绍了几个关键的字段清理功能。接下来,我们介绍其他几个常见功能:数据拆分、数据分组、筛选器、字符串清理。

3.2.1  数据拆分和提取:按照长度、分隔符或规则拆分

很多情况下,我们必须把一个字段拆分为两个甚至更多的字段。拆分包括两个关键的类型:

  1. 指定位置和长度拆分字段值,比如从身份证ID中提取出生年月日,典型的方法有LEFT、RIGHT、MID函数;适用于特别规范的数据字段;

以HR常用的身份证ID为例,如果身份证ID是标准的18位,出生年月日就可以用MID([ID],7,8)来拆分第7位之后的8位数字,提取结果默认是字符串,还需要将其数据类型改为“日期”(对应的类型转换函数DATE,详见第8章)。

同理,可以提取身份证号码第17位,计算其奇偶数推算性别,如图3-11所示。

图3‑11  基于身份证号码截取出生年月和性别

  • 指定分隔符拆分特定部分,比如从“wyp@vizwise.cn”拆分@前的用户名部分(类似Excel的拆分单元格),典型的方法有SPLIT拆分函数、使用LEFT/MID/RIGHD和FIND结合;适用于相对规范的数据字段;

如图3-12所示,超市数据中的“订单ID”,假设我们想要提取其中的区域(CN/US)和日期部分,则可以用LEFT、MID等函数,也可以使用更简单的方式:字符串拆分函数SPLIT。这个函数在两个工具中的体验完全一致。在Prep Builder的操作方法如图3-12所示。

图3‑12  Prep Builder的拆分函数SPLIT

而在Desktop中,选择字段点击,在弹出的下拉菜单中选择“变换→拆分/自定义拆分”命令拆分字段。如图3-13所示,将“订单ID”自动拆分会基于“-”为分隔符创建3个字段。

图3‑13  Desktop的字段拆分功能

  • 指定规则从字符串中提取,比如从文本中提取完整的中文邮箱地址(包含@且后缀为XX.cn),典型的方法是使用“通配符函数”;适用于内容不规范则是又有规则的数据字段。

通配符函数需要熟悉数据类型、计算函数、通配符规则等多个内容,相见第8章。

3.2.2  数据分组:手动合并多个字段值

“分组”是多个字段合并为一组的过程。Tableau的分组功能简单明了,特别是Desktop,可以在分析过程中随时创建,保持思维的连贯性。如图3-14所示,在按住Ctrl键的同时选择多个字段,之后用鼠标右击,在弹出的下拉菜单中选择“组”命令,即可自动创建一个新字段替代当前的字段。

图3‑14  在Desktop中创建分组

而Prep Builder的强大在于提供了更多灵活的分组算法,比如按照拼写、常见字符等方式,还可以自动调整合并的阈值(可以理解为相似度),如图3-15所示。

图3‑15  Prep Builder的“分组和替换”功能

注意,Prep Builder的分组是“分组和替换”功能,也就是用分组的方式,把多个字段内容合并在一起,如图3-16所示,比如把“石药新诺威”“石药中奇制药”“石药恩必普”多个字段替换为“石药集团”,之后就没有原来的3个数据了;而Desktop的分组,是在原来字段的基础上新建一个字段,原来的字段依然保留。

图3‑16  Desktop和Prep Builder分组功能的差异

如图3-17所示,Desktop的分组字段用“曲别针”图标标识,这样就保留了上下的层次关系。而在Prep Builder中,分组字段就是直接合并与替换。在使用Prep Builder进行分组时,建议先“复制字段”再分组,从而保留上下的层次关系。

图 3‑17  用Prep Builder创建分组和编辑成员

在Desktop中,多个字段可以组成“层次结构”,此功能方便在可视化图表中实现层次钻取,而不属于数据整理阶段,无法在数据源实现。在Desktop中如何创建“层次结构”详见第5章。

如果数据分组过程中,需要考虑动态的规则,就要结合更多的计算函数。比如我的客户曾经问: “如何把包含‘小米’的净化器配件自动合并为‘小米配件’?”Tableau中高级用户会在Desktop的分组中发现有一个“查找分组”的功能,它其实就是计算的简化,如下:

IF  CONTAINS([配件名称],’小米’)  THEN  ‘小米配件’  ELSE  ‘其他’  END

归根结底,所有数据处理都是计算函数的组合,软件中的操作只是为了简化初学者的入门难度;因此,熟练掌握字段整理的关键是计算,好在大部分工具中的常见函数都是一致的,详见第8章。

3.2.3  筛选器:布尔计算的典型应用

针对数据内容的清理,都是借助“筛选器”完成的。顾名思义,筛选(Filter)就是筛掉无用的,留下可用的,因此它由两类动作可选:“排除”和“只保留”。在数据分析过程中,筛选器是一个特别庞大的体系,在Desktop中尤为如此,随着分析的深入,大家会逐步认识到它的魅力。

此前,我们对Prep Builder和Desktop的差异性进行了比较,“Desktop为可视化过程而整理,而Prep Builder则为接下来的数据整理而整理”,二者的筛选器用法也不同。Desktop的筛选主要为满足交互访问的需求,比如总经理想看全公司销售情况、东区负责人要看东区销售情况等,因此Desktop的筛选器是“差异化查询”的过程,是随时可以变化的。而Prep Builder筛选器则是去伪存真,是在当前节点删除(Delete)无效的数据,是静态的,和Desktop中可随时调整的查询(Search)不同。

笔者会在第5章5.5节专门介绍Desktop的筛选器。这里,重点介绍Prep Builder筛选器。

在Prep Builder中,在任意字段的字段值上右键,都可以创建“只保留”或“排除”筛选。特别注意,排除筛选时,建议先确认排除排除数据和其他字段的关联关系。Prep Builder为此提供了极其简单的关联查看的方法——高亮显示。如图3-18所示,在想排除某个字段的数据时,可先点击它,从而查看这个数据在其他字段的高亮分布。

图3‑18  Prep Builder高亮选定值在其他字段的分布

考虑到字段的类型,字符串、日期和度量的筛选功能略有差异。筛选的本质是TRUE/FALSE的布尔计算,因此计算是面向中高级用户的通用方法;针对离散的字符串,可以使用“选定值”(单选或者多选),也可以使用“通配符”(相当于前面提及的CONTAINS函数);针对连续的日期和度量,则可以选定范围筛选。这里以【产品名称】字段为例,执行三个筛选方法,如图3-19所示:

  1. 使用计算筛选:保留首字母为A的产品名称;
  2. 使用通配符筛选,保留包含“打孔机”的产品名称;
  3. 使用选定值筛选,保留两个产品名称。

图3‑19  Prep Builder对【产品名称】字段的计算、通配符、选定值多种筛选

相比之下,连续的日期和度量筛选还有特殊性:基于连续的范围筛选。其中,日期又可以包括“绝对范围”和“相对范围”两种方式,前者是基于公历日期构建范围,后者基于日期锚点(通常是今天Today())构建范围。如下图3-20所示,展示了基于【订单日期】字段的绝对范围筛选(从2021年1月1日以来)、相对范围筛选(本年,默认锚点今天)、利润的范围筛选(小于等于0的交易)。

图3‑20  Prep Builder中针对连续字段的筛选:日期筛选与度量筛选

相比于离散的【产品名称】,具有连续性的【订单日期】和【利润】字段虽然可以用“选定值”,但应用更多的是范围,范围对应滑块选择。

注意,每一种设置都包含了更多的选项,比如“日期范围”下可选“范围、最小值、最大值”,而“相对日期”则有更多的选项。每一种选项,都会对应不同的计算。筛选在本质上都是布尔判断(保留true的部分,排除false的部分)。当读者读到第8章计算,将会更好地理解这里的选择。

3.2.4  字符串清理和日期转换:常见函数的选择

Prep Builder有一个功能非常好用——清理,它内置了常见的清理函数工具,如图3-21左侧所示,在处理复杂数据时,经常能一招制胜。Prep Builder2020.1以来的版本中,还把常用的日期转换也设置为自选项,进一步提高了日期清理的易用性,如图3-21右侧所示。

图3‑21  Prep Builder的清理功能和日期转换

这里的设为大写、设为小写,和日期转换都是最常见的行级别函数,本书会在第8章详细介绍,这里以【产品名称】和【日期】字段为例,先把对应关系记录如下,初学者务必要逐步建立对基本函数的认识,以后才能在复杂问题面前游刃有余:

  • 设为大写:UPPER([产品名称])     
    • 设为小写:LOWER([产品名称])
    • 日期和时间:DATETIME([订单日期])   
    • 年数:YEAR([订单日期])
    • 季数:QUARTER([订单日期])
    • 月数:MONTH([订单日期])
    • 月份名称:DATENAME(‘month’, [订单日期])
    • 周数:WEEK([订单日期])
    • 周中某天:DATENAME(‘weekday’, [订单日期])

这样,大家能更好地理解“所有的筛选和清理都是计算”,对于复杂的清理亦然,其特别之处在于复杂的功能需要更高级的函数,比如“自定义财年”是结合了上述日期函数和IF的逻辑判断,而难度更高的“移除字母/数字/表达符号等”,这是使用“正则函数”才能高效完成。Prep Builder的字符串清理,会自动转化为以下REGEXP_REPLACE正则替换函数:

移除字母:REGEXP_REPLACE([产品名称-1], ‘[[:alpha:]]’, ”)

移除数字:REGEXP_REPLACE([产品名称-2], ‘[[:digit:]]’, ”)

移除标点符号:REGEXP_REPLACE([产品名称-3], ‘[[:punct:]]’, ”)

剪裁空格(首尾空格): REGEXP_REPLACE([产品名称-3], ‘(^[[:space:]]*|[[:space:]]*$)’, ”)

移除额外的空格:TRIM(REGEXP_REPLACE([产品名称-3], ‘[[:space:]]+’, ‘ ‘))

移除所有空格:REGEXP_REPLACE([产品名称-3], ‘[[:space:]]’, ”)

初学者初次使用,要了解点击那里,并逐步了解背后的原理,并随着问题复杂度提高,逐渐学会举一反三、游刃有余。

上面我们按照字段标题清理(重命名、类型、移除/隐藏)和字段内容清理(修改、合并、拆分、筛选、清理)两大分类,重点介绍了Prep Builder的数据清理功能,同时介绍了部分功能在Desktop中的实现方法。数据清理和筛选看似功能复杂多样,但相对容易理解,不再一一详述。

后续

3.3  中级结构整理:数据转置

3.4  高级结构整理:数据聚合

3.4.1  聚合的必要性和用法——单一层次聚合
3.4.2  FIXED LOD——指定层次完成聚合,并连接到原数据明细
3.4.3  Prep Builder聚合的注意事项

[1] 这里的“固定LOD”属于明显的翻译过度,笔者多次给Tableau国际化团队提修改建议,已改为Fixed LOD。

@喜乐君

《数据可视化分析:Tableau原理与实践》

发布者:喜乐君

喜乐君 | Tableau Partner,Tableau Desktop and Server QA Certification

发表评论

Fill in your details below or click an icon to log in:

WordPress.com 徽标

您正在使用您的 WordPress.com 账号评论。 注销 /  更改 )

Twitter picture

您正在使用您的 Twitter 账号评论。 注销 /  更改 )

Facebook photo

您正在使用您的 Facebook 账号评论。 注销 /  更改 )

Connecting to %s

%d 博主赞过: