r - Group by day and hour

Question

I created a data frame with three columns date, ID and price(e5). I want to get the mean price by day and hour.

> head(fuel_price, n = 5) 
                        date                         station_uuid    e5
    1 2019-04-15 04:01:06+02 88149d2f-3258-445b-bfa4-60898e7fb186 1.529
    2 2019-04-15 04:56:05+02 5c2d04fd-e464-4c96-b4a6-d996d0a8630c 1.539
    3 2019-04-15 05:00:06+02 c8137d18-edad-4006-9746-18e876b14b1d 1.530
    4 2019-04-16 05:00:06+02 6b2143cb-1cd8-4b4b-b2fb-2502f6ea8b35 1.542
    5 2019-04-16 05:02:06+02 dbdb29f5-93aa-4ee4-a52b-7bff0e4ab75a 1.562

I think the main problem is that the date is not in the right format, but i am not able to change it because of the +02 for the timezone at the end.

price_2019$date <- mdy_hms(prices_2019$date)

If this would be fixed, would it work with dplyr?

agg_price <- price_2019 %>% group_by(Date=floor_date(date, "hour")) %>% summarize(mean_price = mean(price))

Could you help me out?

question from:https://stackoverflow.com/questions/65643250/group-by-day-and-hour

Answer 1

You can use lubridate::ymd_hms to convert the date variable to date-time, group by day and hour from it and take mean value of price for each hour.

library(dplyr)

prices_2019 %>%
  mutate(date = lubridate::ymd_hms(date),
         date_hour = format(date, "%Y-%m-%d %H")) %>%
  group_by(date_hour) %>%
  summarize(mean_price = mean(price))