RSS

Как использовать RegEx в Tableau

11 ноября 2020 gromych

Автор статьи — Мария Мазюк

Регулярные выражения

Необходимость в использовании регулярный выражений (RegEx) может возникнуть при работе с неструктурированными данными, как комментарии, объявления, URL. Также я часто использую RegEx для форматирования подписей к данным на графике при использовании параметра для переключения метрик. Регулярные выражения в Tableau позволяют захватывать подстроки в строковых значениях полей для дальнейшего использования в качестве новых полей.

  • Синтаксис RegEx
  • Функции для работы с RegEx в Tableau
  • Задание переменных с помощью скобок
  • Примеры использования RegEx в Tableau

Синтаксис RegEx

Справка Tableau ссылается на документацию ICU в качестве официального гайда по синтаксису для регулярных выражений в Tableau. RegEx чувствительны к регистру.

В таблице ниже приведены некоторые часто используемые операторы и метасимволы регулярных выражений.

МетасимволПринцип работыПример
.Точка соответствует одному символу.Выражению “.23” будут удовлетворять и “123”, и “223”, и “!23”.
|Прямой слэш соответствует “или”.Выражению “Coffee|Tea” будут удовлетворять и “Coffee”, и “Tea”.
?Вопросительный знак означает: “Последний элемент RegEx является необязательным.”Выражению “i?Phone” будут удовлетворять и “iPhone”, и “Phone”.
\Превращает метасимвол в простой символ.Выражению “\.” будет удовлетворять именно точка “.”, а не любой символ. “\.23” будет удовлетворять только “.23”, но не “123”.Выражению “\?” будет удовлетворять именно вопросительный знак “?”. “/\?utm_source=google” будет удовлетворять только “/?utm_source=google”.
()Круглые скобки выполняют ту же функцию, что и в математике: группируют выражение внутри скобок в единое целое. Круглые скобки часто используются вместе с прямым слэшем, а также для задания переменных $1, $2 и т.д.Выражению “(grand)?father” будут удовлетворять и “grandfather”, и “father”.Выражению “grand(father|mother)” будут удовлетворять и “grandfather”, и “grandmother”.
[]Квадратные скобки означают: “Любой один символ из перечисленных.” С помощью дефиса можно задать диапазон значений. Перечисленные символы разделять запятыми не нужно.Выражению [a-z] будет соответствовать любая буква английского алфавита в нижнем регистре. Выражению [A-Z] будет соответствовать любая буква английского алфавита в верхнем регистре. Выражению [a-zA-Z0-9] будет соответствовать любая буква английского алфавита в нижнем или верхнем регистре, а также любая цифра.
{}Фигурные скобки повторяют последнюю часть RegEx заданное число раз. Если указано одно число {x}, то это означает: “Повторить последнюю часть RegEx ровно x раз”. Если указано два числа {x, y}, то это означает: “Повторить последнюю часть RegEx минимум x и максимум y раз”.Выражению “123\.145\.167\.[0-9]{1,2}” будут соответствовать и IP-адрес “123\.145\.167\.6”, и IP-адрес “123\.145\.167\.75”, и IP-адрес “123\.145\.167\.2”.
+Плюс означает одна или более последних частей RegEx.Выражению “(great-)+grandfather” будут удовлетворять и “great-grandfather”, и “great-great-grandfather”.Выражению “yummy+” будут удовлетворять и “yummy”, и “yummyyyy”.
*Звездочка означает ноль или более последних частей RegEx.Выражению “(great-)*grandfather” будут удовлетворять и “grandfather”, и “great-great-grandfather”.Выражению “yummy*” будут удовлетворять и “yumm”, и “yummyyyy”.
.*Комбинация точки и звездочки означает: “Все что угодно, любые символы в любом количестве.”Выражению “mysite\.com(.*)” будут удовлетворять и “mysite.com/”, и “mysite.com”, и “mysite.com/index.php”.
^Каре означает: “Должно начинаться именно так”.Выражению “^mysite\.com” будет удовлетворять “mysite.com/”, но не будет удовлетворять “www.mysite.com”.
[^]Квадратные скобки в сочетании с каре означает: “Любой символ, кроме перечисленных.”  Выражению [^0-9] будет соответствовать любой символ, кроме цифр.
$Знак доллара означает: “Должно заканчиваться именно так”.Выражению “mysite\.com$” будет удовлетворять и “www.mysite.com”, но не будет удовлетворять “mysite.com/”.
\dОбратный слэш в комбинации с d в нижнем регистре означает любую цифру. Аналог [0-9].Выражению “^\d{1,3}$” будут удовлетворять все числа от 0 до 999.
\DОбратный слэш в комбинации с D в верхнем регистре означает любой символ, кроме цифры. Аналог [^0-9].Выражению “^\D+$” будут удовлетворять все значения без цифр.
\wОбратный слэш в комбинации с w в нижнем регистре соответствует любой цифре, букве в нижнем или верхнем регистре, а также символу нижнего подчеркивания _.Выражению “^\w+$” будут удовлетворять все слова и числа, их комбинации, в том числе с использование нижнего подчеркивания.
\WОбратный слэш в комбинации с W в верхнем регистре соответствует любому символу, кроме цифры, буквы или _.Выражению “^\W{2}$” будет удовлетворять выражение “:)”.
\sОбратный слэш в комбинации с s в нижнем регистре соответствует пробелу.Выражению “^\w+?\s\w+” будет удовлетворять выражение “tea pot”.
\SОбратный слэш в комбинации с S в верхнем регистре соответствует любому символу, кроме пробела.Выражению “^\S+$” будут удовлетворять только значения без пробелов.

