CODEWITHSUNDEEP
rsum
Rayan Sp
Rayan Sp

Reputation: 1020

Count occurrences of values based on value in different columns

I have table like this. Column names V1, V2, V3, V4

V1 | V2 | V3 | V4
2  |  4 | 3  |  1
2  |  1 | 3  |  1
2  |  3 | NA |  NA
2  |  4 | 3  |  1
3  | NA | NA | NA

How can I count the number of occurrences of the number "3" except if number "1" Comes after it in the same row I don't want it to be counted.

in the above example the occurrence of the Value "3" is 2 times

EDIT:

this is a sample of my data, 31 columns... what I'm trying to do is, I want to count how many times a specific value has occurred except if another specific value came in the very next column.

example from the picture, I would like to count the number of occurrence of value 112, how many times did it occur? except it it from the counting if "116" comes in the next column.

please comment me if more clarification needed.

enter image description here

Upvotes: 0

Views: 613

Answers (2)

DeveauP
DeveauP

Reputation: 1237

In the following code, I assume that "after" means the column to its right.

What you want, is the sum of events: "3 in column x & column x+1 is different from 1".

Which you can translate into the following code (may not be efficient though):

TOTAL<-0    
for( i in 1:(ncol(df)-1){
   TOTAL<-TOTAL + sum(df[,i]==3 & df[,i+1]!=1, na.rm= TRUE)
}

Edit : taking into account last column

TOTAL<-TOTAL+sum(df[,ncol(df)]==3 , na.rm = TRUE)

So the full code, is:

TOTAL<-0    
for( i in 1:(ncol(df)-1){
   TOTAL<-TOTAL + sum(df[,i]==3 & df[,i+1]!=1,na.rm = TRUE)
}
TOTAL<-TOTAL+sum(df[,ncol(df)]==3, na.rm = TRUE)

Upvotes: 2

nicola
nicola

Reputation: 24480

I'd try:

sum(tb[,1:3]==3 & (tb[,2:4]!=1 | is.na(tb[,2:4])),na.rm=TRUE)
#[1] 2

The data I used:

tb <- structure(c(2L, 2L, 2L, 2L, 3L, 4L, 1L, 3L, 4L, NA, 3L, 3L, NA, 
3L, NA, 1L, 1L, NA, 1L, NA), .Dim = c(5L, 4L), .Dimnames = list(
    NULL, c("V1", "V2", "V3", "V4")))

Upvotes: 3

Related Questions