CODEWITHSUNDEEP
rdataframedplyrtidyr
Anshul S
Anshul S

Reputation: 281

How to rank each item based on columns?

I would like to rank each Item, based on their columns in the following example.

For each Item, all elements are present in LC1 that should be Ranked-1, in LC2 it would be Ranked-2, in LC3 it would be Ranked-3 and in LC4 it would be Ranked-4

Input:

     Item   LC1     LC2     LC3    LC4
1   0A2926  MW92    RM11    RS11    WK14
2   0A2926  MW92    RM11    RS11    WK15
3   0A2926  MW92    SE92    NA      NA
4   0A3721  RM11    MW92    NA      NA
5   0A4664  MW92    RM11    RS11    NA

Output:

 Item    LC     Rank 
0A2926   MW92     1
0A2926   RM11     2
0A2926   SE92     2
0A2926   RS11     3
0A2926   WK14     4
0A2926   WK15     4
0A3721   RM11     1
0A3721   MW92     2
0A4664   MW92     1
0A4664   RM11     2
0A4664   RS11     3

Can someone guide me how to proceed on this in R?

Upvotes: 2

Views: 55

Answers (2)

Cettt
Cettt

Reputation: 11981

You can use the tidyverse package like this:

library(tidyverse)

df %>% gather(Rank, LC, -Item) %>%
  mutate(Rank = as.numeric(grep("[0-9]", Rank, value = T))) %>%
  select(Item, LC, Rank)

Upvotes: 3

Ronak Shah
Ronak Shah

Reputation: 388862

One way could be to gather data to long format removing NA values and to get Rank remove the character values present in the column so only the numbers remain.

library(dplyr)
df %>%
  tidyr::gather(Rank, value, starts_with("LC"), na.rm = TRUE) %>%
  mutate(Rank = as.integer(gsub("[A-Z]", "", Rank)))


#     Item Rank value
#1  0A2926    1  MW92
#2  0A2926    1  MW92
#3  0A2926    1  MW92
#4  0A3721    1  RM11
#5  0A4664    1  MW92
#6  0A2926    2  RM11
#7  0A2926    2  RM11
#8  0A2926    2  SE92
#9  0A3721    2  MW92
#10 0A4664    2  RM11
#11 0A2926    3  RS11
#12 0A2926    3  RS11
#13 0A4664    3  RS11
#14 0A2926    4  WK14
#15 0A2926    4  WK15

Upvotes: 2

Related Questions