R新手技术求助：按州/县分组汇总多变量并生成规整表格

我是R语言新手，需要按数据集的ut字段（对应州/县）对多个变量进行分组汇总，生成结构规整的表格，列标题包含州/县名称以及各变量的统计汇总结果。我目前尝试的代码如下：

df <- tbl_df(nfhs2)

nfhs.sum <- df %>
    group_by(ut) %>
    select(foos>6, fmpop<15,totsexratio, atbirthsexratio, regbirth, regdeath, popelec,popwat,popsan, hhcleanfuel, hhsalt, hhinsured,child.pprimary) %>% # select variables to summarise#
    summarize_each(funs(min = min, 
                      q25 = quantile(., 0.25), 
                      median = median, 
                      q75 = quantile(., 0.75), 
                      max = max,
                      mean = mean, 
                      sd = sd), na.rm = TRUE)

数据样本结构如下：

structure(list(dist = c("Nicobars", "North & Middle Andaman "
), ut = c("Andaman & Nicobar Islands", "Andaman & Nicobar Islands"
), hh = c(882, 874), fem = c(764, 789), male = c(125, 108), `foos>6` = c(78, 
82.7), `fmpop<15` = c(23, 19.8), totsexratio = c(973, 950), atbirthsexratio = c("927 ", 
"844 "), regbirth = c(98, 100), regdeath = c("83.2 ", "(92.6)"
), popelec = c(97.9, 93.2), popwat = c(98.8, 92.2), popsan = c(83.5, 
86.4), hhcleanfuel = c(56.9, 61.3), hhsalt = c(99.4, 99.9), hhinsured = c(2.7, 
2.1), child.pprimary = c("(29.5)", "(30.1)"), f.literate = c(87.5, 
84), `fyrsofschooling>10` = c(53.5, 41), `marriage<18` = c("11.4 ", 
"15.4 "), thirdorder = c("0.0 ", "1.5 "), youngmothers = c("1.8 ", 
"3.8 "), periodhygiene = c(100, 100), familyplanning1 = c(65.3, 
84.1), familyplanning2 = c(57.2, 73.1), familyplanning3 = c(46.4, 
48.3), familyplanning4 = c(0, 0.6), familyplanning5 = c(2.7, 
6.4), familyplanning6 = c(2, 7.8), familyplanning7 = c(4.9, 9.3
), familyplanning8 = c(1.2, 0), nofamilyplanning = c(9.5, 5.8
), nochildspacing = c(3.3, 1.3), fp.awareness = c(40.4, 23.2), 
    fp.sideeffects = c("49.4 ", "83.2 "), ant.chk = c("62.8 ", 
    "74.5 "), `ant.chk=4` = c("71.7 ", "79.2 "), no.tetanus = c("78.0 ", 
    "91.1 "), `iron.folic>100` = c("72.6 ", "83.7 "), `iron.folic>180` = c("43.9 ", 
    "24.1 "), yes.mcp = c("97.9 ", "99.2 "), yes.postntl = c("85.1 ", 
    "92.5 "), personalexp4delivery = c("2278 ", "1904 "), `ccheckup<24hrs` = c("*", 
    "*"), child.postntl = c("92.5 ", "94.3 "), inst.birth = c(97.8, 
    97.7), govt.inst.birth = c(96.7, 95), homebirth = c(0.8, 
    0.7), profbirth = c(98.6, 98.3), csection.birth = c(11.5, 
    12.9), pvt.inst.birth = c("*", "*"), csection.birth.govt = c("10.7 ", 
    "11.4 "), mixed.vaccine.recall = c("(64.2)", "*"), vaccine.card.recall = c("(94.1)", 
    "*"), tb.vaccine = c("(80.4)", "*"), polio.vaccine = c("(69.1)", 
    "*"), dpt.vaccine = c("(71.9)", "*"), measle.vaccine1 = c("(67.3)", 
    "*"), measle.vaccine2 = c("(20.7)", "*"), rotavirus.vaccine = c("(3.1)", 
    "*"), HPB.vaccine = c("(68.6)", "*"), vitAdose = c("94.9 ", 
    "(89.6)"), govt.vaccine = c("(100.0)", "*"), pvt.vaccine = c("(0.0)", 
    "*"), got.loosemotions = c(5.7, 4.5), ORS = c("*", "*"), 
    zinc = c("*", "*"), hospice.loosemotions = c("*", "*"), ari = c(1.8, 
    7), hospice.ari = c("(85.7)", "*"), `breastfed<1hr` = c("55.4 ", 
    "27.3 "), exclusive.breastfed = c("*", "*"), supple.breastfed = c("*", 
    "*"), adequate.breastfed = c("(19.4)", "(6.5)"), adequate.nonbreastfed = c("*", 
    "*"), adequate.diet = c("(18.7)", "(5.9)"), chld.stunted = c("21.6 ", 
    "27.0 "), chld.wasted = c("15.7 ", "27.0 "), chld.severely.wasted = c("7.8 ", 
    "8.3 "), chld.uweight = c("24.6 ", "42.8 "), chd.oweight = c("1.5 ", 
    "0.8 "), lowBMI = c(8.2, 8.6), obese = c(39.1, 35.9), high.wth = c(62.5, 
    79.3), chd.anaemia = c("37.7 ", "30.4 "), female.anaemia = c(38.4, 
    62.5), preg.anaemia = c("*", "*"), totfem.anaemia = c(38.3, 
    62.1), `1519.anaemia` = c("48.0 ", "47.8 "), female.sugar.high = c(7.4, 
    7.2), female.sugar.vhigh = c(3.9, 6.4), totfemale.sugar = c(13.1, 
    16.7), male.sugar.high = c(9.6, 9.1), male.sugar.vhigh = c(4.4, 
    6.9), totmale.sugar = c(15.4, 18.3), female.bp.mild = c(23.2, 
    18.4), female.bp.elev = c(8.5, 4), totfemale.bp = c(35.4, 
    27.4), male.bp.mild = c(32.9, 22.6), male.bp.elev = c(11.1, 
    6), totmale.bp = c(47, 32.2), cervical.screen = c(13.4, 1.7
    ), breast.exam = c(13.2, 0.3), oral.exam = c(5.4, 15.8), 
    female.tobacco.use = c(63.5, 46.8), male.tobacco.use = c(76.8, 
    70.5), female.alcohol.use = c(29.6, 5.1), male.alcohol.use = c(64.5, 
    45.3)), row.names = 1:2, class = "data.frame")

