Ordering rows when there is a tie between several of them-CodePudding

I have a data.frame (corresponding to a leaderboard) like this one:

structure(list(PJ = c(4, 4, 4, 4, 4, 4), V = c(4, 2, 2, 2, 1, 
1), E = c(0, 0, 0, 0, 0, 0), D = c(0, 2, 2, 2, 3, 3), GF = c(182, 
91, 92, 185, 126, 119), GC = c(84, 143, 144, 115, 141, 168), 
    Dif = c(98, -52, -52, 70, -15, -49), Pts = c(12, 6, 6, 6, 
    3, 3)), class = "data.frame", row.names = c("Player1", "Player2", 
"Player3", "Player4", "Player5", "Player6"))

I would like to order the rows according to the number of points Pts. This can be done by df[order(df$Pts, decreasing=T),]. The issue appears when there is a tie between several players, then, what I want to do is to order the rows according to Dif.

How can this be done?

CodePudding user response：

The order function which you are already using can take multiple arguments, each used sequentially to break ties in the previous one; see ?order

So you simply have to add Dif to you existing call:

df[order(df$Pts, df$Dif, decreasing=T),]

You can add further terms to break any remaining ties, e.g. Player2 and Player3 who have identical Pts and Dif.

If you want to specify which direction each argument should be ordered by (increasing or decreasing), you can either specify the decreasing argument as a vector, as in @r.user.05apr's comment, or my preferred lazy solution of adding - to any term that should be ordered in a decreasing direction

df[order(-df$Pts, df$Dif),]

(this will order by Pts decreasing and Dif increasing; it won't work if e.g. one of the ordering columns is character)

CodePudding user response：

You can use sqldf or dplyr library

library (sqldf)

sqldf('select *
       from "df"
       order by "Pts" desc, "Dif" desc ')

Output

PJ V E D  GF  GC Dif Pts
1  4 4 0 0 182  84  98  12
2  4 2 0 2 185 115  70   6
3  4 2 0 2  91 143 -52   6
4  4 2 0 2  92 144 -52   6
5  4 1 0 3 126 141 -15   3
6  4 1 0 3 119 168 -49   3