This is a test version of Biostars. For the public version, visit https://www.biostars.org.
Counting all samples numbers for every gene

Is there a method to automate the counting of all the numbers for every gene -including all different variants. Thats for large number of genes over 10000 genes.

Variant numbers_cases numbers_control Gene
1 20 55 ABC
2 48 149 ABC
3 20 456 ABC
4 73 55 TET
5 48 77 TET
6 189 454 TET

Out put I want

| Gene | numbers_cases | numbers_control | 
| ------- | --------- | --------| 
|  ABC |88     |660 |
| TET|310     |586 |
r counting awk

Tidyverse solution.

library("dplyr")

df |>
  group_by(Gene) |>
  summarize(across(!Variant, sum), .groups="drop")

with datamash

$ datamash -H -sg4 sum 2 sum 3 < test.txt

GroupBy(Gene)   sum(numbers_cases)  sum(numbers_control)
ABC 88  660
TET 310 586

with csvtk

$ csvtk -t summary -w 0 -f 2:sum,3:sum -g 4 test.txt | sed '1s/:sum//g'|csvtk -t -s "|" pretty

Gene|numbers_cases|numbers_control
----|-------------|---------------
ABC |88           |660
TET |310          |586

with R data table:

df=fread("test.txt", header = T)
df[,.(numbers_cases=sum(numbers_cases), numbers_control=sum(numbers_control)),by=Gene]

  Gene numbers_cases numbers_control
1:  ABC            88             660
2:  TET           310             586

with base R:

$ aggregate(.~Gene, df[,-1], sum)

  Gene numbers_cases numbers_control
1  ABC            88             660
2  TET           310             586

R, Datamash and csvtk don't come by default with OS. You may have to install via apt, yum, brew, conda etc.

1 answer

Here is a solution in R:

First load the data.

counting = read.csv('counting.csv')

Then create a list of genes to group by.

gene.list = list(counting$Gene)

Then combine your rows.

new.counting = aggregate(x = counting[c('numbers_cases', 'numbers_control')], by = gene.list, FUN = sum)
new.counting

Log in to answer this question.