Squashing columns into a single row by nearest time

[Renanel Pickholtz]

Squashing columns into a single row by nearest time

 

Having trouble with averaging multiple columns into one rows.

I have a dataframe containing a timestamp (“real_datetime”, class posixct), activity, and depth.

# A tibble: 9 x 3

  real_datetime       activity depth

  <dttm>                 <dbl> <dbl>

1 2017-02-26 23:35:41   0.115   NA  

2 2017-02-26 23:37:07   NA      15.6

3 2017-02-26 23:38:23   0.0961  NA  

4 2017-02-26 23:40:46   NA      15.6

5 2017-02-26 23:41:57   0.269   NA  

6 2017-02-26 23:42:46   0.365   NA  

7 2017-02-26 23:43:49   NA      15.6

8 2017-02-26 23:45:21   0.0961  NA  

9 2017-02-26 23:46:46   0.0961  NA  

 

I want to collapse all rows that are within 30 minutes of each other, so a row will contain a mean of values (activity, depth, or both) over that time.

 

For example, this df:

real_datetime	activity	depth
2017-02-26 13:35:00	4
2017-02-26 13:39:00	6
2017-02-26 13:49:00		10
2017-02-26 15:41:00	3
2017-02-27 03:00:20	2
2017-02-27 03:05:20		30
2017-02-27 03:10:20		32
2017-02-27 03:13:20	4

 

Would result with this new_df:

real_datetime	activity	depth
2017-02-26 13:49:00	5	10
2017-02-26 15:41:00	3	NA
2017-02-27 03:13:20	3	31

 

I tried using dplyr with lag() to select all rows within 30 minutes, and mutate() summarize() to average all value within all selected rows. But I’m not sure if the isn’t a simpler approach, nor how to syntax these functions correctly. Either way, no success so far..

 

Any assistance would be much appreciated,

Thank you,

Renanel