count the number of columns for each row by condition on character and missing

Question

I want to count the number of columns for each row by condition on character and missing.

For example, I have this dataset, test.

I want to create num columns, counting the number of columns 'not' in missing or empty value.

a<-c("aa","bb","cc","dd","",NA)
b<-c("",NA,"aa","","","dd")
c<-c("aa","",NA,NA,"cc","dd")
d<-c("aa","bb","",NA,"cc","dd")
test<-data.frame(cbind(a,b,c,d))

     a    b    c    d
1   aa        aa   aa
2   bb <NA>        bb
3   cc   aa <NA>     
4   dd      <NA> <NA>
5             cc   cc
6 <NA>   dd   dd   dd

I want to count the number of columns containing NA and empty value like

     a    b    c    d   num
1   aa        aa   aa   3
2   bb <NA>        bb   2
3   cc   aa <NA>        2
4   dd      <NA> <NA>   1
5             cc   cc   2
6 <NA>   dd   dd   dd   3

I tried some approach in other posts, like rowSums

Count number of columns by a condition (>) for each row

> test$num<-rowSums(test!=c("",NA),na.rm=T)
> test
     a    b    c    d num
1   aa        aa   aa   3
2   bb <NA>        bb   0
3   cc   aa <NA>        2
4   dd      <NA> <NA>   0
5             cc   cc   2
6 <NA>   dd   dd   dd   0

However, it returns wrong numbers, and I couldn't find the reasons.

Would you let me know how to solve this problem?

ThomasIsCoding · Answer 1 · 2020-02-07T10:30:38.900

5

You can use nchar + rowSums

test$num <- rowSums(nchar(as.matrix(test))>1,na.rm = TRUE)

or %in% + rowSums

test$num <- rowSums(`dim<-`(!as.matrix(test) %in% c("",NA),dim(test)))

such that

> test
     a    b    c    d num
1   aa        aa   aa   3
2   bb <NA>        bb   2
3   cc   aa <NA>        2
4   dd      <NA> <NA>   1
5             cc   cc   2
6 <NA>   dd   dd   dd   3

edited Feb 07 '20 at 10:30

answered Feb 07 '20 at 08:34

ThomasIsCoding

96,636
9
24
81

score 4 · Accepted Answer · answered Feb 07 '20 at 08:22

4

You could use rowSums to count number of NAs or empty values in each row and then subtract it from number of columns in the dataframe.

test$num <- ncol(test) - rowSums(is.na(test) | test == "")
test
#     a    b    c    d num
#1   aa        aa   aa   3
#2   bb <NA>        bb   2
#3   cc   aa <NA>        2
#4   dd      <NA> <NA>   1
#5             cc   cc   2
#6 <NA>   dd   dd   dd   3

answered Feb 07 '20 at 08:22

Ronak Shah

377,200
20
156
213

Deleted my answer since it was too close to yours: `rowSums(test != "" & !is.na(test))` – s_baldur Feb 07 '20 at 09:32
@sindri_baldur I think it is different enough to be a separate answer. – Ronak Shah Feb 07 '20 at 09:37

score 3 · Answer 3 · answered Feb 07 '20 at 08:29

3

Another idea using rowSums is to replace empty with NA, i.e.

rowSums(!is.na(replace(test, test == '', NA)))
#[1] 3 2 2 1 2 3

answered Feb 07 '20 at 08:29

Sotos

51,121
6
32
66

huttoncp · Answer 4 · 2020-02-08T02:11:38.833

How about this approach from the tidyverse which also tells you how many columns contain NAs or empty strings?

a<-c("aa","bb","cc","dd","",NA)
b<-c("",NA,"aa","","","dd")
c<-c("aa","",NA,NA,"cc","dd")
d<-c("aa","bb","",NA,"cc","dd")
test<-data.frame(cbind(a,b,c,d))

library(magrittr) #import the pipe operator

num_cols <- test %>% 
    tibble::rowid_to_column("row_id") %>% #1st add a rowid column 
    dplyr::group_by(row_id) %>% #split the data into single row groups (i.e. 
                                #row vectors)
    tidyr::nest() %>% #turn it into a list column called data
    dplyr::mutate(num_NAs = purrr::map_dbl(data, #loop over the data column of row 
                                                  #vectors using map_dbl
                                     ~sum(is.na(.))), #count the number of NAs
                  num_empty = purrr::map_dbl(data, 
                                         #count the empty strings 
                                         ~sum(. == "", na.rm = T)),
                  num_values = purrr::map_dbl(data, 
                                          #count columns without NAs or 
                                          #missing values (what you asked for)
                                          ~length(.)-sum(num_NAs, num_empty))
            ) %>%
    dplyr::ungroup() %>% #remove the grouping structure
    dplyr::select(num_NAs, num_empty, num_values) #extract only the variables you need

test_v2 <- cbind(test, num_cols)
test_v2  

     a    b    c    d num_NAs num_empty num_values
1   aa        aa   aa       0         1          3
2   bb <NA>        bb       1         1          2
3   cc   aa <NA>            1         1          2
4   dd      <NA> <NA>       2         1          1
5             cc   cc       0         2          2
6 <NA>   dd   dd   dd       1         0          3

While this solution requires more code than the other options, it gives you more information (should you want it). You could also easily insert a select() layer at the beginning (before the group_by layer) to only apply these counting rules to a subset of columns if you didn't want to get counts for the entire dataframe. — huttoncp, Feb 08 '20 at 02:14

count the number of columns for each row by condition on character and missing

4 Answers4