Groupby and count number of element in df depending on pattern

user_9581909 0
Viewed 28

Hel lo I have a df such as :

Col1 Col2
Group1 Seq_1:(+)Sp1
Group1 Prot_1-A
Group1 Seq_1:(-)Sp2
Group1 Prot_1B
Group1 Seq_2:(-)Sp2
Group1 Seq_1:(+)Sp3 
Group1 Prot_1C
Group2 Seq_1:Sp5 
Group2 Prot_1-E
Group2 Prot_1G
Group2 Seq_2:(+)Sp5 
Group2 Seq_1:(+)Sp6 
Group3 Seq_1:(-)Sp7 
Group3 Prot_1H-AB
Group3 Seq_1:(+)Sp7 Prot_1Y

and I would like grouby each group in Col1: - count the number of different Col2 values that do not have a (+) or a (-) in its element. - count the number of different Col2 values that do have a (+) or a (-) in its element.

ex Group1:

  • Nb seq with (+) or (-) = 4
  • Nb seq without (+) or (-) = 3

Group3:

  • Nb seq with (+) or (-) = 2
  • Nb seq without (+) or (-) = 1
count r group-by
2 Answers

We can group by 'Col1', get the sum of 'Col2' where there are + or - wrapped within (), and those without that

library(dplyr)
library(stringr)
df1 %>%
    group_by(Col1) %>%
    summarise(Sum_minus_plus = sum(str_detect(Col2, "\\([+-]\\)")),
              No_minus_plus = n() - sum(str_detect(Col2, '^Seq')))
# A tibble: 3 x 3
#  Col1   Sum_minus_plus No_minus_plus
#  <chr>           <int>         <int>
#1 Group1              4             3
#2 Group2              2             2
#3 Group3              2             1

data

df1 <- structure(list(Col1 = c("Group1", "Group1", "Group1", "Group1", 
"Group1", "Group1", "Group1", "Group2", "Group2", "Group2", "Group2", 
"Group2", "Group3", "Group3", "Group3"), Col2 = c("Seq_1:(+)Sp1", 
"Prot_1-A", "Seq_1:(-)Sp2", "Prot_1B", "Seq_2:(-)Sp2", "Seq_1:(+)Sp3", 
"Prot_1C", "Seq_1:Sp5", "Prot_1-E", "Prot_1G", "Seq_2:(+)Sp5", 
"Seq_1:(+)Sp6", "Seq_1:(-)Sp7", "Prot_1H-AB", "Seq_1:(+)Sp7 Prot_1Y"
)), class = "data.frame", row.names = c(NA, -15L))
user_37322710

Data tables allow expressions in by (or keyby) so you can set a grouping argument to those that do or do not contain (+) or (-) and then the .N special symbol to get counts:

library(data.table)
setDT(df1)
df1[, .N, keyby = .(Col1, Col2 %like% "\\([-+]\\)")]

Giving

     Col1  Col2 N
1: Group1 FALSE 3
2: Group1  TRUE 4
3: Group2 FALSE 3
4: Group2  TRUE 2
5: Group3 FALSE 1
6: Group3  TRUE 2
user_10400350
Related