countif like excel function in r

user_3936919 0
Viewed 50

I want to fill counts of every criteria in a cell using a row-wise ranges like excel. I need to do this thing for 100s of file. So, I wan to use R to finish it quickly. I've a table where I want to insert a new column with counts of number in col1 from the same row.

    col1 col5 col7 col8 col9 col10 col11 col12
1    0    A    0    0    0     0     0     0
2    1    A    1    1    1     1     1     1
3    1    A    1    1    2     1     1     1
4    2    A    2    2    2     2     2     2
5    0    A    0    0    0     0     0     0
6    0    A    0    1    0     0     0     0

Something like this:

    col1 counts col5 col7 col8 col9 col10 col11 col12
1    0      6    A    0    0    0     0     0     0
2    1      6    A    1    1    1     1     1     1
3    1      5    A    1    1    2     1     1     1
4    2      6    A    2    2    2     2     2     2
5    0      6    A    0    0    0     0     0     0
6    0      5    A    0    1    0     0     0     0

Excel code is like this

=COUNTIF(D2:I2,A2)

To access the data

dput(all)
structure(list(col1 = c(0L, 1L, 1L, 2L, 0L, 0L), col5 = c("A", 
"A", "A", "A", "A", "A"), col7 = c(0L, 1L, 1L, 2L, 0L, 0L), col8 = c(0L, 
1L, 1L, 2L, 0L, 1L), col9 = c(0L, 1L, 2L, 2L, 0L, 0L), col10 = c(0L, 
1L, 1L, 2L, 0L, 0L), col11 = c(0L, 1L, 1L, 2L, 0L, 0L), col12 = c(0L, 
1L, 1L, 2L, 0L, 0L)), class = "data.frame", row.names = c(NA, 
-6L))

I hope, it is possible through r.

Thanks,

r excel
2 Answers

NB: all() is also a base r-function... so it might not be the best move to name your object all

library(data.table)
setDT(all)[, counts := rowSums(.SD == all$col1), .SDcols = names(all)[-c(1:2)]][]
#    col1 col5 col7 col8 col9 col10 col11 col12 counts
# 1:    0    A    0    0    0     0     0     0      6
# 2:    1    A    1    1    1     1     1     1      6
# 3:    1    A    1    1    2     1     1     1      5
# 4:    2    A    2    2    2     2     2     2      6
# 5:    0    A    0    0    0     0     0     0      6
# 6:    0    A    0    1    0     0     0     0      5
user_63562780

You can just use rowSums().

df$counts <- rowSums(df[,paste0("col", 7:12)] == df$col1)
df$counts
#> [1] 6 6 5 6 6 5
user_103573830
Related