Adjust date order in output table

The order of the output table is incorrect, after 01/01 it goes to 01/02 instead of 03/01, how to adjust?

 df1<- structure(
  list(date2= c("01-01-2021","01-01-2021","03-01-2021","03-01-2021","01-02-2021","01-02-2021"),
       Category= c("ABC","CDE","ABC","CDE","ABC","CDE"),
       coef= c(5,4,0,2,4,5)),
  class = "data.frame", row.names = c(NA, -6L))

x<-df1 %>%
  group_by(date2) %>%
  summarize(across("coef", sum),.groups = 'drop')

> x
# A tibble: 3 x 2
  date2       coef
  <chr>      <dbl>
1 01-01-2021     9
2 01-02-2021     9
3 03-01-2021     2

Expected output table

  date2       coef
      <chr>      <dbl>
    1 01-01-2021     9
    2 03-01-2021     2
    3 01-02-2021     9

If I have the code below:

df1<- structure(
  list(date2= c("01-01-2022","01-01-2022","03-01-2021","03-01-2021","01-02-2021","01-02-2021"),
       Category= c("ABC","CDE","ABC","CDE","ABC","CDE"),
       coef= c(5,4,0,2,4,5)),
  class = "data.frame", row.names = c(NA, -6L))

x<-df1 %>%
  group_by(date2) %>%
  summarize(across("coef", sum),.groups = 'drop')%>% 
  arrange(date2 = as.Date(date2, format = "%d-%m-%y"))

> x
# A tibble: 3 x 2
  date2       coef
  <chr>      <dbl>
1 01-01-2022     9
2 03-01-2021     2
3 01-02-2021     9
      

This order is incorrect as 01-01-2022 would have to be last not first.

Solution 1:

You can convert your date column to date format, and arrange it. I'd also suggest using the date format for your date columns for future data processing.

base R

x$date2 <- as.Date(x$date2, format = "%d-%m-%Y")
x[order(x$date2),]

dplyr

x %>% 
  arrange(date2 = as.Date(date2, format = "%d-%m-%Y")

# A tibble: 3 x 2
  date2       coef
  <chr>      <dbl>
1 01-01-2021     9
2 03-01-2021     2
3 01-02-2021     9

Solution 2:

You need to convert to date first, you appear to think imperial.

aggregate(coef ~ date2, transform(df1, date2=as.Date(date2, format='%m-%d-%Y')), sum)
#        date2 coef
# 1 2021-01-01    9
# 2 2021-01-02    9
# 3 2021-03-01    2