r - 通过 R 中的 id 更正上一年

Question

我有类似这样的数据:

df <- data.frame(Id=c(1,1,2,2,3,3,4,4,5,5,6,6,7,7,8,9,9,9,9),Date=c("2013-04","2013-12","2013-01","2013-12","2013-11",
             "2013-12","2012-04","2013-12","2012-08","2014-12","2013-08","2014-12","2013-08","2014-12","2011-01","2013-11","2013-12","2014-01","2014-04"))

要获得正确的格式:

df$Date <- paste0(df$Date,"-01")

我只需要获取 years，这样每个 id 都包含 2 个相互紧随的日期。

如果对现有数据执行如下操作:

require(lubridate)
df$Date <- year(as.Date(df$Date)-days(1))

对于给定的 id，我有时会得到相同的日期。

Date 列所需的输出是这样的:

 2012 2013 2012 2013 2012 2013 2012 2013 2013 2014 2013 2014 2013 2014 2011 2013 2014

请注意，给定 id 的最后日期总是正确的，因此只需根据最后日期更正前一年。日期必须采用只能转换为年份的格式，如图所示。

编辑是这样的:

Id Date 
1 2013-11-01    
1 2013-12-01     
1 2014-01-01    
1 2014-04-01

现在我得到了这个:2012,2013,2013,2013

我需要:2012,2013,2013,2014

Answer 1

这就是我使用 data.table 包解决这个问题的方法(虽然它看起来对我来说很复杂)

library(data.table)
setDT(df)[, year := year(Date)][, 
            year := if(.N == 2) (year[2] - 1):year[2] else year,
            Id][]    

#     Id       Date year indx
#  1:  1 2013-04-01 2012    2
#  2:  1 2013-12-01 2013    2
#  3:  2 2013-01-01 2012    2
#  4:  2 2013-12-01 2013    2
#  5:  3 2013-11-01 2012    2
#  6:  3 2013-12-01 2013    2
#  7:  4 2012-04-01 2012    2
#  8:  4 2013-12-01 2013    2
#  9:  5 2012-08-01 2013    2
# 10:  5 2014-12-01 2014    2
# 11:  6 2013-08-01 2013    2
# 12:  6 2014-12-01 2014    2
# 13:  7 2013-08-01 2013    2
# 14:  7 2014-12-01 2014    2
# 15:  8 2011-01-01 2011    1

或一步完成(感谢@Arun 提供):

setDT(df)[, year := {tmp = year(Date); 
            if (.N == 2L) (tmp[2]-1L):tmp[2] else tmp},
            Id]

---

编辑:每个 OPs 新数据，我们可以通过添加额外的索引来修改代码

setDT(df)[, indx := if(.N > 2) rep(seq_len(.N/2), each = 2) + 1L else .N, Id] 
df[, year := {tmp = year(Date); if (.N > 1L) (tmp[2] - 1L):tmp[2] else tmp}, 
     list(Id, indx)][]
#     Id       Date indx year
#  1:  1 2013-04-01    2 2012
#  2:  1 2013-12-01    2 2013
#  3:  2 2013-01-01    2 2012
#  4:  2 2013-12-01    2 2013
#  5:  3 2013-11-01    2 2012
#  6:  3 2013-12-01    2 2013
#  7:  4 2012-04-01    2 2012
#  8:  4 2013-12-01    2 2013
#  9:  5 2012-08-01    2 2013
# 10:  5 2014-12-01    2 2014
# 11:  6 2013-08-01    2 2013
# 12:  6 2014-12-01    2 2014
# 13:  7 2013-08-01    2 2013
# 14:  7 2014-12-01    2 2014
# 15:  8 2011-01-01    1 2011
# 16:  9 2013-11-01    2 2012
# 17:  9 2013-12-01    2 2013
# 18:  9 2014-01-01    3 2013
# 19:  9 2014-04-01    3 2014

或者@akrun 提供的另一种可能的解决方案

setDT(df)[, `:=`(year = year(Date), indx = .N, indx2 = as.numeric(gl(.N,2, .N))), Id]
df[indx > 1, year:=(year[2]-1):year[2], list(Id, indx2)][]