R函数将行中的日期转换为时间范围

Question

我有一个包含主题和年份的数据框架，每年只有一个主题。当日期连续时，我想从类别中创建时间范围:

cat   <- c("Cat1","Cat1","Cat2","Cat2","Cat2","Cat3","Cat2","Cat2","Cat2")
year <- c(2010,2011,2012,2013,2014,2015,2016,2017,2018)
df <- data.frame(Cat=cat, Year=year)
#  Which looks like the following:
# Cat1 2010
# Cat1 2011
# Cat2 2012
# Cat2 2013
# Cat2 2014
# Cat3 2015
# Cat2 2016
# Cat2 2017
# Cat2 2018

我想要的输出是一个像这样的数据帧:

cat   <- c("Cat1","Cat2","Cat3","Cat2")
year <- c(2010,2012,2015,2016)
e_year <- c(2011,2014,2015,2018)
df_goal <- data.frame(Cat=cat, Year=year, EYear = e_year)
#  Cat Year EYear
# Cat1 2010  2011
# Cat2 2012  2014
# Cat3 2015  2015
# Cat2 2016  2018

我想用循环来做，但我认为这在r中不合适，所以在我花时间解决这个问题之前，我想问一下。

Answer 1

这与按组计算平均值(按组汇总)和使用rle从组中获取第一个和最后一个值密切相关，尽管两者略有不同。

基地R

out <- aggregate(Year ~ Cat + grp, data = df, FUN = range)
out <- do.call(cbind.data.frame, out[,-2])
names(out)[2:3] <- c("Year", "EYear")
out
#    Cat Year EYear
# 1 Cat1 2010  2011
# 2 Cat2 2012  2014
# 3 Cat3 2015  2015
# 4 Cat2 2016  2018

dplyr

library(dplyr)
df %>%
group_by(grp = cumsum(Cat != lag(Cat, default = ""))) %>%
summarize(Cat = Cat[1], EYear = max(Year), Year = min(Year)) %>%
ungroup() %>%
select(-grp)
# # A tibble: 4 x 3
#   Cat   EYear  Year
#   <chr> <dbl> <dbl>
# 1 Cat1   2011  2010
# 2 Cat2   2014  2012
# 3 Cat3   2015  2015
# 4 Cat2   2018  2016

data.table

library(data.table)
as.data.table(df)[, .(Cat = Cat[1], EYear = max(Year), Year = min(Year)), by = .(grp = rleid(Cat))
][, grp := NULL]
#       Cat EYear  Year
#    <char> <num> <num>
# 1:   Cat1  2011  2010
# 2:   Cat2  2014  2012
# 3:   Cat3  2015  2015
# 4:   Cat2  2018  2016

Answer 2

与dplyr:

df %>% group_by(Cat, N=cumsum(Cat != lag(Cat, default=""))) %>%
summarize(SYear=min(Year), EYear=max(Year)) %>%
arrange(N) %>% select(-N)

输出:

Cat   SYear EYear
<chr> <dbl> <dbl>
1 Cat1   2010  2011
2 Cat2   2012  2014
3 Cat3   2015  2015
4 Cat2   2016  2018