修正后的代码及说明 #

你的原始代码存在语法错误和数据适配问题，以下是优化后的完整实现：

library(dplyr)

# 转换为tibble格式
df <- as_tibble(nfhs2)

# 定义数据清理函数：处理带括号、空格、*的字符型变量，转为数值型
clean_numeric <- function(x) {
  x <- gsub("[() ]", "", x)  # 移除括号和空格
  x <- ifelse(x == "*", NA, x)  # 将*替换为缺失值
  as.numeric(x)
}

# 预处理数据：将需要统计的字符型变量转为数值
df_cleaned <- df %>%
  mutate(across(c(atbirthsexratio, regdeath, child.pprimary, fp.sideeffects, 
                  ant.chk, `ant.chk=4`, no.tetanus, `iron.folic>100`, 
                  `iron.folic>180`, yes.mcp, yes.postntl, personalexp4delivery), 
                clean_numeric))

# 按ut分组，对指定变量计算统计量
nfhs.sum <- df_cleaned %>%
  group_by(ut) %>%
  select(`foos>6`, `fmpop<15`, totsexratio, atbirthsexratio, regbirth, regdeath, 
         popelec, popwat, popsan, hhcleanfuel, hhsalt, hhinsured, child.pprimary) %>%
  summarise(across(everything(), 
                   list(min = ~min(., na.rm = TRUE),
                        q25 = ~quantile(., 0.25, na.rm = TRUE),
                        median = ~median(., na.rm = TRUE),
                        q75 = ~quantile(., 0.75, na.rm = TRUE),
                        max = ~max(., na.rm = TRUE),
                        mean = ~mean(., na.rm = TRUE),
                        sd = ~sd(., na.rm = TRUE)),
                   .names = "{.col}_{.fn}")) %>%
  ungroup()

# 输出规整表格
print(nfhs.sum)

核心优化点 #

1. 修复管道符%>%的语法错误，替换已弃用的summarize_each()为新版本推荐的across()
2. 对带特殊符号的变量名用反引号包裹，避免R识别为表达式
3. 新增数据清理步骤，将带格式的字符型变量转为数值型，确保统计函数正常运行
4. 生成的表格列名为变量名_统计量格式，结构清晰，便于后续分析

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