Getting time spread between max spells

user_3570187 0
Viewed 52

I have a data frame as follows and would like to create a time spread variable based on max spread of events.

 A<- c('1244', '1244', '1244', '1245', '1245', '1245', '1245', '1245', '1245', '1245')
 sequence<- c(1,1,0, 1,1,0,0,1,1,1)
 # 1= lived locally and 0 lived internationally 
 date<- c('19/Oct/12', '19/Oct/12', '20/Oct/12', '19/Oct/11', '19/Oct/11', '22/Nov/12', '24/Nov/12', '29/Nov/12','2/Dec/12', '29/Dec/12')

 df<- data.frame(A,sequence, date)

I would like to calculate the max time spread of these people in locations both locally and internationally . For example if we see the person 1244 she/he has sequence 110 (i.e., they lived in two spells in locally and 1 spell internationally and if we want to calculate the time spread the max_local_timespread= 0 day (which is difference between the last date- start date (so, 0 days= 19/Oct/12-19/Oct/12) and max_international_timespread= 0(20/Oct/12-20/Oct/12)

And for person 1245 it is 1100111 so they have lived locally in two spells, the first spell is 11 and second one is 111. As I want to get the max time spread locally in this case it is the time difference between start date and final date for last spell 111. Hence max_local_timespread= 30 days (29/Dec/12 -29/Nov/12) and max_international_timespread= 2 days ('24/Nov/12'-'22/Nov/12')

I am expecting an output as follows:

A max_local max_international max_local_timespread max_international_timespread

   1244    2        1                  0                     0
   1245    3        2                  30                    2

Any suggestions on how this can be achieved?

tidyr dplyr r
1 Answers

You can achieve this in the following way using dplyr and data.table:

df$date <- as.Date(df$date, "%d/%b/%y")

df <- df %>%
  group_by(A, sequence, rleid(sequence)) %>%
  mutate(max = n(),
         timespread = difftime(max(date), min(date), units = "days")) %>%
  ungroup() %>%
  group_by(A, sequence) %>%
  summarize(max = max(max),
            timespread = max(timespread)) %>%
  ungroup()


dcast(setDT(df), A~sequence, value.var = c("max", "timespread"))

Resulting in the following table:

      A max_0 max_1 timespread_0 timespread_1
1: 1244     1     2       0 days       0 days
2: 1245     2     3       2 days      30 days

a data.table alternative:

library(data.table)

dt <- data.table(A,sequence, date)

dt[, date := as.Date(date, format = "%d/%b/%y")]

dt <- dt[, .(max = .N, timespread = difftime(max(date), min(date), units = "days")), by=.(A, sequence, rleid(sequence))]
dt <- dt[, .(max = max(max), timespread = max(timespread)), by=.(A, sequence)]

dcast(dt, A~sequence, value.var = c("max", "timespread"))
user_94228710
Related