0

I'm sure it isn't hard to do but I cant find a way to sum up breeding time per hour. I have a couple thousands of event data (breeding) of birds that arrive on (AR) and leave from (LV) their nest. The corresponding times of arriving- and leaving-events are given in time and look like this:

times = as.POSIXct(c("2007-07-11 22:47:21 UTC", "2007-07-11 22:58:39 UTC", "2007-07-11 22:58:48 UTC", "2007-07-11 23:57:45 UTC", "2007-07-12 02:29:52 UTC", "2007-07-12 03:46:23 UTC", "2007-07-12 03:46:36 UTC", "2007-07-12 04:28:54 UTC", "2007-07-12 04:29:03 UTC", "2007-07-12 05:36:38 UTC"), tz = "UTC")
breeding = c("AR", "LV", "AR", "LV", "AR", "LV", "AR", "LV", "AR", "OFF")

Now I want to calculate what fraction per hour the birds spend on their nest, based on hourly breaks like these

cut(times, breaks = "hour")

only that the right end shall be included, too.

I tried summing up with difftime but then of course it didn't cut at the hour- and date- breaks. So the result should look like somehow this:

 Hour       fraction

22-23        12min

23-00        57min

00-01         0min

01-02         0min

02-03        31min

03-04        46min

04-05         1min

05-06        24min

Thanks already!!

mrbakin
  • 1

2 Answers2

0

A solution from dplyr, tidyr, and lubridate.

We can calculate the time difference as follows

library(dplyr)
library(tidyr)
library(lubridate)

dt2 <- dt %>% 
  mutate(breeding = ifelse(breeding %in% "OFF", "LV", breeding)) %>%
  mutate(ID = rep(1:(n()/2), each = 2)) %>%
  spread(breeding, times) %>%
  mutate(DiffTime = difftime(LV, AR, "mins"))
dt2
  ID                  AR                  LV      DiffTime
1  1 2007-07-11 22:47:21 2007-07-11 22:58:39 11.30000 mins
2  2 2007-07-11 22:58:48 2007-07-11 23:57:45 58.95000 mins
3  3 2007-07-12 02:29:52 2007-07-12 03:46:23 76.51667 mins
4  4 2007-07-12 03:46:36 2007-07-12 04:28:54 42.30000 mins
5  5 2007-07-12 04:29:03 2007-07-12 05:36:38 67.58333 mins

If you want to know the hour of that date, we can further extract the date and hour information as follows.

dt3 <- dt2 %>%
  mutate(AR_Date = as.Date(AR), LV_Date = as.Date(LV),
         AR_Hour = hour(AR), LV_Hour = hour(LV))
dt3

From here you can decide how to further summarize your data.

  ID                  AR                  LV      DiffTime    AR_Date    LV_Date AR_Hour LV_Hour
1  1 2007-07-11 22:47:21 2007-07-11 22:58:39 11.30000 mins 2007-07-11 2007-07-11      22      22
2  2 2007-07-11 22:58:48 2007-07-11 23:57:45 58.95000 mins 2007-07-11 2007-07-11      22      23
3  3 2007-07-12 02:29:52 2007-07-12 03:46:23 76.51667 mins 2007-07-12 2007-07-12       2       3
4  4 2007-07-12 03:46:36 2007-07-12 04:28:54 42.30000 mins 2007-07-12 2007-07-12       3       4
5  5 2007-07-12 04:29:03 2007-07-12 05:36:38 67.58333 mins 2007-07-12 2007-07-12       4       5
www
  • 38,575
  • 12
  • 48
  • 84
0

Try this! I really wanted to come up with an answer, because this was interesting and I could see myself using something similar. I think it's close.

See what you think!

Looks like this:

         Date hours    Total
1: 2007-07-11    22 01:10:15
2: 2007-07-12     2 01:16:31
3: 2007-07-12     3 00:42:18
4: 2007-07-12     4 01:07:35

So, at 22:00, they will spend over an hour. The birds don't arrive until 2:00, and will also spend over an hour.

The final output is in times. 

#---Load the required libraries
require(chron)              #---For time manipulation
require(data.table)         #---Calculate and store data.tables
require(splitstackshape)    #---Split columns by a delimiter

#---Input Data
times = as.POSIXct(c("2007-07-11 22:47:21 UTC", "2007-07-11 22:58:39 UTC", "2007-07-11 22:58:48 UTC", "2007-07-11 23:57:45 UTC", "2007-07-12 02:29:52 UTC", "2007-07-12 03:46:23 UTC", "2007-07-12 03:46:36 UTC", "2007-07-12 04:28:54 UTC", "2007-07-12 04:29:03 UTC", "2007-07-12 05:36:38 UTC"), tz = "UTC")
breeding = c("AR", "LV", "AR", "LV", "AR", "LV", "AR", "LV", "AR", "OFF")

#---The times need to be a data.table for cSplit to work
times <- as.data.table(times)

#---This splits your time input into "Date" and "Time" columns
times <- cSplit(times, "x", sep = " ")
setnames(times, c("x_1", "x_2"),c("Date", "Time"))
times[] <- lapply(times, as.character)

#---Bring in "AR" or "LV" into a fresh data.table
Data <- cbind(times, breeding)

#---Format the time column for computation
Data$Time <- chron(times = Data$Time, format = 'h:m:s')

#rm(times,breeding)

#---Arrival times
arrive <- Data[breeding == "AR",]
setnames(arrive, c("Time"), c("ArriveTime"))
arrive[,3] <- NULL

#---Departure times
leave <- Data[breeding != "AR",]
setnames(leave, c("Time"), c("LeaveTime"))
leave[,c(1,3)] <- NULL

#---But them together
times <- cbind(arrive, leave)
#rm(arrive, leave)

#---Calculate elapsed times
times <- times[, Elapsed := LeaveTime - ArriveTime]
#rm(times3,Data)

#---Sum elapsed time by the hour of the Arrival time
times <- times[, .(Total = sum(Elapsed)), by = .(Date,hours(ArriveTime))]
times
Jake
  • 113
  • 1
  • 7