use a value from an R dataframe to normalise a column of another dataframe

Question

Apologies if this has been asked before!

I have a big data set that I need to normalise. The data is in one dataframe, and the value needed for normalisation is in a different one. I've created a small example here:

data = data.frame(AMR = c("cfxA", "23S", "sul2"),
                  ACLOD_1 = c(347, 323, 186),
                  ACLOD_11 = c(0, 90, 0), 
                  ACLOD_12 = c(74, 298, 0))

scg = data.frame(gene = "dnaG", 
                 ACLOD_1 = 683,
                 ACLOD_11 = 789,
                 ACLOD_12 = 556)

What I need to do is divide the values in "data" by the corresponding value in "scg"; i.e. divide the values in the ACLOD_1 column (347, 323, 186) by 683 so that I get 0.51, 0.47 and 0.27, while the values in ACLOD_11 would be divided by 789, etc.

I've previously managed this by separating out each column of the data and working on it that way, but as I will likely have more than 1,000 columns in total to work on I need to work out a different way - so I was wondering if anyone had any ideas, please?

Many thanks!

Answer 1

Select the columns of interest (assuming we need the columns except the first), use Map to divide the corresponding selected columns from 'data' and 'scg' and assign it back to the 'data' columns

nm1 <- names(data)[-1]
data[nm1] <- Map(`/`,data[nm1], scg[nm1])

---

Or with vectorized

data[nm1] <- data[nm1]/scg[nm1][col(data[nm1])]

Or using sweep

data[nm1] <- sweep(data[nm1], 2, unlist(scg[nm1]), `/`)