Creating a monthly average column with weekly data R

Question

I have a dataframe called Demandts and it is presented as a tsibble below:

(with mam representing monthly MA)

# A tsibble: 255 x 7 [1]
# Key:       Date [255]
    Week Demand Date         mam Quarter Month year 
   <dbl>  <dbl> <date>     <dbl>   <qtr> <dbl> <chr>
 1     1     48 2018-01-01  199. 2018 Q1     1 2018 
 2     2    101 2018-01-08  199. 2018 Q1     1 2018 
 3     3    129 2018-01-15  106. 2018 Q1     1 2018 
 4     4    113 2018-01-22  118. 2018 Q1     1 2018 
 5     5    116 2018-01-29  121. 2018 Q1     1 2018 
 6     6    123 2018-02-05  125. 2018 Q1     2 2018 
 7     7    137 2018-02-12  132. 2018 Q1     2 2018 
 8     8    136 2018-02-19  132. 2018 Q1     2 2018 
 9     9    151 2018-02-26  126. 2018 Q1     2 2018 
10    10     87 2018-03-05  142. 2018 Q1     3 2018 
# ... with 245 more row

I am trying to create a new column with the monthly average demand so that I can create a monthly plot with ggplot.

edit* How do I average monthly demand?

I don't understand exactly what you are getting stuck at. Having the months as a numeric is not going to prevent you from being able to average the demand nor creating a plot — koolmees, Sep 17 '21 at 10:13
As above, which part are you stuck with? To convert months to a 'tidy' name use the 'month.abb' function, perhaps as a factor, then proceed to summarise and plot — Quixotic22, Sep 17 '21 at 10:19
I'm just trying to understand how to calculate and write the calculation for finding the monthly average. (I'm absolutely useless at this so I need it explaining as if I were a child) — Joseph Droogan, Sep 17 '21 at 10:28
this: `result_df<-aggregate(Demandts , by = list(Demandts$Month), FUN = mean)` ? — D.J, Sep 17 '21 at 10:43

score 0 · Answer 1 · answered Sep 17 '21 at 10:40

You can use something like the following code

library(tidyverse)

df %>% 
   group_by(Month) %>% 
   summarise(monthly = mean(mam)) %>% 
   ggplot(aes(x = Month, y = monthly)) +
   geom_col()

Data

df = structure(list(Week = 1:10, Demand = c(48L, 101L, 129L, 113L, 
116L, 123L, 137L, 136L, 151L, 87L), Date = c("2018-01-01", "2018-01-08", 
"2018-01-15", "2018-01-22", "2018-01-29", "2018-02-05", "2018-02-12", 
"2018-02-19", "2018-02-26", "2018-03-05"), mam = c(199L, 199L, 
106L, 118L, 121L, 125L, 132L, 132L, 126L, 142L), Quarter = c("Q1", 
"Q1", "Q1", "Q1", "Q1", "Q1", "Q1", "Q1", "Q1", "Q1"), Month = c(1L, 
1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 3L), year = c(2018L, 2018L, 2018L, 
2018L, 2018L, 2018L, 2018L, 2018L, 2018L, 2018L)), class = "data.frame", row.names = c(NA, 
-10L))

Creating a monthly average column with weekly data R

1 Answers1