R: Rearrange the order of the row in a dataframe

Question

I want rearrange the order of my data. I have a dataset that looks like the following:

Animal<-c("bird","Bird ","Dog","Cat F","Lion","Lion","Lion","dog","Horse","cat", "Lion")

A_date<-c("02-08-2020","20-06-2018","01-01-2015","10-07-2021","20-06-2018","15-08-2019","05-08-2013","20-06-2010","15-11-2016","22-03-2022","15-05-2019")

ID<-c("T1", "T1","T1","T2","T2","T3","T3","T4","T4","T15","T15")

Mydata<-data.frame(Animal, A_date,ID)

 Animal   A_date       ID
bird     02-08-2020    T1
Bird     20-06-2018    T1
Dog      01-01-2015    T1
Cat F    10-07-2021    T2
Lion     20-06-2018    T2
Lion     15-08-2019    T3
lion     05-08-2013    T3
dog      20-06-2010    T4
Horse    15-11-2016    T4
cat      22-03-2022    T15
Lion     15-05-2019    T15

I used the code below to remove duplicate row so that only the row with latest date pr. ID will remain.

library(lubridate)
library(dplyr)

Mydata %>%
  mutate(Animal = trimws(toupper(Animal)), A_date = lubridate::dmy(A_date)) %>%
  group_by(ID, Animal) %>%
  arrange(ID, Animal, -desc(A_date)) %>%
  slice(1)

and the OUTPUT looks like the following:

  A tibble: 9 x 3
# Groups:   ID, Animal [9]
  Animal A_date     ID   
         
1 BIRD   2018-06-20 T1   
2 DOG    2015-01-01 T1   
3 CAT    2022-03-22 T15  
4 LION   2019-05-15 T15  
5 CAT F  2021-07-10 T2   
6 LION   2018-06-20 T2   
7 LION   2013-08-05 T3   
8 DOG    2010-06-20 T4   
9 HORSE  2016-11-15 T4

I want to change the order so that the order should be pr. ID from the highest ID, latest date. I want the result to look like the following:

 Animal    A_date     ID   
             
    CAT    2022-03-22 T15   
    LION   2019-05-15 T15
    HORSE  2016-11-15 T4    
    DOG    2010-06-20 T4   
    LION   2019-08-15 T3   
    CAT F  2021-07-10 T2   
    LION   2018-06-20 T2
    BIRD   2020-08-02 T1   
    DOG    2015-01-01 T1

Any suggestions on how to get that result?

user_18723649 · Answer

Starting from Mydata being your last output:

Mydata <- Mydata %>% 
  ungroup %>% 
  arrange(desc(as.numeric(gsub("T","",ID))),
          desc(A_date))

Output

# A tibble: 9 x 3
  Animal A_date     ID   
         
1 CAT    2022-03-22 T15  
2 LION   2019-05-15 T15  
3 HORSE  2016-11-15 T4   
4 DOG    2010-06-20 T4   
5 LION   2013-08-05 T3   
6 CAT F  2021-07-10 T2   
7 LION   2018-06-20 T2   
8 BIRD   2018-06-20 T1   
9 DOG    2015-01-01 T1

I first removed the grouping so I could arrange for all rows, then removed the T in the ID, turned it into a numeric to arrange it in a descending order, and arranged again on the date like you did yourself earlier.

The dates for LION with ID T3, and BIRD T1, are probably incorrect in your expected output. If that's not the case, tell me because it means I didn't understand your question.

user_2985007 · Answer

Using data.table, starting with your original Mydata:

library(data.table)
setDT(Mydata)[, c('Animal', 'A_date'):=.(trimws(toupper(Animal)), as.Date(A_date, '%d-%m-%Y'))]
setorder(Mydata, A_date)
Mydata[, .SD[.N], by=.(ID, Animal)][order(-as.integer(gsub('T', '', ID)), -A_date)]
##     ID Animal     A_date
## 1: T15    CAT 2022-03-22
## 2: T15   LION 2019-05-15
## 3:  T4  HORSE 2016-11-15
## 4:  T4    DOG 2010-06-20
## 5:  T3   LION 2019-08-15
## 6:  T2  CAT F 2021-07-10
## 7:  T2   LION 2018-06-20
## 8:  T1   BIRD 2020-08-02
## 9:  T1    DOG 2015-01-01

The first line converts Mydata to a data.table and transforms Animal and A_date. The second orders by A_date. The third line groups by ID and Animal and extracts the last row ( .SD[.N] ) of each group (latest date), then reorders in descending order by ID number and date.

R: Rearrange the order of the row in a dataframe

2 Answers