How subtract two columns correlativelly in a data frame

Question

Context

We would like to know how subtract columns, two by two. Specifically, we want to subtract the columns of the dataframe as follow:

the u_2018 - u_2019.
the u_2019 - u_2020.
the u_2020 - u_2021.
the u_2021 - u_2022.

We looked for a optimal solution in the several stackoverflow question posts, like this (How to subtract two columns using tidyverse mutate with columns named by external variables), but the only undesired approach achieved is datiled in the R code used section. The R session version is 4.2.0 and the dplyr package version is 1.0.9.

Input data in R session

> dat
# A tibble: 6 × 5
          u_2018        u_2019        u_2020        u_2021        u_2022
           <int>         <int>         <int>         <int>         <int>
 1         90035         88015         76135         50725         16517
 2            20            NA            NA         13792         12793
 3           555           620         15032         19309          6479
 4         11171         11782         10281          8974          3901
 5            NA        116896         40169         13191          3610

R code used


dat %>%
  mutate(
    diff_2018_2019 = u_2018 - u_2019,
    diff_2019_2020 = u_2019 - u_2020,
    diff_2020_2021 = u_2020 - u_2021,
    diff_2021_2022 = u_2021 - u_2022)

Question

We would like to know the optimal solution to subtract two by two columns. Maybe one approach includes mutate_at() or across to obtain the differences between and save the subtraction in new columns.

Thanks in advance

PaulS · Answer 1 · 2022-07-28T15:22:13.837

3

A possible solution:

library(tidyverse)

df %>% 
  mutate(map2_dfc(names(.)[-ncol(.)], names(.)[-1], ~ df[.x] - df[.y]) %>%
    set_names(str_c("new", 1:(ncol(df)-1))))

#>   u_2018 u_2019 u_2020 u_2021 u_2022 new1   new2  new3  new4
#> 1  90035  88015  76135  50725  16517 2020  11880 25410 34208
#> 2     20     NA     NA  13792  12793   NA     NA    NA   999
#> 3    555    620  15032  19309   6479  -65 -14412 -4277 12830
#> 4  11171  11782  10281   8974   3901 -611   1501  1307  5073
#> 5     NA 116896  40169  13191   3610   NA  76727 26978  9581

Or in base R:

cbind(df, setNames(df[-ncol(df)] - df[-1], paste0("new", 1:(ncol(df)-1))))

#>   u_2018 u_2019 u_2020 u_2021 u_2022 new1   new2  new3  new4
#> 1  90035  88015  76135  50725  16517 2020  11880 25410 34208
#> 2     20     NA     NA  13792  12793   NA     NA    NA   999
#> 3    555    620  15032  19309   6479  -65 -14412 -4277 12830
#> 4  11171  11782  10281   8974   3901 -611   1501  1307  5073
#> 5     NA 116896  40169  13191   3610   NA  76727 26978  9581

edited Jul 28 '22 at 15:22

answered Jul 28 '22 at 14:35

PaulS

21,159
2
9
26

how does the columns reference work with `-ncol(.)` and `-1`? do you know if there's a `purrr` tutorial/example of that? – Eric Jul 28 '22 at 15:02
1

@Eric: I am not sure I understand your question: With `map2_dfc`, I am iterating the columns indexes of the columns of df, pair of indexes by pair of indexes: `(1,2)`, `(2,3)`,... Therefore, it is needed to remove the index of last column of `df` with `-ncol(df)`, and the index of the first column of `df` (`-1`). – PaulS Jul 28 '22 at 15:11
2

Great work! Maybe you could give more effort to create custom column names to make this answer more complete. – Darren Tsai Jul 28 '22 at 15:23
2

Thanks, @DarrenTsai! I am a bit lazy today: If the OP needs more meaningful column names, he can accept your answer, which is also a great solution! – PaulS Jul 28 '22 at 15:32
I have just upvoted your solution, as it is a clever one, @DarrenTsai! – PaulS Jul 28 '22 at 15:34
1

Thanks everybody for your contributions. – Miquel Vázquez-Santiago Jul 29 '22 at 08:44

Darren Tsai · Accepted Answer · 2022-07-28T15:14:46.973

