R: Rearrange the order of the row in a dataframe-CodePudding

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   
  <chr>  <date>     <chr>
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   
      <chr>  <date>     <chr>
    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?

CodePudding user response：

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   
  <chr>  <date>     <chr>
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.