Filter ID based on three conditions, huge dataframe

I have the following sample code

df <- data.frame(id = c(1,1,1,2,2,2,2,3,3,4), 
                 car = c("subaru", "audi", "subaru", "toyota", "toyota", "audi", "subaru", "nissan", "nissan", "chevrolet"),
                buy_date = c("01/01/2000", "01/01/2001", "01/02/2001", "01/01/2000", "01/12/2004", "01/01/2005", "01/03/2005", "01/01/2000", "02/01/2000", "01/01/2010"))
df$buy_date <- as.Date(df$buy_date, format="%d/%m/%Y") 

And I want filter for the following:
For each ID, I wanna keep the rows from the ID if he/she has bought two different type of cars within 180 days. So it should return a list something like this:

However, since my original dataframe have 12 million rows to start with, doing this through inner_join which creates new rows (squared), which makes the dataframe too big for R studio to handle.

I was thinking if it was possible to do a comparison by row, e.g., a new column which gives the time difference in days (with the first row of each ID being NA (and not 0)), for then to do some filtering that compares car name in row 1 and 2 and the difference in days - so if cars are different and the amount of days are 180 or below = it would keep the row.

Thanks in advance

You probably want to use data.table for this, as it tends to be faster than alternatives when the data is fairly large.

Essentially the logic is as you set out:

1. Calculate the number of days since the previous purchase, by ID.
2. Capture the model of each row and the preceding row in a list, and count how many unique models they're are.
3. Keep rows where there's more than one unique model type and the time since the previous purchase is less than or equal to 180 days.

library(data.table)
setDT(df)
df[
    ,
    buy_date := as.IDate(buy_date)
][, `:=`(
    days_since_last = buy_date - shift(buy_date),
    types = split(
        c(car, shift(car)),
        ceiling(seq(.N))
    )
),
by = .(id)
][
    ,
    num_types := sapply(types, \(x) uniqueN(na.omit(unlist(x))))
][
    ,
    keep := (
        days_since_last <= 180 |
            shift(days_since_last, type = "lead") <= 180
    ) & (
        num_types > 1 | shift(num_types > 1, type = "lead")
    ),
    by = .(id)
]


df[(keep), .(id, car, buy_date)] 

#       id    car   buy_date
#    <num> <char>     <IDat>
# 1:     1   audi 2001-01-01
# 2:     1 subaru 2001-02-01
# 3:     2 toyota 2004-12-01
# 4:     2   audi 2005-01-01
# 5:     2 subaru 2005-03-01

I also converted your Date object to IDate, integer [date] storage for fast sorting and grouping. As the docs state:

> Using integer storage allows dates and/or times to be used as data table keys. With positive integers with a range less than 100,000, grouping and sorting is fast because radix sorting can be used.

Overall, this should be fairly fast. In terms of performance, as these calculations are done by group rather than by row, the biggest factor will be how many ids are in your 12m rows.

The Tidyverse way:

library(dplyr)

df %>% 
    group_by(id) %>% 
    arrange(id, buy_date) %>% 
    mutate(days_between = buy_date - lag(buy_date)) %>%
    mutate(morethanOneCar = +(n_distinct(car) > 1)) %>% 
    mutate(lessthan180 = +(days_between <= 180)) %>% 
    filter(sum(morethanOneCar) > 1 & 
               sum(lessthan180, na.rm = TRUE) > 0) %>% 
    filter(!is.na(days_between)) %>% 
    ungroup()

I have here broken up the conditions into separate columns and tests to make it a bit clearer. I include all steps as separate columns, but it would be easy-peasy too exclude them.

1. group by id and arrange by buy date
2. calculate days since last purchase
3. check if each id has bought more than one unique car brand
4. check if there is less than or equal to 180 days since last purchase
5. filter id on where each id has more than one car and that has purchased a car within 180 days from the last purchase
6. filter away the first purchase of each of those id's

I am sure it is possible to make it more concise. For example you can make the calculations within the first filter and then filter on buy_date != min(buy_date).