CODEWITHSUNDEEP
rdatereplicate
Regan Alpha
Regan Alpha

Reputation: 67

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

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

Upvotes: 2

Views: 637

Answers (3)

Pierre L
Pierre L

Reputation: 28441

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)]

Upvotes: 2

S.C
S.C

Reputation: 740

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)
}

Upvotes: 0

quant
quant

Reputation: 4482

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

Upvotes: 0

Related Questions