如何处理含重复/非重复建筑ID的表格数据：分组统计与列生成

需求说明 #

针对数据集中District为1和6的记录，完成以下操作：

• 区分重复ID与非重复ID
• 统计每个重复ID的出现次数，并求和其对应的Value值
• 新增ID_Count和Floor两列：
  • 当ID出现次数>1时，ID_Count为对应次数，Floor取值为multistory
  • 否则ID_Count为1，Floor取值为singlestory

样本数据 #

# 加载样本数据集
df <- structure(list(ID = c(1, 1, 1, 2, 2, 3, 4, 4, 4, 5, 6, 11, 11, 
11, 22, 22, 33, 44, 44, 44, 55, 66), Zoning = c("Residential", 
"Residential", "Residential", "Commercial", "Commercial", "Residential", 
"Miscellaneous", "Miscellaneous", "Miscellaneous", "Commercial", 
"Residential", "Residential", "Residential", "Residential", "Commercial", 
"Commerical", "Residential", "Miscellaneous", "Miscellaneous", 
"Miscellaneous", "Commercial", "Residential"), District = c(1, 
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6
), Value = c(11111, 12111, 13111, 14111, 16111, 17111, 18111, 
19111, 22222, 33333, 11111, 12111, 13111, 14111, 16111, 17111, 
18111, 19111, 22222, 33333, 44444, 55555)), class = "data.frame", row.names = c(NA, 
-22L))

期望输出 #

ID  Zoning        District Value  ID_Count  Floor
1   Residential   1        36333  3         multistory
2   Commercial    1        30222  2         multistory
3   Residential   1        17111  1         singlestory
4   Miscellaneous 1        59444  3         multistory
5   Commercial    1        33333  1         singlestory
6   Residential   1        11111  1         singlestory
11  Residential   6        36333  3         multistory
22  Commercial    6        30222  2         multistory
33  Residential   6        17111  1         singlestory
44  Miscellaneous 6        59444  3         multistory
55  Commercial    6        33333  1         singlestory
66  Residential   6        11111  1         singlestory 

实现方案 #

方法1：使用dplyr包（推荐，代码简洁易读） #

先确保已安装并加载dplyr包：

install.packages("dplyr")
library(dplyr)

处理代码：

result <- df %>%
  # 筛选District为1和6的记录
  filter(District %in% c(1, 6)) %>%
  # 按ID、Zoning、District分组，聚合计算
  group_by(ID, Zoning, District) %>%
  summarise(
    Value = sum(Value),
    ID_Count = n(),
    .groups = "drop"
  ) %>%
  # 新增Floor列
  mutate(Floor = ifelse(ID_Count > 1, "multistory", "singlestory"))

# 查看结果
print(result)

方法2：使用Base R（无需额外安装包） #

# 筛选目标区域数据
filtered_df <- df[df$District %in% c(1, 6), ]

# 按ID、Zoning、District分组，计算Value总和和ID出现次数
aggregated <- aggregate(Value ~ ID + Zoning + District, data = filtered_df, FUN = sum)
counts <- table(filtered_df$ID)
aggregated$ID_Count <- counts[as.character(aggregated$ID)]

# 新增Floor列
aggregated$Floor <- ifelse(aggregated$ID_Count > 1, "multistory", "singlestory")

# 查看结果
print(aggregated)

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