Adding time lapsed column to data frame with date

Question

I had a data frame with dates, I would like to add another column to df1 with time difference between between the date in $Date and the first entry in df2$Cov.Date, in weeks. so it should look like the following.

This is the data frame that I have, df1, that I would like to add an extra column to:

df1 <- data.frame (Id  = c("1", "1", "2","2"),
    Date = c("2005-02-05", "2005-04-05", "2005-01-15","2005-02-11")
    )

This is df2, I would like to pull the first $Date entry from here

df2 <- data.frame (Id  = c("1", "1", "1"),
    Date = c("2004-12-06", "2005-01-02", "2005-01-15")
    )

This is what I would like for df1 to look like:

df1 <- data.frame (Id  = c("1", "1", "2","2"),
    Date = c("2005-02-05", "2005-04-05", "2005-01-15","2005-02-11")
    t = c(7.14, 14.29, 2.86, 6.71)
    )

I can manually add a column with times, but I can't get it to automatically populate a new column with the difference in time from a date and an origin date

Please share sample data and code in a [reproducible format](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example). Please [do not post code or data in images](https://meta.stackoverflow.com/q/285551/2372064) — MrFlick, Feb 15 '23 at 18:57
Do you want `df2 %>% slice_head(n = 1, by = 'Id') %>% left_join(df1, ., by = 'Id') %>% group_by(Id) %>% mutate(t = as.numeric(difftime(Date.x, Date.y, units = "weeks")))` — akrun, Feb 15 '23 at 19:29

score 0 · Accepted Answer · answered Feb 15 '23 at 19:31

Maybe something like this, dividing difference in days by 7.

cbind(df1, t = as.numeric(as.Date(df1$Date) - as.Date(df2$Date[1])) / 7)
  Id       Date         t
1  1 2005-02-05  8.714286
2  1 2005-04-05 17.142857
3  2 2005-01-15  5.714286
4  2 2005-02-11  9.571429

Adding time lapsed column to data frame with date

1 Answers1