Geek asks and answers

Using R to calculate summary events per day from a data frame containing all events and their timestamps

I have a data frame dfthat contains "messages". Each line is a message. Each message has a timestamp df$messagedatein POSIXct format %Y-%m-%d %H:%M:%S. Example:

> head(df)
messageid   user.id    message.date         
123         999       2011-07-17 17:54:27
456         888       2011-07-19 16:56:50

(The following is the version of dput()'ed):

df <- structure(list(messageid = c(123L, 456L), user.id = c(999L, 888L), 
      message.date = structure(c(1310950467, 1311119810), class = c("POSIXct", 
      "POSIXt"), tzone = "")), .Names = c("messageid", "user.id", 
      "message.date"), row.names = c(NA, -2L), class = "data.frame")

How to create a data frame with the total number of messages per day? Example:

day                   message.count 
2011-07-17             1
2011-07-18             0
2011-07-19             1

Instead of not including dates without messages, I want to make sure that message.countzero is set for those days .

What I have done so far: I extracted part of the calendar day message.dateby doing:

df$calendar.day<-as.POSIXct(strptime(substr(df$message.date,1,10),"%Y-%m-%d",tz="CST6CDT"))
> head(df$calendar.day)
[1] "2011-07-17 CDT" "2011-07-18 CDT" "2011-07-19 CDT"

:   daterange < - seq (min (df $calendar.day), max (df $calendar.day), by = "day" )

+3
2

, sapply() , .

countMessages <- function(timeStamps) {
    Dates <- as.Date(strftime(df$message.date, "%Y-%m-%d"))
    allDates <- seq(from = min(Dates), to = max(Dates), by = "day")
    message.count <- sapply(allDates, FUN = function(X) sum(Dates == X))
    data.frame(day = allDates, message.count = message.count)
}

countMessages(df$message.date)
#          day message.count
# 1 2011-07-17             1
# 2 2011-07-18             0
# 3 2011-07-19             1
+2

as.data.frame table(), . :

test_data <- data.frame(date=c("March","April","April","May"),messageid=c(1,2,3,4),userid=c(55,33,1,56))
print(as.data.frame(table(test_data[1])))

:

   Var1 Freq
1 April    2
2 March    1
3   May    1

, , , (, ), , . 0 , NA.

:

months <- c("January","February","March","April","May","June")
full <- merge(counts,months,by=1,all=TRUE)

, , POSIX, .

+1

More articles:


All Articles