CODEWITHSUNDEEP
rdataframe
user3690243
user3690243

Reputation: 113

R , how to Aggregate data with same date field in an R dataframe

Hi I have an R dataframe that looks like the following:

        SURVEY.DATE A   B   C
1898    2010-05-13  38  34  21
1899    2010-05-13  38  33  21
1897    2010-05-14  37  34  21
1895    2010-05-21  38  29  21
1896    2010-05-21  39  32  21
1894    2010-05-23  39  32  21

I would like to average the rows with the same date so to have only one average observation per day. Ideally I would like to end up with an xts obsject that would look like :

        SURVEY.DATE    A      B  C
1898    2010-05-13    38   33.5 21
1897    2010-05-14    37     34 21
1896    2010-05-21  38.5   30.5 21
1894    2010-05-23    39     32 21

Seems to be a challenge for my newbie R skills...any help / pointers would be appreciated

Upvotes: 0

Views: 919

Answers (2)

Arun
Arun

Reputation: 118779

Here's how I'd do this using data.table.

require(data.table)
setDT(df)[, lapply(.SD, mean), by=SURVEY.DATE]
#    SURVEY.DATE    A    B  C
# 1:  2010-05-13 38.0 33.5 21
# 2:  2010-05-14 37.0 34.0 21
# 3:  2010-05-21 38.5 30.5 21
# 4:  2010-05-23 39.0 32.0 21

Check the new HTML vignettes if you'd like to learn more.

Upvotes: 1

akrun
akrun

Reputation: 886948

You could try

library(dplyr)
res <- df1 %>%
         group_by(SURVEY.DATE) %>% 
         summarise_each(funs(mean))

Or

res1 <- aggregate(.~SURVEY.DATE, df1, mean)

and then convert it to xts

library(xts)
xts(res1[-1], order.by= as.Date(res1[,1]))
#             A    B  C
#2010-05-13 38.0 33.5 21
#2010-05-14 37.0 34.0 21
#2010-05-21 38.5 30.5 21
#2010-05-23 39.0 32.0 21

Upvotes: 2

Related Questions