How to substract multiple .x from .y with same prefixes

user_13321647 0
Viewed 99

I have this tibble:

# A tibble: 2 x 8
    a.x   b.x   c.x   d.x   a.y   b.y   c.y   d.y
  <int> <int> <int> <int> <int> <int> <int> <int>
1    13    13    12    11     7     1     4     2
2    17    11     0     0    16     2     0     0

df <- structure(list(a.x = c(13L, 17L), b.x = c(13L, 11L), c.x = c(12L, 
0L), d.x = c(11L, 0L), a.y = c(7L, 16L), b.y = 1:2, c.y = c(4L, 
0L), d.y = c(2L, 0L)), row.names = c(NA, -2L), class = c("tbl_df", 
"tbl", "data.frame"))

I want to calculate: a.x - a.y, b.x - b.y, c.x - c.y, and so on ....

My desired output:

    a.x   b.x   c.x   d.x   a.y   b.y   c.y   d.y     a     b     c     d
  <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int>
1    13    13    12    11     7     1     4     2     6    12     8     9
2    17    11     0     0    16     2     0     0     1     9     0     0

I can achieve this by:

df %>% 
    mutate(a = a.x-a.y,
           b = b.x-b.y,
           c = c.x-c.y,
           d = d.x-d.y)

I want to learn:

  1. How to extract the prefixes to new column names.
  2. Automate the calculation .x - .y.
difference dplyr r
4 Answers

One method with cur_column - loop over the columns that ends_with .x, replace the substring in the column name (cur_column()) by changing the 'x' to 'y', get the value, subtract and change the column names in .names

library(dplyr)
library(stringr)
df %>% 
   mutate(across(ends_with('.x'),
     ~ . - get(str_replace(cur_column(), 'x', 'y')), 
         .names = "{str_remove(.col, fixed('.x'))}"))

-output

# A tibble: 2 x 12
    a.x   b.x   c.x   d.x   a.y   b.y   c.y   d.y     a     b     c     d
  <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int>
1    13    13    12    11     7     1     4     2     6    12     8     9
2    17    11     0     0    16     2     0     0     1     9     0     0

or with reshaping by pivot_longer

library(tidyr)
df %>%
     mutate(rn = row_number()) %>%
     pivot_longer(cols = -rn, names_to = c(".value"), 
          names_pattern = "(.)\\..*") %>% 
     group_by(rn) %>% 
     summarise(across(everything(), ~ -diff(.))) %>%
     select(-rn) %>%
     bind_cols(df, .)
# A tibble: 2 x 12
 a.x   b.x   c.x   d.x   a.y   b.y   c.y   d.y     a     b     c     d
  <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int>
1    13    13    12    11     7     1     4     2     6    12     8     9
2    17    11     0     0    16     2     0     0     1     9     0     0
user_37322710

A base R approach for you:

cbind(df, mapply(\(x, y) x - y, df[endsWith(names(df), ".x")],
                 df[endsWith(names(df), ".y")]) |>
        as.data.frame() |>
        setNames(letters[seq_len(ncol(df)/2)]))

  a.x b.x c.x d.x a.y b.y c.y d.y a  b c d
1  13  13  12  11   7   1   4   2 6 12 8 9
2  17  11   0   0  16   2   0   0 1  9 0 0

A similar tidyverse solution:

library(dplyr)
library(purrr)

df %>%
  bind_cols(
    map2_df(".x", ".y", ~ df[grepl(.x, names(df))] - df[grepl(.y, names(df))]) %>%
      rename_with(~ gsub(".x", "", .), everything())
  )

A very simple and compact approach suggested by dear @Henrik

cbind(df, setNames(df[endsWith(names(df), ".x")] - df[endsWith(names(df), ".y")], 
                   sub("\\..*","", names(df[endsWith(names(df), ".x")]))))
user_143145200

I have a package on github {dplyover} for this kind of operations. We could use dplyover::across2 to make the calculation. If we specify "{pre}" in the .names argument, we can extract the common prefix of each pair of variables.

The main advantage to regular {dplyr} solutions is that we do not necessarily need columns with similar names. The downside is that across2 is less performant than dplyr::across.

library(dplyr)
library(dplyover) # https://github.com/TimTeaFan/dplyover


df %>%
  mutate(across2(ends_with(".x"),
                 ends_with(".y"),
                 ~ .x - .y,
                 .names = "{pre}"))

#> # A tibble: 2 x 12
#>     a.x   b.x   c.x   d.x   a.y   b.y   c.y   d.y     a     b     c     d
#>   <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int>
#> 1    13    13    12    11     7     1     4     2     6    12     8     9
#> 2    17    11     0     0    16     2     0     0     1     9     0     0

Created on 2021-07-26 by the reprex package (v0.3.0)

user_93493020

Another way is to do:

df %>%
  mutate(across(ends_with('x'),  .names = "{str_remove(.col, '.x')}")
         - across(ends_with('y')))
# A tibble: 2 x 12
    a.x   b.x   c.x   d.x   a.y   b.y   c.y   d.y     a     b     c     d
  <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int>
1    13    13    12    11     7     1     4     2     6    12     8     9
2    17    11     0     0    16     2     0     0     1     9     0     0

In base R, you could use the split.default:

a <- do.call('-', split.default(df, sub('.', '', names(df)))) 
cbind(df, setNames(a, sub('..$', '', names(a))))
  a.x b.x c.x d.x a.y b.y c.y d.y a  b c d
1  13  13  12  11   7   1   4   2 6 12 8 9
2  17  11   0   0  16   2   0   0 1  9 0 0
user_83802720
Related