Sum rows in data.frame or matrix
RDataframeMatrixRowsumR Problem Overview
I have a very large dataframe with rows as observations and columns as genetic markers. I would like to create a new column that contains the sum of a select number of columns for each observation using R.
If I have 200 columns and 100 rows, then I would like a to create a new column that has 100 rows with the sum of say columns 43 through 167. The columns have either 1 or 0. With the new column that contains the sum of each row, I will be able to sort the individuals who have the most genetic markers.
I feel it is something close to:
data$new=sum(data$[,43:167])
R Solutions
Solution 1 - R
you can use rowSums
rowSums(data) should give you what you want.
Solution 2 - R
The rowSums function (as Greg mentions) will do what you want, but you are mixing subsetting techniques in your answer, do not use "$" when using "[]", your code should look something more like:
data$new <- rowSums( data[,43:167] )
If you want to use a function other than sum, then look at ?apply for applying general functions accross rows or columns.
Solution 3 - R
I came here hoping to find a way to get the sum across all columns in a data table and run into issues implementing the above solutions. A way to add a column with the sum across all columns uses the cbind function:
cbind(data, total = rowSums(data))
This method adds a total column to the data and avoids the alignment issue yielded when trying to sum across ALL columns using the above solutions (see the post below for a discussion of this issue).
https://stackoverflow.com/questions/27790670/adding-a-new-column-to-matrix-error
Solution 4 - R
I will try to support you with the elapsed time for each method by an example:
mat = matrix(runif(4e6), ncol = 50)
Comprison between apply function and rowSums:
apply_func <- function(x) {
apply(x, 1, sum)
}
r_sum <- function(x) {
rowSums(x)
}
# Compare the methods
microbenchmark(
apply_func = app(mat),
r_sum = r_sum(mat), times = 1e5
)
------ output -- in milliseconds --------
expr min lq mean median uq max neval
apply_func 207.84661 260.34475 280.14621 279.18782 294.85119 354.1821 100
r_sum 10.76534 11.53194 13.00324 12.72792 14.34045 16.9014 100
As you notice that the mean time for the rowSums function is 21 times smaller than the mean time of the apply function. You will find that the difference in the elapsed time may be more significant if the matrix has too many columns.
Solution 5 - R
This could also help, however the best option is beyond any doubt the rowSums function:
data$new <- Reduce(function(x, y) {
x + data[, y]
}, init = data[, 43], 44:167)
Solution 6 - R
You can also use this function adorn_totals from janitor package. You can sum the columns or the rows depending on the value you give to the arg: where.
Example:
tibble::tibble(
a = 10:20,
b = 55:65,
c = 2010:2020,
d = c(LETTERS[1:11])) %>%
janitor::adorn_totals(where = "col") %>%
tibble::as_tibble()
Result:
# A tibble: 11 x 5
a b c d Total
<int> <int> <int> <chr> <dbl>
1 10 55 2010 A 2065
2 11 56 2011 B 2067
3 12 57 2012 C 2069
4 13 58 2013 D 2071
5 14 59 2014 E 2073
6 15 60 2015 F 2075
7 16 61 2016 G 2077
8 17 62 2017 H 2079
9 18 63 2018 I 2081
10 19 64 2019 J 2083
11 20 65 2020 K 2085
Solution 7 - R
Just for completeness. I will list other methods no mentioned here this is different ways for do it the same thing using dplyr syntax with a matrix:
mat = matrix(1:12, ncol = 3)
library(dplyr)
mat %>% as_tibble() %>%
mutate(sum = rowSums(across(where(is.numeric))))
# A tibble: 4 x 4
V1 V2 V3 sum
<int> <int> <int> <dbl>
1 1 5 9 15
2 2 6 10 18
3 3 7 11 21
4 4 8 12 24
or c_across:
mat %>% as_tibble() %>%
rowwise() %>%
mutate(sumrange = sum(c_across(), na.rm = T))
or selecting specific column by column name:
mat %>% as_tibble() %>%
mutate( 'B1' = V1, B2 = V2) %>%
rowwise() %>%
mutate(sum_startswithB =
sum(c_across(starts_with("B")), na.rm = T))
V1 V2 V3 B1 B2 sum_startswithx
<int> <int> <int> <int> <int> <int>
1 1 5 9 1 5 6
2 2 6 10 2 6 8
3 3 7 11 3 7 10
4 4 8 12 4 8 12
by column index in this case the first column to 4th column :
mat %>% as_tibble() %>%
mutate( 'B1' = V1, B2 = V2) %>%
rowwise() %>%
mutate(SumByIndex = sum(c_across(c(1:4)), na.rm = T))
V1 V2 V3 B1 B2 SumByIndex
<int> <int> <int> <int> <int> <int>
1 1 5 9 1 5 16
2 2 6 10 2 6 20
3 3 7 11 3 7 24
4 4 8 12 4 8 28
Using Regular Expresion:
mat %>% as_tibble() %>%
mutate( 'B1' = V1, B2 = V2) %>%
mutate(sum_V = rowSums(.[grep("V[2-3]", names(.))], na.rm = TRUE),
sum_B = rowSums(.[grep("B", names(.))], na.rm = TRUE))
V1 V2 V3 B1 B2 sum_V sum_B
<int> <int> <int> <int> <int> <dbl> <dbl>
1 1 5 9 1 5 14 6
2 2 6 10 2 6 16 8
3 3 7 11 3 7 18 10
4 4 8 12 4 8 20 12
Using Apply Funcion is more handy because you can choose sum, mean, max, min, variance and standard deviation across columns.
mat %>% as_tibble() %>%
mutate( 'B1' = V1, B2 = V2) %>%
mutate(sum = select(., V1:B1) %>% apply(1, sum, na.rm=TRUE)) %>%
mutate(mean = select(., V1:B1) %>% apply(1, mean, na.rm=TRUE)) %>%
mutate(max = select(., V1:B1) %>% apply(1, max, na.rm=TRUE)) %>%
mutate(min = select(., V1:B1) %>% apply(1, min, na.rm=TRUE)) %>%
mutate(var = select(., V1:B1) %>% apply(1, var, na.rm=TRUE)) %>%
mutate(sd = select(., V1:B1) %>% apply(1, sd, na.rm=TRUE))
V1 V2 V3 B1 B2 sum mean max min var sd
<int> <int> <int> <int> <int> <int> <dbl> <int> <int> <dbl> <dbl>
1 1 5 9 1 5 16 4 9 1 14.7 3.83
2 2 6 10 2 6 20 5 10 2 14.7 3.83
3 3 7 11 3 7 24 6 11 3 14.7 3.83
4 4 8 12 4 8 28 7 12 4 14.7 3.83
Note: the var and sd same output is not an error is because the data is generated linearly 1:12 you can verify calculating the values of the first columns:
> sd(c(1,5,9,1))
[1] 3.829708
> sd(c(2,6,10,2))
[1] 3.829708