【Tableau】正则表达式，数据整理中的「王炸」

//本文的升级版本加入了新书《数据可视化分析：Tableau原理与实践》第八章“行级别表达式”部分。欢迎京东、当当、天猫购买喔。//目前正在筹划英文版本，有点狂躁的小目标

之前在官方网站看过一篇讲解正则表达式的英文文章，当时看了感觉甚好，只是没有特别好的场景应用。今天在客户这里遇到了一个问题，正好想起来，于是重读，写一下最基本的使用方法。 文件详见： Become a regex regular & wrangle imperfect data

我是一个经世致用的人，不愿意在形式和炫技上浪费一点时间，更希望能借助数据来解决实用的问题。

有一种场景是数据整理的“天敌”，就是你的数据混乱到不做整理完全无法使用，特别是喜欢用爬虫的人尤其如此。今天我在客户这里见到一个数据，大概率是客户的客户从其他渠道整理来的数据，因此混乱的很。

在计算机的世界，有一种特别聪明的方式称之为：正则表达式，英文是Regular Expressions，简称RegEx。它可以根据我们设置的规则，去查询或者返回特定的字符，比如返回从a到z的任意字符，可以用[a_z]来代替——注意是任意一个字母。那如果要返回ab两个字母呢？可以通过大括号设置匹配的数量，比如[a-z]{2}代表返回任意两个从a到z的字母的两位。

Tableau中提供了四种正则表达式：

• REGEXP_EXTRACT(字符串, 模式) 提取：从字符串中，提取符合某些字符模式的字符
• REGEXP_EXTRACT_NTH(字符串, 模式, 序号) 提取第n个：多次符合条件，符合第n个
• REGEXP_REPLACE(字符串, 模式, 替换字符串)  替换：把某些字符更换为特定字符
• REGEXP_MATCH(字符串, 模式)  匹配：如果字符串中有特征一的某些字符，那么返回true

理解的关键是“模式”，指的是一组特定条件的字符组合，比如从a到z，从A到Z，从0到9 等等。“模式”和单一的字符对应，是可以变化的一组条件。 （ regex,  regex as “a pattern describing a certain amount of text” ）

模式用一个特别的符号来表示——方括号，比如用[A_Z]代表从A到Z的任意字母，[1234]代表从1、2、3、4这四个数之一等等。为什么用方括号呢？熟悉python的朋友都知道，方括号在很多地方都代表集合。

• 方括号：用于查找某个范围内的字符
• 小括号：用于提取你想要返回的字符模式
• 大括号：正则匹配的字符数量​

表达式	描述
[abc]	查找方括号之间的任何字符。
[^abc]	查找任何不在方括号之间的字符。
[0-9]	查找任何从 0 至 9 的数字。
[a-z]	查找任何从小写 a 到小写 z 的字符。
[A-Z]	查找任何从大写 A 到大写 Z 的字符。
[A-z]	查找任何从大写 A 到小写 z 的字符。
[adgk]	查找给定集合内的任何字符。
[^adgk]	查找给定集合外的任何字符。
(red|blue|green)	查找任何指定的选项。

1、REGEXP_EXTRACT(string, pattern)——常用

第一个函数用于提取符合特定条件的部分，比如我们从一组混乱的数据中提取电子邮件地址，提取11位的电话号码。这是最常用的正则函数。结合我自己的一个案例，说明如下：

比如我这里有很多的期权合约代码，格式一般为 CF009C12400.ZCE，分别代表“品种（CF）”、“合约（009）”、“看涨C/看跌P”、“行权价格（12400）”、分隔符（.）、交易所（ZCE）。

每个品种、合约、行权价格的位数都不确定，因此不能使用left、right和mid字符串函数来拆分，不过可以使用已知的模式来返回特定的字符串部分。

• 品种和合约：一位或者两位字母+三位或者四位数字；
• 涨跌及行权价格：C或者P开始+数字，以分隔符(.)结尾
• 交易所：分隔符(.)之后的字母，两位至四位。

因此，品种和合约的正则表达式如下：

‘ ([A-Z]{1,2}+[0-9]{3,4}) ‘

