如何用dplyr计算Site和Transect层级下栖息地占样带的长度百分比

我有一个名为df的数据框，每行记录了某一Site（样点）下Transect（样带）中特定Habitat（栖息地）的起始（Start）和结束（End）米数，样带长度在样点内和样点间存在差异。示例数据如下：

df <- data.frame(Site = c("A","A","A","A","A","A","A","A","A","B","B","B","B","B","B","B","B"),
                 Transect = c(1,1,1,1,2,2,2,2,2,1,1,1,1,2,2,2,2),
                 Habitat = c("X","Y","X","Z","Z","Y","X","Z","X","X","Z","X","Y","Z","X","Y","Z"),
                 Start=c(0,2.8,3.4,5,0,1.5,5,8,12,0,2,5,7.5,0,4,8,12),
                 End=c(2.8,3.4,5,10,1.5,5,8,12,15,2,5,7.5,20,4,8,12,15))

数据预览：

Site Transect Habitat Start  End
1     A        1       X   0.0  2.8  # 栖息地`X`位于0到2.8米之间
2     A        1       Y   2.8  3.4  # 栖息地`Y`位于2.8到3.4米之间
3     A        1       X   3.4  5.0  # 栖息地`X`位于3.4到5.0米之间
4     A        1       Z   5.0 10.0  # 栖息地`Z`位于5到10.0米之间
5     A        2       Z   0.0  1.5
6     A        2       Y   1.5  5.0
7     A        2       X   5.0  8.0
8     A        2       Z   8.0 12.0
9     A        2       X  12.0 15.0
10    B        1       X   0.0  2.0
11    B        1       Z   2.0  5.0
12    B        1       X   5.0  7.5
13    B        1       Y   7.5 20.0
14    B        2       Z   0.0  4.0
15    B        2       X   4.0  8.0
16    B        2       Y   8.0 12.0
17    B        2       Z  12.0 15.0

比如样点A的样带1总长度为10米，栖息地X总长度4.4米，占比44%。我已经计算出每行栖息地的长度：

df$Length <- df$End - df$Start

但尝试以下代码时，max(End)只能获取当前Habitat组的最大End值，无法拿到对应Site和Transect的样带总长度：

df2 <- as.data.frame(df %>% group_by(Site, Transect, Habitat) %>% summarise(Percentage = (sum(Length)/max(End))*100))

期望输出：

Site Transect Habitat Percentage
1     A        1       X       44.0
2     A        1       Y        6.0
3     A        1       Z       50.0
4     A        2       X       40.0
5     A        2       Y       23.3
6     A        2       Z       36.7
7     B        1       X       22.5
8     B        1       Y       62.5
9     B        1       Z       15.0
10    B        2       X       26.7
11    B        2       Y       26.7
12    B        2       Z       46.7

请问如何修改代码实现需求？

问题核心是要先获取每个Site-Transect组的总长度，再基于这个总长度计算单个栖息地的占比。以下是两种可行的实现方法：

方法1：先计算样带总长度再合并 #

先按Site和Transect分组计算每个样带的总长度，再将该数据与原数据合并，最后按三级分组计算占比：

library(dplyr)

# 计算每个样带的总长度
transect_total <- df %>% 
  group_by(Site, Transect) %>% 
  summarise(Transect_Length = max(End), .groups = "drop")

# 合并数据并计算占比
df2 <- df %>% 
  left_join(transect_total, by = c("Site", "Transect")) %>% 
  group_by(Site, Transect, Habitat) %>% 
  summarise(Percentage = round((sum(Length)/first(Transect_Length))*100, 1), .groups = "drop")

方法2：嵌套分组计算 #

先按Site-Transect分组，在组内计算总长度并添加为新列，再嵌套分组到Habitat级别计算占比：

df2 <- df %>% 
  group_by(Site, Transect) %>% 
  mutate(Transect_Length = max(End)) %>% 
  group_by(Habitat, .add = TRUE) %>% 
  summarise(Percentage = round((sum(Length)/first(Transect_Length))*100, 1), .groups = "drop")

两种方法均能得到符合期望的输出，其中round()函数用于保留1位小数，与示例输出格式一致。

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