CODEWITHSUNDEEP
r
Dev P
Dev P

Reputation: 449

Is there a way to capture the sequence of values based on there rank

Hi all I have got a dataframe. I need to create another column so that it should tell at what place each categories are there. For example PLease refer expected output

df
ColB   ColA       
  X    A>B>C  
  U    B>C>A
  Z    C>A>B

Expected output

df1
ColB    ColA     A       B       C
  X     A>B>C    1       2       3
  U     B>C>A    3       1       2
  Z     C>A>B    2       3       1

Upvotes: 0

Views: 26

Answers (2)

akrun
akrun

Reputation: 887048

We can do this in base R

df[LETTERS[1:3]] <- t(sapply(regmatches(df$ColA, gregexpr("[A-Z]", 
      df$ColA)), match, x = LETTERS[1:3]))
df
#  ColB  ColA A B C
#1    X A>B>C 1 2 3
#2    U B>C>A 3 1 2
#3    Z C>A>B 2 3 1

data

df <- structure(list(ColB = structure(c(2L, 1L, 3L), .Label = c("U", 
"X", "Z"), class = "factor"), ColA = structure(1:3, .Label = c("A>B>C", 
"B>C>A", "C>A>B"), class = "factor")), class = "data.frame", 
row.names = c(NA, 
-3L))

Upvotes: 0

Ronak Shah
Ronak Shah

Reputation: 388907

We can first bring ColA into separate rows, group_by ColB and give an unique row number for each entry and then convert the data into wide format using pivot_wider. 

library(dplyr)
library(tidyr)

df %>%
  mutate(ColC = ColA) %>%
  separate_rows(ColC, sep = ">") %>%
  group_by(ColB) %>%
  mutate(row = row_number()) %>%
  pivot_wider(names_from = ColC, values_from = row)

#  ColB  ColA      A     B     C
#  <fct> <fct> <int> <int> <int>
#1 X     A>B>C     1     2     3
#2 U     B>C>A     3     1     2
#3 Z     C>A>B     2     3     1

data

df <- structure(list(ColB = structure(c(2L, 1L, 3L), .Label = c("U", 
"X", "Z"), class = "factor"), ColA = structure(1:3, .Label = c("A>B>C", 
"B>C>A", "C>A>B"), class = "factor")), class = "data.frame", row.names = c(NA, -3L))

Upvotes: 1

Related Questions