Calculate average of last 3 non holiday weekdays

Question

I have a dataframe for number of profile hits with date time, week, weekday across various categories.

For sample data refer below (Input Data). What I am looking for is to output a dataframe with average of last 3 weekdays of non holiday weeks from Sunday to Saturday across all categories.

As you can see in the below required output, none of the data from holiday week is considered. Is there any easy way of achieving this without use of loops? If yes how can we do this?

required output:

CAT  Day  Avg
A    SUN  =(1 + 3+99) /3
A    MON  =(6+67+ 45) /3
A    TUE  = (2+ 53+ 68)/3
A    WED
A    THU
A    FRI
A    SAT

Input data:

CAT  DATE       WEEJ    DAY  Hits   Holiday Week
A   9/3/2016    2016-35 SAT  58     No
A   9/2/2016    2016-35 FRI  9      No
A   9/1/2016    2016-35 THU  20     No
A   8/31/2016   2016-35 WED  92     No
A   8/30/2016   2016-35 TUE  2      No
A   8/29/2016   2016-35 MON  6      No
A   8/28/2016   2016-35 SUN  1      No
A   8/27/2016   2016-34 SAT  58     Yes
A   8/26/2016   2016-34 FRI  56     Yes
A   8/25/2016   2016-34 THU  40     Yes
A   8/24/2016   2016-34 WED  42     Yes
A   8/23/2016   2016-34 TUE  59     Yes
A   8/22/2016   2016-34 MON  21     Yes
A   8/21/2016   2016-34 SUN  98     Yes
A   8/20/2016   2016-33 Sat  2      No
A   8/19/2016   2016-33 FRI  85     No
A   8/18/2016   2016-33 THU  29     No
A   8/17/2016   2016-33 WED  37     No
A   8/16/2016   2016-33 TUE  53     No
A   8/15/2016   2016-33 MON  67     No
A   8/14/2016   2016-33 SUN  3      No
A   8/13/2016   2016-32 SAT  35     No
A   8/12/2016   2016-32 FRI  24     No
A   8/11/2016   2016-32 THU  94     No
A   8/10/2016   2016-32 WED  81     No
A   8/9/2016    2016-32 TUE  68     No
A   8/8/2016    2016-32 MON  45     No
A   8/7/2016    2016-32 SUN  99     No

Answer 1

We can use data.table

library(data.table)
setDT(df1)[order(-as.IDate(DATE, "%m/%d/%Y"), toupper(DAY))
     ][HolidayWeek=="No",.(Ave = sum(Hits[1:3])/.N) , by = .(DAY=toupper(DAY))]
#  DAY      Ave
#1: SAT 31.66667
#2: FRI 39.33333
#3: THU 47.66667
#4: WED 70.00000
#5: TUE 41.00000
#6: MON 39.33333
#7: SUN 34.33333

If it is the average of the 3 'Hits'

setDT(df1)[order(-as.IDate(DATE, "%m/%d/%Y"), toupper(DAY))
 ][HolidayWeek=="No",.(Ave = mean(Hits[1:3])) , by = .(DAY=toupper(DAY))]

Answer 2

Here's a solution with dplyr:

library(dplyr)

answer <- x %>% filter(Holiday=="No") %>% group_by(Day)  %>% 
top_n(3,desc(Date))  %>% summarise(Avg = sum(Hits)/n())

It removes all Holiday's, then for every 'DAY' it then takes the last three dates for each of those days and finally summarizes the number of hits and divide by the number of those days, giving you the average.

Please note your 'days' of week aren't all Uppercase.