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.
rowwise() allows you to compute on a data frame a row-at-a-time. This is most useful when a vectorised function doesn't exist. Most dplyr verbs preserve row-wise grouping. The exception is summarise() , which return a grouped_df.
Row wise sum of the dataframe using dplyr: Method 1 rowSums() function takes up the columns 2 to 4 and performs the row wise operation with NA values replaced to zero. row wise sum is performed using pipe (%>%) operator of the dplyr package.
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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With