Divide each column of a dataframe by one row of the dataframe

Question

I would like to divide each column of my dataframe by the values of one row. I tried to transform my dataframe into a matrix and to extract one row of the dataframe as a vector then divide the matrix by the vector but it did not work. Indeed, only the first row of the matrix got divided by the vector.

Here is my original dataframe.

And this is the code I tried to run :

data <- read_excel("Documents/TFB/xlsx_geochimie/solfatara_maj.xlsx")
View(data)
data.mat <- as.matrix(data[,2:20])
vector <- data[12,2:20] 
data.mat/vector

Comments are not for extended discussion; this conversation has been moved to chat. — Samuel Liew♦

akrun akrun · Accepted Answer · 2020-02-06T19:29:24

We replicate the vector to make the length same and then do the division

data.mat/unlist(vector)[col(data.mat)]
#  FeO     Total S SO4 Total N      SiO2     Al2O3     Fe2O3        MnO        MgO        CaO       Na2O       K2O
#[1,] 0.10  16.5555556  NA      NA 0.8908607 0.8987269 0.1835206 0.08333333 0.03680982 0.04175365 0.04823151 0.5738562
#[2,] 0.40 125.8333333  NA      NA 0.5510204 0.4456019 0.2359551 0.08333333 0.04294479 0.01878914 0.04501608 0.2588235
#[3,] 0.85   0.6111111  NA      NA 1.0021295 1.0162037 0.7715356 1.08333333 0.53987730 0.69728601 1.03858521 1.0457516
#[4,] 0.15  48.0555556  NA      NA 1.1027507 0.2569444        NA 0.08333333 0.01840491 0.01878914 0.04180064 0.1647059
#[5,] 0.85          NA  NA      NA 1.0889086 1.0271991 0.6591760 0.75000000 0.59509202 0.53862213 1.02250804 1.1228758
#[6,]   NA          NA  NA      NA 1.3426797 0.6319444 0.0411985 0.08333333 0.03067485 0.11899791 0.65594855 0.7764706
#          TiO2      P2O5        LOI       LOI2     Total   Total 2   Fe2O3(T)
#[1,] 0.7924528 0.3928571  7.0841837  6.6963855 0.9922233 0.9894632 0.14489796
#[2,] 0.5094340 0.3214286 14.5561224 13.7710843 0.9958126 0.9936382 0.31020408
#[3,] 0.8679245 0.6428571  1.5637755  1.5228916 0.9990030 0.9970179 0.80612245
#[4,] 1.4905660 0.2857143  7.4056122  7.0024096 0.9795613 0.9769384 0.05510204
#[5,] 1.0377358 0.2500000  0.3520408  0.3783133 0.9969093 0.9960239 0.74489796
#[6,] 0.3018868 0.2500000  1.2551020  1.1879518 1.0019940 1.0000000 0.04489796

Or use sweep

sweep(data.mat, MARGIN = 2, unlist(vector), FUN = `/`)

Or using mapply with asplit

mapply(`/`, asplit(data.mat, 2), vector)

data

data_mat <- structure(c(0.2, 0.8, 1.7, 0.3, 1.7, NA, 5.96, 45.3, 0.22, 17.3, 
NA, NA, NA, 6.72, NA, 4.08, 0.06, 0.16, NA, NA, NA, NA, NA, NA, 
50.2, 31.05, 56.47, 62.14, 61.36, 75.66, 15.53, 7.7, 17.56, 4.44, 
17.75, 10.92, 0.49, 0.63, 2.06, NA, 1.76, 0.11, 0.01, 0.01, 0.13, 
0.01, 0.09, 0.01, 0.06, 0.07, 0.88, 0.03, 0.97, 0.05, 0.2, 0.09, 
3.34, 0.09, 2.58, 0.57, 0.15, 0.14, 3.23, 0.13, 3.18, 2.04, 4.39, 
1.98, 8, 1.26, 8.59, 5.94, 0.42, 0.27, 0.46, 0.79, 0.55, 0.16, 
0.11, 0.09, 0.18, 0.08, 0.07, 0.07, 27.77, 57.06, 6.13, 29.03, 
1.38, 4.92, 27.79, 57.15, 6.32, 29.06, 1.57, 4.93, 99.52, 99.88, 
100.2, 98.25, 99.99, 100.5, 99.54, 99.96, 100.3, 98.28, 100.2, 
100.6, 0.71, 1.52, 3.95, 0.27, 3.65, 0.22), .Dim = c(6L, 19L), .Dimnames = list(
    NULL, c("FeO", "Total S", "SO4", "Total N", "SiO2", "Al2O3", 
    "Fe2O3", "MnO", "MgO", "CaO", "Na2O", "K2O", "TiO2", "P2O5", 
    "LOI", "LOI2", "Total", "Total 2", "Fe2O3(T)")))

vector <- structure(list(FeO = 2, `Total S` = 0.36, SO4 = NA_real_, `Total N` = NA_real_, 
    SiO2 = 56.35, Al2O3 = 17.28, Fe2O3 = 2.67, MnO = 0.12, MgO = 1.63, 
    CaO = 4.79, Na2O = 3.11, K2O = 7.65, TiO2 = 0.53, P2O5 = 0.28, 
    LOI = 3.92, LOI2 = 4.15, Total = 100.3, `Total 2` = 100.6, 
    `Fe2O3(T)` = 4.9), row.names = c(NA, -1L), class = c("tbl_df", 
"tbl", "data.frame"))

Divide each column of a dataframe by one row of the dataframe

2 Answers

data