CODEWITHSUNDEEP
rdataframe
sherek_66
sherek_66

Reputation: 531

finding overlapping time in each group of data set

I have columns household , persons in each household, tour (each tour contains different trips for each person) ,and mode ( mode of travel of each person in each tour), time_ARR start time of tour, time_Dep end time of the tour.

I want to find an indicator with respect of people who have car mode and people who have non-car mode.

The indicator is 1 for each person who have non-car mode in a tour if the time of tour has intersection with a person in a household with mode car.

here is example to make it clear:

  family    persons    mode    tour   start time    end time
     1      1           car     1        2:30         15:30
     1      1         non-car   2        20:00        8:30
     1      2         non-car   1        3:00         10:00
     1      3           car     1        19:10        24:00
     2      1         non-car   1        3:00         10:00
     2      2           car     1        19:10        24:00

In the first family person 1 has non-car mode in his second tour and it has intersection with third person.

also second person 2 in first family has non-car mode and she is also has intersection with first person in his first tour.

in the second family person 1 has non-car mode and it dose not intersection with car mode of other people . so 

  family    persons    mode    tour   start time    end time. indicator
     1      1           car     1        2:30         15:30.      NA
     1      1         non-car   2        20:00        8:30.       1
     1      2         non-car   1        3:00         10:00.      1 
     1      3           car     1        19:10        24:00.      NA
     2      1         non-car   1        3:00         10:00.      0
     2      2           car     1        19:10        24:00.      NA

instead of NA it can be 0 or one , it dose not matter at all

Upvotes: 0

Views: 46

Answers (1)

r2evans
r2evans

Reputation: 160447

One way to look at it is to use data.table::foverlaps, using the times as overlapping events.

Prepping data

dat <- read.table(header = TRUE, stringsAsFactors = FALSE, text = "
  family    persons    mode    tour   starttime    endtime
     1      1           car     1        2:30         15:30
     1      1         non-car   2        20:00        8:30
     1      2         non-car   1        3:00         10:00
     1      3           car     1        19:10        24:00
     2      1         non-car   1        3:00         10:00
     2      2           car     1        19:10        24:00")
library(data.table)
setDT(dat)

# convert to actual timestamps ... might also use lubridate or hms packages
dat[, c("starttime", "endtime") := lapply(.(starttime, endtime), as.POSIXct, format = "%H:%M") ]
# assign a simple per-row id
dat[, rowid := seq_len(.N)]

Unfortunately, because you only list times in your sample data, you have a backwards event, so I'll shift the endtime to "tomorrow":

dat[starttime > endtime,]
#    family persons    mode tour           starttime             endtime rowid
# 1:      1       1 non-car    2 2019-07-29 20:00:00 2019-07-29 08:30:00     2
dat[starttime > endtime, endtime := endtime + 86400 ]

Fuzzy Overlaps

setkey(dat, starttime, endtime)
merged <- foverlaps(dat[,.(rowid,mode,starttime,endtime)], dat[,.(rowid,mode,starttime,endtime)])
merged[ mode == "car" & i.mode != "car", ]
#    rowid mode           starttime             endtime i.rowid  i.mode         i.starttime           i.endtime
# 1:     1  car 2019-07-29 02:30:00 2019-07-29 15:30:00       3 non-car 2019-07-29 03:00:00 2019-07-29 10:00:00
# 2:     1  car 2019-07-29 02:30:00 2019-07-29 15:30:00       5 non-car 2019-07-29 03:00:00 2019-07-29 10:00:00
# 3:     4  car 2019-07-29 19:10:00 2019-07-30 00:00:00       2 non-car 2019-07-29 20:00:00 2019-07-30 08:30:00
# 4:     6  car 2019-07-29 19:10:00 2019-07-30 00:00:00       2 non-car 2019-07-29 20:00:00 2019-07-30 08:30:00

The gist to take away from this is that i.rowid shows the "second person" who is "non-car" while the first person is "car". From this, it's easy enough to determine

# non-car people without a "car" complement
setdiff(dat$rowid, merged[ mode == "car" & i.mode != "car", ]$i.rowid)
# [1] 1 4 6

# non-car people with a car complement
unique(merged[ mode == "car" & i.mode != "car", ]$i.rowid)
# [1] 3 5 2

# non-car people might be able to use these car people
merged[ mode == "car" & i.mode != "car", ][, .(hascar = rowid, needscar = i.rowid)]
#    hascar needscar
# 1:      1        3
# 2:      1        5
# 3:      4        2
# 4:      6        2

Upvotes: 1

Related Questions