CODEWITHSUNDEEP
rdplyrtidyversetidyr
Dexter1611
Dexter1611

Reputation: 572

For each row the sort top 5 values in desc order and get there column name

I have a data frame and for each row , I want to extract the top 5 columns with max value in each row

DF <- data.frame(a1=c(10,45,100,5000,23,45,2,23,56),
                 a2=c(60,20,5,2,1,2,3,4,5),
                 a3=c(90,2,0,0,0,4,-5,-3,-2),
                 a4=c(900,122,30,40,50,64,-75,-83,-92),
                 a5=c(190,32,30,50,80,49,-50,-7,-2),
                 a6=c(30,27,80,54,84,49,-50,-37,-23),
                 a7=c(0,32,39,50,80,9,-5,-7,-23))

I tried using the below approach

k <- 5

mx <- t(apply(DF,1,function(x)names(DF)[sort(head(order(x,decreasing=TRUE),k))]))
mx<-as.data.frame(mx)

I am able to get results but the order is not correct for all rows

for example

Input is

enter image description here

**Expected O/P for Row 1 should be **

a4 a5 a3 a2 a6

or

a4 a5 a3 a6 a2

My O/P is

enter image description here

I would appreciate dplyr based solution if possible

Upvotes: 1

Views: 121

Answers (1)

Duck
Duck

Reputation: 39595

Try this approach, the issue was you had an additional sort() that was reordering the values again:

#Code
mx <- t(apply(DF,1,function(x)names(DF)[head(order(x,decreasing=TRUE),k)]))
mx<-as.data.frame(mx)

Output:

  V1 V2 V3 V4 V5
1 a4 a5 a3 a2 a6
2 a4 a1 a5 a7 a6
3 a1 a6 a7 a4 a5
4 a1 a6 a5 a7 a4
5 a6 a5 a7 a4 a1
6 a4 a5 a6 a1 a7
7 a2 a1 a3 a7 a5
8 a1 a2 a3 a5 a7
9 a1 a2 a3 a5 a6

A tidyverse approach would imply reshaping data like this:

library(tidyverse)
#Code
DF %>%
  #Create an id by row
  mutate(id=1:n()) %>%
  #Reshape
  pivot_longer(cols = -id) %>%
  #Arrange
  arrange(id,-value) %>%
  #Filter top 5
  group_by(id) %>%
  mutate(Var=1:n()) %>%
  filter(Var<=5) %>%
  select(-c(value,Var)) %>%
  #Format
  mutate(Var=paste0('V',1:n())) %>%
  pivot_wider(names_from = Var,values_from=name) %>%
  ungroup() %>%
  select(-id)

Output:

# A tibble: 9 x 5
  V1    V2    V3    V4    V5   
  <chr> <chr> <chr> <chr> <chr>
1 a4    a5    a3    a2    a6   
2 a4    a1    a5    a7    a6   
3 a1    a6    a7    a4    a5   
4 a1    a6    a5    a7    a4   
5 a6    a5    a7    a4    a1   
6 a4    a5    a6    a1    a7   
7 a2    a1    a3    a7    a5   
8 a1    a2    a3    a5    a7   
9 a1    a2    a3    a5    a6

Upvotes: 2

Related Questions