数据准备：用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-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原理与实践》