Staggered addition of rows in R

Question

I have a database with 10,000 entries and I need to correct the dates. Each row is indexed by ID and Year. The time between events and the start date are correct but the dates are wrong. An example of my dataset is below:

    ID     Date       Time First_Date Year
1 ooo-207 1982-05-09     0 1982-05-09 1982
2 ooo-207 1982-05-09    12 1982-05-09 1982
3 ooo-207 1982-06-02    12 1982-05-09 1982
4 ooo-207 1982-06-02    10 1982-05-09 1982
5 ooo-207 1982-06-02    12 1982-05-09 1982
6 ooo-208 1982-07-06     0 1982-07-06 1982
7 ooo-208 1982-07-07    10 1982-06-12 1982
8 ooo-208 1982-07-08    11 1982-06-12 1982
9 ooo-208 1982-08-09    11 1982-06-12 1982

I need to correct the dates by Time to First_Date in a staggered fashion. After each new date is computed, that new date becomes the starting point to add the next waiting time. I need to do this from each animal for each year. The new dataset would look like:

    ID     Date       Time First_Date Year
1 ooo-207 1982-05-09     0 1982-05-09 1982
2 ooo-207 1982-05-21    12 1982-05-09 1982
3 ooo-207 1982-06-02    12 1982-05-09 1982
4 ooo-207 1982-06-12    10 1982-05-09 1982
5 ooo-207 1982-06-24    12 1982-05-09 1982
6 ooo-208 1982-07-06     0 1982-07-06 1982
7 ooo-208 1982-07-16    10 1982-07-06 1982
8 ooo-208 1982-07-27    11 1982-07-06 1982
9 ooo-208 1982-08-07    11 1982-07-06 1982

user_1335174 · Answer

If I correctly understand your problem, this should help:

library(dplyr)

df %>%
  group_by(ID) %>%
  mutate(
    Date = First_Date + cumsum(Time)
  )

user_12158757 · Answer

Here is a base R option using ave

within(
  df,
  Date <- First_Date + ave(Time, ID, FUN = cumsum)
)

giving

       ID       Date Time First_Date Year
1 ooo-207 1982-05-09    0 1982-05-09 1982
2 ooo-207 1982-05-21   12 1982-05-09 1982
3 ooo-207 1982-06-02   12 1982-05-09 1982
4 ooo-207 1982-06-12   10 1982-05-09 1982
5 ooo-207 1982-06-24   12 1982-05-09 1982
6 ooo-208 1982-07-06    0 1982-07-06 1982
7 ooo-208 1982-07-16   10 1982-06-12 1982
8 ooo-208 1982-07-27   11 1982-06-12 1982
9 ooo-208 1982-08-07   11 1982-06-12 1982

Data

> dput(df)
structure(list(ID = c("ooo-207", "ooo-207", "ooo-207", "ooo-207", 
"ooo-207", "ooo-208", "ooo-208", "ooo-208", "ooo-208"), Date = structure(c(4511,
4511, 4535, 4535, 4535, 4569, 4570, 4571, 4603), class = "Date"),
    Time = c(0L, 12L, 12L, 10L, 12L, 0L, 10L, 11L, 11L), First_Date = structure(c(4511,
    4511, 4511, 4511, 4511, 4569, 4545, 4545, 4545), class = "Date"),
    Year = c(1982L, 1982L, 1982L, 1982L, 1982L, 1982L, 1982L,
    1982L, 1982L)), row.names = c("1", "2", "3", "4", "5", "6",
"7", "8", "9"), class = "data.frame")

user_3732271 · Answer

Using data.table

library(data.table)
setDT(df)[, Date := First_Date + cumsum(Time), ID]

Staggered addition of rows in R

3 Answers