如何为str_glue批量传入多组列对实现文本拼接？

需求说明 #

需要自动将数据框中var1_n与var1_rate、var2_n与var2_rate这类成对列，合并为数值(百分比)格式的新列，无需手动逐个指定列名，且导出CSV需兼容Excel。

示例数据 #

library(tidyverse)

example_data <-  structure(list(state = c("AK", "AL", "AR", "AZ", "CA", 
                                          "CO","FL", "GA", "HI", "IA", 
                                          "ID", "IL", "IN", "KS", "KY", 
                                          "LA", "MA","ME", "MI", "MN", 
                                          "MO", "MS", "MT", "NC", "ND", 
                                          "NE", "NH", "NM","NV", "NY", 
                                          "OH", "OK", "OR", "PA", "SC",
                                          "SD", "TN", "TX", "UT","VA", 
                                          "VT", "WA", "WI", "WV", "WY"), 
                                
                                n = c(13L, 5L, 28L, 15L, 35L, 32L, 10L, 30L, 9L, 
                                      82L, 27L, 52L, 34L, 82L, 27L, 25L, 3L, 16L, 
                                      36L, 77L, 33L, 30L, 49L, 20L, 36L, 63L, 13L, 
                                      11L,13L, 18L, 33L, 40L, 25L, 16L, 3L, 39L, 
                                      15L, 83L, 13L, 8L, 8L,39L, 58L, 21L, 16L), 
                                
                                var1_n = c(4L, 2L, 15L, 6L,14L, 14L, 6L, 22L,4L, 
                                           43L, 14L, 31L, 10L, 42L, 16L, 11L, 2L,
                                           8L, 22L, 23L, 22L, 16L, 23L, 4L, 5L, 
                                           34L, 5L, 4L, 5L, 8L, 18L,24L, 7L, 5L, NA, 
                                           5L, 10L, 40L, 7L, 4L, 2L, 17L, 36L, 8L, 6L), 
                                
                                var2_n = c(4L, 2L, 15L, 6L, 14L, 14L, 6L, 22L, 4L,
                                           43L, 14L, 31L, 10L, 42L, 16L, 11L, 2L, 
                                           8L, 22L, 23L, 22L, 16L,23L, 4L, 5L, 34L, 
                                           5L, 4L, 5L, 8L, 18L, 24L, 7L, 5L, NA, 5L, 
                                           10L, 40L, 7L, 4L, 2L, 17L, 36L, 8L, 6L), 
                                
                                var3_n = c(4L,2L, 5L, 5L, 8L, 7L, 4L, 8L, 2L, 
                                           15L, 9L, 11L, 2L, 12L, 4L, 6L,1L, 
                                           3L, 8L, 18L, 7L, 5L, 10L, 1L, 4L, 
                                           21L, 1L, 6L, 2L, 5L, 3L,6L, 3L, 1L, NA, 
                                           3L, NA, 16L, 1L, 1L, 2L, 8L, 8L, 2L, 6L), 
                                
                                var4_n = c(12L, 5L, 24L, 10L, 25L, 28L,9L, 26L, 7L, 
                                           73L, 20L, 50L, 33L, 66L, 25L, 21L, 3L,14L, 
                                           31L,70L, 31L, 25L, 36L, 15L, 23L, 48L, 9L, 
                                           10L, 8L, 16L, 30L, 28L,24L, 13L, 1L, 38L, 
                                           12L, 52L, 9L, 8L, 8L, 27L, 54L, 21L, 13L), 
                                
                                var1_rate = c("31%","40%", "54%", "40%", "40%", "44%", "60%", "73%", "44%", 
                                              "52%","52%", "60%", "29%", "51%", "59%", "44%", "67%", "50%", 
                                              "61%","30%", "67%", "53%", "47%", "20%", "14%", "54%", "38%", 
                                              "36%","38%", "44%", "55%", "60%", "28%", "31%", "NA%", "13%", 
                                              "67%","48%", "54%", "50%", "25%", "44%", "62%", "38%", "38%"), 
                                
                                var2_rate = c("31%", "40%", "54%", "40%", "40%", "44%","60%", "73%", "44%", 
                                              "52%", "52%", "60%", "29%", "51%", "59%","44%", "67%", "50%", 
                                              "61%", "30%", "67%", "53%", "47%", "20%","14%", "54%", "38%", 
                                              "36%", "38%", "44%", "55%", "60%", "28%","31%", "NA%", "13%", 
                                              "67%", "48%", "54%", "50%", "25%", "44%","62%", "38%", "38%"), 
                                
                                var3_rate = c("31%", "40%","18%", "33%", "23%", "22%", "40%", "27%", "22%", 
                                              "18%", "33%","21%", "6%", "15%", "15%", "24%", "33%", "19%", 
                                              "22%", "23%","21%", "17%", "20%", "5%", "11%", "33%", "8%", 
                                              "55%", "15%","28%", "9%", "15%", "12%", "6%", "NA%", "8%", 
                                              "NA%", "19%","8%", "13%", "25%", "21%", "14%", "10%", "38%"), 
                                
                                
                                var4_rate = c("92%","100%", "86%", "67%", "71%", "88%", "90%", "87%", "78%",
                                              "89%", "74%", "96%", "97%", "80%", "93%", "84%", "100%","88%", 
                                              "86%", "91%", "94%", "83%", "73%", "75%", "64%", "76%","69%", 
                                              "91%", "62%", "89%", "91%", "70%", "96%", "81%", "33%","97%", 
                                              "80%", "63%", "69%", "100%", "100%", "69%", "93%","100%", "81%")), 
                           
                           row.names = c(NA, -45L), class = "data.frame")

解决方案 #

方法一：使用across批量生成合并列 #

利用across匹配所有_n结尾的列，通过列名转换找到对应_rate列，合并后自动生成新列：

pct_table <- example_data %>%
  mutate(
    across(ends_with("_n"), 
           ~ str_glue("{.x} ({get(str_replace(cur_column(), '_n$', '_rate'))})"),
           .names = "both_{str_remove(.col, '_n$')}"
           )
  )

• 关键说明：
  • ends_with("_n")：筛选所有数值列
  • str_replace(...)：将当前列名的_n替换为_rate，匹配对应百分比列
  • .names：自定义新列名格式，比如var1_n生成both_var1

方法二：通过数据重塑实现合并 #

先转长格式合并数值与百分比，再转回宽格式，逻辑更直观：

pct_table <- example_data %>%
  pivot_longer(
    cols = starts_with("var"),
    names_to = c("var", ".value"),
    names_sep = "_"
  ) %>%
  mutate(both = str_glue("{n} ({rate})")) %>%
  pivot_wider(
    names_from = var,
    values_from = c(n, rate, both),
    names_glue = "{var}_{.value}"
  ) %>%
  select(state, n, starts_with("var")) # 恢复原列顺序

方法三：使用map2批量创建列 #

先提取成对列名列表，再循环处理每对列：

# 提取所有var前缀
vars <- str_extract(names(example_data), "^var\\d+") %>% na.omit() %>% unique()

# 生成_n和_rate列名对
n_cols <- paste0(vars, "_n")
rate_cols <- paste0(vars, "_rate")

# 批量合并
pct_table <- example_data %>%
  bind_cols(
    map2_dfc(n_cols, rate_cols, ~ str_glue("{example_data[[.x]]} ({example_data[[.y]]})") %>%
               set_names(paste0("both_", str_remove(.x, "_n$")))
             )
  )

导出兼容性 #

以上方法生成的新列均为普通字符型，直接用write_csv(pct_table, "result.csv")导出即可完美兼容Excel，无需额外处理。

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