0

I want to calculate the average price change for a specific hour on a particular day, e.g. 01:00-02:00 on a Monday.

The sample data is here:


    Date    Time    Change  Weekday
09/03/2016  16:00   1.980786372 Wednesday
09/03/2016  17:00   0.000000000 Wednesday
10/03/2016  16:00   6.123554913 Thursday
10/03/2016  17:00   0.851063830 Thursday
11/03/2016  10:00   0.000000000 Friday
11/03/2016  17:00   0.000000000 Friday
12/03/2016  02:00   1.924686192 Saturday
12/03/2016  03:00   4.448267388 Saturday
13/03/2016  00:00   2.973977695 Sunday
13/03/2016  23:00   -2.587601078    Sunday
14/03/2016  09:00   -1.132075472    Monday
14/03/2016  17:00   0.870827286 Monday
14/03/2016  18:00   6.057553957 Monday
15/03/2016  00:00   1.964415103 Tuesday
15/03/2016  01:00   0.493198632 Tuesday
15/03/2016  10:00   0.023116043 Tuesday
15/03/2016  11:00   0.145716696 Tuesday
16/03/2016  09:00   2.426487263 Wednesday
16/03/2016  10:00   1.609434102 Wednesday

not sure on how to get average for both hour and day of the week together?

JHolmes
  • 23
  • 6

2 Answers2

1

You can try:

library(dplyr)
dfn %>% group_by(Weekday,Time) %>% summarise(Avg=mean(Change,na.rm=T))

# A tibble: 19 x 3
# Groups:   Weekday [7]
   Weekday   Time      Avg
   <chr>     <chr>   <dbl>
 1 Friday    10:00  0     
 2 Friday    17:00  0     
 3 Monday    09:00 -1.13  
 4 Monday    17:00  0.871 
 5 Monday    18:00  6.06  
 6 Saturday  02:00  1.92  
 7 Saturday  03:00  4.45  
 8 Sunday    00:00  2.97  
 9 Sunday    23:00 -2.59  
10 Thursday  16:00  6.12  
11 Thursday  17:00  0.851 
12 Tuesday   00:00  1.96  
13 Tuesday   01:00  0.493 
14 Tuesday   10:00  0.0231
15 Tuesday   11:00  0.146 
16 Wednesday 09:00  2.43  
17 Wednesday 10:00  1.61  
18 Wednesday 16:00  1.98  
19 Wednesday 17:00  0     

#Data
dfn <- structure(list(Date = c("09/03/2016", "09/03/2016", "10/03/2016", 
"10/03/2016", "11/03/2016", "11/03/2016", "12/03/2016", "12/03/2016", 
"13/03/2016", "13/03/2016", "14/03/2016", "14/03/2016", "14/03/2016", 
"15/03/2016", "15/03/2016", "15/03/2016", "15/03/2016", "16/03/2016", 
"16/03/2016"), Time = c("16:00", "17:00", "16:00", "17:00", "10:00", 
"17:00", "02:00", "03:00", "00:00", "23:00", "09:00", "17:00", 
"18:00", "00:00", "01:00", "10:00", "11:00", "09:00", "10:00"
), Change = c(1.980786372, 0, 6.123554913, 0.85106383, 0, 0, 
1.924686192, 4.448267388, 2.973977695, -2.587601078, -1.132075472, 
0.870827286, 6.057553957, 1.964415103, 0.493198632, 0.023116043, 
0.145716696, 2.426487263, 1.609434102), Weekday = c("Wednesday", 
"Wednesday", "Thursday", "Thursday", "Friday", "Friday", "Saturday", 
"Saturday", "Sunday", "Sunday", "Monday", "Monday", "Monday", 
"Tuesday", "Tuesday", "Tuesday", "Tuesday", "Wednesday", "Wednesday"
)), class = "data.frame", row.names = c(NA, -19L))
Duck
  • 39,058
  • 13
  • 42
  • 84
1

Here are three solutions.

Base R

aggregate(Change ~ Weekday + Time, df1, mean)

Package dplyr

library(dplyr)

df1 %>%
  group_by(Weekday, Time) %>%
  summarise(Mean = mean(Change))

Package data.table

library(data.table)

setDT(df1)[, .(Mean = mean(Change)), .(Weekday, Time)]

Data

df1 <-
structure(list(Date = c("09/03/2016", "09/03/2016", "10/03/2016", 
"10/03/2016", "11/03/2016", "11/03/2016", "12/03/2016", "12/03/2016", 
"13/03/2016", "13/03/2016", "14/03/2016", "14/03/2016", "14/03/2016", 
"15/03/2016", "15/03/2016", "15/03/2016", "15/03/2016", "16/03/2016", 
"16/03/2016"), Time = c("16:00", "17:00", "16:00", "17:00", "10:00", 
"17:00", "02:00", "03:00", "00:00", "23:00", "09:00", "17:00", 
"18:00", "00:00", "01:00", "10:00", "11:00", "09:00", "10:00"
), Change = c(1.980786372, 0, 6.123554913, 0.85106383, 0, 0, 
1.924686192, 4.448267388, 2.973977695, -2.587601078, -1.132075472, 
0.870827286, 6.057553957, 1.964415103, 0.493198632, 0.023116043, 
0.145716696, 2.426487263, 1.609434102), Weekday = c("Wednesday", 
"Wednesday", "Thursday", "Thursday", "Friday", "Friday", "Saturday", 
"Saturday", "Sunday", "Sunday", "Monday", "Monday", "Monday", 
"Tuesday", "Tuesday", "Tuesday", "Tuesday", "Wednesday", "Wednesday"
)), class = "data.frame", row.names = c(NA, -19L))
Rui Barradas
  • 70,273
  • 8
  • 34
  • 66