I would like to go through every row of the dataframe and figure out which three column names have the top three maximum values for that row.
I do have code that does it with a for loop, but it is too slow. Does anyone have a faster way to do the same thing that this for loop does?
dataframe2=dataframe
colnames=colnames(dataframe)
dfLength=length(rownames(dataframe))
for(x in 1:dfLength){
vector=as.numeric(dataframe[x,1:length(colnames)])
decreasing=order(vector, decreasing = TRUE)
dataframe2[x,"sector_1"]=colnames[(decreasing[1])+1]
dataframe2[x,"sector_2"]=colnames[(decreasing[2])+1]
dataframe2[x,"sector_3"]=colnames[(decreasing[3])+1]
}
It's much easier if you convert the numeric columns to a matrix first. If you have a frame named myframe, then you might start with:
m <- as.matrix(myframe[numeric_columns])
cn <- colnames(myframe[numeric_columns])
where numeric_columns is a vector of integers (my assumption here) or column names.
Since I don't have your data, I'll make my own:
set.seed(2)
m <- matrix(sample(100), nr=10, nc=10)
cn <- paste0("Z", 1:10)
colnames(m) <- cn
m
# Z1 Z2 Z3 Z4 Z5 Z6 Z7 Z8 Z9 Z10
# [1,] 19 50 53 1 88 72 79 9 8 29
# [2,] 70 22 31 74 63 95 47 45 21 11
# [3,] 57 67 66 56 81 33 24 2 49 69
# [4,] 17 16 12 59 61 64 98 5 38 23
# [5,] 91 35 27 34 80 94 40 52 4 36
# [6,] 90 73 82 41 92 75 87 54 25 60
# [7,] 13 83 77 55 68 86 14 32 93 28
# [8,] 78 100 76 18 84 43 39 20 96 15
# [9,] 44 37 99 42 85 26 58 65 89 6
# [10,] 51 7 10 71 62 30 3 46 48 97
By itself, this code snippet returns the top 3 columns for each row, numerically:
t(apply(m, 1, function(a) order(-a)[1:3]))
# [,1] [,2] [,3]
# [1,] 5 7 6
# [2,] 6 4 1
# [3,] 5 10 2
# [4,] 7 6 5
# [5,] 6 1 5
# [6,] 5 1 7
# [7,] 9 6 2
# [8,] 2 9 5
# [9,] 3 9 5
# [10,] 10 4 5
We can convert them to a matrix of names with:
top3 <- t(apply(m, 1, function(a) order(-a)[1:3]))
top3[] <- cn[top3]
top3
# [,1] [,2] [,3]
# [1,] "Z5" "Z7" "Z6"
# [2,] "Z6" "Z4" "Z1"
# [3,] "Z5" "Z10" "Z2"
# [4,] "Z7" "Z6" "Z5"
# [5,] "Z6" "Z1" "Z5"
# [6,] "Z5" "Z1" "Z7"
# [7,] "Z9" "Z6" "Z2"
# [8,] "Z2" "Z9" "Z5"
# [9,] "Z3" "Z9" "Z5"
# [10,] "Z10" "Z4" "Z5"
Editorial note: if you truly have comparable data in many columns, then it makes sense for many R packages to have this in a "long" format, where you have one column with names and one column with values. Extending the above data, I'll add an "id" column (since it's likely your data has a key field):
myframe <- as.data.frame(cbind(id=100L + 1:10, m))
head(myframe)
# id Z1 Z2 Z3 Z4 Z5 Z6 Z7 Z8 Z9 Z10
# 1 101 19 50 53 1 88 72 79 9 8 29
# 2 102 70 22 31 74 63 95 47 45 21 11
# 3 103 57 67 66 56 81 33 24 2 49 69
# 4 104 17 16 12 59 61 64 98 5 38 23
# 5 105 91 35 27 34 80 94 40 52 4 36
# 6 106 90 73 82 41 92 75 87 54 25 60
Converting to "long" format (using tidyverse packages here):
head(tidyr::gather(myframe, Znum, Zval, -id))
# id Znum Zval
# 1 101 Z1 19
# 2 102 Z1 70
# 3 103 Z1 57
# 4 104 Z1 17
# 5 105 Z1 91
# 6 106 Z1 90
tail(tidyr::gather(myframe, Znum, Zval, -id))
# id Znum Zval
# 95 105 Z10 36
# 96 106 Z10 60
# 97 107 Z10 28
# 98 108 Z10 15
# 99 109 Z10 6
# 100 110 Z10 97
This suggests a clean dplyr pipe for getting the top three per id:
library(dplyr)
library(tidyr)
myframe %>%
tidyr::gather(Znum, Zval, -id) %>%
arrange(-Zval) %>%
group_by(id) %>%
slice(1:3) %>%
ungroup()
# # A tibble: 30 x 3
# id Znum Zval
# <int> <chr> <int>
# 1 101 Z5 88
# 2 101 Z7 79
# 3 101 Z6 72
# 4 102 Z6 95
# 5 102 Z4 74
# 6 102 Z1 70
# 7 103 Z5 81
# 8 103 Z10 69
# 9 103 Z2 67
# 10 104 Z7 98
# # ... with 20 more rows
It's a different way of looking at the problem, certainly, but depending on your other work, it might provide some simplification and payoff elsewhere.