How to order a column within a group in R?

Question

I am having a data frame

df <- data.frame(
  "Class" = c('Class 8','Class 9','Class 10','Class 8','Class 9','Class 10','Class 8','Class 9','Class 10'),
  "Status" = c('Good','Good','Good','Better','Better','Better','Best','Best','Best'),
  "Percentage" = c(4,4,6,14,13,15,83,81,78),
  stringsAsFactors = FALSE
)

I am having a vector describing the order of Status within each group.

ratingOrder <<- c('Good','Better','Best')

I am trying to arrange the Status for each Class as given in the vector.

This is the expected output

     Class Status Percentage
1  Class 8   Good          4
2  Class 8 Better          4
3  Class 8   Best          6
4  Class 9   Good         14
5  Class 9 Better         13
6  Class 9   Best         15
7  Class 10  Good         83
8  Class 10 Better         81
9  Class 10  Best         78

Can anyone give a suitable solution to achieve this?

Thanks in advance!!

jlesuffleur · Answer 1 · 2020-06-19T15:09:16.657

You can use a factor column to take the order into account. Another thing you need to solve is the Class column that needs to be numeric in order to be sorted numerically.

Here is a solution using dplyr:

library(dplyr)

df %>% 
  mutate(Status = factor(Status, levels = ratingOrder),
         Class = as.numeric(gsub("Class ", "", Class))) %>%
  arrange(Class, Status)

Output:

  Class Status Percentage
1     8   Good          4
2     8 Better         14
3     8   Best         83
4     9   Good          4
5     9 Better         13
6     9   Best         81
7    10   Good          6
8    10 Better         15
9    10   Best         78

Ric S · Answer 2 · 2020-06-19T15:08:45.823

A possible solution using dplyr

df %>% 
  mutate(Status = factor(Status, levels = ratingOrder),
         Class = as.numeric(gsub("\\D", "", Class))) %>%
  arrange(Class, Status)

The match function is used to create on-the-fly a numeric vector resembling the order of ratingOrder (see this post for reference).

Output

#      Class Status Percentage
# 1  Class 8   Good          4
# 2  Class 8 Better         14
# 3  Class 8   Best         83
# 4  Class 9   Good          4
# 5  Class 9 Better         13
# 6  Class 9   Best         81
# 7 Class 10   Good          6
# 8 Class 10 Better         15
# 9 Class 10   Best         78

score 1 · Answer 3 · answered Jun 19 '20 at 15:01

A base R option:

df$Status <- factor(df$Status, levels = ratingOrder) 
df$Class <- factor(df$Class, levels = paste("Class", 8:10))

df[order(df$Class, df$Status), ]

     Class Status Percentage
1  Class 8   Good          4
4  Class 8 Better         14
7  Class 8   Best         83
2  Class 9   Good          4
5  Class 9 Better         13
8  Class 9   Best         81
3 Class 10   Good          6
6 Class 10 Better         15
9 Class 10   Best         78

score 0 · Answer 4 · answered Jun 19 '20 at 15:03

There are some dplyr solutions but I want to propose:

df %>%
  arrange(as.integer(str_extract(Class, "\\d+")), match(Status, ratingOrder))

which also yields

     Class Status Percentage
1  Class 8   Good          4
2  Class 8 Better         14
3  Class 8   Best         83
4  Class 9   Good          4
5  Class 9 Better         13
6  Class 9   Best         81
7 Class 10   Good          6
8 Class 10 Better         15
9 Class 10   Best         78

How to order a column within a group in R?

4 Answers4