Sort a data frame column based on another sorted column value in R

Question

I have a data frame that is sorted based on one column (numeric column) to assign the rank. If this column value is zero then arrange the data frame based on another character column for those rows which have zero as a value in a numeric column.

But to give rank I have to consider var2 that is the reason I sorted based on var2, if there is any identical values in var2 for those rows I have to consider var3 to give rank. Please see the data frame 2 and 3 rows, var2 values are identical in that case I have to consider var3 to give rank. In case var2 is zero I have to sort the var1 column (character column) in alphabetical order and give rank. If var2 is NA no rank. Please refer the data frame given below.

Below, the data frame is sorted based on var2 column descending order, but var2 contains zero also if var2 is zero I have to sort the data frame based on var1 for the rows which are having zero in var2. I need sort by var1 for those rows which are having var2 as zero and followed by NA in alphabetical order of var1.

    example:
    #      var1    var2    var3    rank
    # 1     c      556      45       1
    # 2     a      345      35       3
    # 3     f      345      64       2
    # 4     b      134      87       4
    # 5     z       0       34       5
    # 6     d       0       32       6
    # 7     c       0       12       7
    # 8     a       0       23       8
    # 9     e      NA      
    # 10    b      NA       

below is my code 
df <- data.frame(var1=c("c","a","f","b","z","d", "c","a", "e", "b", "ad", "gf", "kg", "ts", "mp"), var2=c(134, NA,345, 200, 556,NA, 345, 200, 150, 0, 25,10,0,150,0), var3=c(65,'',45,34,68,'',73,12,35,23,34,56,56,78,123))

# To break the tie between var3 and var2 
orderdf <- df[order(df$var2, df$var1, decreasing = TRUE), ] 

#assigning rank 
rankdf <- orderdf %>% mutate(rank = ifelse(is.na(var2),'', seq(1:nrow(orderdf))))

The expected output is sort the var1 in alphabetical order if var2 value is zero (for those rows with var2 value is zero).

    expected output:
    #      var1    var2    var3    rank
    # 1     c      556      45       1
    # 2     a      345      35       3
    # 3     f      345      64       2
    # 4     b      134      87       4
    # 5     a       0       34       5
    # 6     c       0       32       6
    # 7     d       0       12       7
    # 8     z       0       23       8
    # 9     b      NA      
    # 10    e      NA       

Answer 1

You can simply arrange by two columns:

library(dplyr)

df %>%
   arrange(desc(var2),var1)

Edit:

To clarify why this works, in your example simply arranging the df by var2 in descending order will already put all 0 and NA values at the bottom (since NA is "worth" less than 0). However since these rows all have the same value their order will be "random" because we do not specify a tie method. By supplying a second column for arrange, we sort all ties by a second criteria, var1.

This will actually also re-arrange rows with a duplicate var2 value as well but that should be a bonus.

Answer 2

In addition to @Fnguyen great answer.

If you are familiar with SQL you can use the following sqldf library and achieve the same results

library(sqldf)
df <- data.frame(var1=c("c","a","f","b","z","d", "c","a", "e", "b", "ad", "gf", "kg", "ts", "mp"), var2=c(134, NA,345, 200, 556,NA, 345, 200, 150, 0, 25,10,0,150,0), var3=c(65,'',45,34,68,'',73,12,35,23,34,56,56,78,123))
sqldf("SELECT * FROM df ORDER BY var2 desc, var1 desc")

OUTPUT:
   var1 var2 var3
1     z  556   68
2     f  345   45
3     c  345   73
4     b  200   34
5     a  200   12
6    ts  150   78
7     e  150   35
8     c  134   65
9    ad   25   34
10   gf   10   56
11   mp    0  123
12   kg    0   56
13    b    0   23
14    d   NA     
15    a   NA