如何用R dplyr一步实现两同值异名列的汇总统计

问题背景 #

现有如下样本数据框，其中RNG_BEFORE_WDL与RNG_AFTER_WDL值集合一致但列名不同，存在重复ID记录：

ID RNG_BEFORE_WDL RNG_AFTER_WDL
     <int> <chr>          <chr>        
 1 3490115 Less than 10K  Less than 10K
 2 3564671 Less than 1K   0            
 3 3914214 Less than 30K  0            
 4 3971472 More than 60K  More than 60K
 5 3971472 More than 60K  More than 60K
 6 4138130 Less than 1K   0            
 7 4143893 Less than 10K  Less than 10K
 8 4145280 Less than 10K  Less than 100
 9 4146908 Less than 30K  Less than 30K
10 4146929 Less than 10K  Less than 1K 
11 4146958 Less than 30K  Less than 10K
12 4147813 Less than 10K  Less than 1K 
13 4148128 Less than 1K   0            
14 4148446 Less than 60K  Less than 60K
15 4148446 Less than 60K  Less than 60K

需要得到按自定义顺序排序的汇总表，统计每个区间在两列中的唯一ID数量：

SV_RNG         BF_WDL  AF_WDL
  <chr>           <int>   <int>
1 0                  NA  926339
2 Less than 100   15165   78106
3 Less than 1K   289663  588669
4 Less than 10K 1225950  558488
5 Less than 30K  848153  469356
6 Less than 60K  464382  354119
7 More than 60K 4531080 4399316

单步骤dplyr实现方案 #

通过数据重塑、分组统计、格式转换的链式操作完成，无需拆分多个中间对象：

library(dplyr)
library(tidyr)

# 单步骤实现目标汇总表
s_es00 <- df %>%
  distinct(ID, .keep_all = TRUE) %>%
  pivot_longer(cols = starts_with("RNG_"), names_to = "type", values_to = "SV_RNG") %>%
  mutate(type = ifelse(type == "RNG_BEFORE_WDL", "BF_WDL", "AF_WDL")) %>%
  count(SV_RNG, type) %>%
  pivot_wider(names_from = type, values_from = n) %>%
  arrange(factor(SV_RNG, levels = c("0", "Less than 100", "Less than 1K", "Less than 10K", "Less than 30K", "Less than 60K", "More than 60K")))

代码逻辑说明 #

1. distinct(ID, .keep_all = TRUE)：先对ID去重，保留每个ID的唯一记录，避免重复统计同一ID
2. pivot_longer(...)：将两列区间值转为长表结构，统一用SV_RNG存储区间值，type区分原列来源
3. mutate(type = ...)：把原列名映射为最终需要的汇总列名BF_WDL和AF_WDL
4. count(SV_RNG, type)：按区间和类型分组，统计每组的ID数量
5. pivot_wider(...)：将长表转回宽表，得到每个区间对应的前后计数
6. arrange(factor(...))：将SV_RNG转为指定排序级别的因子，实现自定义顺序

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