Summarize dataframe by day from timestamp

问题:

I have a dataset data that contains a timestamp and a suite of other variables with values at each timestamp. I am trying to use ddply within plyr to create a new dataframe that is the summary (e.g. mean) of a variable by the group day.

How can I get ddply to group by day? Or how can I can create a group or grouping variable from the day (%d) within the timestamp?

The result dataframe would consist of the average values per day for each day present in data.

library(plyr)
data <- read.csv("data.csv", header=T)
data$TIMESTAMP <- strptime(data$TIMESTAMP, "%m/%d/%Y %H:%M")
ddply(data,.(DAY),summarise, V1 = mean(P), V2 = max(WS)) # I know that day is wrong here

# dput of data 
data <- structure(list(TIMESTAMP = structure(list(sec = c(0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), min = c(0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), hour = c(11L, 12L, 13L, 
14L, 15L, 11L, 12L, 13L, 14L, 15L, 11L, 12L, 13L, 14L, 15L), 
    mday = c(1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 7L, 7L, 
    7L, 7L, 7L), mon = c(0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
    0L, 3L, 3L, 3L, 3L, 3L), year = c(112L, 112L, 112L, 112L, 
    112L, 112L, 112L, 112L, 112L, 112L, 112L, 112L, 112L, 112L, 
    112L), wday = c(0L, 0L, 0L, 0L, 0L, 1L, 1L, 1L, 1L, 1L, 6L, 
    6L, 6L, 6L, 6L), yday = c(0L, 0L, 0L, 0L, 0L, 1L, 1L, 1L, 
    1L, 1L, 97L, 97L, 97L, 97L, 97L), isdst = c(0L, 0L, 0L, 0L, 
    0L, 0L, 0L, 0L, 0L, 0L, 1L, 1L, 1L, 1L, 1L)), .Names = c("sec", 
"min", "hour", "mday", "mon", "year", "wday", "yday", "isdst"
), class = c("POSIXlt", "POSIXt")), P = c(992.4, 992.4, 992.4, 
992.4, 992.4, 992.4, 992.4, 992.4, 992.4, 992.4, 239, 239, 239, 
239, 239), WS = c(4.023, 3.576, 4.023, 6.259, 4.47, 3.576, 3.576, 
2.682, 4.023, 3.576, 2.682, 3.129, 2.682, 2.235, 2.682), WD = c(212L, 
200L, 215L, 213L, 204L, 304L, 276L, 273L, 307L, 270L, 54L, 24L, 
304L, 320L, 321L), AT = c(16.11, 18.89, 20, 20, 19.44, 10.56, 
11.11, 11.67, 12.22, 11.11, 17.22, 18.33, 19.44, 20.56, 21.11
), FT = c(17.22, 22.22, 22.78, 22.78, 20, 11.11, 15.56, 17.22, 
17.78, 15.56, 24.44, 25.56, 29.44, 30.56, 29.44), H = c(50L, 
38L, 38L, 39L, 48L, 24L, 19L, 18L, 16L, 18L, 23L, 20L, 18L, 17L, 
15L), B = c(1029L, 1027L, 1026L, 1024L, 1023L, 1026L, 1025L, 
1024L, 1023L, 1023L, 1034L, 1033L, 1032L, 1031L, 1030L), FM = c(14.9, 
14.4, 14, 13.7, 13.6, 13.1, 12.8, 12.3, 12, 11.7, 12.8, 12, 11.4, 
10.9, 10.4), GD = c(204L, 220L, 227L, 222L, 216L, 338L, 311L, 
326L, 310L, 273L, 62L, 13L, 312L, 272L, 281L), MG = c(8.047, 
9.835, 10.28, 13.41, 11.18, 9.388, 8.941, 8.494, 9.835, 10.73, 
6.706, 7.153, 8.047, 8.047, 7.6), SR = c(522L, 603L, 604L, 526L, 
248L, 569L, 653L, 671L, 616L, 487L, 972L, 1053L, 1061L, 1002L, 
865L), WS2 = c(2.235, 3.576, 4.47, 4.47, 5.364, 4.023, 2.682, 
3.576, 3.576, 4.023, 3.129, 3.129, 3.576, 2.682, 3.129), WD2 = c(200L, 
201L, 206L, 210L, 211L, 319L, 315L, 311L, 302L, 290L, 49L, 39L, 
15L, 348L, 329L)), .Names = c("TIMESTAMP", "P", "WS", "WD", "AT", 
"FT", "H", "B", "FM", "GD", "MG", "SR", "WS2", "WD2"), row.names = c(NA, 
-15L), class = "data.frame")

回答1:

You can use format to extract the day

 dat$DAY <- as.factor(format(dat$TIMESTAMP,'%d'))

 [1] 01 01 01 01 01 02 02 02 02 02 07 07 07 07 07
Levels: 01 02 07

For some reasons I get error if I don't remove the first column ( as said in comment in moment I am writing this)

ddply(data[,-1],.(DAY),summarise, V1 = mean(P), V2 = max(WS))
  DAY    V1    V2
1  01 992.4 6.259
2  02 992.4 4.023
3  07 239.0 3.129

converting the TIMESTAMP to POSIXct seems to correct the problem

 dat$TIMESTAMP <- as.POSIXct(dat$TIMESTAMP)

ddply(dat,.(DAY),summarise, V1 = mean(P), V2 = max(WS))
  DAY    V1    V2
1  01 992.4 6.259
2  02 992.4 4.023
3  07 239.0 3.129

EDIT no need to use format

Since Your TIMESTAMP column is a POSIXlt , it easy to retrieve day part from it, You can do this :

dat$TIMESTAMP$mday
 [1] 1 1 1 1 1 2 2 2 2 2 7 7 7 7 7