CODEWITHSUNDEEP
rdplyr
Vikram
Vikram

Reputation: 51

ordering rows in an R data frame based on value in one column

My data set is as follows

dput(data2)
structure(list(School = structure(c(1L, 1L, 1L, 2L, 2L, 2L, 3L, 
3L, 3L), .Label = c("School1", "School2", "School3"), class = "factor"), 
    Year = c(2015L, 2014L, 2013L, 2015L, 2014L, 2013L, 2015L, 
    2014L, 2013L), Rate = c(70L, 50L, 30L, 80L, 90L, 11L, 60L, 
    50L, 40L)), .Names = c("School", "Year", "Rate"), class = "data.frame", row.names = c(NA, 
-9L))


   School Year Rate
1 School1 2015   70
2 School1 2014   50
3 School1 2013   30
4 School2 2015   80
5 School2 2014   90
6 School2 2013   11
7 School3 2015   60
8 School3 2014   50
9 School3 2013   40

What I am trying do is produce an output where the data is grouped by column School and the order for the schools is defined on the descending order of rate in Year 2015.

So the output should be like

   School Year Rate
1 School2 2015   80
2 School2 2014   90
3 School2 2013   11
4 School1 2015   70
5 School1 2014   50
6 School1 2013   30
7 School3 2015   60
8 School3 2014   50
9 School3 2013   40

Using the data in my example the order will be as follows, based on the descending value of rate. School2 -> School1 -> School3 80 -> 70 -> 60

I have tried using dplyr package to get the desired output but have not been achieve the result.

Upvotes: 2

Views: 1817

Answers (2)

www
www

Reputation: 39154

We can find the rate in 2015 and then arrange based on the columns.

library(dplyr)

dat2 <- dat %>%
  group_by(School) %>%
  mutate(Year2015 = Rate[Year == 2015]) %>%
  arrange(desc(Year2015), desc(Year)) %>%
  ungroup(School) %>%
  select(-Year2015)
dat2
# # A tibble: 9 x 3
#   School   Year  Rate
#   <fct>   <int> <int>
# 1 School2  2015    80
# 2 School2  2014    90
# 3 School2  2013    11
# 4 School1  2015    70
# 5 School1  2014    50
# 6 School1  2013    30
# 7 School3  2015    60
# 8 School3  2014    50
# 9 School3  2013    40

Upvotes: 2

MKR
MKR

Reputation: 20095

An option to calculate maximum rate (MaxRate) for each school first. Then arrange data in descending order on MaxRate and Year. 

library(dplyr)

data2 %>% group_by(School) %>%
  mutate(MaxRate = max(Rate)) %>%
  arrange(desc(MaxRate), desc(Year)) %>%
  ungroup() %>%
  select(-MaxRate) %>% as.data.frame()

# School Year Rate
# 1 School2 2015   80
# 2 School2 2014   90
# 3 School2 2013   11
# 4 School1 2015   70
# 5 School1 2014   50
# 6 School1 2013   30
# 7 School3 2015   60
# 8 School3 2014   50
# 9 School3 2013   40

Upvotes: 1

Related Questions