You could use pmap_dfr + diff:

library(purrr)

pmap_dfr(df, ~ -diff(c(...))) %>%
  set_names(~ as.integer(sub('u_', '', .x)) %>% paste('dif', . - 1, ., sep = '_'))

# # A tibble: 5 × 4
#   dif_2018_2019 dif_2019_2020 dif_2020_2021 dif_2021_2022
#           <int>         <int>         <int>         <int>
# 1          2020         11880         25410         34208
# 2            NA            NA            NA           999
# 3           -65        -14412         -4277         12830
# 4          -611          1501          1307          5073
# 5            NA         76727         26978          9581

Note that diff() defaults to subtract the former from the latter, so you need to invert the sign with -diff().

Data

df <- structure(list(u_2018 = c(90035L, 20L, 555L, 11171L, NA), u_2019 = c(88015L, 
NA, 620L, 11782L, 116896L), u_2020 = c(76135L, NA, 15032L, 10281L, 40169L),
u_2021 = c(50725L, 13792L, 19309L, 8974L, 13191L), u_2022 = c(16517L, 
12793L, 6479L, 3901L, 3610L)), class = "data.frame", row.names = c("1", "2", "3", "4", "5"))

Thanks to improve the estructure of the post. Also your solution works fine. Thanks so much @Darren Tsai — Miquel Vázquez-Santiago, Jul 29 '22 at 08:38

IceCreamToucan · Answer 3 · 2022-07-28T15:45:46.467

df <- structure(list(u_2018 = c(90035L, 20L, 555L, 11171L, NA), u_2019 = c(88015L, 
NA, 620L, 11782L, 116896L), u_2020 = c(76135L, NA, 15032L, 10281L, 40169L),
u_2021 = c(50725L, 13792L, 19309L, 8974L, 13191L), u_2022 = c(16517L, 
12793L, 6479L, 3901L, 3610L)), class = "data.frame", row.names = c("1", "2", "3", "4", "5"))

library(dplyr, warn.conflicts = FALSE)
library(stringr)

df %>% 
  mutate(rename_with(df[-ncol(df)] - df[-1], ~ str_replace(., 'u', 'diff')))
#>   u_2018 u_2019 u_2020 u_2021 u_2022 diff_2018 diff_2019 diff_2020 diff_2021
#> 1  90035  88015  76135  50725  16517      2020     11880     25410     34208
#> 2     20     NA     NA  13792  12793        NA        NA        NA       999
#> 3    555    620  15032  19309   6479       -65    -14412     -4277     12830
#> 4  11171  11782  10281   8974   3901      -611      1501      1307      5073
#> 5     NA 116896  40169  13191   3610        NA     76727     26978      9581

^{Created on 2022-07-28 by the reprex package (v2.0.1)}

score 1 · Answer 4 · answered Jul 28 '22 at 15:49

We could use across2 from dplyover

library(dplyover)
library(stringr)
df1 %>%
    mutate(across2(-last_col(), -1, `-`,
    .names_fn = ~str_replace(.x, "u", "dif")))

-output

 u_2018 u_2019 u_2020 u_2021 u_2022 dif_2018_u_2019 dif_2019_u_2020 dif_2020_u_2021 dif_2021_u_2022
1  90035  88015  76135  50725  16517            2020           11880           25410           34208
2     20     NA     NA  13792  12793              NA              NA              NA             999
3    555    620  15032  19309   6479             -65          -14412           -4277           12830
4  11171  11782  10281   8974   3901            -611            1501            1307            5073
5     NA 116896  40169  13191   3610              NA           76727           26978            9581

data

df1 <- structure(list(u_2018 = c(90035L, 20L, 555L, 11171L, NA), u_2019 = c(88015L, 
NA, 620L, 11782L, 116896L), u_2020 = c(76135L, NA, 15032L, 10281L, 
40169L), u_2021 = c(50725L, 13792L, 19309L, 8974L, 13191L), u_2022 = c(16517L, 
12793L, 6479L, 3901L, 3610L)), class = "data.frame", row.names = c("1", 
"2", "3", "4", "5"))