How can I sort POSIXct datetime format in R?-CodePudding

I have a data frame with 4 columns, the first of which is called Date_et_heure. In that column, I have a POSIXct POSIXt class datetimes with format "%Y-%m-%d %H:%M:%S". I would like to arrange my dataframe so that the rows are always chronological.

I tried to use the arrange() function but it cannot accept POSIXct POSIXt format data; I always get the error:

Error in UseMethod("arrange") : no applicable method for 'arrange' applied to an object of class "c('POSIXct', 'POSIXt')

I tried to use the order() function, for which I needed to transform POSIXct with the as.Date() function. But as.Date() ignores the time ("%H:%M:%S") format of the column.

Does anyone know if there is a method to order POSIXct class data? Hopefully with a conversion that is reliable.

Thank you!

CodePudding user response：

POSIXct in R is both useful and powerful. Internally, it is 'just' a double and you can use all the usual operation on them dirrectly.

Here is a minimal base R demo:

> set.seed(123)    # reproducible
> v <- as.POSIXct(Sys.time()   rnorm(5)*3600)
> v                # random draw around 'now', not sorted
[1] "2021-11-09 06:05:15.009926 CST" "2021-11-09 06:25:04.083292 CST" 
[3] "2021-11-09 08:12:24.072185 CST" "2021-11-09 06:43:06.552463 CST" 
[5] "2021-11-09 06:46:38.158100 CST"
> diff(v)          # not sorted -> pos. and neg. differences
Time differences in mins
[1]  19.81789 107.33315 -89.29200   3.52676
>

So here use order() to rearrange:

> w <- v[order(v)]
> w
[1] "2021-11-09 06:05:15.009926 CST" "2021-11-09 06:25:04.083292 CST" 
[3] "2021-11-09 06:43:06.552463 CST" "2021-11-09 06:46:38.158100 CST"
[5] "2021-11-09 08:12:24.072185 CST"
> diff(w)
Time differences in mins
[1] 19.81789 18.04115  3.52676 85.76523
>

That arranged the timestamps as expected.

CodePudding user response：

Post your code, the error message does not indicate the issue is your object with the class mentioned, but that you provided a non applicable method for the object that happen to have that class in this case.

The issue lays not in the dplyr functionality as seen also in the examples in other replies.

Here an example of both POSIXlt and POSIXct (which both have the class "POSIXct" "POSIXt"). You can sort on both, and both ways.

df <- data.frame(
  Date_et_heurePOSIXct = sample(seq(as.POSIXct('2021-08-01'), as.POSIXct('2021-11-09', tz = "UTC"), by = "1 sec"), 5),
  Date_et_heurePOSIXlt = sample(seq(as.POSIXlt('2021-08-01'), as.POSIXlt('2021-11-09', tz = "UTC"), by = "1 sec"), 5)
)

df %>% arrange(Date_et_heurePOSIXct)
df %>% arrange(desc(Date_et_heurePOSIXct))
df %>% arrange(Date_et_heurePOSIXlt)
df %>% arrange(desc(Date_et_heurePOSIXlt))


class(df$Date_et_heurePOSIXct)
class(df$Date_et_heurePOSIXlt)

CodePudding user response：

Both order and dplyr::arrange can sort "POSIXct" objects.

i <- order(df1$Date_et_heure)
df1[i,]
#         Date_et_heure x
#1  2021-11-09 12:41:57 i
#2  2021-11-09 12:41:58 d
#3  2021-11-09 12:41:59 j
#4  2021-11-09 12:42:00 e
#5  2021-11-09 12:42:01 h
#6  2021-11-09 12:42:02 b
#7  2021-11-09 12:42:03 a
#8  2021-11-09 12:42:04 f
#9  2021-11-09 12:42:05 c
#10 2021-11-09 12:42:06 g

df1 |> dplyr::arrange(Date_et_heure)
#         Date_et_heure x
#1  2021-11-09 12:41:57 i
#2  2021-11-09 12:41:58 d
#3  2021-11-09 12:41:59 j
#4  2021-11-09 12:42:00 e
#5  2021-11-09 12:42:01 h
#6  2021-11-09 12:42:02 b
#7  2021-11-09 12:42:03 a
#8  2021-11-09 12:42:04 f
#9  2021-11-09 12:42:05 c
#10 2021-11-09 12:42:06 g

Test data

set.seed(2021)
n <- 10
Date_et_heure <- Sys.time()   sample(n)
df1 <- data.frame(Date_et_heure, x = letters[1:n])