清洗和格式化文本
如何使用自定义表达式清理不一致、非结构化或空白的文本。
假设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”是否出现在回复的任何位置。
使用以下方式创建“牛肉”自定义列
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. | |
然后，我们要在“主菜”列中搜索有效值“鹰嘴豆炖菜”。
创建“鹰嘴豆”列
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. | |
整合不同列的值
接下来，我们将创建一个名为“主菜（清洗）”的列，该列将整合每位客人的回复中的有效主菜。我们希望设置一些逻辑，以便如果“主菜”包含
- 一个有效的选项（牛肉蒂布斯或鹰嘴豆炖菜），则用该选项填充“主菜（清洗）”。
- 多个有效选项，则将第一个（最左边）有效选项放入“主菜（清洗）”中。
- 没有有效选项，则用空白值（空字符串）填充“主菜（清洗）”。
为了创建“主菜（清洗）”，我们将使用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. | | | |
提取文本并整合结果
我们将以处理“主菜”列相同的方式处理“配菜”列。首先，使用regexextract函数从“配菜”列中搜索并返回有效值。
创建“印吉拉”自定义列
regexextract([Side], "(?i)injera")
和“蔬菜”自定义列
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函数来处理人们输入部分、多个或无效配菜选项的情况，并将所有值转换为小写
创建“配菜（清洗）”自定义列
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 | | | | |
合并不同列的值
最后，我们希望通过检查每种情况来生成完整的订单
- 如果“主菜（清洗）”和“配菜（清洗）”都包含有效选项，则返回“主菜加配菜”。
- 如果只有一个有效选项，则返回“仅主菜”或“仅配菜”。
- 如果没有有效选项，则订单留空（返回空字符串）。
要检查列是否非空，我们将使用isempty函数。
例如，要检查“主菜（清洗）”是否为空白
isempty([Main (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函数按顺序评估每个表达式，并在找到*第一个*有效情况时立即停止。- 如果您将第一种情况与第二种情况交换，表达式将立即确认“配菜（清洗）”为空，并返回“仅主菜”，而不会检查“主菜（清洗）”是否也为空。
最后，为了填写每位客人的最终订单，我们将使用concat函数将“主菜（清洗）”和“配菜（清洗）”的值与其他词（包括空格）连接起来。
使用以下内容创建“订单”列
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. | | | | |
用空白值标记行
假设我们想添加一个名为“跟进？”的列，以跟踪缺少有效主菜、配菜或两者兼有的订单。这意味着我们需要检查“订单”、“主菜（清洗）”或“配菜（清洗）”中的任何一个是否为空白。
我们可以将isempty函数与OR运算符结合使用，如果这三列中的任何一列为空白，则返回“是”，如果所有列都填充了有效数据，则返回“否”。
使用以下内容创建“跟进？”列
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表达式。