Adjust result to equal mode values

Question

The code below is generating the mode value considering the columns Method1, Method2, Method3 and Method4. However, notice that for alternative 10 and 12 it has the same mode value, that is, it has a value of 2. However, I would like my Mode column to have different values, as if it were a rank. Therefore, the alternative that had Mode=1 is the best, but I have no way of knowing the second best alternative, because it has two numbers 2 in the Mode column. Do you have suggestions on what approach I can take?

database<-structure(list(Alternatives = c(3, 4, 5, 6, 7, 8, 9, 10, 11, 12), 
    Method1 = c(1L, 10L, 7L, 8L, 9L, 6L, 5L, 3L, 4L, 2L), Method2 = c(1L, 
    8L, 6L, 7L, 10L, 9L, 4L, 2L, 3L, 5L), Method3 = c(1L, 
    10L, 7L, 8L, 9L, 6L, 4L, 2L, 3L, 5L), Method4 = c(1L, 
    9L, 6L, 7L, 10L, 8L, 5L, 3L, 4L, 2L)), class = "data.frame", row.names = c(NA, 
10L))

 ModeFunc <- function(Vec) {
    tmp <- sort(table(Vec),decreasing = TRUE)
    Nms <- names(tmp)
    if(max(tmp) > 1) {
      as.numeric(Nms[1])
    } else NA}
  
  
  output <- database |> rowwise() |> 
    mutate(Mode = ModeFunc(c_across(Method1:Method4))) %>% 
    data.frame()

> output
   Alternatives Method1 Method2 Method3 Method4 Mode
1             3       1       1       1       1    1
2             4      10       8      10       9   10
3             5       7       6       7       6    6
4             6       8       7       8       7    7
5             7       9      10       9      10    9
6             8       6       9       6       8    6
7             9       5       4       4       5    4
8            10       3       2       2       3    2
9            11       4       3       3       4    3
10           12       2       5       5       2    2

@prubin answer

output |> mutate(Weight = Mode +
1/ nrow(database) * ((Method1 + Method2 + Method3 + Method4) / 4)) |>
 mutate(rank = rank(Weight))

     Alternatives Method1 Method2 Method3 Method4 Mode Weight rank
1             3       1       1       1       1    1  1.100    1
2             4      10       8      10       9   10 10.925   10
3             5       7       6       7       6    6  6.650    6
4             6       8       7       8       7    7  7.750    8
5             7       9      10       9      10    9  9.950    9
6             8       6       9       6       8    6  6.725    7
7             9       5       4       4       5    4  4.450    5
8            10       3       2       2       3    2  2.250    2
9            11       4       3       3       4    3  3.350    4
10           12       2       5       5       2    2  2.350    3

Answer 1

There is no guarantee you can avoid ties using mode (or anything else, if it is possible for two alternatives to have identical scores and thus be indistinguishable). Rob's approach (which I endorse) works in your example, but with somewhat different data the integer program could wind up being infeasible.

A simple way to break most (but possibly not all ties) would be to create a weight column that is equal to the mode plus a constant (maybe 0.1 or 0.01, depending on the scale of the data) times the mean for that alternative. So alternative 10 would get weight 2.25 (using 0.1 for the multiplier) while alternative 12 would get weight 2.35. That still does not exclude the possibility of ties, but it would hopefully reduce the frequency of them.

Answer 2

You are looking for a transversal or system of distinct representatives. One approach is to construct a bipartite graph with a left node for each alternative $i$, a right node for each mode $j$, and an edge $(i,j)$ if alternative $i$ has $j$ as a possible mode. Now find a perfect matching in this graph. You can do so via integer linear programming by introducing binary decision variable $x_{ij}$ to indicate whether edge $(i,j)$ is selected and imposing linear constraints: \begin{align} \sum_j x_{ij} &= 1 &&\text{for all $i$} \\ \sum_i x_{ij} &= 1 &&\text{for all $j$} \end{align}

Your example above has $10+10=20$ nodes and $17$ edges, and it turns out that there are two solutions.