R: replicate a row and update by next date per row

Question

The input and its intended output show that I want to replicate the row of the input and update the date entry. How can I do this?

Input

> aa<- data.frame(a=c(1,11,111),b=c(2,22,222),length=c(3,5,1),date=c(as.Date("28.12.2016",format="%d.%m.%Y"), as.Date("30.12.2016",format="%d.%m.%Y"), as.Date("01.01.2017",format="%d.%m.%Y")))
> aa
    a   b length       date
1   1   2      3 2016-12-28
2  11  22      5 2016-12-30
3 111 222      1 2017-01-01

Intended Output

  a   b length       date
1 1   2      3 2016-12-28
2 1   2      3 2016-12-29
3 1   2      3 2016-12-30
4 11  22     5 2016-12-30
5 11  22     5 2016-12-31
6 11  22     5 2017-01-01
7 11  22     5 2017-01-02
8 11  22     5 2017-01-03
9 111 222    1 2017-01-01

Answer 1

You can use base, dplyr, or data.table for the grouping operations. First repeat the rows to get the size of the new data correct. Then increment the days.

library(dplyr)
aa2 <- aa[rep(1:nrow(aa), aa$length),]
aa2 %>% group_by(a,b) %>% mutate(date= date + 1:n() - 1L)
# Source: local data frame [9 x 4]
# Groups: a, b [3]
# 
#       a     b length       date
#   <dbl> <dbl>  <dbl>     <date>
# 1     1     2      3 2016-12-28
# 2     1     2      3 2016-12-29
# 3     1     2      3 2016-12-30
# 4    11    22      5 2016-12-30
# 5    11    22      5 2016-12-31
# 6    11    22      5 2017-01-01
# 7    11    22      5 2017-01-02
# 8    11    22      5 2017-01-03
# 9   111   222      1 2017-01-01

#data.table
library(data.table)
aa2 <- aa[rep(1:nrow(aa), aa$length),]
setDT(aa2)[, date := date + 1:.N - 1L, by= .(a,b)]

#base
aa2 <- aa[rep(1:nrow(aa), aa$length),]
transform(aa2, date=ave(date, a, FUN=function(x) x + 1:length(x) - 1L))

For more concise syntax, we can take advantage of the recycling rules of data.table, credit @Henrik:

setDT(aa)[ , .(date = date + 1:length - 1), by = .(a, b)]

Answer 2

Not as elegant as the one using dplyr and data.table packages, but low level:

replicaterow1 <- function(df1 = aa) {
    newdf <- df1[0,]
    rowss <- nrow(df1)
    rowcount <- 1
    for (i in 1:rowss) {
        rowi <- df1[i,]
        reps <- as.integer(rowi[3])
        newrow <- rowi
        newdf[rowcount,] <- rowi
        rowcount <- rowcount + 1
        if (reps > 1) {
            for(j in 1:(reps-1)) {
                newrow[4] <- newrow[4] + 1
                newdf[rowcount,] <- newrow
                rowcount <- rowcount + 1
            }
        }
    }
    return(newdf)
}

Answer 3

This

aa<- data.frame(a=c(1),b=c(2),length=c(3),date=as.Date("28.12.2016",format="%d.%m.%Y"))


aa <- aa[rep(row.names(aa), aa$length), 1:4]
aa <- as.data.table(aa)
aa[,row:=.I]
aa[,date:=date+row-1]
aa[,row:=NULL]

Results in

   a b length       date
1: 1 2      3 2016-12-28
2: 1 2      3 2016-12-29
3: 1 2      3 2016-12-30