Dividing selected columns by vector in dplyr

user_2952838 0
Viewed 590

This has to be simple in base R, but it is driving me crazy with dplyr (which overall has made my life much better!). Suppose you have the following tibbles

library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union



df1 <- tibble(x=seq(5)*19, a1=seq(5)*1, a2=seq(5)*2, a3=seq(5)*4)

df1
#> # A tibble: 5 x 4
#>       x    a1    a2    a3
#>   <dbl> <dbl> <dbl> <dbl>
#> 1    19     1     2     4
#> 2    38     2     4     8
#> 3    57     3     6    12
#> 4    76     4     8    16
#> 5    95     5    10    20


df2 <- tibble(b1=3, b2=0.5, b3=10)

df2
#> # A tibble: 1 x 3
#>      b1    b2    b3
#>   <dbl> <dbl> <dbl>
#> 1     3   0.5    10

Created on 2020-06-11 by the reprex package (v0.3.0)

Then I simply want to replace in df1 a1 with a1/b1, a2 with a2/b2 and so on. This has to be general enough to handle the case when I have many columns. Any suggestion is appreciated.

dplyr r
6 Answers

You can use Map

df1[-1] <- Map(`/`, df1[-1], df2)

# 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

Or if you want a tidyverse solution you can use map2 in purrr : 

df1[-1] <- purrr::map2(df1[-1], df2, `/`)
user_39629140

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
user_100689850

One solution could be:

bind_cols(select(df1, x),
          sweep(select(df1, -x), 2, FUN = `/`, unlist(df2)))

      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
user_59645570

Or like this if you have more columns:

df1[,2:4] <- df1[,2:4] / df2 %>% slice(rep(1:n(), each = nrow(df1)))

# A tibble: 5 x 4
      x    a1    a2    a3
  <dbl> <dbl> <dbl> <dbl>
1    19 0.111     8  0.04
2    38 0.222    16  0.08
3    57 0.333    24  0.12
4    76 0.444    32  0.16
5    95 0.556    40  0.2
user_62421320

Another option which considers the column names of your variables and pairs them with the number they have to be divided for. The function cur_column() comes in handy inside the mutate(across()) - the function you wanted to use

# vector of divisors
l <- as.list(as.numeric(df2[1,]))

df1 %>% 
  mutate(across(starts_with("a"),
                ~ ./l[[na.omit(as.numeric(unlist(strsplit(cur_column(), "[^[:digit:]]"))))]]))

Output

# 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
user_74654620

An option with base R

df1[-1] <- df1[-1]/unlist(df2)[col(df1)]
user_37322710
Related