Create 24 columns and a dummy if timespan overlaps with hour

Question

I have a "start" and "end" timestamp.

df <- structure(list(Vagt_Start = structure(c(1535412600, 1531006200, 
                                              1518823800, 1535671800, 1531092600, 1527550200, 1535499000, 1530919800, 
                                              1518910200, 1535585400), class = c("POSIXct", "POSIXt"), tzone = "UTC"), 
                     Vagt_Slut = structure(c(1535441400, 1531035000, 1518852600, 
                                             1535700600, 1531121400, 1527579000, 1535527800, 1530948600, 
                                             1518939000, 1535614200), class = c("POSIXct", "POSIXt"), tzone = "UTC")), .Names = c("start", 
                                                                                                                                  "end"), row.names = c(NA, -10L), class = c("tbl_df", "tbl", 
                                                                                                                                                                                   "data.frame"))

I would like to create 24 new columns and put ones in those hours. For example if the first row has start=23:30 and end=7:30 I would like to put ones in the columns 23, 00, 01, ..., 07. How do I capture all the "hours" a period crosses.

a new data set with 100 rows:

df <- structure(list(start = structure(c(1514818800, 1514793600, 1514764800, 
                                         1514791800, 1514793600, 1514764800, 1514764800, 1514793600, 1514793600, 
                                         1514793600, 1514847600, 1514764800, 1514764800, 1514764800, 1514793600, 
                                         1514764800, 1514790000, 1514764800, 1514764800, 1514794500, 1514793600, 
                                         1514793600, 1514764800, 1514793600, 1514793600, 1514793600, 1514764800, 
                                         1514764800, 1514793600, 1514793600, 1514764800, 1514791800, 1514764800, 
                                         1514764800, 1514764800, 1514794500, 1514793600, 1514793600, 1514793600, 
                                         1514818800, 1514764800, 1514793600, 1514793600, 1514793600, 1514764800, 
                                         1514793600, 1514764800, 1514764800, 1514764800, 1514793600, 1514793600, 
                                         1514764800, 1514764800, 1514764800, 1514820600, 1514764800, 1514764800, 
                                         1514764800, 1514792700, 1514764800, 1514793600, 1514793600, 1514793600, 
                                         1514764800, 1514793600, 1514793600, 1514764800, 1514793600, 1514793600, 
                                         1514764800, 1514797200, 1514793600, 1514764800, 1514793600, 1514791800, 
                                         1514764800, 1514793600, 1514764800, 1514764800, 1514764800, 1514793600, 
                                         1514764800, 1514764800, 1514793600, 1514764800, 1514791800, 1514764800, 
                                         1514764800, 1514764800, 1514793600, 1514764800, 1514764800, 1514793600, 
                                         1514793600, 1514764800, 1514764800, 1514793600, 1514764800, 1514764800, 
                                         1514793600), class = c("POSIXct", "POSIXt"), tzone = "UTC"), 
                     end = structure(c(1514847600, 1514820600, 1514851200, 1514819700, 
                                       1514820600, 1514851200, 1514851200, 1514820240, 1514820600, 
                                       1514817000, 1514876400, 1514851200, 1514851200, 1514851200, 
                                       1514820600, 1514851200, 1514804400, 1514851200, 1514851200, 
                                       1514819700, 1514818800, 1514820600, 1514851200, 1514820600, 
                                       1514820600, 1514820600, 1514851200, 1514851200, 1514820600, 
                                       1514820240, 1514851200, 1514818800, 1514851200, 1514851200, 
                                       1514851200, 1514821500, 1514820600, 1514820240, 1514820240, 
                                       1514847600, 1514851200, 1514820600, 1514822400, 1514820600, 
                                       1514851200, 1514820600, 1514851200, 1514851200, 1514851200, 
                                       1514820600, 1514820600, 1514791800, 1514851200, 1514851200, 
                                       1514849400, 1514851200, 1514851200, 1514851200, 1514819340, 
                                       1514851200, 1514820240, 1514820600, 1514820600, 1514851200, 
                                       1514817000, 1514820240, 1514851200, 1514820600, 1514820240, 
                                       1514851200, 1514808000, 1514820600, 1514851200, 1514820600, 
                                       1514819700, 1514851200, 1514820240, 1514851200, 1514851200, 
                                       1514851200, 1514820600, 1514851200, 1514851200, 1514820600, 
                                       1514851200, 1514818800, 1514851200, 1514851200, 1514851200, 
                                       1514820600, 1514851200, 1514851200, 1514820600, 1514820600, 
                                       1514851200, 1514851200, 1514820600, 1514851200, 1514791800, 
                                       1514820600), class = c("POSIXct", "POSIXt"), tzone = "UTC"), 
                     startN = c(1514818800, 1514793600, 1514764800, 1514791800, 
                                1514793600, 1514764800, 1514764800, 1514793600, 1514793600, 
                                1514793600, 1514847600, 1514764800, 1514764800, 1514764800, 
                                1514793600, 1514764800, 1514790000, 1514764800, 1514764800, 
                                1514794500, 1514793600, 1514793600, 1514764800, 1514793600, 
                                1514793600, 1514793600, 1514764800, 1514764800, 1514793600, 
                                1514793600, 1514764800, 1514791800, 1514764800, 1514764800, 
                                1514764800, 1514794500, 1514793600, 1514793600, 1514793600, 
                                1514818800, 1514764800, 1514793600, 1514793600, 1514793600, 
                                1514764800, 1514793600, 1514764800, 1514764800, 1514764800, 
                                1514793600, 1514793600, 1514764800, 1514764800, 1514764800, 
                                1514820600, 1514764800, 1514764800, 1514764800, 1514792700, 
                                1514764800, 1514793600, 1514793600, 1514793600, 1514764800, 
                                1514793600, 1514793600, 1514764800, 1514793600, 1514793600, 
                                1514764800, 1514797200, 1514793600, 1514764800, 1514793600, 
                                1514791800, 1514764800, 1514793600, 1514764800, 1514764800, 
                                1514764800, 1514793600, 1514764800, 1514764800, 1514793600, 
                                1514764800, 1514791800, 1514764800, 1514764800, 1514764800, 
                                1514793600, 1514764800, 1514764800, 1514793600, 1514793600, 
                                1514764800, 1514764800, 1514793600, 1514764800, 1514764800, 
                                1514793600), endN = c(1514847600, 1514820600, 1514851200, 
                                                      1514819700, 1514820600, 1514851200, 1514851200, 1514820240, 
                                                      1514820600, 1514817000, 1514876400, 1514851200, 1514851200, 
                                                      1514851200, 1514820600, 1514851200, 1514804400, 1514851200, 
                                                      1514851200, 1514819700, 1514818800, 1514820600, 1514851200, 
                                                      1514820600, 1514820600, 1514820600, 1514851200, 1514851200, 
                                                      1514820600, 1514820240, 1514851200, 1514818800, 1514851200, 
                                                      1514851200, 1514851200, 1514821500, 1514820600, 1514820240, 
                                                      1514820240, 1514847600, 1514851200, 1514820600, 1514822400, 
                                                      1514820600, 1514851200, 1514820600, 1514851200, 1514851200, 
                                                      1514851200, 1514820600, 1514820600, 1514791800, 1514851200, 
                                                      1514851200, 1514849400, 1514851200, 1514851200, 1514851200, 
                                                      1514819340, 1514851200, 1514820240, 1514820600, 1514820600, 
                                                      1514851200, 1514817000, 1514820240, 1514851200, 1514820600, 
                                                      1514820240, 1514851200, 1514808000, 1514820600, 1514851200, 
                                                      1514820600, 1514819700, 1514851200, 1514820240, 1514851200, 
                                                      1514851200, 1514851200, 1514820600, 1514851200, 1514851200, 
                                                      1514820600, 1514851200, 1514818800, 1514851200, 1514851200, 
                                                      1514851200, 1514820600, 1514851200, 1514851200, 1514820600, 
                                                      1514820600, 1514851200, 1514851200, 1514820600, 1514851200, 
                                                      1514791800, 1514820600)), .Names = c("start", "end", "startN", 
                                                                                           "endN"), class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA, 
                                                                                                                                                            -100L))

