8

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)
Tdebeus
  • 1,519
  • 5
  • 21
  • 43
  • Use `as.Date()` or `dmy()` from `lubridate` package to cast them to actual date formats. If you store dates as strings they are sorted as such, i.e. alphabetically. – m-dz Oct 06 '17 at 09:32
  • You need to convert your date vector to a Date class something like `dat <- as.Date(dat$date, "%m-%d-%Y")`. where dat is the name of your tibbly. – lmo Oct 06 '17 at 09:33

2 Answers2

9

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
Preston
  • 7,399
  • 8
  • 54
  • 84
  • 1
    The format should be %d-%m-%Y but the data gives this %Y-%m-%d as a result. When I format it like this dates$date <- format(as.Date(dates$date, format = "%Y-%m-%d"), "%d-%m-%Y") it doesn't work either and the class is switched to character again... – Tdebeus Oct 06 '17 at 10:10
  • @Tdebeus you're confusing the textual representation of a date with a date data type. The first is a string of characters which looks to a human like a date, the second is a date readable by a machine, you can only filter/sort on the latter – Preston Oct 06 '17 at 10:13
  • 1
    Very nice edit thanks! – Tdebeus Oct 06 '17 at 10:51
  • 2
    To sort by dates in descending order use `arrange(desc(date))`. – trevi Feb 14 '19 at 15:43
3

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
KenHBS
  • 6,756
  • 6
  • 37
  • 52
  • The format should be `%d-%m-%Y` but the data gives this `%Y-%m-%d` as a result. When I format it like this `dates$date <- format(as.Date(dates$date, format = "%Y-%m-%d"), "%d-%m-%Y")` it doesn't work either and the class is switched to character again... – Tdebeus Oct 06 '17 at 10:10
  • The format the machine interprets after `as.Date()` is the format you want to have. What you want to *view* however, is irrelevant, unless you have to prepare some kind of report where they have standard date formats. In the case of a report, you could just transform your date formatted data into strings with the `format()` you referred to in your comment – KenHBS Oct 06 '17 at 10:17