CODEWITHSUNDEEP
rdplyrdata.table
Mártonka
Mártonka

Reputation: 103

Duplicate last row of every group in a data.table and change the value of one column

I have a data.table like the following:

    ID Date        v3  v4
     1 2015.01.01   a   5
     1 2015.02.01   b   5
     1 2015.03.01   f   1
     1 2015.04.01   z   5
     1 2015.05.01   a   2
     2 2013.03.01   a   6
     2 2013.04.01   a   2
     2 2013.05.01   g  13
     2 2013.06.01   a   2
     2 2013.07.01   e   8
     2 2013.08.01   h   9
     2 2013.09.01   h   9

And I would like to insert a row at the end of every group (by ID), which would be the same as the last row except for the date, which should be one month more, than the last observation:

    ID Date        v3  v4
     1 2015.01.01   a   5
     1 2015.02.01   b   5
     1 2015.03.01   f   1
     1 2015.04.01   z   5
     1 2015.05.01   a   2
     1 2015.06.01   a   2
     2 2013.03.01   a   6
     2 2013.04.01   a   2
     2 2013.05.01   g  13
     2 2013.06.01   a   2
     2 2013.07.01   e   8
     2 2013.08.01   h   9
     2 2013.09.01   h   9

Thank you in advance!

Upvotes: 2

Views: 718

Answers (1)

akrun
akrun

Reputation: 887118

We can extract the last row and rbind with the dataset

library(data.table)
library(lubridate)
rbind(setDT(df1), df1[, Date := as.Date(Date, "%Y.%m.%d")][,
       .SD[.N], ID][, Date := Date %m+% months(1)])[order(ID)]
#    ID       Date v3 v4
# 1:  1 2015-01-01  a  5
# 2:  1 2015-02-01  b  5
# 3:  1 2015-03-01  f  1
# 4:  1 2015-04-01  z  5
# 5:  1 2015-05-01  a  2
# 6:  1 2015-06-01  a  2
# 7:  2 2013-03-01  a  6
# 8:  2 2013-04-01  a  2
# 9:  2 2013-05-01  g 13
#10:  2 2013-06-01  a  2
#11:  2 2013-07-01  e  8
#12:  2 2013-08-01  h  9
#13:  2 2013-09-01  h  9
#14:  2 2013-10-01  h  9

Note: It may be better to convert the 'Date' to Date class

Or as @Frank mentioned, seq.Date from base R can be used to get the sequence of 'month'

rbind(setDT(df1), df1[, Date := as.Date(Date, "%Y.%m.%d")][,
     .SD[.N], ID][, Date := seq(Date, length.out = 2, by = 'month')[2], by = ID])[order(ID)]

Upvotes: 1

Related Questions