How to calculate daily average of hourly data and for different variables at the same time

Question

I have the data in the form:

Date_Time            Address      Consumption (Litres/hr)
19/4/2016 13:00      41-2TV            20
20/4/2016 14:00      41-2TV            40
21/4/2016 15:00      41-2TV            16
19/4/2016 13:00      39-2TV            20
20/4/2016 14:00      39-2TV            40
21/4/2016 15:00      39-2TV            16
19/4/2016 13:00      43-2TV            20
20/4/2016 14:00      43-2TV            40
21/4/2016 15:00      43-2TV            16

I have the data for each address for the complete day and so on for complete month, How can I calculate the daily average & then leading to monthly average for different addresses at the same time. I have over 100's of addresses and over 1 year of data , given on daily basis.

Answer 1

From your dataframe I created a mock dataframe. I would use dplyr package to group_by and summarise.

If the values are by day you can just group_by(Adress). If you want then to check by month you can use lubridate function floor_date(Date, by = 'month') on the group by function to have a read by month for each Address.

library(dplyr)
library(lubridate)
Consumption <- data.frame(Date_Time = c('19/4/2016 13:00','20/4/2016 14:00', '21/4/2016 15:00', '19/4/2016 13:00', '20/4/2016 14:00', '21/4/2016 15:00'), 
                          Address = c('41-2TV', '41-2TV', '41-2TV', '39-2TV', '39-2TV','39-2TV'), 
                          Consumption = c(20, 40, 16, 20, 40, 16))


NewDataFrame <- Consumption %>%
  mutate(Date_Time = as.Date(Date_Time,  format = "%d/%m/%Y"), 
         Address = as.factor(Address)) %>%
  group_by(Address) %>%
  summarise(AverageDay = mean(Consumption))

Address AverageDay
  <fct>        <dbl>
1 39-2TV        25.3
2 41-2TV        25.3

Check dplyr package for all these simple transformation or provide more data if unclear.