CODEWITHSUNDEEP
raggregategrouping
florencest
florencest

Reputation: 37

Get the average of the 5 highest values of each column per month in R

I have a dataframe that looks like this:

              ANDRITZ       VERBUND  STRABAG SE TELEKOM AUSTRIA VOESTALPINE WIENERBERGER
2009-01-29 -0.01191567  0.0252923579 -0.04838710     0.005430566  0.01360294  -0.03309218
2009-01-30  0.02922078 -0.0009725906 -0.01355932     0.037037037 -0.07072905   0.01399473
2009-02-02 -0.02140604 -0.0493849013 -0.04123711    -0.008928571  0.01834504  -0.08239956
2009-02-03  0.07460281  0.0031654408  0.01433692     0.027777778  0.01303181   0.01295607
2009-02-04  0.01221341  0.0216241299 -0.01060071    -0.004382761  0.11464245   0.08027051
2009-02-05 -0.01248942  0.0274345930 -0.02142857    -0.031548056 -0.04175153  -0.04953729

It contains about 35 years worth of daily data, and I would like to get the average of the 5 highest values per month, per column.

So far, I have tried this:

data <- as.data.frame(xts(matrix(runif(108, -1, 1), ncol=6), 
                          order.by = seq.Date(as.Date("2009-01-24"), by = "day", length.out = 18))) #reproducible example
MAX <- apply(data, 2, function(x) order(x, decreasing = T)[1:5])
result <- unlist(lapply(1:NCOL(data), function(x) mean(data[MAX[,x],x])))

Which gives me the average of the 5 highest values per column, but not per month (I realise this is probably not the most efficient way). I have tried combining this with the aggregate function, or by using the dplyr package, but haven't been able to manage grouping it to get the average of the 5 highest values per month.

Any help would be greatly appreciated.

Upvotes: 1

Views: 440

Answers (1)

GKi
GKi

Reputation: 39657

You can use aggregate with sort, tail and mean to get the mean of the top 5 per month.

data$date <- format(data$date, "%Y-%m")

aggregate(. ~ date, data, function(x) mean(tail(sort(x), 5)))
#     date        X1         X2         X3        X4         X5        X6
#1 2009-01 0.4155773 -0.1588251  0.6956570 0.4914652  0.4077123 0.5147612
#2 2009-02 0.5392858  0.4840573 -0.2273043 0.3931874 -0.1614169 0.1222684

In case you want to ignore NA try:

aggregate(. ~ date, data, function(x) {
  x <- x[!is.na(x)]
  if(length(x) > 0) {mean(tail(sort(x), 5))} else {NA}
})

Data:

set.seed(42)
data <- data.frame(date=seq.Date(as.Date("2009-01-24"), by = "day", length.out = 18), matrix(runif(108, -1, 1), ncol=6))

Upvotes: 1

Related Questions