R语言数据处理异常：Restaurants i hotels组despesamonetaria值错误为0

问题描述 #

数据集Despesa_2021_Red（小样本示例）：

structure(list(grup_CNAE_Red = structure(c(4L, 4L, 4L, 4L, 8L, 
11L, 12L, 1L, 1L, 1L, 1L), .Label = c("Aliments i begudes no alcohòliques", 
"Begudes alcohòliques i tabac", "Vestit i calçat", "Habitatge", 
"Mobles i parament de la llar", "Salut", "Transport", "Comunicacions", 
"Oci i cultura", "Educació", "Restaurants i hotels", "Altres béns i serveis"
), class = "factor"), despesatotal = structure(c(57629.21, 186827.47, 
210879.71, 105439.85, 91381.21, 344980.45, 117155.39, 44334.78, 
426350.53, 199874.51, 41750.52), format.stata = "%12.0g"), despesamonetaria = structure(c(57629.21, 
186827.47, 210879.71, 105439.85, 91381.21, 344980.45, 117155.39, 
44334.78, 426350.53, 199874.51, 41750.52), format.stata = "%12.0g")), row.names = c(NA, 
-11L), class = c("tbl_df", "tbl", "data.frame"))

使用的代码：

despesatotal_llar <- Despesa_2021_Red %>%
  select("grup_CNAE_Red", "despesatotal", "despesamonetaria") %>%
  group_by(grup_CNAE_Red) %>%
  mutate(despesatotal = sum(despesatotal)) %>%
  mutate(despesamonetaria=sum(despesamonetaria)) %>%
  summarise_if(is.numeric, sum, na.rm=TRUE) %>%
  adorn_totals("row")%>% 
  mutate(proporcio_total=(despesatotal/last(despesatotal))*100)%>% 
  mutate(proporcio_monetaria=(despesamonetaria/last(despesamonetaria))*100)

运行后，Restaurants i hotels组的despesamonetaria字段显示为0，但实际该组对应值不为0，其余结果正常。

错误原因 #

1. 缺失值处理遗漏：
   代码中mutate(despesamonetaria=sum(despesamonetaria))未添加na.rm=TRUE参数，若原数据中Restaurants i hotels组的despesamonetaria存在缺失值（NA），这一步会得到NA值；后续summarise_if的sum(na.rm=TRUE)会忽略NA，最终计算结果为0。
2. 统计逻辑冗余错误：
   mutate将每组所有行替换为组内总和后，再用summarise求和，实际得到的是组内总和 × 组内行数，并非你需要的组内总计值，属于逻辑错误。

解决方案 #

修正代码，直接通过summarise完成组内求和，去掉冗余的mutate步骤，统一处理缺失值：

despesatotal_llar <- Despesa_2021_Red %>%
  select(grup_CNAE_Red, despesatotal, despesamonetaria) %>%
  group_by(grup_CNAE_Red) %>%
  # 直接计算组内总和，添加na.rm=TRUE处理缺失值
  summarise(across(is.numeric, sum, na.rm = TRUE)) %>%
  adorn_totals("row") %>%
  mutate(proporcio_total = (despesatotal / last(despesatotal)) * 100) %>%
  mutate(proporcio_monetaria = (despesamonetaria / last(despesamonetaria)) * 100)

补充说明 #

• 使用across(is.numeric, sum, na.rm = TRUE)替代summarise_if，是因为summarise_if已被tidyverse弃用，across是更推荐的写法。
• 若坚持使用原逻辑，需在mutate的sum中添加na.rm=TRUE，但这种方式仍会导致重复求和，不推荐：

  mutate(despesamonetaria = sum(despesamonetaria, na.rm = TRUE))
  

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