Geek Answers Handbook

Get the amount of events in one day

I have a dataset similar to the one shown below (15 years of data) on half-hour interval data and the occurrence of an event (1 means this happened, and 0 does not)

Date                    Event
2002-04-27 19:30:00      0
2002-04-27 20:00:00      0
2002-04-27 20:30:00      0
2002-04-27 21:00:00      0
2002-04-27 21:30:00      1
2002-04-27 22:00:00      1
2002-04-27 22:30:00      0
2002-04-27 23:00:00      0
2002-04-27 23:30:00      1
2002-04-28 00:00:00      1
2002-04-28 00:30:00      1
2002-04-28 01:00:00      1
2002-04-28 01:30:00      0
2002-04-28 02:00:00      0
2002-04-28 02:30:00      0
2002-04-28 03:00:00      0
2002-04-28 03:30:00      0
2002-04-28 04:00:00      0
2002-04-28 04:30:00      0
2002-04-28 05:00:00      0
2002-04-28 05:30:00      0
2002-04-28 06:00:00      0
2002-04-28 06:30:00      0
2002-04-28 07:00:00      0

I would like to calculate for each day (for example 2002-04-27) the number of events that occurred. However, sequential 1 means that this is only one event, and also 1 transition every other day, for example, 2002-04-27 21:30:00has 1, which means 2002-04-28 00:00:00, but this will be considered 1 event that happened only on 2002-04-27, A result similar to something below would be ideal .

Date           No_Event
2002-04-27       2
2002-04-28       0

So how will I do this? Any help is greatly appreciated.

+4
source share
3 answers

lubridate ( ) data.table

library(data.table)
library(lubridate)
setDT(df)
df[Event!=shift(Event, fill=0), sum(Event), by=floor_date(Date, unit="day")]

#   floor_date V1
#1: 2002-04-27  2
#2: 2002-04-28  0

df,

 df <- data.frame(Date=seq(as.POSIXct("2002-04-27 19:30:00 ", tz="GMT"), as.POSIXct("2002-04-28 07:00:00 ", tz="GMT"), by="30 min"),
                     Event=c(0L, 0L, 0L, 0L, 1L, 1L, 0L, 0L, 1L, 1L, 1L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L))
+3

:

1) base . , , 1, 0 ( , diff == 1), :

No_Events <- tapply(diff(c(0, df$Event)) == 1, as.Date(df$Date), sum)

:

> No_Events
2002-04-27 2002-04-28 
         2          0

, , as.data.frame(No_Events) data.frame(Date = as.Date(names(No_Events)), No_Event = unname(No_Events)).

1a) aggregate tapply, tr, Date No_Event , 1, 1. aggregate:

tr <- transform(df, No_Event = diff(c(0, df$Event)) == 1, Date = as.Date(Date))
aggregate(No_Event ~ Date, tr, sum)

:

        Date No_Event
1 2002-04-27        2
2 2002-04-28        0

2) . , diff == 1 aggregate:

library(zoo)
z <- read.zoo(df, tz = "")
m <- merge(z, No_Event = diff(z) == 1, fill = coredata(z[1]))
z.ag <- aggregate(m, as.Date(format(time(z))), sum)

:

> z.ag
           z No_Event
2002-04-27 3        2
2002-04-28 3        0

z z.ag[, -1] z.ag[, -1, drop = FALSE].

+3

First create a day column

dat$day <- strftime(x = dat$Date, format = "%D") # try %F as well

Find serial 1 and save only the first

for(i in nrow(dat):2) {
  if(dat$Event[i]==1 && dat$Event[i-1]==1)
      dat$Event[i] <- 0
}

Then aggregate the results

by(data = dat$Event, INDICES = dat$day, FUN = sum)

dat$day: 04/27/02
[1] 2
-----------------------------------------------------------------
dat$day: 04/28/02
[1] 0
+1
source

More articles:


All Articles