How do I arrange or sort dates with R and dplyr

Question

I want to sort my dataframe on the dates column. My example dataframe:

library(tidyverse)    

dates <- tibble(date = c("01-01-2017", "02-03-2017", "01-02-2017", "02-01-2017", "01-03-2017"), 
                   value = c(8, 12, 4, 14, 11)) 

So the following isn't working because it only sorts on the days.

arrange(dates, date)

Solution 1

There is an issue here based on your comment, date data types should be stored as such, a date, not a string of characters, that way you can sort by them and filter etc.

When you choose to output the information, you can then format it and make it look pretty to people.

the first example will make the dates into actual dates, then you can filter/sort by this column, the second will only sort it, and if you wish to perform another operation you will need to convert again.

Option 1 (Good):

dates_mos <- dates %>%
  mutate(date = as.Date(date, "%d-%m-%Y")) %>%
  arrange(date)

Output 1:

        date value
      <date> <dbl>
1 2017-01-01     8
2 2017-01-02    14
3 2017-02-01     4
4 2017-03-01    11
5 2017-03-02    12

Option 2 (Not so good):

dates_mos <- dates %>%
  arrange(date = as.Date(date, "%d-%m-%Y"))

Output 2:

        date value
       <chr> <dbl>
1 01-01-2017     8
2 02-01-2017    14
3 01-02-2017     4
4 01-03-2017    11
5 02-03-2017    12

Solution 2

The way you have saved the data in your question is not appropriate for sorting according to dates. It is saved as regular strings, whereas you'd want R to recognise it as dates.

Do this with as.Date() including a certain format for the date string. From your question it is not clear whether your date strings are day-month-year (format = "%d-%m-%Y") or month-day-year (format = "%m-%d-%Y"):

dates$date <- as.Date(dates$date, format="%d-%m-%Y")
arrange(dates, date)
# 1 2017-01-01     8
# 2 2017-01-02    14
# 3 2017-02-01     4
# 4 2017-03-01    11
# 5 2017-03-02    12