How to change values ​​from character to number using if and for operators?

Question

I process microchip data.

I have two tables, one of them is a table of paths and genes (I will call it table A) and the other is a table of microchips (let's say B)

I need to change the characters (characters) of the genes to the value of the expression (number) in table A according to each value of the expression of the gene characters in B

Tables are as follows

A table                                            B table
Pathway   v1    v2   ...v249 v250                 Gene      Value         
   1       A    E        NA   NA                   E        1000
   2       B    A        Z    I                    A         500
   3       C    G        X    NA                   G         200
   4       D    K        P    NA                   B         300
                                                   P          10
                                                   Z          20

I want to change the table as follows

   A table                            
Pathway   v1       v2   ...    v249 v250      
   1       500    1000         NA    NA 
   2       300    500          20    NA
   3       NA     200          NA    NA
   4       NA     NA           10    NA 

If there are no harmonized gene symbols, they should be replaced by "NA"

Answer 1

, base R. "A" ( , "Pathway" ) matrix, match "Gene" "B", "", .

A1 <- A
A1[-1] <- B$Value[match(as.matrix(A[-1]), B$Gene)]
A1
#  Pathway  v1   v2
#1       1 500 1000
#2       2 300  500
#3       3  NA  200
#4       4  NA   NA

: @DavidArenburg.

Answer 2

, , . A. data.table CRAN (v 1.9.6 +)

library(data.table) # V 1.9.6+
res <- melt(setDT(A), id = "Pathway")[setDT(B), Value := i.Value, on = c(value = "Gene")]
dcast(res, Pathway ~ variable, value.var = "Value")
#    Pathway  v1   v2
# 1:       1 500 1000
# 2:       2 300  500
# 3:       3  NA  200
# 4:       4  NA   NA

---

Hadleyverse

library(dplyr)
library(tidyr)
A %>%
  gather(res, Gene, -Pathway) %>%
  left_join(., B, by = "Gene") %>%
  select(-Gene) %>%
  spread(res, Value)
#   Pathway  v1   v2
# 1       1 500 1000
# 2       2 300  500
# 3       3  NA  200
# 4       4  NA   NA  

---

A <- structure(list(Pathway = 1:4, v1 = structure(1:4, .Label = c("A", 
"B", "C", "D"), class = "factor"), v2 = structure(c(2L, 1L, 3L, 
4L), .Label = c("A", "E", "G", "K"), class = "factor")), .Names = c("Pathway", 
"v1", "v2"), class = "data.frame", row.names = c(NA, -4L))

B <- structure(list(Gene = structure(c(3L, 1L, 4L, 2L), .Label = c("A", 
"B", "E", "G"), class = "factor"), Value = c(1000L, 500L, 200L, 
300L)), .Names = c("Gene", "Value"), class = "data.frame", row.names = c(NA, 
-4L))

Answer 3

, R:

library(dplyr)
df = data.frame(v1 = sample(LETTERS[1:8], 100, replace = TRUE),
                v2 = sample(LETTERS[1:8], 100, replace = TRUE),
                v3 = sample(LETTERS[1:8], 100, replace = TRUE),
                v4 = sample(LETTERS[1:8], 100, replace = TRUE))
lut = runif(6)
names(lut) = LETTERS[1:6]

replace_fun = function(vec) lut[vec]    
df %>% mutate_each(funs(replace_fun), v1:v4)
           a         b
1 0.97821935 0.8584000
2         NA        NA
3 0.56299342 0.9782194
4 0.85840001 0.8584000
5 0.97821935 0.8584000
6 0.06881867 0.9782194

In fact, the name of each element is a letter in dfand lut[letter]that looks at what value belongs to that letter. Using lut[vec], we put the entire vector containing the letters in the lookup table, which translates the entire vector into an appropriate number.

%>%and mutate_eachare functions from dplyrthat I use to practically perform a replacement in the example data.