reclassify columns according to score R-CodePudding

I am working with a df organized as follow

a = c("A","B","C","D")
b = c(55,3,5,0)
c = c(6, 22, 4.5, 9)
d = c(0.5, 44, 33, 87)
df = data.frame(t(rbind(a,b,c,d)))

It can represent scores of players A,B,C,D in successive runs (b,c,d) of a game.

I want to reclassify each column of df from the best score to the worst. Ideally this should be the expected df:

a b c d

A 1 3 4

B 3 1 2

C 2 4 3

D 4 2 1

I tried multiple ways to do it with command "order" or "rank" in a loop reading each column, but id does not give me the correct output (I mean...the one I want).

It looks a simple logic work but I'm stuck!! I very appreciate any help!

CodePudding user response：

You haven't shown what code you used but using an sapply loop across the three numeric columns with order produces:

sapply(df1[-1], order)
     b c d
[1,] 4 2 1
[2,] 2 3 3
[3,] 3 1 2
[4,] 1 4 4

sapply(df1[-1], order, decreasing=TRUE)
     b c d
[1,] 1 4 4
[2,] 3 1 2
[3,] 2 3 3
[4,] 4 2 1

(I did change the name of the data object because df is a legitimate R function name, as is dt for that matter.)

CodePudding user response：

Using the tidyverse, assuming a higer score is better for "b" and "d" and a lower score is better for "c":

    library(tidyverse)
    df = tribble(
      ~a, ~b, ~c, ~d, 
      "A", 55, 6, 0.5,
      "B", 3, 22, 44,
      "C", 5, 4.5, 33,
      "D", 0, 9, 87
    )
    
    df %>%
      mutate(
        across(c(b,d), ~ order(., decreasing = TRUE)),
        c = order(c)
        )

Output:

a         b     c     d
<chr> <int> <int> <int>
A         1     3     4
B         3     1     2
C         2     4     3
D         4     2     1

CodePudding user response：

Lots of ways to do this. Here is a data.table approach.

a = c("A","B","C","D")
b = c(55,3,5,0)
c = c(6, 22, 4.5, 9)
d = c(0.5, 44, 33, 87)
# df = data.frame(t(rbind(a,b,c,d)))   # NO, NO, NO!
df = data.frame(a,b,c,d)               # create df this way

library(data.table)
rankCols <- c('b', 'c', 'd')
setDT(df)[, c(rankCols):=lapply(.SD, \(x) frank(-x)), .SDcols=rankCols]
df

Sidebar: rbind(a,b,c,d) will create a matrix with rows formed from your vectors. But in R all elements of a matrix must have the same data type. R uses a least common denominator approach so the numerics are coerced to character.