使用dplyr处理R数据框：替换字符串值并按阈值中位数填充

先看原始数据：

df <- data.frame(CRP = c("10", "2", "3", NA, "<4", ">5"))

核心思路 #

用矢量化操作替代循环，先提取数值和符号类型，再基于普通数值计算对应中位数填充，适合百万行级别的大数据量，也支持多列批量处理。

单列处理代码 #

library(dplyr)
library(stringr)

# 提取数值部分与符号类型
df_processed <- df %>%
  mutate(
    num_val = as.numeric(str_extract(CRP, "\\d+")),
    type = case_when(
      str_starts(CRP, "<") ~ "lt",
      str_starts(CRP, ">") ~ "gt",
      !is.na(CRP) ~ "normal",
      TRUE ~ NA_character_
    )
  )

# 提取所有有效的普通数值
normal_nums <- df_processed %>% filter(type == "normal") %>% pull(num_val)

# 生成最终数值列
df_final <- df_processed %>%
  mutate(
    CRP_numeric = case_when(
      type == "normal" ~ num_val,
      type == "lt" ~ {
        x <- num_val
        subset_vals <- normal_nums[normal_nums < x]
        if (length(subset_vals) == 0) NA_real_ else median(subset_vals)
      },
      type == "gt" ~ {
        x <- num_val
        subset_vals <- normal_nums[normal_nums > x]
        if (length(subset_vals) == 0) NA_real_ else median(subset_vals)
      },
      TRUE ~ NA_real_
    )
  ) %>%
  select(CRP, CRP_numeric)

运行后结果：

print(df_final)
#    CRP CRP_numeric
# 1   10        10.0
# 2    2         2.0
# 3    3         3.0
# 4 <NA>          NA
# 5   <4         2.5
# 6   >5        10.0

多列批量处理 #

如果数据框有多列需要处理，可封装成函数用across批量操作：

# 构造多列测试数据
df_multi <- data.frame(
  CRP1 = c("10", "2", "3", NA, "<4", ">5"),
  CRP2 = c("<1", "5", ">8", NA, "3", "7")
)

# 定义处理单列的函数
process_col <- function(col) {
  num_val <- as.numeric(str_extract(col, "\\d+"))
  type <- case_when(
    str_starts(col, "<") ~ "lt",
    str_starts(col, ">") ~ "gt",
    !is.na(col) ~ "normal",
    TRUE ~ NA_character_
  )
  normal_nums <- num_val[type == "normal"]
  
  case_when(
    type == "normal" ~ num_val,
    type == "lt" ~ ifelse(length(normal_nums[normal_nums < num_val]) == 0, 
                          NA_real_, median(normal_nums[normal_nums < num_val])),
    type == "gt" ~ ifelse(length(normal_nums[normal_nums > num_val]) == 0, 
                          NA_real_, median(normal_nums[normal_nums > num_val])),
    TRUE ~ NA_real_
  )
}

# 批量处理所有以CRP开头的列
df_multi_final <- df_multi %>%
  mutate(across(starts_with("CRP"), process_col, .names = "{.col}_numeric"))

说明 #

• 全程用矢量化函数，避免for循环，处理百万级数据效率更高
• 原有NA保持不变，无对应观测值时（比如"<1"但没有小于1的普通数值）设为NA
• 多列处理时只需调整across的列选择规则即可适配不同列名

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