CODEWITHSUNDEEP
rtimeintervals
Bernd V.
Bernd V.

Reputation: 193

R: Split observation values by and aggregate to time intervals

There are bird observations from various observation points (obs) over certain areas (name). The start and end time was taken, and the time difference (diff_corr) recalculated with a correction factor, so it is not simply difftime of the start-end-interval.

I now need to "split" these values to "nice" intervals (15 minutes, e.g. 10:15:00, 10:30:00, ...) and then aggregate area-wise(name) in order be able to make a plot of the presence of birds on those areas in those clean 15-minute-intervals.

So, to make it a little more clear: An observation might start at 10:14 and goes till 10:25, so it spans over the interval 10:00-10:15 and 10:15-10:30, so the value I got should be split and assigned accordingly to the appropriate intervals by the part they have into that interval.

In a more complicated setting, an observation might span over 3 or 4 intervals, and so the value has to be split there accordingly as well.

The last step would be to aggregate all observation parts per interval and plot them.

I already searched for solutions for some days, but only found very simplistic examples where intervals were rearranged with cut and breaks, but never examples what to do with associated values, but simple frequency counts.

example data:

structure(list(obs = structure(c(2L, 2L, 2L, 2L, 2L, 2L, 2L, 
2L, 2L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), .Label = c("b", 
"C2", "Dürnberg2"), class = "factor"), name = c("C2", "C2", 
"C2", "C2", "C2", "C2", "C2", "C2", "C2", "b", "981", "1627", 
"b", "b", "981", "1627", "b", "b", "b", "b"), start = structure(c(1495441500, 
1495441590, 1495441650, 1495441680, 1495447380, 1495447410, 1495447530, 
1495447560, 1495447580, 1496996580, 1496996580, 1496996580, 1496996760, 
1496996820, 1496996820, 1496996820, 1496997180, 1496997300, 1496997420, 
1496998260), class = c("POSIXct", "POSIXt"), tzone = ""), end = structure(c(1495441590, 
1495441650, 1495441680, 1495441800, 1495447410, 1495447530, 1495447560, 
1495447580, 1495447620, 1496996760, 1496996760, 1496996760, 1496996820, 
1496997180, 1496997180, 1496997180, 1496997300, 1496997420, 1496997540, 
1496998320), class = c("POSIXct", "POSIXt"), tzone = ""), diff_corr = c(1.46739130434783, 
0.978260869565217, 0.489130434782609, 1.95652173913043, 0.489130434782609, 
1.95652173913043, 0.489130434782609, 0.326086956521739, 0.652173913043478, 
2.96703296703297, 2.96703296703297, 2.96703296703297, 0.989010989010989, 
5.93406593406593, 5.93406593406593, 5.93406593406593, 1.97802197802198, 
1.97802197802198, 1.97802197802198, 0.989010989010989)), .Names = c("obs", 
"name", "start", "end", "diff_corr"), row.names = c("1", "9", 
"7", "8", "3", "2", "4", "5", "6", "13", "13.1", "13.2", "22", 
"11", "11.1", "11.2", "12", "23", "15", "16"), class = "data.frame")

p.s. I have real difficulties to name my question properly, so any hints (not only on that) are highly appreciated

New attempt for a small example: Assigning the value to intervals by their proportion (and later sum up equal intervals)

start         end         value     new values in new 15-min-intervals
10:03:00      10:14:00    11        ---> 10:00:00 =  11
10:14:00      10:16:00     2        ---> 10:00:00 = 1 ; 10:15:00 = 1
10:00:00      10:35:00    40        ---> 10:00:00 = 40/35*15 ; 10:15:00 = 40/35*15 ; 10:30:00 = 40/35*5
10:15:00      10:30:00    12        ---> 10:15:00 = 12

Upvotes: 0

Views: 828

Answers (2)

Gautam
Gautam

Reputation: 2753

Here's a data.table approach which allows you to use SQL-type queries to sort/filter data and perform operations.

DATA

> p
    obs name               start                 end diff_corr
 1:  C2   C2 2017-05-22 04:25:00 2017-05-22 04:26:30 1.4673913
 2:  C2   C2 2017-05-22 04:26:30 2017-05-22 04:27:30 0.9782609
 3:  C2   C2 2017-05-22 04:27:30 2017-05-22 04:28:00 0.4891304
 4:  C2   C2 2017-05-22 04:28:00 2017-05-22 04:30:00 1.9565217
 5:  C2   C2 2017-05-22 06:03:00 2017-05-22 06:03:30 0.4891304
 6:  C2   C2 2017-05-22 06:03:30 2017-05-22 06:05:30 1.9565217
 7:  C2   C2 2017-05-22 06:05:30 2017-05-22 06:06:00 0.4891304
 8:  C2   C2 2017-05-22 06:06:00 2017-05-22 06:06:20 0.3260870
 9:  C2   C2 2017-05-22 06:06:20 2017-05-22 06:07:00 0.6521739
10:   b    b 2017-06-09 04:23:00 2017-06-09 04:26:00 2.9670330
11:   b  981 2017-06-09 04:23:00 2017-06-09 04:26:00 2.9670330
12:   b 1627 2017-06-09 04:23:00 2017-06-09 04:26:00 2.9670330
13:   b    b 2017-06-09 04:26:00 2017-06-09 04:27:00 0.9890110
14:   b    b 2017-06-09 04:27:00 2017-06-09 04:33:00 5.9340659
15:   b  981 2017-06-09 04:27:00 2017-06-09 04:33:00 5.9340659
16:   b 1627 2017-06-09 04:27:00 2017-06-09 04:33:00 5.9340659
17:   b    b 2017-06-09 04:33:00 2017-06-09 04:35:00 1.9780220
18:   b    b 2017-06-09 04:35:00 2017-06-09 04:37:00 1.9780220
19:   b    b 2017-06-09 04:37:00 2017-06-09 04:39:00 1.9780220
20:   b    b 2017-06-09 04:51:00 2017-06-09 04:52:00 0.9890110

