A little clarification first, you are talking about columns, but I think you actually mean rows (horizontal).
I think this produces your desired output
library(tidyverse)
library(lubridate)
df <- data.frame(stringsAsFactors=FALSE,
Row = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18,
19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33),
Date = c("1/11/2018", "1/15/2018", "1/17/2018", "1/17/2018",
"2/20/2018", "2/20/2018", "2/21/2018", "2/26/2018",
"2/27/2018", "3/27/2018", "5/10/2018", "5/21/2018", "6/26/2018",
"7/9/2018", "7/20/2018", "8/3/2018", "8/3/2018", "8/3/2018", "8/9/2018",
"8/9/2018", "8/29/2018", "9/10/2018", "10/3/2018", "10/3/2018",
"10/6/2018", "10/6/2018", "10/12/2018", "10/12/2018",
"10/25/2018", "10/29/2018", "11/12/2018", "12/19/2018", "12/19/2018"),
Yr.Month = c("2018/01", "2018/01", "2018/01", "2018/01", "2018/02",
"2018/02", "2018/02", "2018/02", "2018/02", "2018/03",
"2018/05", "2018/05", "2018/06", "2018/07", "2018/07", "2018/08",
"2018/08", "2018/08", "2018/08", "2018/08", "2018/08", "2018/09",
"2018/10", "2018/10", "2018/10", "2018/10", "2018/10", "2018/10",
"2018/10", "2018/10", "2018/11", "2018/12", "2018/12"),
Product = c("A", "B", "B", "C", "A", "A", "A", "A", "C", "C", "C", "C",
"C", "B", "A", "C", "C", "A", "B", "B", "B", "C", "A", "A",
"A", "B", "A", "B", "C", "C", "A", "B", "C")
)
df <- df %>%
mutate(Date = mdy(Date))
df %>%
mutate(prev_purchase = pmap_int(.l = list(floor_date(Date - months(3), unit = "month"),
floor_date(Date, unit = "month"),
Product),
~ df %>%
filter(Date >= ..1, Date < ..2, Product == ..3) %>%
count() %>%
pull()
))
#> Row Date Yr.Month Product prev_purchase
#> 1 1 2018-01-11 2018/01 A 0
#> 2 2 2018-01-15 2018/01 B 0
#> 3 3 2018-01-17 2018/01 B 0
#> 4 4 2018-01-17 2018/01 C 0
#> 5 5 2018-02-20 2018/02 A 1
#> 6 6 2018-02-20 2018/02 A 1
#> 7 7 2018-02-21 2018/02 A 1
#> 8 8 2018-02-26 2018/02 A 1
#> 9 9 2018-02-27 2018/02 C 1
#> 10 10 2018-03-27 2018/03 C 2
#> 11 11 2018-05-10 2018/05 C 2
#> 12 12 2018-05-21 2018/05 C 2
#> 13 13 2018-06-26 2018/06 C 3
#> 14 14 2018-07-09 2018/07 B 0
#> 15 15 2018-07-20 2018/07 A 0
#> 16 16 2018-08-03 2018/08 C 3
#> 17 17 2018-08-03 2018/08 C 3
#> 18 18 2018-08-03 2018/08 A 1
#> 19 19 2018-08-09 2018/08 B 1
#> 20 20 2018-08-09 2018/08 B 1
#> 21 21 2018-08-29 2018/08 B 1
#> 22 22 2018-09-10 2018/09 C 3
#> 23 23 2018-10-03 2018/10 A 2
#> 24 24 2018-10-03 2018/10 A 2
#> 25 25 2018-10-06 2018/10 A 2
#> 26 26 2018-10-06 2018/10 B 4
#> 27 27 2018-10-12 2018/10 A 2
#> 28 28 2018-10-12 2018/10 B 4
#> 29 29 2018-10-25 2018/10 C 3
#> 30 30 2018-10-29 2018/10 C 3
#> 31 31 2018-11-12 2018/11 A 5
#> 32 32 2018-12-19 2018/12 B 2
#> 33 33 2018-12-19 2018/12 C 3
Created on 2019-12-13 by the reprex package (v0.3.0.9000)