Use conditions from multiple variables to replace a variable in R

user_6627211 0
Viewed 194

I did some searches but could not find the best keywords to phrase my question so I think I will attempt to ask it here.

I am dealing with a data frame in R that have two variables represent the identity of the data points. In the following example, A and 1 represent the same individual, B and 2 are the same and so are C and 3 but they are being mixed in the original data.

ID1 ID2 Value
  A   1   0.5
  B   2   0.8
  C   C   0.7
  A   A   0.6
  B   2   0.3
  3   C   0.4
  2   2   0.3
  1   A   0.4
  3   3   0.6

What I want to achieve is to unify the identity by using only one of the identifiers so it can be either:

ID1 ID2 Value ID
  A   1   0.5  A
  B   2   0.8  B
  C   C   0.7  C
  A   A   0.6  A
  B   2   0.3  B
  3   C   0.4  C
  2   2   0.3  B
  1   A   0.4  A
  3   3   0.6  C

or:

ID1 ID2 Value ID
  A   1   0.5  1
  B   2   0.8  2
  C   C   0.7  3
  A   A   0.6  1
  B   2   0.3  2
  3   C   0.4  3
  2   2   0.3  2
  1   A   0.4  1
  3   3   0.6  3

I can probably achieve it by using ifelse function but that means I have to write two ifelse statements for each condition and it does not seem efficient so I was wondering if there is a better way to do it. Here is the example data set.

df=data.frame(ID1=c("A","B","C","A","B","3","2","1","3"),
              ID2=c("1","2","C","A","2","C","2","A","3"),
              Value=c(0.5,0.8,0.7,0.6,0.3,0.4,0.3,0.4,0.6))

Thank you so much for the help!

Edit:

To clarify, the two identifiers I have in my real data are longer string of texts instead of just ABC and 123. Sorry I did not make it clear.

r replace
2 Answers

An option is to to detect the elements that are only digits, convert to integer, then get the corresponding LETTERS in case_when

library(dplyr)
library(stringr)
df %>%
    mutate(ID = case_when(str_detect(ID1, '\\d+')~ 
           LETTERS[as.integer(ID1)], TRUE ~ ID1))
#   ID1 ID2 Value ID
#1   A   1   0.5  A
#2   B   2   0.8  B
#3   C   C   0.7  C
#4   A   A   0.6  A
#5   B   2   0.3  B
#6   3   C   0.4  C
#7   2   2   0.3  B
#8   1   A   0.4  A
#9   3   3   0.6  C

Or more compactly

df %>%
    mutate(ID = coalesce(LETTERS[as.integer(ID1)], ID1))

If we have different sets of values, then create a key/value dataset and do a join

keyval <- data.frame(ID1 = c('1', '2', '3'), ID = c('A', 'B', 'C'))
left_join(df, keyval) %>% mutate(ID = coalesce(ID, ID1))
user_37322710

A base R option using replace

within(
  df,
  ID <- replace(
    ID1,
    !ID1 %in% LETTERS,
    LETTERS[as.numeric(ID1[!ID1 %in% LETTERS])]
  )
)

or ifelse

within(
  df,
  ID <- suppressWarnings(ifelse(ID1 %in% LETTERS,
    ID1,
    LETTERS[as.integer(ID1)]
  ))
)

which gives

  ID1 ID2 Value ID
1   A   1   0.5  A
2   B   2   0.8  B
3   C   C   0.7  C
4   A   A   0.6  A
5   B   2   0.3  B
6   3   C   0.4  C
7   2   2   0.3  B
8   1   A   0.4  A
9   3   3   0.6  C
user_121587570
Related