Search code examples
rsum

Sum column with similar name

I have a Dataset in R like this (my real dataset has more rows and columns):

AB1 AB3 AB4 XB1 XB3 XB4
12 34 0 5 3 7

I need to sum the column similar like

AB1+XB1 AB3+XB3 AB4+XB4

What is the code I can use?

Solution

  • You could use

    library(dplyr)
df %>% 
  mutate(across(starts_with("AB"),
                ~.x + df[[gsub("AB", "XB", cur_column())]],
                .names = "sum_{.col}"))

    This returns

    # A tibble: 1 x 9
    AB1   AB3   AB4   XB1   XB3   XB4 sum_AB1 sum_AB3 sum_AB4
  <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>   <dbl>   <dbl>   <dbl>
1    12    34     0     5     3     7      17      37       7
    • We use across and mutate in this approach.
    • First we select all columns starting with AB. The desired sums are always ABn + XB2, so we can use this pattern.
    • Next we replace AB in the name of the current selected column with XB and sum those two columns. These sums are stored in a new column prefixed with sum_.