CODEWITHSUNDEEP
rdplyrreplacematchingsubtraction
ZubB
ZubB

Reputation: 13

Match and subtract: one column to multiple columns

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?

Upvotes: 1

Views: 68

Answers (3)

zx8754
zx8754

Reputation: 56239

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

Upvotes: 0

jkatam
jkatam

Reputation: 3447

Alternatively in base r

merged_data <- merge(table2, table1, by = "Samples", all= TRUE)

merged_data$Val <- ifelse(!is.na(merged_data$Val),merged_data$Val,0)
merged_data[, -1] <- merged_data[, -1] - merged_data$Val
merged_data[, 5] <- NULL

merged_data <- merged_data[rowSums(!is.na(merged_data[, -1]))>0,]

merged_data[rowSums(merged_data[,-1]<0)==3,-1] <- 0 

merged_data

Created on 2023-08-16 with reprex v2.0.2

  Samples A1 A2  A3
1       a 98 65   1
3       c 50 85 116
4       d  0  0   0
5       f  6 99  35

Upvotes: 0

Quinten
Quinten

Reputation: 41533

You could use dplyr to check across the columns using an ifelse statement like this:

library(dplyr)

table2 %>%
  left_join(., table1, by = "Samples") %>%
  mutate(across(A1:A3, ~ ifelse(!is.na(Val), .x - Val, .x))) %>%
  mutate(across(A1:A3, ~ ifelse(.x < 0 , 0, .x))) %>%
  select(-Val)
#>   Samples A1 A2  A3
#> 1       a 98 65   1
#> 2       c 50 85 116
#> 3       d  0  0   0
#> 4       f  6 99  35

Created on 2023-08-16 with reprex v2.0.2

Data used:

table1 = read.table(text = "Samples Val
a        2
b       58 
c        4
d      100", header = TRUE)
 
table2 = read.table(text = "Samples  A1    A2    A3
a       100    67     3
c        54    89   120
d        23    64    46
f         6    99    35 ", header = TRUE)

Upvotes: 0

Related Questions