1

I am currently working on some datasets in R that contain a combined date and time column with data formatted as follows:

"21/02/23, 13:40:39"

So we have "DD/MM/YY, HH:MM:SS" in each row for the column containing the values, df$DateTime

I would like to be able to sort my rows chronologically by the date and time provided, but I'm having quite a bit of trouble identifying a package or function to get this working.

Any suggestions? Thank you everyone

I have seen this question: Ordering date/time in descending order in R

But have had trouble adapting the solutions given to the format of my data.

Shaun Lehmann
  • 25
  • 4

1 Answers1

1

Using lubridate (part of the tidyverse collection):

library(tidyverse)

str <- "21/02/23, 13:40:39"
# turn it into a datetime
dmy_hms(str)
# [1] "2023-02-21 13:40:39 UTC"


# if instead it is in a dataframe
df <- tibble(
    dates = c("21/02/23, 13:40:39", "21/02/24, 08:15:00", "21/02/25, 19:30:12", "21/02/26, 22:55:03")
)
# right now, it's unsorted
  dates             
  <chr>             
1 21/02/23, 13:40:39
2 21/02/24, 08:15:00
3 21/02/25, 19:30:12
4 21/02/26, 22:55:03

df %>%
    # turn it into a datetime
    mutate(dates = dmy_hms(dates)) %>%
    # sort it
    arrange(dates)

  dates              
  <dttm>             
1 2023-02-21 13:40:39
2 2024-02-21 08:15:00
3 2025-02-21 19:30:12
4 2026-02-21 22:55:03

To change the timezone, use the tz option:

dmy_hms(str, tz = "America/New_York")
# [1] "2023-02-21 13:40:39 EST"
Mark
  • 7,785
  • 2
  • 14
  • 34
  • Thank you! That's fantastic. Is there a way I can drop the UTC? And does lubridate have a sort function? Or will 'order' understand this? – Shaun Lehmann Jul 02 '23 at 03:39
  • I've figured out the timezone by using tz = "Australia/Sydney" but I'm encountering issues trying to sort by the datetime. I've tried: df %>% arrange(dmy_hms(df$DateTime)) But this is throwing an error: Caused by warning: ! All formats failed to parse. No formats found. – Shaun Lehmann Jul 02 '23 at 03:57
  • I suspect that the error is because you have already applied `dmy_hms` to the `DateTime` column in `df` and saved the result back to `df`. In that case just `df %>% arrange(DateTime)` should work. See what `str(df)` tells you about the column types. – neilfws Jul 02 '23 at 04:29
  • see updated code! @ShaunLehmann – Mark Jul 02 '23 at 04:30
  • the issue with `arrange(dmy_hms(df$DateTime))` is you're trying to do mutate-y stuff within an `arrange` call. Change the value first (using `mutate(DateTime = dmy_hms(DateTime, tz = "Australia/Sydney")`, then arrange using `arrange(DateTime)` – Mark Jul 02 '23 at 04:32
  • 1
    It worked! Thank you! Total hero! – Shaun Lehmann Jul 02 '23 at 06:02