CODE

library(data.table)
library(lubridate)
p <- as.data.table(p)
p[, .(new_diff = mean(diff_corr)), .(tme_start = round_date(start, unit = "15min"))]

OUTPUT

> p[, .(new_diff = mean(diff_corr)), .(tme_start = round_date(start, unit = "15min"))]
             tme_start  new_diff
1: 2017-05-22 04:30:00 1.2228261
2: 2017-05-22 06:00:00 0.7826087
3: 2017-06-09 04:30:00 3.3626374
4: 2017-06-09 04:45:00 0.9890110

What is Data.Table doing?

Since you aren't familiar with data.table, here's a very quick, elementary description of what is happening. General form of the data.table call is: 

DT[select rows, perform operations, group by]

Where DT is the data.table name. Select rows is a logical operation e.g. say you want only observations for C2 (name), the call would be DT[name == "C2",] There is no operation required to be performed and no grouping. If you want the sum of diff_corr column for all name == "C2", the call becomes DT[name == "C2", list(sum(diff_corr))]. Instead of writing list() you can use .(). The output will now have a only one row and one column called V1 which is the sum of all diff_corr when name == "C2". The column doesn't have a lot of information so we assign it a name (can be the same as the old one): DT[name == "C2", .(diff_corr_sum = sum(diff_corr))]. Suppose you had another column called "mood" which reported the mood of the person making the observation and can assume three values ("happy", "sad", "sleepy"). You could "group by" the mood: DT[name == "C2", .(diff_corr_new = sum(diff_corr)), by = .(mood)]. The output would be three rows corresponding to each of the moods and one column diff_corr_new. To understand this better try playing around with a sample dataset like mtcars. Your sample data doesn't have enough complexity etc. to allow you to explore all of these functions.

Back to the answer - other variations

It's not clear from the question or comments if you want to round based on start or end. I used the former but you can change that. The example above uses mean but you can perform any other operations you may need. The other columns seem more or less redundant since they are strings and you can't do much with them. You could use them to further sort the results in the by entry (last field in the code). Below are two examples using obs and name respectively. You can also combine all of them together. 

> p[, .(new_diff = mean(diff_corr)), .(tme_start = round_date(start, unit = "15min"), obs)]
             tme_start obs  new_diff
1: 2017-05-22 04:30:00  C2 1.2228261
2: 2017-05-22 06:00:00  C2 0.7826087
3: 2017-06-09 04:30:00   b 3.3626374
4: 2017-06-09 04:45:00   b 0.9890110


> p[, .(new_diff = mean(diff_corr)), .(tme_start = round_date(start, unit = "15min"), name)]
             tme_start name  new_diff
1: 2017-05-22 04:30:00   C2 1.2228261
2: 2017-05-22 06:00:00   C2 0.7826087
3: 2017-06-09 04:30:00    b 2.6373626
4: 2017-06-09 04:30:00  981 4.4505495
5: 2017-06-09 04:30:00 1627 4.4505495
6: 2017-06-09 04:45:00    b 0.9890110

Upvotes: 1

Jason
Jason

Reputation: 581

This is slow and clunky, but maybe it's helpful. Calculates counts and weighted diff_corr sums by name and 15 minute interval:

library(dplyr)
range <- seq.POSIXt(min(df$start)-(15*60), max(df$end)+(15*60), by = "15 min")

df$totalDuration <- as.numeric(as.difftime(df$end-df$start),units=c("secs"))

out <- NULL
for (r in 1:length(range)){
  subset <- df %>% filter( (start >= (range[r]-(15*60)) & start<range[r]) |
                             (end>= (range[r]-(15*60)) & end<range[r] ) |
                             (end > range[r] & start < range[r])) %>%
    mutate(bin=range[r],
           duration = ifelse(start>=(range[r]-(15*60)) & end<range[r],totalDuration,
                        ifelse(start>=(range[r]-(15*60)),as.numeric(as.difftime(range[r]-start),units="secs"),
                          ifelse(end<range[r],
                                 as.numeric(as.difftime(end-(range[r]-(15*60))),units="secs"),
                                            as.numeric(as.difftime(range[r]-(range[r]-(15*60))),units="secs")
                        )))
           ) %>% 
    mutate (diff_corr_W = diff_corr*(duration/as.double(totalDuration, units='secs'))) %>%
    group_by(bin,name) %>% summarise(count=n(),
                                     diff_corr_sum = sum(diff_corr_W)) %>% ungroup()


  if (is.null(out)){
    out <- subset
  } else {
    out <- rbind(out,subset)
  }
}


> out
# A tibble: 9 x 4
bin  name count diff_corr_sum
*              <dttm> <chr> <int>         <dbl>
  1 2017-05-22 04:40:00    C2     4      4.891304
2 2017-05-22 06:10:00    C2     5      3.913043
3 2017-06-09 04:25:00  1627     1      1.978022
4 2017-06-09 04:25:00   981     1      1.978022
5 2017-06-09 04:25:00     b     1      1.978022
6 2017-06-09 04:40:00  1627     2      6.923077
7 2017-06-09 04:40:00   981     2      6.923077
8 2017-06-09 04:40:00     b     6     13.846154
9 2017-06-09 04:55:00     b     1      0.989011

Upvotes: 1

Related Questions