Функции для работы с RegEx в Tableau

Для работы с регулярными выражениями в Tableau есть 4 функции. Данные функции работают со строковыми значениями, т.е. каждая функция принимает на вход значение типа String. Если вы хотите использовать регулярные выражения с числовыми полями или значениями дат, вам придется сначала конвертировать эти значения в строковый тип, например, с помощью функции STR(). Рассмотрим каждую из функций подробно с примерами.

REGEXP_REPLACE(string, ‘pattern’, ‘replacement’)

Возвращает строковое поле, которое является результатом замены RegEx в значениях исходного строковом поля.

string — это исходное строковое поле, для каждого значения которого будет производится поиск подстрок, удовлетворяющих заданному RegEx.

‘pattern’ — это регулярное выражение (RegEx), на соответствие которому будут проверяться подстроки исходного строкового поля. RegEx указывается в формуле в кавычках.

‘replacement’ — это строковое значение, на которое будет произведена замена обнаруженной подстроки, соответствующей паттерну.

Функцию можно представить и в таком виде:
REGEXP_REPLACE([Строковое поле], ‘RegEx’, ‘заменяющее строковое значение’)

В формуле в качестве ‘pattern’ и ‘replacement’ могут быть использованы только константные значения, т.е. нельзя использовать поля.

Эта функция аналогична инструменту Find and Replace в Excel, только вместо замены исходных строковых значений создается отдельное поле.

Например, результатом вычисления REGEXP_REPLACE(’email@gmail.com’, ‘gmail\.com’, ‘yandex.ru’) будет значение email@yandex.ru.

REGEXP_MATCH(string, ‘pattern’)

Возвращает логическое поле со значения TRUE и FALSE: возвращает TRUE, если строковое значение поля соответствует RegEx, и FALSE, если строковое значение не соответствует RegEx.

string — это исходное строковое поле, каждое значение которого будет оцениваться на удовлетворение заданному регулярному выражению.

‘pattern’ — это регулярное выражение (RegEx), на соответствие которому будут проверяться значения строкового поля. RegEx указывается в формуле в кавычках. В формуле в качестве ‘pattern’ могут быть использованы только константные значения, т.е. нельзя использовать поля.

