R counting appearance of a value across consecutive dates

Question

I am trying to count the appearances of a value (across 2 columns) consecutively over the previous days. In the example this would be counting the consecutive days a team made an appearance (either in Hteam or Ateam) prior to that date. The aim would be to produce additional columns for both the home and away teams that showed these new values.

Test data:

data<- data.frame(
Date= c("2018-01-01", "2018-01-01", "2018-01-02", "2018-01-03", "2018-01-04", "2018-01-05"),
Hteam= c("A","D","B","A","C","A"),
Ateam= c("B","C","A","C","B","C"))


        Date Hteam Ateam
1 2018-01-01     A     B
2 2018-01-01     D     C
3 2018-01-02     B     A
4 2018-01-03     A     C
5 2018-01-04     C     B
6 2018-01-05     A     C

The aim would end up looking like:

         Date Hteam Ateam Hdays Adays
1 2018-01-01     A     B    0    0
2 2018-01-01     D     C    0    0
3 2018-01-02     B     A    1    1
4 2018-01-03     A     C    2    0
5 2018-01-04     C     B    1    0
6 2018-01-05     A     C    0    2

In my searching I haven't found an example close enough that I am able to adapt to this situation. I feel like I should be using a rollapply or dplyr grouping, but I can't get close to a solution.

Thanks.

Answer 1

Maybe the following gives what you wanted assuming that data is sorted by Date and missing days are not considered.

t1 <- unique(unlist(data[-1]))
t2 <- do.call(rbind, lapply(split(data[-1], data$Date), function(x) t1 %in% unlist(x)))
t3 <- apply(t2, 2, function(x) ave(x, cumsum(!x), FUN=cumsum))-1

data.frame(data
 , Hdays=t3[cbind(match(data$Date, rownames(t3)), match(data$Hteam, t1))]
 , Adays=t3[cbind(match(data$Date, rownames(t3)), match(data$Ateam, t1))])
#        Date Hteam Ateam Hdays Adays
#1 2018-01-01     A     B     0     0
#2 2018-01-01     D     C     0     0
#3 2018-01-02     B     A     1     1
#4 2018-01-03     A     C     2     0
#5 2018-01-04     C     B     1     0
#6 2018-01-05     A     C     0     2