Sum columns row-wise with similar names

Question

I have a dataframe that has lots of columns that are something like this:

data <- data.frame (a.1 = 1:5, a.2b = 3:7, a.5 = 5:9, bt.16 = 4:8, bt.12342 = 7:11)

I'd like a result with columns that sum the variables that have the same prefix. In this example, I want to return a dataframe: a = (9:13), bt = (11:15)

My real data set is quite a bit more complicated (I want to combine page view counts for web pages with different utm parameters) but a solution for this case should put me on the right track.

Answer 1

Here a solution with base R:

> prefixes = unique(sub("\\..*", "", colnames(data)))
> sapply(prefixes, function(x)rowSums(data[,startsWith(colnames(data), x)]))
      a bt
[1,]  9 11
[2,] 12 13
[3,] 15 15
[4,] 18 17
[5,] 21 19

Answer 2

You can try

library(tidyverse)
data.frame (a.1 = 1:5, a.2b = 3:7, a.5 = 5:9, bt.16 = 4:8, bt.12342 = 7:11) %>% 
  rownames_to_column() %>% 
  gather(k, v, -rowname) %>% 
  separate(k, letters[1:2]) %>% 
  group_by(rowname, a) %>% 
  summarise(Sum=sum(v)) %>% 
  spread(a, Sum)
#> # A tibble: 5 x 3
#> # Groups:   rowname [5]
#>   rowname     a    bt
#>   <chr>   <int> <int>
#> 1 1           9    11
#> 2 2          12    13
#> 3 3          15    15
#> 4 4          18    17
#> 5 5          21    19

Created on 2018-04-16 by the reprex package (v0.2.0).

You can also do:

data.frame (a.1 = 1:5, a.2b = 3:7, a.5 = 5:9, bt.16 = 4:8, bt.12342 = 7:11) %>% 
  rownames_to_column() %>% 
  pivot_longer(-1, names_to = c(".value", "set"), names_sep = "[.]") %>% 
  group_by(rowname) %>% 
  summarise(across(a:bt,sum, na.rm=T))
# A tibble: 5 x 3
  rowname     a    bt
  <chr>   <int> <int>
1 1           9    11
2 2          12    13
3 3          15    15
4 4          18    17
5 5          21    19