Divide All Columns by a Chosen Column Using Mutate_All

Divide all columns by a chosen column using mutate_all

Something like this perhaps:

library(tidyverse)

d <- seq(0, 100, 0.5) 
Fe <- runif(201, min = 0, max = 1000) 
Ca <- runif(201, min = 0, max = 1000) 
Zr <- runif(201, min = 0, max = 1000) 
Ti <- runif(201, min = 0, max = 1000) 
Al <- runif(201, min = 0, max = 1000) 
example <- data.frame(d, Fe, Ca, Zr, Ti, Al)
Ratio_Elements <- c("Fe", "Ti", "Zr", "d") #this subset of the 

Example_Ratio <- example %>%
  mutate_at(vars(-Zr), funs(. / Zr)) %>%
  select(Ratio_Elements)

I know you said you'd like to see a mutate_all solution, but I guess you don't want to divide Zr by itself?

In this case mutate_at is more helpful, otherwise you can do mutate(across(everything(), ~ . / Zr)).

If you want to keep the mentioned vector, there are at least two options.

Either converting it to name via as.symbol and using !! as shown below:

Detrital_Divisor <- as.symbol("Zr")

Example_Ratio <- example %>%
  mutate(across(-Detrital_Divisor, ~ . / !! Detrital_Divisor)) %>%
  select(all_of(Ratio_Elements))

Or using .data pronoun and keeping it as character/plain string:

Detrital_Divisor <- "Zr"

Example_Ratio <- example %>%
  mutate(across(-Detrital_Divisor, ~ . / .data[[Detrital_Divisor]])) %>%
  select(all_of(Ratio_Elements))

---

dplyr versions < 1.0.0

With dplyr versions below 1.0.0 you cannot use across. However, both in 0.8 and 1.0 you can do:

Detrital_Divisor <- as.symbol("Zr")

Example_Ratio <- example %>%
  mutate_at(vars(- !! Detrital_Divisor), ~ . / !! Detrital_Divisor) %>%
  select(Ratio_Elements)

On the other hand there's also list - useful for mutating in multiple ways or naming the output, e.g.:

Example_Ratio <- example %>%
  mutate_at(vars(- !! Detrital_Divisor), list(appended_name = ~ . / !! Detrital_Divisor))

With the versions before 0.8.0, there is funs (deprecated since 0.8.0):

Detrital_Divisor <- as.symbol("Zr")

Example_Ratio <- example %>%
  mutate_at(vars(- !! Detrital_Divisor), funs(. / !! Detrital_Divisor)) %>%
  select(Ratio_Elements)

dividing all columns by another

You are receiving the error because your first column is character and you are dividing it by PanCk.

In base R, you can do this directly with

counts[-1] <- counts[-1]/counts$PanCk

---

If you are interested in dplyr approach note that your data is grouped by Image so you need to ungroup that first and then divide by PanCk ignoring the first column.

library(dplyr)
counts %>% ungroup %>% mutate(across(-1, ~(./ PanCk)))

#  Image                PanCk  `PD-L1`       CD8  `PD-1`    FoxP3     CD68
#   <chr>                <dbl>    <dbl>     <dbl>   <dbl>    <dbl>    <dbl>
# 1 38_restained.ome.tif     1 0.00162  0.000130  0.0104  0.000705 0.0116  
# 2 58 RESTAIN.ome.tif       1 0.000436 0.0000637 0.00970 0.000794 0.00256 
# 3 64_restain.ome.tif       1 0.000381 0.0000693 0.00337 0.000315 0.000898
# 4 B06.ome.tif              1 0.0163   0.0707    0.189   0.144    0.144   
# 5 B07.ome.tif              1 0.109    0.157     0.124   0.0430   0.132   
# 6 B09.ome.tif              1 0.00478  0.0114    0.0146  0.00500  0.0277  
# 7 B12.ome.tif              1 0.00699  0.0284    0.0249  0.00606  0.0289  
# 8 B15.ome.tif              1 0.296    0.105     0.103   0.0471   0.0398  
# 9 B16.ome.tif              1 0.0252   0.0724    0.0403  0.0460   0.0809  
#10 B32.ome.tif              1 0.0113   0.0135    0.0165  0.0193   0.0259  
#11 B38-1.ome.tif            1 0.0240   0.0694    0.0310  0.0117   0.0458  
#12 B39.ome.tif              1 0.00691  0.0288    0.0119  0.00660  0.0162  
#13 B40.ome.tif              1 0.00995  0.0503    0.0344  0.0112   0.0688  
#14 B49.ome.tif              1 0.0629   0.143     0.0639  0.0118   0.0490  
#15 B60.ome.tif              1 0.00941  0.0300    0.0332  0.00955  0.0246  
#16 B62.ome.tif              1 0.0198   0.0771    0.0581  0.00443  0.0122  
#17 b64.ome.tif              1 0.00534  0.0709    0.00771 0.00472  0.0250  

