Aggregating second-by-second sampling interval to 30 sec interval, POSIXct

Question

New to [R]studio and respectfully requesting help.

Goal: I'd like to take data collected at 1 second intervals, collapse it to 30 sec intervals, and, subsequently, have the "mean" of each variable associated with it.

Here is what my data looks like:

line    datetime         AA  BB  CC
1  2016-06-27 14:13:16    6  0  0.0
2  2016-06-27 14:13:17   10  0 48.6
3  2016-06-27 14:13:18    7  0 52.0
4  2016-06-27 14:13:19   13  0 54.4
5  2016-06-27 14:13:20   16  0 60.8
6  2016-06-27 14:13:21    6  0 65.5
7  2016-06-27 14:13:22    6  0 47.5
8  2016-06-27 14:13:23    6  1 46.8
9  2016-06-27 14:13:24    4  1 55.5
10 2016-06-27 14:13:25    4  1 51.1
11 2016-06-27 14:13:26    4  1 53.4

What I'd like to see is this:

line    datetime        AA  BB  CC
1   2016-06-27 14:13:16 18  1 50.5
2   2016-06-27 14:13:46 19  1 52.8

(here, variables AA, BB, and CC were averaged).

There have been questions similar to this, but none that were similar enough to give me a foundation to work on with my little coding and programming knowledge. I've been pacing back and forth between probable base r solutions and probable package solutions to no avail; mainly because the language/syntax implementation is still a bit foreign to me.

did you try the answer? did it work? – Zahiro Mor Jan 23 '17 at 15:24 — Zahiro Mor, Jan 23 '17 at 15:24

Zahiro Mor · Answer 1 · 2017-01-02T14:25:13.607

I think you want to try this: (base solution)

etw
              datetime AA BB   CC
1  2016-06-27 14:13:16  6  0  0.0
2  2016-06-27 14:13:17 10  0 48.6
3  2016-06-27 14:13:18  7  0 52.0
4  2016-06-27 14:13:19 13  0 54.4
5  2016-06-27 14:13:20 16  0 60.8
6  2016-06-27 14:13:21  6  0 65.5
7  2016-06-27 14:13:22  6  0 47.5
8  2016-06-27 14:13:23  6  1 46.8
9  2016-06-27 14:13:24  4  1 55.5
10 2016-06-27 14:13:25  4  1 51.1
11 2016-06-27 14:13:26  4  1 53.4

aggregate(x = etw, by = list(cut(etw$datetime,breaks = "10 sec")), FUN=mean )

              Group.1            datetime  AA  BB    CC
1 2016-06-27 14:13:16 2016-06-27 14:13:20 7.8 0.3 48.22
2 2016-06-27 14:13:26 2016-06-27 14:13:26 4.0 1.0 53.40

you can change the 10 sec part to 30 sec. however - take care: breaks = "10 sec" will cut the range into 10 sec slices starting with the minimum time. which in your case result in a single slice.

you can also manually define the range using

breaks = seq.POSIXt(from = as.POSIXct("2016-06-27 14:13:00"),to = as.POSIXct("2016-06-27 14:14:00"),by="10 sec"))

aggregate(x = etw,FUN=mean, by = list(cut(etw$datetime,breaks = seq.POSIXt(from =  as.POSIXct("2016-06-27 14:13:00"),to =  as.POSIXct("2016-06-27 14:14:00"),by="10 sec"))) )
              Group.1            datetime       AA        BB       CC
1 2016-06-27 14:13:10 2016-06-27 14:13:17 9.000000 0.0000000 38.75000
2 2016-06-27 14:13:20 2016-06-27 14:13:23 6.571429 0.5714286 54.37143

this is not exactly what you wanted to get but imho - your sample data does not correspond to the desired output :)

Aggregating second-by-second sampling interval to 30 sec interval, POSIXct

1 Answers1