How to group data by month in R with dplyr-CodePudding

How can I group my data by month (y%,m%,d%), then create a table with a count of how many rows are in each month? Date is in column “orderdate”.

So what I’m looking for is 01= 35744, 02= 747362, etc

All in a new table

I have gotten data arranged by date, but not month specifically. So current grouped and filtered table is Example_Table

CodePudding user response：

By using lubridate::date and simple dplyr

library(dplyr)

dummy <- data.frame(
  orderdate = seq(as.Date("2020-01-02"),as.Date("2021-07-13"), by = "days")
)

dummy %>%
  as_tibble %>%
  mutate(month = lubridate::month(orderdate)) %>%
  group_by(month) %>%
  summarise(n = n())

   month     n
   <dbl> <int>
 1     1    61
 2     2    57
 3     3    62
 4     4    60
 5     5    62
 6     6    60
 7     7    44
 8     8    31
 9     9    30
10    10    31
11    11    30
12    12    31

as table

dummy2 <- dummy %>%
  as_tibble %>%
  mutate(month = lubridate::month(orderdate)) %>%
  group_by(month) %>%
  summarise(n = n()) %>%
  select(n) %>%
  t %>%
  as.table
colnames(dummy2) <- seq(1:12)
dummy2

   1  2  3  4  5  6  7  8  9 10 11 12
n 61 57 62 60 62 60 44 31 30 31 30 31

CodePudding user response：

If you have data for multiple years do you count each month separately ? Meaning June 2020 should be captured separately from June 2021.

If so, you can use

library(dplyr)

result <- Example_Table %>% count(month = format(orderdate, '%b-%Y'))

Or in base R -

result <- table(format(Example_Table$orderdate, '%b-%Y'))

If you want to consider June 2020 and June 2021 in the same group use only '%b' in format instead of '%b-%Y'.