CODEWITHSUNDEEP
rgroup-bydplyrtidyversesapply
LukeP
LukeP

Reputation: 93

How to find percentage of maximum of groups across multiple variable columns

I have a dataset df that looks like this:

Experiment Ch1    Ch2     Ch3     Ch4
exp_1   -1548   -19296  -65764  -64392
exp_1   -1572   -19304  -65756  -64392
exp_1   -1588   -19284  -65748  -64400
exp_1   -1580   -19292  -65760  -64392
exp_1   -1584   -19292  -65744  -64388
exp_1   -1580   -19292  -65756  -64408
exp_1   -1612   -19280  -65748  -64428
exp_2   -1620   -19276  -65740  -64464
exp_2   -1632   -19272  -65728  -64452
exp_2   -1636   -19268  -65732  -64464
exp_2   -1644   -19260  -65728  -64476
exp_2   -1652   -19268  -65736  -64476
exp_2   -1652   -19292  -65728  -64484
exp_2   -1660   -19268  -65740  -64480
exp_2   -1648   -19276  -65736  -64492
exp_3   -1664   -19276  -65736  -64504
exp_3   -1672   -19260  -65752  -64496
exp_3   -1668   -19276  -65728  -64496
exp_3   -1684   -19272  -65732  -64476
exp_3   -1676   -19260  -65728  -64476
exp_3   -1672   -19264  -65716  -64492
exp_3   -1680   -19268  -65732  -64480
exp_3   -1668   -19276  -65728  -64496
exp_3   -1684   -19272  -65732  -64476

I would like to produce columns that show a percentage of maximum for each group of my values based on the corresponding "Experiment number".

Such as this:

Experiment Ch1  %.Ch1    Ch2    %.Ch2    Ch3    %.Ch3    Ch4    %.Ch4
exp_1   -1548   100.00  -19296  99.92   -65764  99.97   -64392  99.99
exp_1   -1572   98.47   -19304  99.88   -65756  99.98   -64392  99.99
exp_1   -1588   97.48   -19284  99.98   -65748  99.99   -64400  99.98
exp_1   -1580   97.97   -19292  99.94   -65760  99.98   -64392  99.99
exp_1   -1584   97.73   -19292  99.94   -65744  100.00  -64388  100.00
exp_1   -1580   97.97   -19292  99.94   -65756  99.98   -64408  99.97
exp_1   -1612   96.03   -19280  100.00  -65748  99.99   -64428  99.94
exp_2   -1620   100.00  -19276  99.92   -65740  99.98   -64464  99.98
exp_2   -1632   99.26   -19272  99.94   -65728  100.00  -64452  100.00
exp_2   -1636   99.02   -19268  99.96   -65732  99.99   -64464  99.98
exp_2   -1644   98.54   -19260  100.00  -65728  100.00  -64476  99.96
exp_2   -1652   98.06   -19268  99.96   -65736  99.99   -64476  99.96
exp_2   -1652   98.06   -19292  99.83   -65728  100.00  -64484  99.95
exp_2   -1660   97.59   -19268  99.96   -65740  99.98   -64480  99.96
exp_2   -1648   98.30   -19276  99.92   -65736  99.99   -64492  99.94
exp_3   -1664   100.00  -19276  99.92   -65736  99.97   -64504  99.96
exp_3   -1672   99.52   -19260  100.00  -65752  99.95   -64496  99.97
exp_3   -1668   99.76   -19276  99.92   -65728  99.98   -64496  99.97
exp_3   -1684   98.81   -19272  99.94   -65732  99.98   -64476  100.00
exp_3   -1676   99.28   -19260  100.00  -65728  99.98   -64476  100.00
exp_3   -1672   99.52   -19264  99.98   -65716  100.00  -64492  99.98
exp_3   -1680   99.05   -19268  99.96   -65732  99.98   -64480  99.99
exp_3   -1668   99.76   -19276  99.92   -65728  99.98   -64496  99.97
exp_3   -1684   98.81   -19272  99.94   -65732  99.98   -64476  100.00

I know that the solution likely can be accomplished with Base R, or dplyr's group_by function, but I am stuck on how to do multiple columns at once. Any help would be appreciated!

Upvotes: 3

Views: 65

Answers (1)

Nautica
Nautica

Reputation: 2018

Something like this:

df <- df %>% group_by(Experiment) %>% mutate_at(vars(contains("Ch")), .funs = list(
  PCT = function(x) {
    round((max(x) / x) * 100, 2)
  }
))

Mutate at columns with names which contains "Ch". Putting the function in the mutate call as a list allows you to append an identifier to the column names you're mutating to create new columns.

