I have a dataframe in 'long format' with subjects being observed multiple time:
dat1 <- tribble(
~CODE, ~V1, ~V2, ~session, ~date,
"1111P11", 2, 3, 1, "2020-09-01",
"1111P11", 3, 2, 2, "2020-09-08",
"1111P11", 1, 3, 3, "2020-09-15",
"1111P11", 3, 4, 4, "2020-09-25",
"2222P22", 5, 1, 1, "2020-05-15",
"2222P22", 3, 2, 2, "2020-05-22",
"2222P22", 1, 4, 3, "2020-05-30",
"3333P33", 3, 4, 1, "2020-06-10",
"3333P33", 4, 1, 2, "2020-06-17",
"3333P33", 3, 5, 3, "2020-06-24",
"3333P33", 4, 2, 4, "2020-07-01",
"3333P33", 3, 4, 5, "2020-07-10"
)
dat1$date <- date(dat$date)
I would like to calculate for each subject the time interval between each session and the first session, which should result in this:
dat2 <- tribble(
~CODE, ~V1, ~V2, ~session, ~date, ~interv.1st.sess,
"1111P11", 2, 3, 1, "2020-09-01", 0,
"1111P11", 3, 2, 2, "2020-09-08", 7,
"1111P11", 1, 3, 3, "2020-09-15", 14,
"1111P11", 3, 4, 4, "2020-09-25", 24,
"2222P22", 5, 1, 1, "2020-05-15", 0,
"2222P22", 3, 2, 2, "2020-05-22", 7,
"2222P22", 1, 4, 3, "2020-05-30", 15,
"3333P33", 3, 4, 1, "2020-06-10", 0,
"3333P33", 4, 1, 2, "2020-06-17", 7,
"3333P33", 3, 5, 3, "2020-06-24", 14,
"3333P33", 4, 2, 4, "2020-07-01", 21,
"3333P33", 3, 4, 5, "2020-07-10", 30
)
I've been trying to solve this somehow with group_by()
, but didn't succeed. Is there a tidyverse way (or any other) to do this?
CodePudding user response:
Try with dplyr
and lubridate
.
Have included date1
to be explicit about converting date format
library(dplyr)
library(lubridate)
dat1 %>%
group_by(CODE) %>%
mutate(date1 = ymd(date),
diff = date1 - first(date1))
#> # A tibble: 12 x 7
#> # Groups: CODE [3]
#> CODE V1 V2 session date date1 diff
#> <chr> <dbl> <dbl> <dbl> <chr> <date> <drtn>
#> 1 1111P11 2 3 1 2020-09-01 2020-09-01 0 days
#> 2 1111P11 3 2 2 2020-09-08 2020-09-08 7 days
#> 3 1111P11 1 3 3 2020-09-15 2020-09-15 14 days
#> 4 1111P11 3 4 4 2020-09-25 2020-09-25 24 days
#> 5 2222P22 5 1 1 2020-05-15 2020-05-15 0 days
#> 6 2222P22 3 2 2 2020-05-22 2020-05-22 7 days
#> 7 2222P22 1 4 3 2020-05-30 2020-05-30 15 days
#> 8 3333P33 3 4 1 2020-06-10 2020-06-10 0 days
#> 9 3333P33 4 1 2 2020-06-17 2020-06-17 7 days
#> 10 3333P33 3 5 3 2020-06-24 2020-06-24 14 days
#> 11 3333P33 4 2 4 2020-07-01 2020-07-01 21 days
#> 12 3333P33 3 4 5 2020-07-10 2020-07-10 30 days
Created on 2021-12-19 by the reprex package (v2.0.1)
CodePudding user response:
Here's a base R option using ave
-
transform(dat1, diff_in_days = as.integer(date - ave(date, CODE,
FUN = function(x) x[1])))
# CODE V1 V2 session date diff_in_days
#1 1111P11 2 3 1 2020-09-01 0
#2 1111P11 3 2 2 2020-09-08 7
#3 1111P11 1 3 3 2020-09-15 14
#4 1111P11 3 4 4 2020-09-25 24
#5 2222P22 5 1 1 2020-05-15 0
#6 2222P22 3 2 2 2020-05-22 7
#7 2222P22 1 4 3 2020-05-30 15
#8 3333P33 3 4 1 2020-06-10 0
#9 3333P33 4 1 2 2020-06-17 7
#10 3333P33 3 5 3 2020-06-24 14
#11 3333P33 4 2 4 2020-07-01 21
#12 3333P33 3 4 5 2020-07-10 30