基于含空值的tibble列按指定规则创建虚拟变量

原始数据 #

数据结构 #

# A tibble: 11 × 1
   messycol                   
   <chr>                      
 1 ""                         
 2 ""                         
 3 ""                         
 4 ""                         
 5 ""                         
 6 ""                         
 7 "|15|16|32|33|36|39|40|41|"
 8 "|15|16|32|39|"            
 9 "|10|29|32|38|39|40|"      
10 "|32|39|"                  
11 ""                         

数据dput输出 #

structure(list(messycol = structure(c("", "", "", "", "", "", 
"|15|16|32|33|36|39|40|41|", "|15|16|32|39|", "|10|29|32|38|39|40|", 
"|32|39|", ""), label = "Noteffectiveaccountingrule reason keys", format.sas = "$"), row.names = c(NA, 
-11L), class = c("tbl_df", "tbl", "data.frame"))

需求说明 #

从messycol列生成虚拟变量DUMMY，规则如下：

• 若messycol包含15或16（匹配完整的|15|或|16|格式，避免误匹配），赋值为1
• 若messycol非空且不包含上述值，赋值为0
• messycol中的空值保持为空字符串

解决方案 #

使用dplyr和stringr包实现需求：

library(dplyr)
library(stringr)

# 加载数据
df <- structure(list(messycol = structure(c("", "", "", "", "", "", 
"|15|16|32|33|36|39|40|41|", "|15|16|32|39|", "|10|29|32|38|39|40|", 
"|32|39|", ""), label = "Noteffectiveaccountingrule reason keys", format.sas = "$"), row.names = c(NA, 
-11L), class = c("tbl_df", "tbl", "data.frame"))

# 生成DUMMY变量
df_processed <- df %>%
  mutate(DUMMY = case_when(
    messycol == "" ~ "",
    str_detect(messycol, "\\|15\\||\\|16\\|") ~ "1",
    TRUE ~ "0"
  ))

# 查看处理后的数据
df_processed

处理结果 #

# A tibble: 11 × 2
   messycol                    DUMMY
   <chr>                       <chr>
 1 ""                          ""   
 2 ""                          ""   
 3 ""                          ""   
 4 ""                          ""   
 5 ""                          ""   
 6 ""                          ""   
 7 "|15|16|32|33|36|39|40|41|" "1"  
 8 "|15|16|32|39|"             "1"  
 9 "|10|29|32|38|39|40|"       "0"  
10 "|32|39|"                   "0"  
11 ""                          ""   

内容的提问来源于stack exchange，提问作者Sharif