CODEWITHSUNDEEP
rdate
user2327621
user2327621

Reputation: 997

Ordering date/time in descending order in R

I have a data frame DF with one of the columns being date/Time and I would like to order the data frame in descending order of this column. 

DF <- data.frame(ID=c('ID3', 'ID2','ID1'), end=c('4/1/10 12:00', '6/1/11 14:20', '1/1/09 11:10'), age=c(40,30,20));

I first converted the end column into et using et = as.POSIXct(DF$end,format='%m/%d/%Y %H:%M'), and used the following, but got the error that unary operator '-' is not accepted for the argument :

out <- DF[order(-DF$et),];

I also tried used the descending flag but again got an error about arguments not being of same length.

out <- DF[order(DF$et, descending=TRUE),];

However, the ascending order seems to work: out <- DF[order(DF$et),].

How can I order in descending order (most recent time first)? Thank you.

Upvotes: 23

Views: 77348

Answers (3)

Ronak Shah
Ronak Shah

Reputation: 389335

Using dplyr and lubridate -

library(dplyr)
library(lubridate)

DF %>% mutate(end = dmy_hm(end)) %>% arrange(desc(end))

#   ID                 end age
#1 ID2 2011-01-06 14:20:00  30
#2 ID3 2010-01-04 12:00:00  40
#3 ID1 2009-01-01 11:10:00  20

If you don't want to the change the format of end column.

DF %>% arrange(desc(dmy_hm(end)))

#   ID          end age
#1 ID2 6/1/11 14:20  30
#2 ID3 4/1/10 12:00  40
#3 ID1 1/1/09 11:10  20

Upvotes: 2

pommedeterresautee
pommedeterresautee

Reputation: 1903

There is an easy and general solution for your issue with few code.

As you have noticed, the minus sign doesn't work with Dates because negative dates don't yet exist!

However you can have the same effect with a general purpose function: rev(). Therefore, you mix rev and order like:

#init data
DF <- data.frame(ID=c('ID3', 'ID2','ID1'), end=c('4/1/10 12:00', '6/1/11 14:20', '1/1/09 11:10')
#change order
out <- DF[rev(order(as.Date(DF$end))),]

When you use the minus sign with numbers you are classing negative numbers in one pass. I think that when you use rev() function, you are doing two passes, one to sort in ascending order and one for reversing the order. But on 3 observations, it's hard to see.

Hope it helped.

Upvotes: 18

Simon O&#39;Hanlon
Simon O&#39;Hanlon

Reputation: 60000

I think this will work:

## Slightly bigger dataset with two times on same day:
DF <- data.frame(ID=c('ID3', 'ID2','ID1','ID4'), end=c('4/1/10 12:00', '6/1/11 14:20', '1/1/09 11:10' , '1/1/09 13:11'), age=c(40,30,20,20));

## Note to self - ALWAYS include a timezone.
DF$DTime <- as.POSIXct( DF$end , format = "%d/%m/%y %H:%M" , tz = "GMT")
DF[ order(DF$DTime , decreasing = TRUE ),]
#   ID          end age               DTime
#2 ID2 6/1/11 14:20  30 2011-01-06 14:20:00
#1 ID3 4/1/10 12:00  40 2010-01-04 12:00:00
#4 ID4 1/1/09 13:11  20 2009-01-01 13:11:00
#3 ID1 1/1/09 11:10  20 2009-01-01 11:10:00

Upvotes: 11

Related Questions