Convert daily sales into monthly

[jak123]

Hi everyone

I got these daily sales ts, that I want to make monthly:

I got data from 18-01-01 to 20-10-31 with multiple duplicates of the same date since it is split between different customers.

(expenses <- data_frame(
  date=seq(as.Date("2018-01-01"), as.Date("2020-10-31"), by=1),
  Sales=rgamma(length(date), shape = 2, scale = 20)))

expenses %>% group_by(month=floor_date(df$Date, "month")) %>%
  summarize(amount=sum(df$Sales))

So in short what I want is monthly sales data, I'm using a mac with the latest version of R-studio

Thanks for helping

[rdr]

One way to approach this would be to add a salesMonth column to your data frame using the floor_date function from lubridate. Then you can use dplyr to group by the salesMonth and summarize that variable with sum.

Here's a simple using a data.frame I constructed called dailySales:

library(dplyr)
library(lubridate)

dailySales <- data.frame(
  saleDate = as.Date(c("2020-01-10", "2020-01-12", "2020-01-12", "2020-01-15", "2020-02-10", "2020-02-25")),
  saleAmount = c(100, 100, 200, 100, 50, 100)
)

dailySales <- dailySales %>% 
  mutate(month = floor_date(dailySales$saleDate, "month"))

dailySales %>% 
  group_by(month) %>% 
  summarize(total = sum(saleAmount))

[jak123]

Hi rdr,
Thanks alot for your help, the problem is solved! but when make the monthly data into a timeseries and plot it I get this odd looking graph!
image|690x439
can u explain why it plots the straight line above the actual plot?

head(ts_monthlydata)

Best Jakob

[rdr]

Jakob -- Post the code for your graph and perhaps someone can help with that.

For me, picking up from above, once I have the monthly totals, I can create a quick bar chart of monthly sales using ggplot():

library(ggplot2)

monthly <- dailySales %>% 
  group_by(month) %>% 
  summarize(total = sum(saleAmount))

ggplot(monthly) + 
  geom_col(aes(x = month, y = total))

Hope this helps.

-- Robert

[jak123]

It does, and thanks a lot Robert, it was really helpful