如何为多分组年份数据添加Total列？dplyr报错排查与解决

原始数据如下：

Huc12           Year    Density Value
Accotink Creek  2018    0   18054.121680    
Accotink Creek  2018    1   NA  
Accotink Creek  2018    2   NA  
Accotink Creek  2018    3   NA  
Accotink Creek  2018    4   NA  
Accotink Creek  2018    Total   18054.121680    
Accotink Creek  2019    0   17646.898598    
Accotink Creek  2019    1   NA  
Accotink Creek  2019    2   5.736108    
Accotink Creek  2019    3   16.362691   
Accotink Creek  2019    4   385.123872  
Accotink Creek  2019    Total   18054.121269    

尝试执行以下代码：

tidy <- tidy |> group_by(Year) |> 
    mutate(Total = Value[Density == 'Total'])  |>
    filter(Density != 'Total')

触发错误：

Error in `mutate()`:
ℹ In argument: `Total = Value[Density == "Total"]`.
ℹ In group 1: `Year = "2018"`.
Caused by error:
! `Total` must be size 1488 or 1, not 248.
Backtrace:
 1. dplyr::filter(...)
 9. dplyr:::dplyr_internal_error(...)

问题核心：年份会随每个Huc12重复（数据集共7440行），每个Huc12对应年份的总计值是独立的，仅按Year分组会导致每个组内包含多个Huc12的Total值，无法匹配组内行数。

期望输出格式：

Huc12           Year    Density Value         Total
Accotink Creek  2018    0   18054.121680  18054.121680  
Accotink Creek  2018    1   NA        18054.121680
Accotink Creek  2018    2   NA        18054.121680
Accotink Creek  2018    3   NA        18054.121680
Accotink Creek  2018    4   NA        18054.121680  
Accotink Creek  2019    0   17646.898598  18054.121269  
Accotink Creek  2019    1   NA        18054.121269
Accotink Creek  2019    2   5.736108      18054.121269  
Accotink Creek  2019    3   16.362691     18054.121269  
Accotink Creek  2019    4   385.123872    18054.121269      

解决方案 #

调整分组维度，同时按Huc12和Year分组，确保每个分组内仅对应单个流域的单一年份数据，这样Value[Density == "Total"]会返回唯一值并广播到组内所有行。修改后代码如下：

tidy <- tidy |> 
  group_by(Huc12, Year) |> 
  mutate(Total = Value[Density == "Total"]) |> 
  filter(Density != "Total") |> 
  ungroup()

补充说明 #

• 同时按Huc12和Year分组后，每个组内仅包含单个流域对应单一年份的所有行，其中Density == "Total"的行只有一条，因此Value[Density == "Total"]会返回单个值，能匹配组内所有行的行数要求。
• 最后用ungroup()取消分组，避免后续数据操作受分组状态影响。

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