Conditional Count of changes over time?

Question

Hi all,

I´d like to count the number of changes of binary factor variable. This variable can change from time to time back and forth multiple times for every user id. Now I´d like to count he number of changes per user id to this variable over a given timespan.

The data is sorted by id,year,month,myfactor. I tried this in MySQL but had no success so far. Is there an easy way to do it in R? I though about adding another column to my data.frame and adding up conditions step by step... Maybe some %in% stuff ?

Thx in advance for suggestions...

Hmm, of course... here´s some example – sorry for not providing it immediately, my head hurts ;):

---

   myf   Year    month userid   
  1 A    2005       1    260           
  2 B    2005       2    260           
  3 B    2005       4    260           
  4 A    2005       5    260           
  5 B    2005       6    260           
  6 B    2005       1    261 

if this is my dataset, I want to update the changes column, counting the number of changes of myf per user. Basically id like to end up with:

  user  changes
   260     3
   260     0

and so forth...

HTH

Answer 1

Here's my guess.

set.seed(21)
Data <- data.frame(id=sample(letters[1:3],20,TRUE),
                   date=sample(1:3,20,TRUE),
                   myfactor=sample(0:1,20,TRUE))
Data <- Data[order(Data$id,Data$date),]

DataCh <- aggregate(Data[,"myfactor",FALSE],
            by=Data[,c("id","date")], function(x) sum(diff(x)!=0))
DataCh <- DataCh[order(DataCh$id,DataCh$date),]

EDIT: Here's an update with your example data.

lines <- "   myf   Year    month userid   
 1 A    2005       1    260           
 2 B    2005       2    260           
 3 B    2005       4    260           
 4 A    2005       5    260           
 5 B    2005       6    260           
 6 B    2005       1    261 "

Data <- read.table(con <- textConnection(lines)); close(con)

DataCh <- aggregate(Data[,"myf",FALSE],
            by=Data[,"userid",FALSE], function(x) sum(diff(unclass(x))!=0))

merge(Data,DataCh,by="userid",suffixes=c("",".change"))
#   userid myf Year month myf.change
# 1    260   A 2005     1          3
# 2    260   B 2005     2          3
# 3    260   B 2005     4          3
# 4    260   A 2005     5          3
# 5    260   B 2005     6          3
# 6    261   B 2005     1          0

Answer 2

#Some data
dfr <- data.frame(
   binary_variable = runif(100) < .7,
   id = sample(7, 100, replace = TRUE)
)

#Split by id
split_by_id <- with(dfr, split(binary_variable, id))

#Number of changes
sapply(split_by_id, function(x) sum(diff(x) != 0))

Answer 3

Another edit :

Given your responses on the other solutions, you could get what you want in one line:

Data$extra <- ave(as.integer(Data$myf),Data$id,FUN=function(x) sum(diff(x)!=0))

No merge needed in this case.

---

"Over a given timespan" means that you could select a timespan and then apply the function. Joshuas answer is the fastest way around. There's a more general function that gives you more information on run lengths and values, rle. Be sure to check that one out.

Based on Joshuas answer, this example shows you how you can easily work with the dates to select a given timespan.

Edit: I updated the answer to show you how to easily convert your columns year and month into a date. You should also use as.numeric when applying the whole thing on a factor like yours.

#Testdata
set.seed(21)
Data <- data.frame(id=rep(letters[1:3],each=24),
                   year= rep(rep(c(2005,2006),each=12),6),
                   month=rep(1:12,6),
                   myf=sample(c("A","B"),24*3,TRUE))

#transformation
Data$dates <- as.Date(paste(Data$year,Data$month,"1",sep="-"))
#function

cond.count <- function(from,to,data){
    x <- data[data$dates>from & data$dates<to,]
    tapply(as.numeric(x$myf),x$id,function(y)sum(diff(y)!=0))
}

#example
from <- as.Date("2005-01-01")
to <- as.Date("2006-04-15")

cond.count(from,to,Data)