Actual figures are only rounded to two digits if you use View(df).

   Experiment   Ch1    Ch2    Ch3    Ch4 Ch1_PCT Ch2_PCT Ch3_PCT Ch4_PCT
   <fct>      <int>  <int>  <int>  <int>   <dbl>   <dbl>   <dbl>   <dbl>
 1 exp_1      -1548 -19296 -65764 -64392   100      99.9    100.   100. 
 2 exp_1      -1572 -19304 -65756 -64392    98.5    99.9    100.   100. 
 3 exp_1      -1588 -19284 -65748 -64400    97.5   100.     100.   100. 
 4 exp_1      -1580 -19292 -65760 -64392    98.0    99.9    100.   100. 
 5 exp_1      -1584 -19292 -65744 -64388    97.7    99.9    100    100  
 6 exp_1      -1580 -19292 -65756 -64408    98.0    99.9    100.   100. 
 7 exp_1      -1612 -19280 -65748 -64428    96.0   100      100.    99.9
 8 exp_2      -1620 -19276 -65740 -64464   100      99.9    100.   100. 
 9 exp_2      -1632 -19272 -65728 -64452    99.3    99.9    100    100  
10 exp_2      -1636 -19268 -65732 -64464    99.0   100.     100.   100. 
11 exp_2      -1644 -19260 -65728 -64476    98.5   100      100    100. 
12 exp_2      -1652 -19268 -65736 -64476    98.1   100.     100.   100. 
13 exp_2      -1652 -19292 -65728 -64484    98.1    99.8    100    100. 
14 exp_2      -1660 -19268 -65740 -64480    97.6   100.     100.   100. 
15 exp_2      -1648 -19276 -65736 -64492    98.3    99.9    100.    99.9
16 exp_3      -1664 -19276 -65736 -64504   100      99.9    100.   100. 
17 exp_3      -1672 -19260 -65752 -64496    99.5   100      100.   100. 
18 exp_3      -1668 -19276 -65728 -64496    99.8    99.9    100.   100. 
19 exp_3      -1684 -19272 -65732 -64476    98.8    99.9    100.   100  
20 exp_3      -1676 -19260 -65728 -64476    99.3   100      100.   100  
21 exp_3      -1672 -19264 -65716 -64492    99.5   100.     100    100. 
22 exp_3      -1680 -19268 -65732 -64480    99.0   100.     100.   100. 
23 exp_3      -1668 -19276 -65728 -64496    99.8    99.9    100.   100. 
24 exp_3      -1684 -19272 -65732 -64476    98.8    99.9    100.   100

Then later sort the columns by:

df %>% select(Experiment, names(df)[-1] %>% sort())

   Experiment   Ch1 Ch1_PCT    Ch2 Ch2_PCT    Ch3 Ch3_PCT    Ch4 Ch4_PCT
   <fct>      <int>   <dbl>  <int>   <dbl>  <int>   <dbl>  <int>   <dbl>
 1 exp_1      -1548   100   -19296    99.9 -65764    100. -64392   100. 
 2 exp_1      -1572    98.5 -19304    99.9 -65756    100. -64392   100. 
 3 exp_1      -1588    97.5 -19284   100.  -65748    100. -64400   100. 
 4 exp_1      -1580    98.0 -19292    99.9 -65760    100. -64392   100. 
 5 exp_1      -1584    97.7 -19292    99.9 -65744    100  -64388   100  
 6 exp_1      -1580    98.0 -19292    99.9 -65756    100. -64408   100. 
 7 exp_1      -1612    96.0 -19280   100   -65748    100. -64428    99.9
 8 exp_2      -1620   100   -19276    99.9 -65740    100. -64464   100. 
 9 exp_2      -1632    99.3 -19272    99.9 -65728    100  -64452   100  
10 exp_2      -1636    99.0 -19268   100.  -65732    100. -64464   100. 
11 exp_2      -1644    98.5 -19260   100   -65728    100  -64476   100. 
12 exp_2      -1652    98.1 -19268   100.  -65736    100. -64476   100. 
13 exp_2      -1652    98.1 -19292    99.8 -65728    100  -64484   100. 
14 exp_2      -1660    97.6 -19268   100.  -65740    100. -64480   100. 
15 exp_2      -1648    98.3 -19276    99.9 -65736    100. -64492    99.9
16 exp_3      -1664   100   -19276    99.9 -65736    100. -64504   100. 
17 exp_3      -1672    99.5 -19260   100   -65752    100. -64496   100. 
18 exp_3      -1668    99.8 -19276    99.9 -65728    100. -64496   100. 
19 exp_3      -1684    98.8 -19272    99.9 -65732    100. -64476   100  
20 exp_3      -1676    99.3 -19260   100   -65728    100. -64476   100  
21 exp_3      -1672    99.5 -19264   100.  -65716    100  -64492   100. 
22 exp_3      -1680    99.0 -19268   100.  -65732    100. -64480   100. 
23 exp_3      -1668    99.8 -19276    99.9 -65728    100. -64496   100. 
24 exp_3      -1684    98.8 -19272    99.9 -65732    100. -64476   100

Upvotes: 5

Related Questions