如何在R语言中按两列分组生成li_unit占比统计表格

样本数据 #

s_name <- c("AL", "AL", "CA", "CA", "WI", "WI", "NJ", "NJ", "UT", "UT")
n_unit <- c(40, 30, 150, 110, 45, 80, 70, 40, 50, 90)
li_unit <- c(30, 30, 70, 40, 15, 80, 50, 40, 45, 45)
pv_lvl <- c("High", "Very High", "Medium", "Low", "Very Low", "Medium", "Very High", "low", "Very Low", "High")

mydata <- as.data.frame(cbind(s_name, n_unit, li_unit, pv_lvl))
mydata$n_unit <- as.numeric(mydata$n_unit)
mydata$li_unit <- as.numeric(mydata$li_unit)

mydata$per_li = mydata$li_unit/mydata$n_unit*100

print(mydata)

需求说明 #

需要生成按s_name分组，展示每种pv_lvl下li_unit占比（per_li）的表格，缺失的pv_lvl类别填充0，数值保留两位小数，目标格式如下：

s_name Very Low    Low    Medium   High    Very High
AL     0.00        0.00   0.00     75.00   100.00 
CA     0.00        36.36  46.67    0.00    0.00
WI     33.33       0.00   100.00   0.00    0.00 
NJ     100.00      0.00   0.00     0.00    71.43
UT     90.00       0.00   0.00     50.00   0.00

解决方案 #

步骤1：统一pv_lvl的大小写 #

原数据中pv_lvl存在大小写不一致（如"low"和"Low"），先统一格式避免分类混乱：

library(dplyr)
library(tidyr)
library(stringr)

# 转换为标题大小写（首字母大写，其余小写）
mydata <- mydata %>%
  mutate(pv_lvl = str_to_title(pv_lvl))

步骤2：将长格式数据转为宽格式 #

使用pivot_wider重塑表格，自动补全缺失类别并填充0：

result <- mydata %>%
  select(s_name, pv_lvl, per_li) %>%
  pivot_wider(
    id_cols = s_name,
    names_from = pv_lvl,
    values_from = per_li,
    values_fill = 0  # 缺失类别填充0
  )

步骤3：格式化数值保留两位小数 #

对数值列进行四舍五入处理：

result <- result %>%
  mutate(across(-s_name, ~round(., 2)))

完整代码 #

library(dplyr)
library(tidyr)
library(stringr)

# 样本数据
s_name <- c("AL", "AL", "CA", "CA", "WI", "WI", "NJ", "NJ", "UT", "UT")
n_unit <- c(40, 30, 150, 110, 45, 80, 70, 40, 50, 90)
li_unit <- c(30, 30, 70, 40, 15, 80, 50, 40, 45, 45)
pv_lvl <- c("High", "Very High", "Medium", "Low", "Very Low", "Medium", "Very High", "low", "Very Low", "High")

mydata <- as.data.frame(cbind(s_name, n_unit, li_unit, pv_lvl))
mydata$n_unit <- as.numeric(mydata$n_unit)
mydata$li_unit <- as.numeric(mydata$li_unit)
mydata$per_li = mydata$li_unit/mydata$n_unit*100

# 生成目标表格
result <- mydata %>%
  mutate(pv_lvl = str_to_title(pv_lvl)) %>%
  select(s_name, pv_lvl, per_li) %>%
  pivot_wider(
    id_cols = s_name,
    names_from = pv_lvl,
    values_from = per_li,
    values_fill = 0
  ) %>%
  mutate(across(-s_name, ~round(., 2)))

# 打印结果（隐藏行名）
print(result, row.names = FALSE)

最终输出 #

运行代码后将得到符合需求的表格：

s_name Very Low   Low Medium  High Very High
    AL      0.00 0.00   0.00 75.00     100.00
    CA      0.00 36.36  46.67  0.00       0.00
    WI     33.33 0.00  100.00  0.00       0.00
    NJ    100.00 0.00   0.00  0.00      71.43
    UT     90.00 0.00   0.00 50.00       0.00

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