Extract intervals from time data in R

Question

My problem is simple. I have table where each row is event (month, day, hour, minute is given). However, the machine was set to record 24/7. So I have more events (rows) than I need. How to remove surplus rows from daytime and to keep only rows from night (from sunset to sunrise)? Dreadful thing is, that the timing of sunrise/sunset is slightly different each day.

In this example I provide two tables. First is table with all events, second contain timings of sunset/sunrise for each day.

If it is possible to extract, please notice that EACH night consists from two dates could be a additional column inserted in table containing ID of night? (see scheme below)

enter image description here

# table with all events
my.table <- data.frame(event = 1:34,
                       day = rep(c(30,31,1,2,3), times = c(8,9,7,8,2)),
                       month = rep(c(3,4), each = 17),
                       hour = c(13,13,13,13,22,
                                22,23,23,2,2,2,
                                14,14,14,19,22,22,
                                2,2,2,14,15,22,22,
                                3,3,3,14,14,14,
                                23,23,2,14),
                       minute = c(11,13,44,55,27,
                                  32,54,57,10,14,
                                  26,12,16,46,30,
                                  12,13,14,16,45,
                                  12,15,12,15,24,
                                  26,28,12,16,23,12,13,11,11))


# timings of sunset/sunrise for each day
sun.table <- data.frame(day = c(30,31,31,1,1,2,2,3),
                        month = rep(c(3,4), times = c(3,5)),
                        hour = rep(c(19,6), times = 4),
                        minute = c(30,30,31,29,32,
                                   28,33,27),
                        type = rep(c("sunset","sunrise"), times = 4))

# rigth solution reduced table would contain only rows:
# 5,6,7,8,9,10,11,16,17,18,19,20,23,24,25,26,27,31,32,33.
# nrow("reduced table") == 20

Should there also be a sunrise and sunset for each day in the sun.table? — Mike.Gahan
Well, not necessarily. The machine start recording first day before near evening (there is only sunset in first day) and end recording also in evening (only sunrise in last day). All days in the "middle" have both "sunset" and "sunrise" timings. If you want, I could add timing so they would be always two per day, or zou can just subset only days with both timings from tables above. It do not matter if few days will be excluded if such procedure will simplify the extraction process. — Ladislav Naďo

MrFlick MrFlick · Accepted Answer · 2014-08-28T00:25:56

Here's one possible strategy

#convert sun-up, sun-down times to proper dates
ss <- with(sun.table, ISOdate(2000,month,day,hour,minute))
up <- ss[seq(1,length(ss),by=2)]
down <- ss[seq(2,length(ss),by=2)]

Here I assume the table is ordered and starts with a sunrise and alternates back and forth and ends with a sunset. Date values also need a year, here I just hard coded 2000. As long as your data doesn't span years (or leap days) that should be fine, but you'll probably want to pop in the actual year of your observations.

Now do the same for events

tt <- with(my.table, ISOdate(2000,month,day,hour,minute))

Find rows during the day

daytime <- sapply(tt, function(x) any(up<x & x<down))

and extract those rows

my.table[daytime, ]

#    event day month hour minute
# 5      5  30     3   22     27
# 6      6  30     3   22     32
# 7      7  30     3   23     54
# 8      8  30     3   23     57
# 9      9  31     3    2     10
# 10    10  31     3    2     14
# 11    11  31     3    2     26
# 16    16  31     3   22     12
# 17    17  31     3   22     13
# 18    18   1     4    2     14
# 19    19   1     4    2     16
# 20    20   1     4    2     45
# 23    23   1     4   22     12
# 24    24   1     4   22     15
# 25    25   2     4    3     24
# 26    26   2     4    3     26
# 27    27   2     4    3     28
# 31    31   2     4   23     12
# 32    32   2     4   23     13
# 33    33   3     4    2     11

Here we only grab values that are after sunrise and before sunset. Since there isn't enough information in the sun.table to make sure that row 34 actually happens before subset, it is not returned.

Extract intervals from time data in R

1 Answers