大括号中的数字，代表匹配的位数，因此字母可以是一位（M1707），也可以是两位（CU2003）；同样数字可以是三位，也可以是四位。返回的结果如下：

那，如果除了开头，其中还有符合这个规律的，我要提取如何处理？

我们可以增加条件，但是保持返回的范围不变。

第二组符合条件的字符串可以加一个特征：跟紧在数字0-9之后，这个特征是第一组字符串没有的。表达式如下：

当然，这里的字母只有C或者P两个可能，因此也可以把([A-Z]{1,2} 改为：（C |P ）

REGEXP_EXTRACT([ts_code],'[0-9]+((C|P)+[0-9]{3,4})’)

new：

今天客户提了一个需求，SAP的数据中经常有“前导0”，也就是10位的字符串，不足10位的数字前面都用0补齐，但是这样不适合我们搜索，因此需要把前面的0删除。

这是一个明显具有一定模式的字符串，而且难以用其他方式解决。

模式是，前面是多个0，紧跟一个数字1～9，然后后面是任意数字的任意组合。

这个模式结构用这样的字符代替： [0]+[1-9]+[0-9]

不过上面没有代表每个的数量，用大括号代表数量，增加如下：

[0]{1,}+[1-9]+[0-9]{1,}

这里的{1,}代表1位或者高于1位的任意数量。 

我们想要返回的是 后面的部分，因此，把后面的用括号包围，如下：

[0]{1,}+ ( [1-9]+[0-9]{1,}  )

完整的字段如下：

REGEXP_EXTRACT( [field], ‘ [0]{1,}+ ( [1-9]+[0-9]{1,}  )   ‘)

完美解决！

2、REGEXP_REPLACE (字符串, 模式, 替换字符串)   正则匹配后替换

Tableau提供了一个常见的替换方式，replace([string],’A’,’new’) ，就是把string字符串中的A替换为new。

正则匹配的替换，和这个replace有些类似，但是它适用的场景更多，比如除了A，还想要把B，C，F，Z也替换为new如何？我们可以使用正则匹配把上面的‘A’替换为'[A-Z]’ ， 使用下面的正则匹配替换，就可以把所有的从A到Z的字符，全部替换为new字符串。

Regexp_Replace([string],  ‘[A-Z]’,  ‘new’ )

这里的关键是，一方面要保留单引号，证明这是一个字符串，另一方面使用 方括号，指多个字符之一——  ‘[A-Z]’ 。

那如果我们不仅仅是 [A-Z],还想要小写的 [0-9],那怎么办呢？正则匹配可以同时写多个范围，因此可以改为下面的样式：

Regexp_Replace([string],  ‘[A-Z][0-9]’,  ‘new’ )

再则，还有一些特别的字符，我们也可以使用正则来匹配，比如把逗号、引号、短横，都改为新字符，每一个特殊字符，都可以加上一个斜线来代表，比如 – 代表短横， \” 代表双引号。

Regexp_Replace([string],  ‘[A-Z][0-9] \- \” \, ‘,  ‘new’ )

3、REGEXP_MATCH(字符串, 模式)

如果我们要查找在一个字符串中是否包含了一个特征的字符，比如 string字段中是否包含’A’，可以使用Tableau内置的contain函数，如下：

Contain( [string], ‘A’ )  如果返回包含字符A，返回 true（是）

和上面的正则匹配替换类似，如果我们把需求扩展一下，查找符合匹配条件的某一些字符串，比如只要是包含A到Z的任意一个字符，即返回true，刚才的contain函数就需要增加正则匹配，这里使用了Regexp_match( ) 。

Regexp_match ( [string], ‘[A_Z]’  )

总结Tableau中正则表达式的关键：

• 匹配的模式，或者说条件用单引号包围；
• 要返回的部分，使用括号包围，适用于REGEXP_EXTRACT函数；
• 范围用[中括号]包围，代表这个集合内的任意一个值；
• 如果要匹配多个数值，使用{大括号}指定数量。

喜乐君  Dec 21, 2019
增加一个 例子，使用了 {1,} Jan 8, 2020