教程：清理和格式化文本
如何使用自定义表达式来清理不一致、非结构化或空白的文本。
假设 Metabase 想为我们可爱的社区举办一场晚宴。主菜方面，我们有牛排或鹰嘴豆炖菜两种选择，配菜方面，我们有英杰拉或烤蔬菜。我们已经发送了一份包含菜单选项的调查问卷，以便每个人都能告诉我们他们想吃什么。
不幸的是，我们在表单中忘记设置数据验证，所以回复是这样的
| Response ID | Main | Side |
|-------------|-------------------------------------|-------------------------|
| 1 | beef tibs | injera |
| 2 | chickpea stew | grilled vegetables |
| 3 | BEEF TIBS WITH CHICKPEA STEW PLEASE | |
| 4 | | Grilled Vegetables |
| 5 | Surprise me. | |
我们想要
- 清理和合并主菜和配菜的值，以便我们可以统计最受欢迎的餐点。
- 处理无效的回复（例如，多道主菜，或菜单上不存在的主菜）。
- 跟踪那些提交了信息缺失回复的客人。
总的来说，我们希望最终得到一个如下表所示的表格（向右滚动查看完整表格）
| Response ID | Main | Side | Order | Follow up? |
|-------------|-------------------------------------|-------------------------|---------------------------------------|------------|
| 1 | beef tibs | injera | beef tibs with injera | no |
| 2 | chickpea stew | grilled vegetables | chickpea stew with grilled vegetables | no |
| 3 | BEEF TIBS WITH CHICKPEA STEW PLEASE | | beef tibs only | yes |
| 4 | | Grilled Vegetables | grilled vegetables only | yes |
| 5 | Surprise me. | | | yes |
搜索和提取文本
假设唯一有效的主菜选项是牛排和鹰嘴豆炖菜。我们可以使用 regexextract 函数来检查每个回复中的有效菜单选项。
要搜索 **主菜** 列中的“牛排”值，我们将创建一个 自定义列，其正则表达式模式为 (?i)(beef tibs)。此正则表达式模式会进行不区分大小写的检查，以确定“beef tibs”是否出现在回复中的任何位置。
使用以下命令创建 **Beef** 自定义列
regexextract([Main], "(?i)(beef tibs)")
你应该得到如下输出
| Response ID | Main | Beef |
|-------------|-------------------------------------|-----------|
| 1 | beef tibs | beef tibs |
| 2 | chickpea stew | |
| 3 | BEEF TIBS WITH CHICKPEA STEW PLEASE | BEEF TIBS |
| 4 | | |
| 5 | Surprise me. | |
然后，我们要搜索 **主菜** 列中的有效值“chickpea stew”。
创建 **Chickpea** 列
regexextract([Main], "(?i)(chickpea stew)")
得到如下输出
| Response ID | Main | Chickpea |
|-------------|-------------------------------------|---------------|
| 1 | beef tibs | |
| 2 | chickpea stew | chickpea stew |
| 3 | BEEF TIBS WITH CHICKPEA STEW PLEASE | CHICKPEA STEW |
| 4 | | |
| 5 | Surprise me. | |
合并不同列中的值
接下来，我们将创建一个名为 **Main (Clean)** 的列，用于整合每位客人回复中的有效主菜。我们希望设置一些逻辑，以便如果 **Main** 包含
- 一个有效选项（牛排或鹰嘴豆炖菜），则将 **Main (Clean)** 填为此选项。
- 多个有效选项，则将第一个（最左边）有效选项填入 **Main (Clean)**。
- 没有有效选项，则将 **Main (Clean)** 填为空白值（空字符串）。
为了创建 **Main (Clean)**，我们将使用 coalesce 函数来处理上述三种情况，并将整个表达式用 lower 函数包裹起来，以标准化所有内容为小写。
lower(coalesce([Beef],[Chickpea],""))
这将得到如下输出（向右滚动查看完整表格）
| Response ID | Main | Beef | Chickpea | Main (Clean) |
|-------------|-------------------------------------|-----------|---------------|----------------|
| 1 | beef tibs | beef tibs | | beef tibs |
| 2 | chickpea stew | | chickpea stew | chickpea stew |
| 3 | BEEF TIBS WITH CHICKPEA STEW PLEASE | BEEF TIBS | CHICKPEA STEW | beef tibs |
| 4 | | | | |
| 5 | Surprise me. | | | |
提取文本并合并结果
我们将以与 **Main** 列相同的方式处理 **Side** 列。首先，使用 regexextract 函数来搜索并返回 **Side** 列中的有效值。
创建 **Injera** 自定义列
regexextract([Side], "(?i)injera")
创建 **Vegetables** 自定义列
regexextract([Side], "(?i)(grilled vegetables)")
得到如下输出
| Response ID | Side | Injera | Vegetables |
|-------------|--------------------|--------|--------------------|
| 1 | injera | injera | |
| 2 | grilled vegetables | | grilled vegetables |
| 3 | | | |
| 4 | Grilled Vegetables | | Grilled Vegetables |
| 5 | | | |
然后，使用 coalesce 函数和 lower 函数来处理人们输入了部分、多个或无效的配菜选项的情况，并将所有值转换为小写
创建 **Side (Clean)** 自定义列
lower(coalesce([Injera],[Vegetables], ""))
得到
| Response ID | Side | Injera | Vegetables | Side (Clean) |
|-------------|--------------------|--------|--------------------|--------------------|
| 1 | injera | injera | | injera |
| 2 | grilled vegetables | | grilled vegetables | grilled vegetables |
| 3 | | | | |
| 4 | Grilled Vegetables | | Grilled Vegetables | grilled vegetables |
| 5 | | | | |
合并不同列中的值
最后，我们将通过检查每种情况来生成完整的订单
- 如果 **Main (Clean)** 和 **Side (Clean)** 都包含一个有效选项，则返回“main with side”。
- 如果只有一个有效选项，则返回“main only”或“side only”。
- 如果没有有效选项，则将订单留空（返回空字符串）。
要检查一个列是否为空，我们将使用 isempty 函数。
例如，检查 **Main (Clean)** 是否为空
isempty([Main (Clean)])
要检查 **Main (Clean)** 和 **Side (Clean)** 是否 *都* 为空，你可以使用 AND 来组合表达式
isempty([Main (Clean)]) AND isempty([Side (Clean)])
isempty 目前 只能在另一个函数中使用，所以我们需要将我们的每个检查都放在一个 case 函数中。我们暂时放一些占位符文本作为输出
case(
(isempty([Main (Clean)]) AND isempty([Side (Clean)])), "",
isempty([Side (Clean)]), "main only",
isempty([Main (Clean)]), "side only",
"main with side"
)
请注意，case 的顺序很重要，因为
- case 函数按顺序评估每个表达式，并在找到 *第一个* 有效 case 后停止。
- 如果你将第一个 case 与第二个 case 交换，表达式将确认 **Side (Clean)** 为空，并立即返回“main only”，而不会检查 **Main (Clean)** 是否也为空。
最后，为了填充每位客人的最终订单，我们将使用 concat 函数将 **Main (Clean)** 和 **Side (Clean)** 的值与其他词语（包括空格）链接起来。
使用以下命令创建 **Order** 列
case(
(isempty([Main (Clean)]) AND isempty([Side (Clean)])), "",
isempty([Side (Clean)]), concat([Main (Clean)], " only"),
isempty([Main (Clean)]), concat([Side (Clean)], " only"),
concat([Main (Clean)], " with ", [Side (Clean)])
)
总的来说，这将为我们提供一组格式化的列，如下所示（向右滚动查看完整表格）
| Response ID | Main | Side | Main (Clean) | Side (Clean) | Order |
|-------------|-------------------------------------|--------------------|-----------------|--------------------|---------------------------------------|
| 1 | beef tibs | injera | beef tibs | injera | beef tibs with injera |
| 2 | chickpea stew | grilled vegetables | chickpea stew | grilled vegetables | chickpea stew with grilled vegetables |
| 3 | BEEF TIBS WITH CHICKPEA STEW PLEASE | | beef tibs | | beef tibs only |
| 4 | | Grilled Vegetables | | grilled vegetables | grilled vegetables only |
| 5 | Surprise me. | | | | |
标记空白值的行
假设我们想添加一个名为 **Follow up?** 的列来跟踪缺少有效主菜、配菜或两者都缺的订单。这意味着我们需要检查 **Order**、**Main (Clean)** 或 **Side (Clean)** 是否为空。
我们可以结合使用 isempty 函数和 OR 运算符，如果三个列中的任何一个为空，则返回“yes”，如果所有列都填满了有效数据，则返回“no”。
使用以下命令创建 **Follow up?**
case(( isempty([Order])
OR isempty([Main (Clean)])
OR isempty([Side (Clean)])), "yes", "no")
最终结果（向右滚动查看完整表格）
| Response ID | Main | Side | Order | Follow up? |
|-------------|-------------------------------------|-------------------------|---------------------------------------|------------|
| 1 | beef tibs | injera | beef tibs with injera | no |
| 2 | chickpea stew | grilled vegetables | chickpea stew with grilled vegetables | no |
| 3 | BEEF TIBS WITH CHICKPEA STEW PLEASE | | beef tibs | yes |
| 4 | | vegetables | grilled vegetables | yes |
| 5 | Surprise me. | | | yes |
最佳实践和技巧
在本教程中，我们每次需要提取、合并或标记文本数据时都创建了一个新的自定义列。我们也组合了更简单的函数（例如 lower 和 isempty）与其他函数。总的来说，我们建议每次使用具有多个参数的函数（例如 case、regexextract 和 coalesce）时都创建一个新的自定义列，因为
- 你可以确认你的表达式是否按预期工作。
- 逻辑更容易阅读和更新。
而且，如果你习惯于使用其他工具中的函数，例如 SQL、电子表格或 Python，请查看自定义表达式文档中的 **相关函数** 部分。例如，你可以学习如何使用 与 case 相关的函数将 if-then 逻辑转换为 Metabase 表达式。