How to rank ID's using fail percentage

Question

I have a dataframe like this

library(plyr)
library(dplyr)

ID <- c("ID001","ID002","ID003","ID004","ID005",
        "ID006","ID007","ID008","ID009","ID010")
Fail <- c(3,7,2,3,9,7,3,2,3,9)
Pass <- c(0,0,4,26,1,1,3,0,9,9)
df <- data.frame(ID,Fail,Pass)

I add another column to calculate fail percentage

df$Fail_Percentage <- (df$Fail/(df$Fail+df$Pass))*100

Now, I order this data frame and create a variable "Rank"

library(data.table)
df <- df%>%
  arrange(-Fail_Percentage) %>%
  mutate(Rank = rleid(Fail_Percentage))
df

I get this output

      ID Fail Pass Fail_Percentage Rank
   ID001    3    0       100.00000    1
   ID002    7    0       100.00000    1
   ID008    2    0       100.00000    1
   ID005    9    1        90.00000    2
   ID006    7    1        87.50000    3
   ID007    3    3        50.00000    4
   ID010    9    9        50.00000    4
   ID003    2    4        33.33333    5
   ID009    3    9        25.00000    6
   ID004    3   26        10.34483    7

The problem here is that I create duplicate ranks this way. I want to rank by giving preference to the "Fails".

For example: ID001,ID002,ID008 all have rank 1 but I want to rank by giving preference to highest fails by pass percentage. So ID002 will have rank 1, ID001 will have rank 2 and ID008 will have rank 3. I want to do it this way and similarly rank other entries too.

My desired output would be

          ID Fail Pass Fail_Percentage Rank
       ID002    7    0       100.00000    1
       ID001    3    0       100.00000    2
       ID008    2    0       100.00000    3
       ID005    9    1        90.00000    4
       ID006    7    1        87.50000    5
       ID010    9    9        50.00000    6
       ID007    3    3        50.00000    7
       ID003    2    4        33.33333    8
       ID009    3    9        25.00000    9
       ID004    3   26        10.34483    10

How can we do this better? Could someone help me point in the right direction?

Those three packages have a lot of overlapping features and function names. Probably best to stick with one. Plyr is superseded by dplyr and if you only want data.table for rleid... http://stackoverflow.com/questions/33507868/is-there-a-dplyr-equivalent-to-data-tablerleid — Frank, Apr 20 '17 at 21:35

score 3 · Accepted Answer · answered Apr 20 '17 at 21:51

3

ID <- c("ID001","ID002","ID003","ID004","ID005",
        "ID006","ID007","ID008","ID009","ID010")
Fail <- c(3,7,2,3,9,7,3,2,3,9)
Pass <- c(0,0,4,26,1,1,3,0,9,9)
df <- data.frame(ID,Fail,Pass)
df$Fail_Percentage <- (df$Fail/(df$Fail+df$Pass))*100

Using just data.table

df <- setDT(df)[order(-Fail_Percentage, -Fail)][, Rank := 1:.N]

answered Apr 20 '17 at 21:51

ahly

1,121
7
9

Or `setDT(df)[, Rank2 := frank(-.SD), .SDcols = c("Fail_Percentage", "Fail")]` – David Arenburg Apr 20 '17 at 23:06

score 0 · Answer 2 · answered Apr 20 '17 at 21:52

You can already get to the intended order by using a 2nd argument to arrange:

library(dplyr)

df = structure(list(ID = structure(1:10, .Label = c("ID001", "ID002", 
"ID003", "ID004", "ID005", "ID006", "ID007", "ID008", "ID009", 
"ID010"), class = "factor"), Fail = c(3, 7, 2, 3, 9, 7, 3, 2, 
3, 9), Pass = c(0, 0, 4, 26, 1, 1, 3, 0, 9, 9)), .Names = c("ID", 
"Fail", "Pass"), row.names = c(NA, -10L), class = "data.frame") 

df = df %>%
    mutate(Fail_Percentage = Fail / (Fail + Pass) * 100) %>%
    arrange(-Fail_Percentage, -Fail) %>%
    mutate(Rank = order(-Fail_Percentage))

> df

ID Fail Pass Fail_Percentage Rank
1  ID002    7    0       100.00000    1
2  ID001    3    0       100.00000    2
3  ID008    2    0       100.00000    3
4  ID005    9    1        90.00000    4
5  ID006    7    1        87.50000    5
6  ID010    9    9        50.00000    6
7  ID007    3    3        50.00000    7
8  ID003    2    4        33.33333    8
9  ID009    3    9        25.00000    9
10 ID004    3   26        10.34483   10

How to rank ID's using fail percentage

2 Answers2