CODEWITHSUNDEEP
r
Molia
Molia

Reputation: 311

Group by and Count using specified columns in list

I am having some difficulty creating a function which would group by according to column P and count all the "YES" for the columns that have been saved in a list such as "list_col". I am pretty sure I would be using a function from the apply family but not sure how to group it by a certain column (Col P in this case)

P <- as.character(c("A", "A", "A", "A", "A", "B", "B", "B", "B", "B"))
a <- as.character(c(NA,"Yes","Yes",NA,NA,NA,"Yes","Yes","Yes",NA))
b <- as.character(c(NA,"Yes",NA,NA,"Yes",NA,"Yes","Yes",NA,NA))
c <- as.character(c(NA,NA,NA,NA,"Yes",NA,"Yes",NA,NA,NA))

df_sample <- as.data.frame(cbind(P, a, b, c))
df_sample

list_col <- c("a","b","c")

Ideally I would be looking for the following answer with the above sample dataframe and the answer below with the columns changing according to the "list_col"

P   a   b   c
A   2   2   1
B   3   2   1

Any help would be appreciated

Upvotes: 3

Views: 81

Answers (4)

Mike H.
Mike H.

Reputation: 14360

In data.table:

library(data.table)
list_col <- c("a","b","c")
setDT(df_sample)[, (lapply(.SD, function(x) sum(x=="Yes", na.rm = TRUE))), by = P, .SDcols = list_col]

#  P a b c
#1: A 2 2 1
#2: B 3 2 1

Alternatively, a base R solution still using lapply:

res <-lapply(split(df_sample[,list_col], df_sample$P), function(x) colSums(x=="Yes", na.rm = TRUE))
do.call(rbind, res) 
# a b c
#A 2 2 1
#B 3 2 1

For what it's worth, a microbenchmark on my machine:

microbenchmark::microbenchmark(splitlapply = do.call(rbind, lapply(split(df_sample[,list_col], df_sample$P), function(x) colSums(x=="Yes", na.rm = TRUE))),
+                                dt = sampleDT[, (lapply(.SD, function(x) sum(x=="Yes", na.rm = TRUE))), by = P, .SDcols = list_col])
Unit: microseconds
        expr     min        lq      mean    median        uq      max neval
 splitlapply 455.841  505.0715  546.6699  529.3225  561.2315  889.436   100
          dt 861.722 1052.9920 1114.2752 1111.7040 1166.7695 1707.761   100

Upvotes: 2

pzhao
pzhao

Reputation: 335

df_sample <- as.data.frame(cbind(P, a, b, c), stringsAsFactors = FALSE)
for (i in list_col){
  df_r <- df_sample[, i] == 'Yes' & !is.na(df_sample[, i])
  df_sample[df_r, i] <- df_sample$P[df_r]
}
sapply(df_sample[, list_col], table)

Upvotes: 0

BENY
BENY

Reputation: 323236

Using melt from reshape

library(reshape)    
df=na.omit(melt(df_sample,id.vars='P'))
table(df$P,df$variable)

  a b c
A 2 2 1
B 3 2 1

Upvotes: 1

Sotos
Sotos

Reputation: 51592

Here is an approach via dplyr,

library(dplyr)

df_sample %>% 
    group_by(P) %>% 
    select(list_col) %>% 
    summarise_all(funs(sum(. == 'Yes', na.rm = TRUE)))

#Adding missing grouping variables: `P`
# A tibble: 2 x 4
#       P     a     b     c
#  <fctr> <int> <int> <int>
#1      A     2     2     1
#2      B     3     2     1

Upvotes: 3

Related Questions