I have some questions. Start hour will be always 23:30? Start and ending day will always be different days? (If not, 00 to 23 range could be a little bit confusing). Can a task last more than 24 hours? — Nacho Glez, Jan 21 '19 at 15:48
@ Nacho: Hi. No a task cannot be more than 24 hours. It does not need to start at 23:30. The normal start periods are 23:30, 7:30 and 15:30. Actually I would like a fraction if the whole hour is not covered, i.e. start 23:30 should put 0.5 in the "23" column. — xhr489, Jan 21 '19 at 15:59

score 0 · Accepted Answer · answered Jan 24 '19 at 16:21

I've been working in one solution. It is not so fancy but it works

is.wholenumber <- function(x, tol = .Machine$double.eps^0.5)  abs(x - round(x)) < tol
df <- structure(list(Vagt_Start = structure(c(1535412600, 1531006200, 
                                              1518823800, 1535671800, 1531092600, 1527550200, 1535499000, 1530919800, 
                                              1518910200, 1535585400), class = c("POSIXct", "POSIXt"), tzone = "UTC"), 
                     Vagt_Slut = structure(c(1535441400, 1531035000, 1518852600, 
                                             1535700600, 1531121400, 1527579000, 1535527800, 1530948600, 
                                             1518939000, 1535614200), class = c("POSIXct", "POSIXt"), tzone = "UTC"), 
                Vagt_Start2 = structure(c(1535412600, 1531006200, 
                                         1518823800, 1535671800, 1531092600, 1527550200, 1535499000, 1530919800, 
                                         1518910200, 1535585400)),
                Vagt_Slut2 = structure(c(1535441400, 1531035000, 1518852600, 
                                        1535700600, 1531121400, 1527579000, 1535527800, 1530948600, 
                                        1518939000, 1535614200))),.Names = c("start", "end", 'startN', 'endN'), 
                row.names = c(NA, -10L), class = c("tbl_df", "tbl", "data.frame"))

