Search code examples
rdataframematrixfrequencycrosstab

How to build counts filled matrix in R

I have working on a analytics project using R. As a part of data preparation module I got stuck up with a specific task. I have a categorical column and a response variable as shown: Pic1.

I need to construct sequential fills as below. It should be constructed as shown: Pic2

The logic is as follows : Consider step =3. From the actual data consider first 3 rows a,b,c and yes/no column has one 'Yes'. So the respective values of a,b,c,d,e,f in the resultant matrix is filled as 1,1,1,0,0,0 with Yes. The next step has b,c,b, so the resultant matrix is filled as 0,2,1,0,0,0 with Yes. This process should be repeated until the last sequence in the source data.

Is there any possibility of constructing the above. Can anyone please help on building this logic to construct the above resultant matrix using R.

Thanking you in advance.

Solution

  • Use rollapply from zoo with table and any:

    library(zoo)

step <- 3

data.frame(
     rollapply(DF$Features, step, table), 
     "Yes/No" = ifelse(rollapply(DF$"Yes/No" == "Yes", step, any), "Yes", "No"),
       check.names = FALSE
)

    giving:

      a b c d e f Yes/No
1 1 1 1 0 0 0    Yes
2 0 2 1 0 0 0    Yes
3 0 1 1 1 0 0     No
4 0 1 0 1 1 0    Yes
5 1 0 0 1 1 0    Yes
6 1 0 0 0 1 1    Yes

    Note

    The input DF in reproducible form:

    DF <- data.frame(Features = c("a", "b", "c", "b", "d", "e", "a", "f"),
 "Yes/No" = c("No", "Yes", "No", "No", "No", "Yes", "No", "No"), 
  check.names = FALSE)