CODEWITHSUNDEEP
rtimefrequency
user102343
user102343

Reputation: 41

R - How to calculate the frequency of occurence within a specific time step

I have a data frame with a specific variable (Var1) and a time variable (Var2).

I would like to calculate the frequency of occurrence (Frequency) of Var1 withing a specific time step (let say 1 min) during a year.

sample dataset:

Var1 <- c(rep("A", 4), rep("B", 3), rep("C", 2)) 
Var2 <- c("2018-09-01 10:00:00", "2018-09-01 10:00:30", "2018-09-01 10:00:45", 
    "2018-09-10 22:10:00", "2017-09-05 10:54:30", "2018-12-15 10:00:30", 
    "2018-12-15 10:01:00", "2017-02-20 17:16:30", "2017-12-20 20:08:56") 
df <- data.frame(Var1, Var2) 
df$Var2 <- as.POSIXct(df$Var2)

desired output:

Frequency <- c(rep(3, 3), rep(1, 2), rep(2,2), rep(1,2)) 
dfOut <- data.frame(Var1, Var2, Frequency) 

#  Var1                Var2 Frequency
#1    A 2018-09-01 10:00:00         3
#2    A 2018-09-01 10:00:30         3
#3    A 2018-09-01 10:00:45         3
#4    A 2018-09-10 22:10:00         1
#5    B 2017-09-05 10:54:30         1
#6    B 2018-12-15 10:00:30         2
#7    B 2018-12-15 10:01:00         2
#8    C 2017-02-20 17:16:30         1
#9    C 2017-12-20 20:08:56         1

Upvotes: 2

Views: 416

Answers (3)

Calum You
Calum You

Reputation: 15062

You can use lubridate::floor_date to get the minute grouping column that accounts for date as you are describing. Note that your displayed desired output does not seem to match your comment

Var1 <- c(rep("A", 4), rep("B", 3), rep("C", 2)) 
Var2 <- c("2018-09-01 10:00:00", "2018-09-01 10:00:30", "2018-09-01 10:00:45", 
          "2018-09-10 22:10:00", "2017-09-05 10:54:30", "2018-12-15 10:00:30", 
          "2018-12-15 10:01:00", "2017-02-20 17:16:30", "2017-12-20 20:08:56") 
df <- data.frame(Var1, Var2) 
df$Var2 <- as.POSIXct(df$Var2)

library(tidyverse)
library(lubridate)

df %>%
  mutate(minute = floor_date(Var2, unit = "minute")) %>%
  add_count(Var1, minute)
#> # A tibble: 9 x 4
#>   Var1  Var2                minute                  n
#>   <fct> <dttm>              <dttm>              <int>
#> 1 A     2018-09-01 10:00:00 2018-09-01 10:00:00     3
#> 2 A     2018-09-01 10:00:30 2018-09-01 10:00:00     3
#> 3 A     2018-09-01 10:00:45 2018-09-01 10:00:00     3
#> 4 A     2018-09-10 22:10:00 2018-09-10 22:10:00     1
#> 5 B     2017-09-05 10:54:30 2017-09-05 10:54:00     1
#> 6 B     2018-12-15 10:00:30 2018-12-15 10:00:00     1
#> 7 B     2018-12-15 10:01:00 2018-12-15 10:01:00     1
#> 8 C     2017-02-20 17:16:30 2017-02-20 17:16:00     1
#> 9 C     2017-12-20 20:08:56 2017-12-20 20:08:00     1

Created on 2018-09-11 by the reprex package (v0.2.0).

Upvotes: 1

chinsoon12
chinsoon12

Reputation: 25225

Here is a data.table approach. You can first create an index showing if the datetime for next row is 1 min after the datetime of current row. Then, use this as one of the grouping criteria to calculate the frequency.

library(data.table)
setDT(df)[, idx := cumsum(c(0L, Var2[-1L] > Var2[-.N] + 60L)), by=.(Var1)][, 
    Freq := .N, by=.(Var1, idx)]

output:

   Var1                Var2 idx Freq
1:    A 2018-09-01 10:00:00   0    3
2:    A 2018-09-01 10:00:30   0    3
3:    A 2018-09-01 10:00:45   0    3
4:    A 2018-09-10 22:10:00   1    1
5:    B 2017-09-05 10:54:30   0    1
6:    B 2018-12-15 10:00:30   1    2
7:    B 2018-12-15 10:01:00   1    2
8:    C 2017-02-20 17:16:30   0    1
9:    C 2017-12-20 20:08:56   1    1

Upvotes: 0

Jelger van Zaane
Jelger van Zaane

Reputation: 69

You can do something like this. Create a new character vector to define the groups, then group by Var1 and the new variable. This doesn't give exactly your desired output because the minutes are defined differently. 

    library(dplyr)
    df %>% 
      mutate(minute = substring(as.character(Var2), 1, 16)) %>% 
      group_by(Var1, minute) %>% 
      mutate(frequency = n())

Upvotes: 0

Related Questions