Squashing columns into a single row by nearest time

10 views
Skip to first unread message

Renanel Pickholtz

unread,
Dec 18, 2018, 6:14:18 PM12/18/18
to Israel R User Group

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

Reply all
Reply to author
Forward
0 new messages