在R循环中使用paste0()生成n_pct列遇分组数据错误求助

问题 #

在R中遍历人口统计数据集demographics_df，汇总各问题的响应总数、各类响应数量及占比，最终合并各问题的结果数据框生成报告。希望新增n_pct相关列，将每行的响应数量n与对应百分比拼接为“n (%)”格式，但因数据分组，尝试多种apply组合均失败，出现如下错误：

Error in $<-.data.frame(*tmp*, "n_pct", value = c("c(175, 79) (c(44.5, 40.7)%)", :
replacement has 6 rows, data has 2

现有实现代码 #

dem_col<-colnames(demographics_df)
dem_col1<-c(3,21,28,29,30,38:43,52,56:58)

sum_dem_df<-demographics_df%>%
  select(site_name,record_id)%>%
  summarise(sum=n(),.groups='keep')%>%
  mutate(question=NA_character_)

#Loop through all columns and get totals by group
for(i in 3:length(dem_col)){
  dem_df_i<-as.data.frame(demographics_df[,c(1,i)])
  name<-paste0(names(demographics_df[i]))
  }if(i%in%dem_col1){
    dem_df_i$j<-dem_df_i[,2] 
    dem_df_i<-dem_df_i%>%
      group_by(site_name)%>%
      drop_na()%>%
      count(j) %$>
      rename(!!name:=j)%>%
      pivot_wider(names_from =!!name,values_from=n )%>%
      mutate(question=!!name)%>%
      replace(is.na(.), 0) %$>
      mutate(sum = rowSums(across(where(is.numeric))))
    dem_df_i<-as.data.frame(dem_df_i)
    dem_df_i$pct<-dem_df_i[,c(2:(length(colnames(dem_df_i))-2))]/dem_df_i$sum
    dem_df_i$n_pct<-paste0(dem_df_i[,c(2:(length(colnames(dem_df_i))-2))],' (',100*round(dem_df_i$pct,3),'%)')
    sum_dem_df<-full_join(sum_dem_df,dem_df_i,by=c('site_name','question','sum'))
  }

示例数据 #

demographics_df<-data.frame(site_name=c('city1','city2','city1','city2','city1','city2'),
record_id=c(1,2,3,4,5,6),
gender.factor=c('male','female','male','male','trans female',NA_character_),
employment.factor=c('unemployed','unemployed','temporary employed',NA_character_,'unemployed','part-time employed'))

desired_df<-data.frame(site_name=c('city1','city2','city1','city2'),
                       sum=c(3,2,3,2),
                       male=c(2,1,NA,NA),
                       female=c(0,1,NA,NA),
                       trans_female=c(1,0,NA,NA),
                       `pct$male`=c(.66,.5,NA,NA),
                       `pct$female`=c(0,.5,NA,NA),
                       `pct$trans_female`=c(.33,0,NA,NA),
                       umemployed=c(NA,NA,2,1),
                       temp_employed=c(NA,NA,1,0),
                       part_time=c(NA,NA,0,1),
                       `pct$unemployed`=c(NA,NA,.66,.5),
                       `pct$temp_employed`=c(NA,NA,.33,0),
                       `pct$part_time`=c(NA,NA,0,.5),
                       n_pct_female=c('0 (0%)','1 (50%)'))

期望输出 #

site_name sum male female trans_female pct.male pct.female pct.trans_female umemployed temp_employed part_time
1     city1   3    2      0            1     0.66        0.0             0.33         NA            NA        NA
2     city2   2    1      1            0     0.50        0.5             0.00         NA            NA        NA
3     city1   3   NA     NA           NA       NA         NA               NA          2             1         0
4     city2   2   NA     NA           NA       NA         NA               NA          1             0         1
pct.unemployed pct.temp_employed pct.part_time n_pct_female
1             NA                NA            NA       0 (0%)
2             NA                NA            NA      1 (50%)
3           0.66              0.33           0.0       0 (0%)
4           0.50              0.00           0.5      1 (50%)

解决方案 #

错误原因 #

原代码生成n_pct列时，直接对多列执行paste0操作，会将所有列的元素按顺序拼接成一个长向量（长度等于列数×行数），但数据框仅对应行数（如示例中2行），导致长度不匹配报错。同时原逻辑试图生成单一n_pct列，但期望输出是每个响应类别对应独立的n_pct_xxx列。

修正后的代码 #

library(dplyr)
library(tidyr)

dem_col <- colnames(demographics_df)
dem_col1 <- c(3,21,28,29,30,38:43,52,56:58) # 对应需要处理的列索引

# 初始化汇总数据框
sum_dem_df <- demographics_df %>%
  select(site_name, record_id) %>%
  summarise(sum = n(), .groups = 'keep') %>%
  mutate(question = NA_character_)

# 遍历目标列处理
for(i in dem_col1) {
  # 提取当前列和site_name
  current_col <- dem_col[i]
  dem_df_i <- demographics_df %>%
    select(site_name, all_of(current_col)) %>%
    drop_na(all_of(current_col)) %>%
    group_by(site_name, .data[[current_col]]) %>%
    count(name = "n") %>%
    ungroup() %>%
    pivot_wider(names_from = all_of(current_col), values_from = n, values_fill = 0) %>%
    mutate(question = current_col) %>%
    rowwise() %>%
    mutate(sum = sum(c_across(where(is.numeric)), na.rm = TRUE)) %>%
    ungroup()
  
  # 生成百分比列
  resp_cols <- setdiff(colnames(dem_df_i), c("site_name", "question", "sum"))
  dem_df_i <- dem_df_i %>%
    mutate(across(all_of(resp_cols), 
                  list(pct = ~ ./sum), 
                  .names = "pct_{.col}"))
  
  # 生成n_pct列：每个响应类别对应一列
  dem_df_i <- dem_df_i %>%
    mutate(across(all_of(resp_cols), 
                  ~ paste0(., " (", round(100 * .data[[paste0("pct_", cur_column())]], 2), "%)"),
                  .names = "n_pct_{.col}"))
  
  # 合并到汇总数据框
  sum_dem_df <- full_join(sum_dem_df, dem_df_i, by = c("site_name", "question", "sum"))
}

# 清理NA（可选，根据需求调整）
sum_dem_df <- sum_dem_df %>%
  arrange(site_name, question)

代码说明 #

1. 循环逻辑优化：直接遍历目标列索引dem_col1，避免无效循环，同时用all_of()处理列名，兼容tidyeval语法。
2. 百分比列生成：用across()批量生成pct_xxx格式的百分比列，无需手动计算列索引。
3. n_pct列生成：针对每个响应类别列，用across()结合cur_column()动态获取对应百分比列，拼接成"n (%)"格式，自动生成n_pct_xxx列，完全匹配期望输出格式。
4. 分组与合并：保持按site_name分组统计，最终用full_join合并所有问题的结果，保留所有行和列。

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