Counting all samples numbers for every gene

Question

[Nova]

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 |

[cpad0112]

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.

[rpolicastro]

Tidyverse solution.

library("dplyr")

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

Answer 1

[Jeremy]

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