如何将FIPS与County列的频次存储到新DataFrame中

基于提供的R语言数据框，需统计FIPS和County组合的出现频次，将结果存储为新数据框，得到如下指定输出。

样本数据 #

df = structure(list(Key = c("080012020", "120012020", "120012018", "120012017", "080012017", "120012016", "120012015", "080012014", "120012013", "120012012", "080012012", "080012011", "080012016"), County = c("Adams County", "Alachua County", "Alachua County", "Alachua County", "Adams County", "Alachua County", "Alachua County", "Adams County", "Alachua County", "Alachua County", "Adams County", "Adams County", "Adams County"), State = c("CO", "FL", "FL", "FL", "CO", "FL", "FL", "CO", "FL", "FL", "CO", "CO", "CO"), FIPS = c("08001", "12001", "12001", "12001", "08001", "12001", "12001", "08001", "12001", "12001", "08001", "08001", "08001"), Inflow = c(38L, 261L, 321L, 339L, 58L, 288L, 254L, 46L, 413L, 433L, 30L, 42L, NA), InAGI = c(1817L, 6287L, 8423L, 8364L, 1865L, 14720L, 5224L, 1074L, 11774L, 10151L, 921L, 500L, NA), FiscalYear = c("2019- 2020", "2019- 2020", "2017 - 2018", "2016 - 2017", "2016 - 2017", "2015 - 2016", "2014 - 2015", "2013 - 2014", "2012 - 2013", "2011 - 2012", "2011 - 2012", "2010 - 2011", "2015 - 2016"), Year = c(2020L, 2020L, 2018L, 2017L, 2017L, 2016L, 2015L, 2014L, 2013L, 2012L, 2012L, 2011L, 2016L), Outflow = c(54L, 447L, 444L, 558L, 44L, 436L, 334L, 49L, 466L, 495L, 39L, 31L, 51L), OutAGI = c(1879L, 13106L, 15409L, 16496L, 2408L, 12675L, 7448L, 733L, 10309L, 11677L, 847L, 605L, 1114L), NetMigration = c(-16L, -186L, -123L, -219L, 14L, -148L, -80L, -3L, -53L, -62L, -9L, 11L, NA)), row.names = c(NA, -13L), class = "data.frame")

期望输出 #

FIPS       County Frequency
1 12001 Alachua County         7
2 08001   Adams County         6

解决方案 #

方法1：基础R实现 #

无需额外安装包，直接使用aggregate函数统计频次：

# 按FIPS和County分组统计行数（即频次）
freq_df <- aggregate(
  list(Frequency = df$FIPS),
  by = list(FIPS = df$FIPS, County = df$County),
  FUN = length
)

# 查看结果
print(freq_df)

也可以用table函数转换为数据框：

# 生成频次表并转为数据框
freq_df <- as.data.frame(table(df$FIPS, df$County))
# 修改列名匹配期望输出
colnames(freq_df) <- c("FIPS", "County", "Frequency")

方法2：tidyverse（dplyr）实现 #

如果习惯使用tidyverse生态，用dplyr的分组汇总更直观：

library(dplyr)

freq_df <- df %>%
  group_by(FIPS, County) %>%  # 按FIPS和County分组
  summarize(Frequency = n(), .groups = "drop")  # 统计每组行数并取消分组

# 查看结果
print(freq_df)

以上两种方法都能得到与期望一致的新数据框。

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