R Ordering by ascending numerical value and then by string order

user_12670898 0
Viewed 37

I have a very large dataset that I need ordered in ascending numerical order by the year, month, day columns AND THEN by a specific order of the ID column. If I wanted all of the data in ascending order or something I would do something like this, but I'm not sure how to make my ID column in the specific order.

# Create fake data:
df <- data.frame(Year = rep(2018,20),
                 Month = rep(1, 20),
                 Day = rep(seq(1:4), 5),
                 ID = rep(c("cat", "dog", "fish", "pie", "mango"), 4))

# Order data
df <- df %>%
  data.table::setDT() %>%
  data.table::setorder(c("Year", "Month", "Day"))

Pretend this is the order I need the ID column:

Desired_ID_order <- c("fish", "dog", "pie", "mango", "cat")

So the final ordered answer would look like this:

   Year Month Day    ID
1  2018     1   1  fish
2  2018     1   1   dog
3  2018     1   1   pie
4  2018     1   1 mango
5  2018     1   1   cat
6  2018     1   2  fish
7  2018     1   2   dog
8  2018     1   2   pie
9  2018     1   2 mango
10 2018     1   2   cat
11 2018     1   3  fish
12 2018     1   3   dog
13 2018     1   3   pie
14 2018     1   3 mango
15 2018     1   3   cat
16 2018     1   4  fish
17 2018     1   4   dog
18 2018     1   4   pie
19 2018     1   4 mango
20 2018     1   4   cat

Thanks!

r
2 Answers

Perhaps this helps

library(dplyr)
library(data.table)
df %>% 
   arrange(Year, Month, rowid(match(ID, Desired_ID_order)),
       match(ID, Desired_ID_order))

-output

 #   Year Month Day    ID
 #1: 2018     1   1  fish
 #2: 2018     1   1   dog
 #3: 2018     1   1   pie
 #4: 2018     1   1 mango
 #5: 2018     1   1   cat
 #6: 2018     1   2  fish
 #7: 2018     1   2   dog
 #8: 2018     1   2   pie
 #9: 2018     1   2 mango
#10: 2018     1   2   cat
#11: 2018     1   3  fish
#12: 2018     1   3   dog
#13: 2018     1   3   pie
#14: 2018     1   3 mango
#15: 2018     1   3   cat
#16: 2018     1   4  fish
#17: 2018     1   4   dog
#18: 2018     1   4   pie
#19: 2018     1   4 mango
#20: 2018     1   4   cat
user_37322710 
library(dplyr)
df %>% 
  mutate(new_ID = case_when(ID == "fish" ~ 1,
                            ID == "dog" ~ 2,
                            ID == "pie" ~3,
                            ID == "mango" ~ 4,
                            ID == "cat" ~ 5)) %>% 
  arrange(Year, Month, Day, new_ID) %>%
  select(-new_ID)

Output:

   Year Month Day    ID
1  2018     1   1  fish
2  2018     1   1   dog
3  2018     1   1   pie
4  2018     1   1 mango
5  2018     1   1   cat
6  2018     1   2  fish
7  2018     1   2   dog
8  2018     1   2   pie
9  2018     1   2 mango
10 2018     1   2   cat
11 2018     1   3  fish
12 2018     1   3   dog
13 2018     1   3   pie
14 2018     1   3 mango
15 2018     1   3   cat
16 2018     1   4  fish
17 2018     1   4   dog
18 2018     1   4   pie
19 2018     1   4 mango
20 2018     1   4   cat
user_133216470
Related