1

I have multiple values for given dates. I want to calculate the average of the n highest values for each given day.

If the values for day one are (1,2,3) and for day two (4,5,6), I'd like to see 2.5 for day 1 and 5.5 for day2. Below some sample data to illustrate the problem and my idea so far. 

library(tidyverse)
#Setting up Dummy Data
Dummy_date<-c("2017-01-01","2017-01-01","2017-01-01","2017-01-02","2017-01-02","2017-01-02")
Dummy_data<-seq(1:6)
Dummy_df<-as.data.frame(cbind(Dummy_date,Dummy_data))
names(Dummy_df[1])<-"Date"
names(Dummy_df[2])<-"Data"

#Format Dummy Dataframe
Dummy_df$Dummy_date<-as.POSIXct(Dummy_date)
Dummy_df$Dummy_data<-as.numeric(Dummy_data)

#Defining  N
 N=2

#My initial approach. which only works for the 1st day...
Best_N<-Dummy_df%>%
  group_by(Dummy_date)%>%
  summarise(Max2=sum(order(-Dummy_data)[1:N])/N)

Best_N

The output yields a value of 2.5 for both days which is the correct result for day one.

Dr. Jones
  • 141
  • 1
  • 10

1 Answers1

1

You could use the top_n function:

Best_Two<-Dummy_df%>%
  group_by(Dummy_date)%>%
  top_n(Dummy_data,n=2) %>%
  summarize(mean = mean(Dummy_data))

Output:

# A tibble: 2 x 2
  Dummy_date  mean
      <dttm> <dbl>
1 2017-01-01   2.5
2 2017-01-02   5.5

Hope this helps!

Florian
  • 21,690
  • 4
  • 34
  • 66