Fast way to summarize a data frame across columns

Question

I have this data.frame of five possible character states (genotypes):

genotypes <- c("0/0","1/1","0/1","1/0","./.")
library(dplyr)
set.seed(1)
df <- do.call(rbind, lapply(1:100, function(i)
  matrix(sample(genotypes, 30, replace = T), nrow = 1, dimnames = list(NULL, paste0("V", 1:30))))) %>%
  data.frame()

And I wan to summarize each row into how many I have of each:

ref.hom (0/0)
alt.hom (1/1)
het (0/1 or 1/0)
na (./.)

This seems rather slow:

sum.df <- do.call(rbind,lapply(1:nrow(df), function(i){
  data.frame(ref.hom = length(which(df[i,] == "0/0")),
             alt.hom = length(which(df[i,] == "1/1")),
             het = length(which(df[i,] == "0/1") | which(df[i,] == "1/0")),
             na = length(which(df[i,] == "./.")))
}))

Any more efficient, perhaps dplyr based way to do this?

MAPK · Answer 1 · 2019-08-28T03:19:23.663

4

For genotyping data, I would use setDT(). You will save considerable amount of RAM.

library(data.table)
df$key <- 1:nrow(df)
df <- melt(setDT(df),id.vars = "key")
table(df$key, df$value) 

 # > head(table(df$key, df$value))
 #
 #   ./. 0/0 0/1 1/0 1/1
 # 1   6   6   4   7   7
 # 2   6   3   8   5   8
 # 3   7   3   5   5  10
 # 4   4   8   1   7  10
 # 5   5   9   4   3   9
 # 6   9   2   6   8   5
# and
table(df$value)
# > table(df$value)
# ./. 0/0 0/1 1/0 1/1 
# 620 581 601 584 614

Execution time check:

> time.taken.DT
Time difference of 0.005386114 secs
> time.taken.dplyr
Time difference of 0.08833909 secs

edited Aug 28 '19 at 03:19

answered Aug 27 '19 at 21:18

MAPK

5,635
4
37
88

2

It's worth noting that `setDT()` is from the *data.table* package so people aren't wondering where it suddenly popped up from. – thelatemail Aug 27 '19 at 22:30
Also, if you're taking advantage of data.table, you could use the much more efficient `by=` grouping instead. – thelatemail Aug 27 '19 at 22:50

tmfmnk · Accepted Answer · 2019-08-27T21:49:18.363

3

With dplyr, you can try:

df %>%
 transmute(ref.hom = rowSums(. == "0/0"),
           alt.hom = rowSums(. == "1/1"),
           het = rowSums(. == "0/1") + rowSums(. == "1/0"),
           na = rowSums(. == "./."))

    ref.hom alt.hom het na
1         4      11   9  6
2         5       2  20  3
3         3      11  10  6
4         5       5  15  5
5         5       4  17  4
6         3       8  13  6
7         6       8  11  5
8         4       8  11  7
9         6       6  14  4
10       14       8   5  3

edited Aug 27 '19 at 21:49

answered Aug 27 '19 at 21:14

tmfmnk

38,881
4
47
67

1

hi, how can it be faster with the mutate than without? – Johannes Stötzer Aug 27 '19 at 21:20
@Johannes Stötzer a good point. I confused it with a different situation. – tmfmnk Aug 27 '19 at 21:44

score 1 · Answer 3 · answered Aug 28 '19 at 01:24

In base R, you can use apply with table which will return count of all possible levels in each row.

output <- t(apply(df, 1, table))
output

#     ./. 0/0 0/1 1/0 1/1
#[1,]   7   8   4   3   8
#[2,]   5   7   4   9   5
#[3,]   6   5   6   5   8
#[4,]   4   7   9   6   4
#[5,]   6   5   6   5   8
#[6,]   8   8   2   7   5
#....

Later if you need you can combine the columns into one level output[, 3] + output[, 4].

Another option would be to gather the data into long format and count

library(dplyr)

df %>%
  mutate(row = row_number()) %>%
  tidyr::gather(key, value, -row) %>%
  count(row, value)
  #If needed
  #tidyr::spread(value, n)

Fast way to summarize a data frame across columns

3 Answers3