基于现有列批量生成新列及格式转换的R语言技术问询

初始数据结构 #

structure(list(Treatment = c("Long Term Arm", "", "Short Term Arm", 
"", "Lumpsum Arm", ""), `# Enterprises` = c("9.93∗∗", "[3.96]", 
"3.39", "[3.57]", "14.67∗∗∗", "[3.92]"), Revenues = c("61379.40∗∗", 
"[24346.05]", "23177.47", "[16080.92]", "107746.75∗∗∗", 
"[34895.03]"), Costs = c("32055.29∗", "[16478.13]", "8497.42", 
"[10462.44]", "71903.23∗∗∗", "[24360.84]"), `Net Revenues` = 
c("28226.05∗∗",  "[12334.27]", "14824.71∗", "[8143.69]",
"35576.39∗∗∗",  "[13382.81]"), Assets = c("36050.66∗∗∗", "[12589.11]",
"16441.81", "[10029.27]", "29404.54∗∗∗", "[10977.68]")), row.names = 
3:8, class = "data.frame")

问题1：批量生成显著性标记列并清洗原列数值 #

使用dplyr的across()函数高效批量处理多列，一次性完成_Sig列生成和原列数值清洗：

library(dplyr)
library(stringr)

# 定义需要处理的指标列
target_cols <- c(`# Enterprises`, Revenues, Costs, `Net Revenues`, Assets)

df <- df %>%
  # 批量生成_Sig列，提取单元格内所有星号
  mutate(across(target_cols, ~ str_extract(., "\\*+"), .names = "{col}_Sig")) %>%
  # 批量清洗原列，移除星号
  mutate(across(target_cols, ~ str_remove_all(., "\\*+")))

• str_extract(., "\\*+")：匹配并提取1个或多个星号
• .names = "{col}_Sig"：自动以原列名+_Sig命名新列
• str_remove_all(., "\\*+")：移除原列中所有星号，为后续转数值做准备

问题2：提取标准误差并保留处理组数据 #

数据按「处理组行+标准误差行」成对排列，通过分组提取SE后过滤空行：

df <- df %>%
  # 每2行分为一组（处理组+SE行）
  mutate(grp = ceiling(row_number()/2)) %>%
  group_by(grp) %>%
  # 批量提取SE列：从第二行的方括号中提取数值
  mutate(across(target_cols, ~ ifelse(row_number() == 2, str_extract(., "(?<=\\[).*(?=\\])"), NA), .names = "{col}_SE")) %>%
  # 仅保留有Treatment名称的处理组行
  filter(Treatment != "") %>%
  ungroup() %>%
  select(-grp) # 移除临时分组标识列

• (?<=\\[).*(?=\\])：正则表达式匹配方括号内的内容（正向预查+反向预查）
• 过滤Treatment != ""直接剔除SE行，最终保留3组处理数据

问题3：批量转换非_Sig列为数值型 #

用ends_with()精准排除_Sig列，剩余列统一转换为数值型：

df <- df %>%
  mutate(across(-ends_with("_Sig"), as.numeric))

• -ends_with("_Sig")：选择所有列，排除以_Sig结尾的显著性标记列
• as.numeric()：统一转换为数值类型，避免手动逐个列名输入的繁琐

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