I have to two data tables, one with two columns and the other with multiple columns.
table1
Samples Val
a 2
b 58
c 4
d 100
table2
Samples A1 A2 A3
a 100 67 3
c 54 89 120
d 23 64 46
f 6 99 35
Now I have a big issue with that in R:
I would like to substract values of table2 from values (Val) of table1 when Samples match. The final substracted values should replace those one in table2 (or in a new table row and column names should be the same as table2). values < 0 should be replace by 0
The final table should look like that:
Final_table
Samples A1 A2 A3
a 98 65 1
c 50 85 116
d 0 0 0
f 6 99 35
Can anyone please help me with that?
Make a vector that matches the sample names, then subtract:
x <- table1$Val[ match(table2$Samples, table1$Samples) ]
# if sample doesn't match, then zero, nothing to subtract
x <- ifelse(is.na(x), 0, x)
x
# [1] 2 4 100 0
table2[, -1] <- table2[, -1] - x
# if negative then assign zero
table2[, -1][ table2[, -1] < 0 ] <- 0
table2
# Samples A1 A2 A3
# 1 a 98 65 1
# 2 c 50 85 116
# 3 d 0 0 0
# 4 f 6 99 35