Dividing rows of a dataset by a column value-CodePudding

Here i wish to divide the value in each row by the corresponding daily (each row is a day) maximum value, how would i do this? Finding it tricky as i dont want to divide the first 2 columns of the data by this. Data snipet below;

so i want to divide each of the values in each row by the max in each row but avoiding the date and substation

Date         Substation `00:00` `00:10` `00:20` `00:30` `00:40` `00:50` `01:00` `01:10` `01:20`   max
 <date>          <int>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>
 1 2013-01-03     511016   257.   259.    264.    263.    248.     239.   243.     230.   228.    264.  
 2 2013-01-03     511029    96    111.    116.     99.0   123.     128.   130.     126.   116.    130.  
 3 2013-01-03     511030   138.   129.    127.    124.    119.     126.   125.     121.   112.    138.  
 4 2013-01-03     511033   172.   165.    167.    170.    171.     173.   173.     166.   157.    173.  
 5 2013-01-03     511034   302.   298.    302.    290.    291.     287.   280.     291.   277.    302.  
 6 2013-01-03     511035   116.   131.    130.    121.    116.     108.   106.     112.   109.    131.

CodePudding user response：

With pmax and do.call:

df[-c(1, 2)] / do.call(pmax, df[-c(1, 2)])

Or, you can apply a function, x / max(x), for each rows (MARGIN = 1) of your selected columns (df[-c(1, 2)]) using apply.

t(apply(df[-c(1, 2)], 1, function(x) x / max(x)))

CodePudding user response：

Actually, since you already have the max in your last column you just need to do

cc <- grep('^Date|^Subst|^max', names(dat))  ## storing columns NOT to be divided
dat[-cc] <- dat[-cc]/dat$max  ## divide matrix by vector

Before having the max column you could use matrixStats::rowMaxs to divide by. And rowMeans for averages. There's also a matrixStats::rowMeans2 which might be a little faster.

dat[-cc]/matrixStats::rowMaxs(as.matrix(dat[-cc]))
dat[-cc]/rowMeans(as.matrix(dat[-cc]))

Data:

dat <- structure(list(Date = c("2013-01-03", "2013-01-03", "2013-01-03", 
"2013-01-03", "2013-01-03", "2013-01-03"), Substation = c(511016L, 
511029L, 511030L, 511033L, 511034L, 511035L), X.00.00. = c(257, 
96, 138, 172, 302, 116), X.00.10. = c(259, 111, 129, 165, 298, 
131), X.00.20. = c(264, 116, 127, 167, 302, 130), X.00.30. = c(263, 
99, 124, 170, 290, 121), X.00.40. = c(248, 123, 119, 171, 291, 
116), X.00.50. = c(239, 128, 126, 173, 287, 108), X.01.00. = c(243, 
130, 125, 173, 280, 106), X.01.10. = c(230, 126, 121, 166, 291, 
112), X.01.20. = c(228, 116, 112, 157, 277, 109), max = c(264, 
130, 138, 173, 302, 131)), row.names = c("1", "2", "3", "4", 
"5", "6"), class = "data.frame")