R语言：如何按分组和年份统计变量值的频次与占比并生成指定数据集

样本数据集 #

df <- structure(list(year = c("1", "2", "3", "1", "2", "3", "1", "2", 
"3", "1", "2", "3", "1", "2", "3", "1", "2", "3", "1", "2", "3", 
"1", "2", "3", "1", "2", "3", "1", "2", "3"), var2tab = c("1", 
"3", "1", "2", "2", "3", "3", "1", "3", "3", "3", "2", "2", "1", 
"2", "1", "1", "2", "3", "3", "1", "1", "2", "2", "2", "3", "1", 
"1", "3", "2"), group = c("1", "1", "1", "1", "1", "1", "1", 
"1", "1", "1", "1", "2", "2", "2", "2", "2", "2", "2", "2", "2", 
"2", "2", "3", "3", "3", "3", "3", "3", "3", "3")),
class = "data.frame", row.names = c(NA, -30L))

现有代码步骤 #

1. 索引目标列 #

var2use <- which(colnames(df) == "var2tab")
var2x <- which(colnames(df) == "group")

2. 统计唯一取值 #

years_unique <- unique(df$year)

x_unique <- unique(df[var2x])
x_unique <- unlist(x_unique)

n_years <- length(years_unique)
n_var2x <- length(x_unique)

3. 按分组生成数据框列表 #

my_list <- NULL
my_list <- list()
for (i in x_unique) {
  for (j in years_unique)
  {
    my_list[[i]] <- filter(df, df[var2x] == i)
  }
}

4. 尝试统计但仅实现全年份整体统计 #

my_df <- NULL
my_df <- list()
for (j in years_unique) {
  my_df[[j]] <- count(my_list[[j]][var2use])
  my_df[[j]]$var_rel <- my_df[[j]][, 2] / sum(my_df[[j]][, 2])
  my_df[[j]]$group <- paste0(j)
}

需求与预期输出 #

需要按分组+年份统计var2tab的绝对数量和相对占比，生成如下结构的数据集：

df_new <- data.frame(group = c("1", "1", "1", "2", "2", "2", "3", "3", "3"), 
                     value = c("1", "2", "3"), 
                     abs_year1 = c("1", "1", "1", "2", "2", "1", "1", "0", "1"), 
                     rel_year1 = c(".25", ".25", ".333", ".5", ".667", ".25", ".5", "0", ".333"), 
                     abs_year2 = c("1", "1", "0", "1", "0", "3", "1", "1", "2"), 
                     rel_year2 = c(".25", ".25", "0", ".25", "0", ".75", ".5", ".333", ".667"), 
                     abs_year3 = c("2", "2", "2", "1", "1", "0", "0", "2", "0"), 
                     rel_year3 = c(".5", ".5", ".667", ".25", ".333", "0", "0", ".667", "0"))

解决方案 #

使用dplyr和tidyr的管道操作高效实现，无需嵌套循环，适配大型数据集：

library(dplyr)
library(tidyr)

df_new <- df %>%
  # 按分组、年份、目标变量分组，统计绝对数量
  count(group, year, var2tab, name = "abs") %>%
  # 计算每个分组+年份下的相对占比
  group_by(group, year) %>%
  mutate(rel = abs / sum(abs)) %>%
  ungroup() %>%
  # 将年份转为列名，分别拆分绝对/相对值列
  pivot_wider(
    id_cols = c(group, var2tab),
    names_from = year,
    values_from = c(abs, rel),
    names_glue = "{.value}_year{year}",
    values_fill = list(abs = 0, rel = 0)
  ) %>%
  # 重命名列匹配预期输出
  rename(value = var2tab) %>%
  # 按分组和目标变量排序
  arrange(group, value) %>%
  # 格式化相对值为三位小数，去掉开头的0
  mutate(across(starts_with("rel"), ~ sprintf("%.3f", .x) %>% gsub("^0", ".", .))) %>%
  # 转换为字符类型匹配预期输出格式
  mutate(across(c(group, value, starts_with("abs")), as.character))

验证输出：

print(df_new)

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