R: Select the Top N elements of each row.
I have a dataframe in R that records ranked customer preferences for different brands. An example data frame looks something like the table below. The actual table is much larger in both dimensions (approximately 80,000 x 30).
In the table I have:
+------+---------+---------+---------+---------+
| User | Brand_A | Brand_B | Brand_C | Brand_D |
+------+---------+---------+---------+---------+
| A | 1 | NA | 3 | 2 |
| B | NA | NA | NA | 1 |
| C | 3 | 2 | 4 | 1 |
| D | NA | 1 | 2 | NA |
+------+---------+---------+---------+---------+
where 1 indicates that the customer rates the brand as "best" and NA indicates that the customer has not rated the brand. I would like to create a table that selects Top 3 (or Top N) brands for each user and outputs a table that might look like this:
+------+---------+---------+---------+
| User | Ranked1 | Ranked2 | Ranked3 |
+------+---------+---------+---------+
| A | Brand_A | Brand_D | Brand_C |
| B | Brand_D | NA | NA |
| C | Brand_D | Brand_B | Brand_A |
| D | Brand_B | Brand_C | NA |
+------+---------+---------+---------+
Let's assume that each customer's rating is exhaustive, i.e. if I only used one brand, that brand is automatically rated 1.
I tried using for for loops to get the required output, but with no success. I think there is something rather simple that I am missing.
source to share
You can do this using apply
:
df2=data.frame(User=df$User,t(apply(df,1,function(x) names(x)[-1][order(x[-1],na.last=NA)][1:3])))
colnames(df2)=c("User",paste0("Ranked",c(1:3)))
This returns:
User Ranked1 Ranked2 Ranked3
1 A Brand_A Brand_D Brand_C
2 B Brand_D <NA> <NA>
3 C Brand_D Brand_B Brand_A
4 D Brand_B Brand_C <NA>
source to share
One option is to melt your data and then remake it. This option using data.table
will look like this:
library(data.table)
dcast(setDT(melt(data, id.vars = "user"))[, rank := paste0("Ranked",value)][!is.na(value),], user ~ rank, value.var = "variable")
# user Ranked1 Ranked2 Ranked3 Ranked4
#1 A Brand_A Brand_D Brand_C <NA>
#2 B Brand_D <NA> <NA> <NA>
#3 C Brand_D Brand_B Brand_A Brand_C
#4 D Brand_B Brand_C <NA> <NA>
source to share
with tidyverse
...
df <- read.table(header = T, text = '
User Brand_A Brand_B Brand_C Brand_D
A 1 NA 3 2
B NA NA NA 1
C 3 2 4 1
D NA 1 2 NA
')
library(tidyverse)
df %>%
gather(brand, rank, -User, na.rm = T) %>%
filter(rank < 4) %>%
spread(rank, brand, sep = '')
generates ...
User rank1 rank2 rank3
1 A Brand_A Brand_D Brand_C
2 B Brand_D <NA> <NA>
3 C Brand_D Brand_B Brand_A
4 D Brand_B Brand_C <NA>
source to share