Divide each column in a df by each other column using mutate_all() from dplyr in r

Another answe using purrr which will also have the columns named as you specified:

library(tidyverse)

df %>% 
  select_if(is.numeric) %>% 
  map(~./df %>% select_if(is.numeric)) %>% 
  imap(~set_names(.x, paste0(names(.x), "_", .y))) %>% 
  bind_cols()

Using summarize_at to divide multiple columns by other column

The issue is that summarise/summarise_at returns a length of 1, but insstead when we divide by the whole column, it is of length n(). So, instead of summarise_at, use mutate_at to modify the column

...
 %>%
  mutate_at(vars(starts_with('total')),list(~./count_short))

or if the need is to. create new column, after the list wrap specify a name that will get suffixed with the existing column to create new column

... %>%
   mutate_at(vars(starts_with('total')),list(value = ~./count_short))  

Dividing columns by particular values using dplyr

I trust this is what you are after:

Divide each column by the sum of that column - grouped by Country

library(tidyverse)
df1 %>%
  group_by(Country) %>%
  mutate_at(vars(c_school: c_leisure), funs(./ sum(.)))
#output
  Setting   q02_id c_school c_home  c_work c_transport c_leisure Country 
  <fct>      <int>    <dbl>  <dbl>   <dbl>       <dbl>     <dbl> <fct>   
1 Rural   11900006   NaN     0.238   0.600        1.00     0.250 Vietnam 
2 Rural   11900031     1.00  1.00  NaN          NaN      NaN     China   
3 Rural   11900033   NaN     0.143   0            0        0.750 Vietnam 
4 Rural   11900053   NaN     0.333   0.400        0        0     Vietnam 
5 Rural   11900114     1.00  1.00  NaN          NaN      NaN     Malaysia
6 Rural   11900446   NaN     0.286   0            0        0     Vietnam 

or alternatively divide each column by the total sum for each country as in your example (only difference is I used columns 3:7 as I trust you intended.

df1 %>%
  mutate(sum = rowSums(.[,3:7])) %>%
  group_by(Country) %>%
  mutate_at(vars(c_school: c_leisure), funs(./ sum(sum))) %>%
  select(-sum)
#output
  Setting   q02_id c_school c_home c_work c_transport c_leisure Country 
  <fct>      <int>    <dbl>  <dbl>  <dbl>       <dbl>     <dbl> <fct>   
1 Rural   11900006    0     0.161  0.0968      0.0323    0.0323 Vietnam 
2 Rural   11900031    0.667 0.333  0           0         0      China   
3 Rural   11900033    0     0.0968 0           0         0.0968 Vietnam 
4 Rural   11900053    0     0.226  0.0645      0         0      Vietnam 
5 Rural   11900114    0.333 0.667  0           0         0      Malaysia
6 Rural   11900446    0     0.194  0           0         0      Vietnam 

data:

df1 = read.table(text ="Setting   q02_id c_school c_home c_work c_transport c_leisure Country
  Rural 11900006        0      5      3           1         1 Vietnam
  Rural 11900031       10      5      0           0         0 China
  Rural 11900033        0      3      0           0         3 Vietnam
  Rural 11900053        0      7      2           0         0 Vietnam
  Rural 11900114        3      6      0           0         0 Malaysia
  Rural 11900446        0      6      0           0         0 Vietnam", header = T)

create new columns with mutate_all

The usage of funs would be deprecated in favor of list from dplyr_0.8.0 So, the option would be

library(dplyr)
df %>%
    mutate_at(vars(Revenue:Rent), list(percentage_of_rent = ~  ./Rent))
#  Year Revenue Cost Rent Revenue_percentage_of_rent Cost_percentage_of_rent Rent_percentage_of_rent
#1 2016    3000    4  100                         30                    0.04                       1
#2 2017    4000    5  100                         40                    0.05                       1
#3 2018    5000    6  100                         50                    0.06                       1

Dividing selected columns by vector in dplyr

You can use rowwise() with c_across()

df1 %>%
  rowwise() %>% 
  mutate(c_across(a1:a3) / df2, .keep = "unused") %>%
  ungroup()

# # A tibble: 5 x 4
#       x    b1    b2    b3
#   <dbl> <dbl> <dbl> <dbl>
# 1    19 0.333     4   0.4
# 2    38 0.667     8   0.8
# 3    57 1        12   1.2
# 4    76 1.33     16   1.6
# 5    95 1.67     20   2  

Another base R option

df1[-1] <- t(t(df1[-1]) / unlist(df2))
df1

# # A tibble: 5 x 4
#       x    a1    a2    a3
#   <dbl> <dbl> <dbl> <dbl>
# 1    19 0.333     4   0.4
# 2    38 0.667     8   0.8
# 3    57 1        12   1.2
# 4    76 1.33     16   1.6
# 5    95 1.67     20   2  

---

Related Topics