如何按FIPS分组计算指定字段的平均值？附示例数据与输出要求

需求说明 #

• 数据框df存在同名县，需按FIPS字段分组
• 计算InIndividuals、OutIndividuals、InAGI、OutAGI、NetMigration字段的平均值
• 无需保留Key字段，输出列结构需包含：FIPS、County、State、AvgInIndividuals、AvgOutIndividuals、AvgNetMigration、AvgInAGI、AvgOutAGI

示例数据 #

df = structure(list(FIPS = c(12001L, 8001L, 16001L, 12001L, 
    8001L, 16001L), State = c("FL", "CO", "ID", "FL", 
    "CO", "ID"), County = c("Alachua County", "Adams County", 
    "Ada County", "Alachua County", "Adams County", "Ada County"), 
        InIndividuals = c(433L, 30L, 16L, 381L, 42L, 21L), OutIndividuals = c(426L, 33L, 12L, 382L, 47L, 25L), InAGI = c(111L, 222L, 333L, 444L, 555L, 666L), NetMigration = c(7L, -3L, 4L, -1L, -5L, -4L), OutAGI = c(570L, 246L, 135L, 123L, 456L, 789L), FiscalYear = c("2011 - 2012", 
        "2011 - 2012", "2011 - 2012", "2011 - 2012", "2010 - 2011", 
        "2010 - 2011"), Year = c(2012L, 2012L, 2012L, 2011L, 2011L, 
        2011L), Key = c(120012012L, 80012012L, 160012012L, 120012011L, 
        80012011L, 160012011L)), class = "data.frame", row.names = c(NA, 
    -6L))

实现方法 #

方法1：Base R 原生实现 #

利用aggregate函数完成分组计算，同时保留与FIPS一一对应的County和State信息：

# 按FIPS、County、State分组，计算指定字段的平均值
result_base <- aggregate(
  cbind(InIndividuals, OutIndividuals, InAGI, OutAGI, NetMigration) ~ FIPS + County + State,
  data = df,
  FUN = mean
)

# 重命名列名以匹配期望输出格式
colnames(result_base) <- c("FIPS", "County", "State", "AvgInIndividuals", "AvgOutIndividuals", 
                           "AvgInAGI", "AvgOutAGI", "AvgNetMigration")

# 调整列顺序，确保AvgNetMigration位置正确
result_base <- result_base[, c("FIPS", "County", "State", "AvgInIndividuals", "AvgOutIndividuals", 
                               "AvgNetMigration", "AvgInAGI", "AvgOutAGI")]

# 输出结果
print(result_base)

方法2：Tidyverse (dplyr) 实现 #

使用dplyr的链式语法，代码更简洁直观：

library(dplyr)

result_dplyr <- df %>%
  # 按FIPS分组（County和State与FIPS一一对应，可一同分组）
  group_by(FIPS, County, State) %>%
  # 计算各字段均值并指定新列名
  summarize(
    AvgInIndividuals = mean(InIndividuals),
    AvgOutIndividuals = mean(OutIndividuals),
    AvgNetMigration = mean(NetMigration),
    AvgInAGI = mean(InAGI),
    AvgOutAGI = mean(OutAGI),
    .groups = "drop"  # 取消分组状态，返回普通数据框
  )

# 输出结果
print(result_dplyr)

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