Count the non-NA numbers in the column and output the data frame

Question

For example, I have this data frame (df):

Color    X1      X2    X3    X4
Red      1       1     0     2
Blue     0       NA    4     1 
Red      3       4     3     1
Green    2       2     1     0

I would like to create a function that counts the number of non-NA in X2 as a function of color. I would like to get this function in a new data frame called newdf. This is what I would like to output:

Color    X2     
Red      2      
Blue     NA    
Green    1

So far I have this code:

Question <- function(Color){
  Result <-
    rowsum((df[c("X2")] > 0) + 0, df[["X2"]], na.rm = TRUE) 
  rowSums(Result)[[Color]]
  }
  Question("Red") 

The only way out of this function is Question("Red")= 2, and I would like instead to get the results of all colors in a new data frame (newdf). Can anyone help with this? Thank you

Answer 1

Or if you want to use data.table:

library(data.table)

dt[,sum(!is.na(X2)),by=.(Color)]

  Color V1
1:   Red  2
2:  Blue  0
3: Green  1

ifelse() data.table, NA , 0. :

dt[,ifelse(sum(!is.na(X2)==0),as.integer(NA),sum(!is.na(X2))),by=.(Color)]

   Color V1
1:   Red  2
2:  Blue NA
3: Green  1

:

 dt <- as.data.table(fread("Color    X1      X2    X3    X4
Red      1       1     0     2
Blue     0       NA    4     1 
Red      3       4     3     1
Green    2       2     1     0"))

Answer 2

library(dplyr)
df1 <-  df %>%
           group_by(Color) %>%
           summarise(sum(!is.na(X2)))
df1
#  (chr)           (int)
#1   Red               2
#2  Blue               0
#3 Green               1

NA 0,

df1[df1 ==0]<-NA

Answer 3

In the R base, we can use aggregatewith the na.actionhow parameter na.passto allow NAvalues

aggregate(X2~Color, df, function(x) sum(!is.na(x)), na.action = na.pass)

#  Color X2
#1  Blue  0
#2 Green  1
#3   Red  2