df$TimeS <- df$endN - df$startN
df$TimeM <- df$TimeS / 60
df$TimeH <- df$TimeM / 60
df$startingD <- substr(df$start, 1, 10)
df$endingD <- substr(df$end, 1, 10)
df$startingT <- as.numeric(substr(df$start, 12, 13)) + as.numeric(substr(df$start, 15, 16)) / 60
df$endingT <- as.numeric(substr(df$end, 12, 13)) + as.numeric(substr(df$end, 15, 16)) / 60
mat <- matrix(NA, nrow = nrow(df), ncol = 24)
colnames(mat) <- paste0('H', seq(1:24))
df <- cbind(df, mat)
for(i in 1:nrow(df)) {
  if (df$startingD[i] == df$endingD[i]) {
    vect <- seq(floor(df$startingT[i]), floor(df$endingT[i]))
    if (is.wholenumber(df$startingT[i]) & is.wholenumber(df$endingT[i])) {
      h_comp <- paste0('H', vect)
      h_nocomp <- NA
    } else if (is.wholenumber(df$startingT[i]) == FALSE & is.wholenumber(df$startingT[i]) == TRUE) {
      h_comp <- paste0('H', vect[-1])
      h_nocomp <- paste0(vect[1])
    } else if (is.wholenumber(df$startingT[i]) == TRUE & is.wholenumber(df$startingT[i]) == FALSE) {
      h_comp <- paste0('H', vect[-9])
      h_nocomp <- paste0('H', vect[9]     )
    } else if (is.wholenumber(df$startingT[i]) == FALSE & is.wholenumber(df$startingT[i]) == FALSE) {
      h_comp <- paste0('H', vect[c(-1, -9)])
      h_nocomp <- paste0('H', vect[c(1, 9)])  
    }
    for (j in 12:length(df)) {
      df[i, j] <- ifelse(names(df)[j] %in% h_comp, 1, ifelse(names(df)[j] %in% h_nocomp, 0.5, 0))
    }
  }
  else {
    vect <- c(seq(floor(df$startingT[i]), 24), seq(1, floor(df$endingT[i])))
    if (is.wholenumber(df$startingT[i]) & is.wholenumber(df$endingT[i])) {
      h_comp <- paste0('H', vect)
      h_nocomp <- NA
    } else if (is.wholenumber(df$startingT[i]) == FALSE & is.wholenumber(df$startingT[i]) == TRUE) {
      h_comp <- paste0('H', vect[-1])
      h_nocomp <- paste0('H', vect[1])
    } else if (is.wholenumber(df$startingT[i]) == TRUE & is.wholenumber(df$startingT[i]) == FALSE) {
      h_comp <- paste0('H', vect[-9])
      h_nocomp <- paste0('H', vect[9])     
    } else if (is.wholenumber(df$startingT[i]) == FALSE & is.wholenumber(df$startingT[i]) == FALSE) {
      h_comp <- paste0('H', vect[c(-1, -9)])
      h_nocomp <- paste0('H', vect[c(1, 9)])
      }
  }
  for (j in 12:length(df)) {
    df[i, j] <- ifelse(names(df)[j] %in% h_comp, 1, ifelse(names(df)[j] %in% h_nocomp, 0.5, 0))
  }
}

PS. I assumed that can only start at half past or o'clock.

@ Thanks Nacho! I will check the code latter or tomorrow. I have to finish something else I am working on. — xhr489, Jan 24 '19 at 16:51
@ Nacho: It seems that the makes some erros. There is a new test data set where the starts are not always 23:30. — xhr489, Jan 25 '19 at 16:25
@ Nacho: I have marked the solution as "solved", even though e.g. when start is 8:00 and end is 15:40 the code also inserts zeros for the whole night... I will try to fix it. Thanks for your help. — xhr489, Jan 25 '19 at 16:29

Create 24 columns and a dummy if timespan overlaps with hour

1 Answers1