按连续日期分组R.

Question

我想根据在同一个洞穴中发现的连续月份对动物进行分组，但如果这些月份不是连续的，也会将这些组分开。

#Input Data
burrow.data <- read.csv
Animal Burrow       Date
1     027  B0961 2022-03-01
2     027  B0961 2022-04-26
3     033  1920  2021-11-02
4     033  1955  2022-03-29
5     033  1955  2022-04-26
6     063  B0540 2021-04-21
7     063  B0540 2022-01-04
8     063  B0540 2022-03-01
9     101  B0021 2020-11-23
10    101  B0021 2020-12-23
11    101  B0021 2021-11-04
12    101  B0021 2022-01-06
13    101  B0021 2022-02-04
14    101  B0021 2022-03-03
#Expected Output
Animal Burrow grp Date.Start   Date.End
1    033  1920   1 2021-11-02 2021-11-02
2    033  1955   1 2022-03-29 2022-04-26
3    101  B0021  1 2020-11-23 2020-12-23
4    101  B0021  2 2022-01-06 2020-03-03
5    063  B0540  1 2021-04-21 2022-03-01
6    027  B0961  1 2022-03-01 2022-04-26

我使用了另一篇文章中的代码:在R中分组连续日期

And wrote:
burrow.input <- burrow.data[order(burrow.data$Date),]
burrow.input$grp <- ave(as.integer(burrow.input$Date), burrow.input[-4], FUN = function(z) cumsum(c(TRUE, diff(z)>1)))
burrow.input
out <- aggregate(Date ~ Animal + Burrow + grp, data = burrow.input, FUN = function(z) setNames(range(z), c("Start", "End")))
out <- do.call(data.frame,out)
out[,4:5] <- lapply(out[,4:5], as.Date, origin = "1970-01-01")
out

代码将101分组为一个组，而不是按日期间隔分成两个组(见下文)。我该如何解决这个问题?

Animal Burrow grp Date.Start   Date.End
1    033  1920   1 2021-11-02 2021-11-02
2    033  1955   1 2022-03-29 2022-04-26
3    101  B0021  1 2020-11-23 2022-03-03
4    063  B0540  1 2021-04-21 2022-03-01
5    027  B0961  1 2022-03-01 2022-04-26

Answer 1

按Animal、Burrow和一个分组变量对数据进行分组，该分组变量在每次日期跳跃超过1个月时更改。在这里。yearmon将日期转换为yearmon对象，其内部为1月为年加0,2月为1/12，…11/12表示Dec，所以将它乘以12，检查它与前一个值之间的差是否大于1。取其累积和来生成分组变量。最后总结一下，对添加的分组变量进行排序并删除。

library(dplyr)
library(zoo)
burrow.data %>%
group_by(Animal, Burrow, 
diff = cumsum( c(1, diff(12 * as.yearmon(Date)) > 1) ) ) %>%
summarize(Date.start = first(Date), Date.end = last(Date), .groups = "drop") %>%
arrange(Burrow) %>%
select(-diff)

给:

# A tibble: 7 × 4
Animal Burrow Date.start Date.end  
<int> <chr>  <chr>      <chr>     
1     33 1920   2021-11-02 2021-11-02
2     33 1955   2022-03-29 2022-04-26
3    101 B0021  2020-11-23 2021-11-04
4    101 B0021  2022-01-06 2022-03-03
5     63 B0540  2021-04-21 2022-01-04
6     63 B0540  2022-03-01 2022-03-01
7     27 B0961  2022-03-01 2022-04-26

注意

可复制形式的输入数据为:

burrow.data <-
structure(list(Animal = c(27L, 27L, 33L, 33L, 33L, 63L, 63L, 
63L, 101L, 101L, 101L, 101L, 101L, 101L), Burrow = c("B0961", 
"B0961", "1920", "1955", "1955", "B0540", "B0540", "B0540", "B0021", 
"B0021", "B0021", "B0021", "B0021", "B0021"), Date = c("2022-03-01", 
"2022-04-26", "2021-11-02", "2022-03-29", "2022-04-26", "2021-04-21", 
"2022-01-04", "2022-03-01", "2020-11-23", "2020-12-23", "2021-11-04", 
"2022-01-06", "2022-02-04", "2022-03-03")), class = "data.frame", 
row.names = c("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", 
"11", "12", "13", "14"))