Reorganize time related data by group and ID to a unique row per ID

user_3108184 0
Viewed 25

I am stuck with a quirky data shape in R and I don't manage to resolve efficiently the issue. Actually i find some solutions with some simple joins but let's assume my data is pretty big and going that way will significantly increase my memory usage which would not be optimal here. The other solution, even less optimal, would involve looping through the entire dataset, bug again it's kinda inefficient.

The idea of the dataset is to provide for a given population, a date-time interval where a disease is tested and the result associated with it. The data is organized by row, and each row is a time interval for a given id and the result of the test (in my example I choose only positive tests, but it could be also negative). Often the periods are contiguous ie the start of a second periode is one day later after the finish of the first (cf exemple), but sometimes months can pass without any information, and then an other test is performed.

Here is a example of the data :

library(tidyverse)
library(lubridate)

start <- c('2017-08-28', '2018-03-14', '2018-08-27', '2020-02-26', '2020-09-01')
finish <- c('2018-03-13', '2018-08-26', '2018-11-28', '2020-08-31', '2021-03-01')
id <- rep('a', 5)

df <- data.frame('start' = ymd(start),
                 'finish' = ymd(finish),
                 'id' = id,
                 'test' = rep('positif', 5))

Which gives :

> df
       start     finish id    test
1 2017-08-28 2018-03-13  a positif
2 2018-03-14 2018-08-26  a positif
3 2018-08-27 2018-11-28  a positif
4 2020-02-26 2020-08-31  a positif
5 2020-09-01 2021-03-01  a positif

With some adjusts, the idea is to calculate the day interval between each test

dff <- df %>%
  mutate(finish_lag = lag(finish),
         interval = start - finish_lag
  )
  
> dff
       start     finish id    test finish_lag interval
1 2017-08-28 2018-03-13  a positif       <NA>  NA days
2 2018-03-14 2018-08-26  a positif 2018-03-13   1 days
3 2018-08-27 2018-11-28  a positif 2018-08-26   1 days
4 2020-02-26 2020-08-31  a positif 2018-11-28 455 days
5 2020-09-01 2021-03-01  a positif 2020-08-31   1 days

What I would like, is to have one ID per row, for a continuous period, in this example I would only have 2 rows : the first period (row 1 to 3), then the second one 455 days after which goes to the end of the data. Often I would only have 1 row because the tests are contiguous.

So in this example the desired output is:

       start     finish id    test
1 2017-08-28 2018-11-28  a positif
2 2020-02-26 2021-03-01  a positif
dataframe r datetime
1 Answers

I hope you want this?

  • I am assuming, your data is sorted for each id & test. If not you have to sort it then prior to application of following code
  • Just modify the default argument in lag to suit the need in our case. I added date one day prior to first date of start, after grouping on id and test (assuming you want your results grouped like that). Else remove that group_by
  • created a dummy grp variable to segregate data where difference is not of consecutive days.
library(tidyverse)
library(lubridate)

df %>% group_by(id, test) %>%
  mutate(finish_lag = lag(finish, default = first(start) - 1),
         interval = start - finish_lag) %>%
  group_by(grp = cumsum(interval != 1) + 1, .add = T) %>%
  summarise(start = first(start),
            finish = last(finish), .groups = 'drop')

#> # A tibble: 2 x 5
#>   id    test      grp start      finish    
#>   <chr> <chr>   <dbl> <date>     <date>    
#> 1 a     positif     1 2017-08-28 2018-11-28
#> 2 a     positif     2 2020-02-26 2021-03-01

Once you understand the code/strategy, this all can be simplified as

df %>% group_by(id, test, grp = cumsum((start - lag(finish, default = first(start) -1)) != 1) + 1) %>%
  summarise(start = first(start),
            finish = last(finish), .groups = 'drop')

# A tibble: 2 x 5
  id    test      grp start      finish    
  <chr> <chr>   <dbl> <date>     <date>    
1 a     positif     1 2017-08-28 2018-11-28
2 a     positif     2 2020-02-26 2021-03-01
user_28848590
Related