Combine counts in categorized dataframe by regex in group_by?

user_11098023 0
Viewed 52

I'm working with a large genetics dataset. Each unique "gene" has "chains", aka groups, which I then need a count of. However, I'd also like to reduce the "gene" column into more generic groups but retain the "chain" category and combine the "count".

I've run the following to initially reduce the data:

df1 <- huge_dataset %>% 
  dplyr::group_by(gene) %>% 
  dplyr::count(chain, name="count")

which can be reproduced with this:

df1 <- data.frame("gene"= c("IGHA1", "IGHA1", "IGHA2","IGHA2", "IGHG1", "IGHG1", "IGHG2", "IGHG2"),
               "chain"= c("IGK", "IGL","IGK", "IGL","IGK", "IGL", "IGK", "IGL"),
               "count" = c(30, 12, 18, 19, 57, 109, 62, 34))

Then I want to combine entries in the "gene" column by the uniqueness of the fourth letter using a regex (i.e. regex--"IGH.") of each gene, and combine the "count" columns but also retain the categorization by the "chain" column. Resulting in this transformation from df1:

df2 <- data.frame("gene"= c("IGHA", "IGHA", "IGHG", "IGHG"),
                 "chain"= c("IGK", "IGL","IGK", "IGL"),
                 "count" = c(48, 31, 119, 143))

Is there a tidy/elegant solution to either

  1. Alter my original group_by() statement with the regex or
  2. Reduce df1 to df2?

I have to eventually vectorize the statement for several different gene iterations so I would rather not individually regex by each individual gene separately

dplyr r
4 Answers

You can do:

df1 %>%
 group_by(gene = substr(gene, 1, 4), chain) %>%
 summarise(count = sum(count))

  gene  chain count
  <chr> <fct> <dbl>
1 IGHA  IGK      48
2 IGHA  IGL      31
3 IGHG  IGK     119
4 IGHG  IGL     143
user_59645570

Using base R's aggregate : 

aggregate(count~ gene + chain, 
          transform(df1, gene = sub('(.{4}).*', '\\1', gene)), sum)

#  gene chain count
#1 IGHA   IGK    48
#2 IGHG   IGK   119
#3 IGHA   IGL    31
#4 IGHG   IGL   143
user_39629140

An approach using count instead of group_by + summarise. Also. As far as I get it you can do the computation directly on huge_dataset:

library(dplyr)
library(stringr)
df1 %>%
  count(gene = stringr::str_extract(gene, "[A-Z]{4}"), chain, wt = count, name = "count")

# Or apply it directly on huge_dataset 
huge_dataset %>%
  count(gene = stringr::str_extract(gene, "[A-Z]{4}"), chain, name = "count")
user_129938610

An option using data.table

library(data.table)
setDT(df1)[ ,.(count = sum(count)), .(gene = substr(gene, 1, 4), chain)]
user_37322710
Related