Например, результатом вычисления REGEXP_MATCH(‘https://www.example.com/?utm-medium=cpc’, ‘\?utm-medium’) будет значение TRUE.

REGEXP_EXTRACT(string, ‘pattern’)

Возвращает строковое поле, где в качестве значений будут подстроки исходного строкового поля, которые соответствуют RegEx.

string — это исходное строковое поле, для каждого значения которого будет производится поиск подстрок, удовлетворяющих заданному RegEx.

‘pattern’ — это регулярное выражение (RegEx), на соответствие которому будут проверяться значения строкового поля. RegEx указывается в формуле в кавычках. В формуле в качестве ‘pattern’ могут быть использованы только константные значения, т.е. нельзя использовать поля. При этом подстрока, которая должна быть возвращена в результате работы функции, указывается в круглых скобках, т.е. задается в качестве переменной (подробнее описано ниже).

Например, результатом вычисления REGEXP_EXTRACT(‘https://www.example.com/?utm_medium=cpc’, ‘utm_medium=(cpc)’) будет строковое значение ‘cpc’.

REGEXP_EXTRACT_NTH(string, ‘pattern’, index)

Возвращает строковое поле, где в качестве значений будут подстроки исходного строкового поля, которые соответствуют RegEx и порядковому индексу переменной, заданной с помощью круглых скобок.

string — это исходное строковое поле, для каждого значения которого будет производится поиск подстрок, удовлетворяющих заданному RegEx.

‘pattern’ — это регулярное выражение (RegEx), на соответствие которому будут проверяться значения строкового поля. При этом подстрока, которая должна быть возвращена в результате работы функции, указывается в круглых скобках, т.е. задается в качестве переменной $1, $2, $3 и т.д. (подробнее описано ниже). В регулярном выражении с помощью круглых скобок одновременно можно задать несколько переменных.

index — целочисленное значение порядкового номера (n-ый индекс) переменной, заданной в RegEx, которую необходимо вернуть в результате работы функции.

В формуле в качестве ‘pattern’ и index могут быть использованы только константные значения, т.е. нельзя использовать поля.

Например, результатом вычисления REGEXP_EXTRACT_NTH(‘https://www.example.com/?utm_source=google&utm_medium=cpc’, ‘utm_source=(google)&utm_medium=(cpc)’, 2) будет строковое значение ‘cpc’.

Задание переменных с помощью скобок

Если в регулярном выражении использованы круглые скобки (), то для подстроки, удовлетворяющей RegEx внутри круглых скобок, задается переменная $1, $2 и т.д. по порядку пар круглых скобок. Данные переменные могут быть в дальнейшем использованы в аргументе ‘replacement’.

Пример: необходимо заменить каждый второй пробел в значениях строкового поля [Comments] символом @ для последующего сплита поля.

Данное регулярное выражение решит эту задачу:
REGEXP_REPLACE([Comments],'(\s.*?)(\s)’,’$1@’)

Разберем выражение по частям:

Аргумент ‘pattern’ (регулярное выражение)
\s — первый пробел
.*? — любое количество символов после первого пробела до той части RegEx, что следует после вопросительного знака: в нашем случае до следующего пробела (\s). В поле с комментариями .*? захватит слово или число.
Причина, по которой здесь использовано .*?, а не .* без вопросительного знака, заключается в том, что использование вопросительного знака позволяет захватывать подстроку до первого совпадения со следующей после вопросительного знака частью регулярного выражения. .* без вопросительного знака захватит всю подстроку до конца строкового значения. Вопросительный знак указывает, что соответствующую подстроку необходимо ограничить следующей частью RegEx. Ограничение с помощью вопросительного знака работает и для выражений .+?, .{1,9}?.
Первый пробел и слово после первого пробела заключены в круглые кавычки (\s.*?), что позволяет задать для них единую переменную $1 для последующей адресации к ней в части формулы ‘replacement’.
(\s) — второй пробел. Круглые кавычки задают переменную $2 для второго пробела \s.

Аргумент ‘replacement’
$1 — адресация на подстроку, состоящую из первого пробела и слова после него.
@ — символ, на который необходимо заменить второй пробел.
С помощью $1@ в качестве значения аргумента ‘replacement’ мы говорим Tableau заменить первый пробел со следующим за ним словом и вторым пробелом на первый пробел со следующим за ним словом и символом @.

Примеры использования RegEx в Tableau

Пример 1: создание поля m2 (площади квартиры в метрах квадратных) с помощью захвата метрики в заголовке объявлений Airbnb. В заголовках в произвольной форме указана площадь квартиры. Я создала целочисленную переменную, содержащую значения с площадью квартир с помощью следующей формулы:

INT(REGEXP_EXTRACT([Airbnb listing title], ‘(\d{1,4})\s?(m2|sm|sqm|m²)’))

Я использовала формулу REGEXP_EXTRACT(), там как необходимо было вернуть подстроку с числовым значение площади. В части RegEx (\d{1,4}) круглые скобки означают переменную, которую необходимо вернуть. \d означает цифру, а вместе с заданными нижним и верхним лимитами повторений в фигурных скобках \d{1,4} означает комбинацию из 1-4 цифр. То есть, в результате выполнения функции необходимо вернуть комбинацию из 1-4 цифр. \s? означает, что за комбинацией цифр может следовать пробел, но не обязательно. Далее после комбинации цифр и возможного пробела должно следовать одно из 4 выражений: или m2, или sm, или sqm, или m². (m2|sm|sqm|m²) и представляет данное условие с одним из 4 выражений.

Пример 2: создание числового поля с подписями значений переменной, заданной параметром. Создан параметр Metric to show с выбором метрики для отображения на графике: Sales в долларах, Profit Ratio в процентах и Orders в виде целого числа. Мы хотим отображать подписи к данным метрикам по-разному:
— Sales как целое число со знаком доллара перед числом и с разделителем “,” для тысяч: $27,562
— Profit Ratio как дробное число с одним дробным знаком и со знаком процента на конце: 3.4%
— Orders как целое число: 341

Для подписей я создала новое вычисляемое поле, как представлено ниже.

Для подписей метрики Sales я использовала следующую формулу:

‘$’ + REGEXP_REPLACE(STR(ROUND([Metric to show],0)), ‘(\d{1,3})(\d{3})$’, ‘$1,$2’)

Здесь функция REGEXP_REPLACE() принимает на вход округленное значение Sales, конвертированное в строковое значение с помощью функции STR(). В RegEx я задала две переменные с помощью круглых скобок: (\d{1,3}) и (\d{3}), то есть $1 и $2 соответственно. В переменной (\d{1,3}) содержатся 1-3 цифры, после которых идут последние три цифры числа, то есть (\d{3})$. В качестве аргумента ‘replacement’ я использовала адресацию к переменным, разделив их запятой.

Для добавления разделителя для порядков независимо от количества цифр в числе можно использовать следующую формулу:

‘$’ + REGEXP_REPLACE(STR(ROUND([Metric to show],0)), ‘(\d)(?=(\d{3})+$)’, ‘$0,’)

Здесь (\d) задает переменную $0, которую необходимо заменить на значение данной переменной и знак запятой справа. Это последняя цифра каждого порядка слева, после которого должен следовать разделитель тысяч. Затем задается условие (в виде переменной $1) для того, что именно должно следовать за последней цифрой отделяемого слева порядка: (?=(\d{3})+$), т.е. каждая комбинация из трех цифр справа до окончания стоки. ?= означает положительный lookahead, т.е. условие для проверки последующего содержания строки справа без включения последующего содержания в переменную, удовлетворяющую условию регулярного выражения. Здесь в качестве условия для последующего содержания задана комбинация из трех цифр. Знак плюс + означает, что таких последующих комбинаций из трех цифр справа может быть одна или более. Знак доллара $ означает окончание строки, т.е. чтобы удовлетворять условию для замены последней цифры порядка на цифру с разделителем в виде запятой, необходимо, чтобы после последнего разделителя до окончания строки оставалось ровно три цифры.

https://maryiamaziuk.com/data-viz-blog/

Опубликовано в рубрике Tableau, Бизнес анализ, Технологии Метки: , , , ,
«
»
Комментирование и размещение ссылок запрещено.

Комментарии закрыты.

63 queries in 0,194 seconds