R语言中aggregate()与distinct()无法彻底清洗重复数据求助

我正在处理一份死亡率数据集，其中国家、年龄组、性别、年份等分类变量均相同，但存在多行仅死亡计数、百分比、每十万人死亡率三列数值不同的准重复行。尝试用dplyr的distinct()函数选择特定列，以及用aggregate()函数以均值插补死亡数据并去重，但处理后仍存在重复行（例如美国2020年男性25-34岁组有两行死亡数分别为3465和3417）。

原尝试代码 #

# dplyr 方法
Death %>% distinct(Death$RegionCode, Death$RegionName, Death$CountryCode, Death$CountryName, Death$Year, Death$Sex, Death$AgeGroup,.keep_all = TRUE)

# aggregate 方法
unalived1 <- aggregate(Death$SuicideCount,by=list(RegionName=Death$RegionName, CountryName=Death$CountryName, Year=Death$Year, Sex=Death$Sex, AgeGroup=Death$AgeGroup, CauseSpecificDeathPercentage=Death$CauseSpecificDeathPercentage, DeathRatePer100K=Death$DeathRatePer100K, Population=Death$Population, GDP=Death$GDP, GDPPerCapita=Death$GDPPerCapita, GrossNationalIncome=Death$GrossNationalIncome, GNIPerCapita=Death$GNIPerCapita, InflationRate=Death$InflationRate, EmploymentPopulationRatio=Death$EmploymentPopulationRatio),FUN=mean)

unalived2 <- aggregate(unalived1$CauseSpecificDeathPercentage,by=list(RegionName=unalived1$RegionName, CountryName=unalived1$CountryName, Year=unalived1$Year, Sex=unalived1$Sex, AgeGroup=unalived1$AgeGroup, SuicideCount=unalived1$x, DeathRatePer100K=unalived1$DeathRatePer100K, Population=unalived1$Population, GDP=unalived1$GDP, GDPPerCapita=unalived1$GDPPerCapita, GrossNationalIncome=unalived1$GrossNationalIncome, GNIPerCapita=unalived1$GNIPerCapita, InflationRate=unalived1$InflationRate, EmploymentPopulationRatio=unalived1$EmploymentPopulationRatio),FUN=mean)

unalived3 <- aggregate(unalived2$DeathRatePer100K,by=list(RegionName=unalived2$RegionName, CountryName=unalived2$CountryName, Year=unalived2$Year, Sex=unalived2$Sex, AgeGroup=unalived2$AgeGroup, SuicideCount=unalived2$SuicideCount, CauseSpecificDeathPercentage=unalived2$x, Population=unalived2$Population, GDP=unalived2$GDP, GDPPerCapita=unalived2$GDPPerCapita, GrossNationalIncome=unalived2$GrossNationalIncome, GNIPerCapita=unalived2$GNIPerCapita, InflationRate=unalived2$InflationRate, EmploymentPopulationRatio=unalived2$EmploymentPopulationRatio),FUN=mean)

unalived4 <- na.omit(unalived3)
unalived <- unalived4

# 筛选美国数据
US_Deaths <- unalived[unalived$CountryName %in% c("United States of America"),]
US_Deaths_Male <- US_Deaths[US_Deaths$Sex %in% c("Male"),]
US_Deaths_Male_2534 <- US_Deaths_Male[US_Deaths_Male$AgeGroup %in% c("25-34 years"),]

问题原因 #

1. distinct()用法错误：调用distinct()时直接传入Death$列名会生成匿名列，而非基于原数据列去重，无法合并准重复行。
2. aggregate()分组逻辑错误：将需要聚合的数值列（如CauseSpecificDeathPercentage、DeathRatePer100K）加入分组列表，导致数值不同就被视为不同组，无法合并；且多次嵌套聚合完全冗余，易引入新问题。

正确解决方法 #

方法1：dplyr group_by() + summarise()（推荐）

通过分组变量分组，对数值列取均值，一次性完成去重和插补：

library(dplyr)

# 定义分组变量、需要聚合的数值列、其他固定列
group_vars <- c("RegionCode", "RegionName", "CountryCode", "CountryName", "Year", "Sex", "AgeGroup")
agg_vars <- c("SuicideCount", "CauseSpecificDeathPercentage", "DeathRatePer100K")
other_vars <- c("Population", "GDP", "GDPPerCapita", "GrossNationalIncome", "GNIPerCapita", "InflationRate", "EmploymentPopulationRatio")

clean_death <- Death %>%
  group_by(across(all_of(group_vars))) %>%
  summarise(
    # 对数值列取均值
    across(all_of(agg_vars), mean, na.rm = TRUE),
    # 其他列取每组第一个值（假设同一分组下值一致，不一致需另行处理）
    across(all_of(other_vars), first, na.rm = TRUE),
    .groups = "drop"
  ) %>%
  na.omit()

# 筛选目标数据
US_Deaths_Male_2534 <- clean_death %>%
  filter(CountryName == "United States of America",
         Sex == "Male",
         AgeGroup == "25-34 years")

方法2：修正aggregate()用法

仅用分组变量分组，一次性聚合所有数值列：

# 分组变量列表
group_list <- list(
  RegionCode = Death$RegionCode,
  RegionName = Death$RegionName,
  CountryCode = Death$CountryCode,
  CountryName = Death$CountryName,
  Year = Death$Year,
  Sex = Death$Sex,
  AgeGroup = Death$AgeGroup
)

# 聚合数值列
clean_death <- aggregate(
  x = Death[c("SuicideCount", "CauseSpecificDeathPercentage", "DeathRatePer100K")],
  by = group_list,
  FUN = mean,
  na.rm = TRUE
)

# 合并其他固定列（取每组第一个值）
other_cols <- Death[!duplicated(group_list), other_vars]
clean_death <- cbind(clean_death, other_cols)
clean_death <- na.omit(clean_death)

# 筛选目标数据
US_Deaths_Male_2534 <- subset(clean_death, 
                              CountryName == "United States of America" &
                              Sex == "Male" &
                              AgeGroup == "25-34 years")

注意事项 #

• 若其他列（如GDP、Population）在同一分组下也存在差异，需根据业务逻辑选择均值、中位数等统计量，不能直接取第一个值。
• 若准重复行源于录入错误，建议先核查原始数据的来源和规则，再确定聚合方式。

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