how to groupby and count on every column in r

Question

I have a following dataframe in r

   Introvert     Extrovert    Biased     Village.Name
    Positive      Negative    Negative     ABC
    Negative      Negative    Negative     ABC
    Negative      Positive    Positive     ABC
    Positive      Negative    Negative     DEF
    Negative      Positive    Positive     DEF
    Negative      Positive    Positive     DEF

I want to count Positive in every columns grouped by Village.Name

My desired dataframe would be

    Village.Name     Introvert    Extrovert    Biased
     ABC                1            1           1
     DEF                1            2           2

How can I do it in R?

`aggregate(.~Village.Name, df, function(x) sum(x=="Positive"))` in base R. — Ronak Shah, Jun 14 '18 at 07:57
`setDT(df)[,lapply(.SD,function(x)sum(x=="Positive")),by="Village.Name"]` in `data.table` — Andre Elrico, Jun 14 '18 at 08:37

score 4 · Accepted Answer · answered Jun 14 '18 at 07:52

We can use a group by summarise_all to get the sum of logical vector (.== "Positive") for each of the columns

library(dplyr)
df1 %>% 
    group_by(Village.Name) %>%
    summarise_all(funs(sum(.=="Positive")))
# A tibble: 2 x 4
#  Village.Name Introvert Extrovert Biased
#  <chr>            <int>     <int>  <int>
#1 ABC                  1         1      1
#2 DEF                  1         2      2

how to groupby and count on every column in r

1 Answers1