Search code examples
rdatetimeaggregatecut

R remove all rows within a timeframe in a dataframe

I have a dataframe with two columns.

The first column is POSIXct, the second is num:

        date                value
    1   09.05.2017 10:30    0.72599362
    2   09.05.2017 10:31    0.6942953
    3   09.05.2017 10:32    0.6913402
    4   09.05.2017 10:33    0.7219035
    5   09.05.2017 10:34    0.7484892
    6   09.05.2017 10:35    0.7566694
    7   09.05.2017 10:36    0.7699520
    8   09.05.2017 10:37    0.7863227
    9   09.05.2017 10:38    0.7955254
    10  09.05.2017 10:39    0.7724675
    11  09.05.2017 10:40    0.7883882
    12  09.05.2017 10:41    0.7975705
    13  09.05.2017 10:42    0.7842776
    14  09.05.2017 10:43    0.7705962
    15  09.05.2017 10:44    0.7607595
    16  09.05.2017 10:45    0.7658722
    17  09.05.2017 10:46    0.7617003
    18  09.05.2017 10:47    0.7536121
    19  09.05.2017 10:48    0.7493686
    ...

I need two remove all entries which are within 5 minutes. Meaning I only want to show the rows of every 5 minutes.

Desired outcome:

        date                value
    1   09.05.2017 10:30    0.72599362
    2   09.05.2017 10:35    0.7566694
    3   09.05.2017 10:40    0.7883882
    4   09.05.2017 10:45    0.7658722

the rows in between should be completely removed.

I was thinking of using cut like this:

    dfResult <- cut(dfResult$date, "5 min")

but cut function won't remove the entries from the dataframe for some reason

So I was thinking of using aggregate with cut. But aggregate always comes with a function like sum or mean, which is not what I want either.

    dfResult <- aggregate(. ~ cut(dfResult$date, "5 min"), 
                           dfResult[setdiff(names(dfResult), "date")], sum)

The line above does what it should, but uses sum as aggregate function. Is there like a function which simply removes the entries in between?

Thanks!!

Solution

  • If date is class POSIXct, you could use the modulus operator (%%) to return just the rows for which the the modulo of five minutes returns 0.

    Given this dataframe (only included for reproducibility- you shouldn't need to do this as long as your date column is a POSIXct object):

    df <- structure(list(date = structure(c(1504632600, 1504632660, 1504632720, 
1504632780, 1504632840, 1504632900, 1504632960, 1504633020, 1504633080, 
1504633140, 1504633200, 1504633260, 1504633320, 1504633380, 1504633440, 
1504633500, 1504633560, 1504633620, 1504633680), class = c("POSIXct", 
"POSIXt"), tzone = ""), value = c(0.72599362, 0.6942953, 0.6913402, 
0.7219035, 0.7484892, 0.7566694, 0.769952, 0.7863227, 0.7955254, 
0.7724675, 0.7883882, 0.7975705, 0.7842776, 0.7705962, 0.7607595, 
0.7658722, 0.7617003, 0.7536121, 0.7493686)), .Names = c("date", 
"value"), row.names = c(NA, -19L), class = "data.frame")

    Return only the desired rows:

    df[which(as.numeric(x$date) %% (60 * 5) == 0 ),]