CODEWITHSUNDEEP
rdata.table
CelineDion
CelineDion

Reputation: 1078

How do I count the number of rows I have summed values in 1 column based on matching values in 3 other columns [R]?

I have a table which looks like this:

> dt
                      variant_id           transcript_id
       1:  chr1_37492738_T_C_b38  chr1_37557076_37557602
       2:  chr1_37492738_T_C_b38  chr1_37557076_37557602
       3:  chr1_37492738_T_C_b38  chr1_37557076_37557602
       4:  chr1_37492738_T_C_b38  chr1_37557076_37557602
       5:  chr1_37492738_T_C_b38  chr1_37557076_37557602
      ---
13527497: chr22_49950090_T_G_b38 chr22_49925558_49927254
13527498: chr22_49950090_T_G_b38 chr22_49925558_49927254
13527499: chr22_49950090_T_G_b38 chr22_49925558_49927254
13527500: chr22_49950090_T_G_b38 chr22_49925558_49927254
13527501: chr22_49950090_T_G_b38 chr22_49925558_49927254
                         tissue_id counts individual is_NL
       1: GTEX-11DXX-1426-SM-5GIDU     46 GTEX-11DXX     0
       2: GTEX-11EM3-1726-SM-5N9D1     54 GTEX-11EM3     0
       3: GTEX-11EMC-1726-SM-5H11P     61 GTEX-11EMC     0
       4: GTEX-11GSP-0226-SM-5A5KV     44 GTEX-11GSP     0
       5: GTEX-11I78-1926-SM-59878     27 GTEX-11I78     0
      ---
13527497:  GTEX-ZVT2-0326-SM-5E44G    110  GTEX-ZVT2     1
13527498:  GTEX-ZVT3-2626-SM-5GU5L     54  GTEX-ZVT3     1
13527499:  GTEX-ZYFG-1726-SM-5GZZB     66  GTEX-ZYFG     1
13527500:  GTEX-ZYY3-2726-SM-5EGH4     96  GTEX-ZYY3     1
13527501:  GTEX-ZZPU-2126-SM-5EGIU     75  GTEX-ZZPU     0

I was successfully able to sum up values in dt$counts by using the line: dt2 <- as.data.table(ddply(dt, c("variant_id", "transcript_id", "is_NL"), numcolwise(sum)))

making the result look like this:

> dt2
                     variant_id             transcript_id is_NL counts
     1: chr10_125381862_C_T_b38 chr10_124989699_124992694     0  30610
     2: chr10_125381862_C_T_b38 chr10_124989699_124992694     1   1932
     3: chr10_125381862_C_T_b38 chr10_124992813_124993201     0  28215
     4: chr10_125381862_C_T_b38 chr10_124992813_124993201     1   1706
     5: chr10_125381862_C_T_b38 chr10_124993330_124993854     0  17974
    ---
232637:   chr9_92815645_A_C_b38    chr9_92517876_92522574     1   2009
232638:   chr9_92815645_A_C_b38    chr9_92522894_92535932     0  10026
232639:   chr9_92815645_A_C_b38    chr9_92522894_92535932     1   1454
232640:   chr9_92815645_A_C_b38    chr9_92535983_92536600     0   2495
232641:   chr9_92815645_A_C_b38    chr9_92535983_92536600     1    341

However, I would also like a column to the right that also indicates the number of rows over which the values of dt2$counts were summed i.e. number of samples per group of matching c("variant_id", "transcript_id", "is_NL"). How would I go about doing this? I could figure it out if it were Python or Java but unfortunately with R, I don't know where I would even start.

Upvotes: 0

Views: 55

Answers (3)

AffableAmbler
AffableAmbler

Reputation: 427

Using dplyr:

library(dplyr)
dt2<- dt %>%
      group_by(variant_id, transcript_id, is_nl) %>%
      summarise(counts=sum(counts), nrows=n())

Upvotes: 1

chinsoon12
chinsoon12

Reputation: 25225

In data.table, it is:

setDT(dt)[, .(counts=sum(counts), .N), .(variant_id, transcript_id, is_NL)]

Upvotes: 0

Salix
Salix

Reputation: 1384

You could also just use the aggregate function which doesn't require any library :

dt2 <- aggregate(counts ~ variant_id+transcript_id+is_NL, dt, function(x) c(sum = sum(x), n = length(x)))

Upvotes: 0

Related Questions