Create a table in R from a date frame grouping the values per month

Asked

Viewed 39 times

0

I have a dataframe df with two columns: Number and Date.

df = data.frame(numero = c("10", "20", "30", "40", "50"), data = c("2021-10-01", "2021-10-02", "2021-09-01", "2021-09-01", "2021-08-01"))

I need to create a table with the amount of records per month based on the values of these columns. It would look like this:

Mes Quantidade Ago 1 Set 2 Out 2

r

  • It has two different values for oububro. Thus, the desired table cannot be elaborated. I could check?

  • 1

    Could you explain it better? In case the data frame will have different values even for each month, what matters in the case, would be how many times the month repeated. The quantity column is not a sum of the Number column, but a count of times that month repeated.

  • Yeah, I got it.

  • I used the months function to get the month of the Date column, but I can’t evolve from this point. df$mes = months(df$Data, abbreviate = FALSE )

  • I edited the answer.

2 answers

2

With dplyr and lubridate you can do so:

library(dplyr)
library(lubridate)

df %>% 
  mutate(.data = ., across(.cols = data, .fns = ~ ymd(.))) %>% 
  mutate(.data = ., across(.cols = data, .fns = ~ month(., label = TRUE))) %>% 
  group_by(data) %>% 
  summarise(count = n())

#  data  count
#  <ord> <int>
#1 ago       1
#2 set       2
#3 out       2

  • Note that I put the arguments of the functions if you want explanations about how this worked.

  • 1

    Thank you, I understand the code perfectly.

1

With R base

df$Mes <- format(as.Date(df$data), "%B")  # ou %m para mês como número

table(df$Mes)
#>
#>   agosto  outubro setembro
#>        1        2        2

Can use as.data.frame(table(...)) if you need it as a date frame..

With dplyr

library(dplyr)

df %>% mutate(Mes = format(as.Date(df$data), "%B")) %>%
       group_by(Mes) %>%
       summarise(Quantidade = n())
#>     # A tibble: 3 x 2
#>   Mes      Quantidade
#>   <chr>         <int>
#> 1 agosto            1
#> 2 outubro           2
#> 3 setembro          2

With data table.

library(data.table)

setDT(df)

df[, Mes := month(data)][, .(Quantidade = .N), Mes]
#>    Mes Quantidade
#> 1:  10          2
#> 2:   9          2
#> 3:   8          1

month is part of the date.table. It is equivalent to lubridate::Month, but has no option to display month as name.

Browser other questions tagged r

You are not signed in. Login or sign up in order to post.