Using "by argument" in the external table data.table to filter the "internal" data.table

Question

I still have problems understanding the data.table format. Can someone explain why the following does not work?

I am trying to classify dates in groups using cut . The breaks used can be found in another data table and depend on the by argument of the external data data.table

 data <- data.table(A = c(1, 1, 1, 2, 2, 2), DATE = as.POSIXct(c("01-01-2012", "30-05-2015", "01-01-2020", "30-06-2012", "30-06-2013", "01-01-1999"), format = "%d-%m-%Y")) breaks <- data.table(B = c(1, 1, 2, 2), BREAKPOINT = as.POSIXct(c("01-01-2015", "01-01-2016", "30-06-2012", "30-06-2013"), format = "%d-%m-%Y")) data[, bucket := cut(DATE, breaks[B == A, BREAKPOINT], ordered_result = T), by = A] 

I can get the desired result by doing

 # expected data[A == 1, bucket := cut(DATE, breaks[B == 1, BREAKPOINT], ordered_result = T)] data[A == 2, bucket := cut(DATE, breaks[B == 2, BREAKPOINT], ordered_result = T)] data # A DATE bucket # 1: 1 2012-01-01 NA # 2: 1 2015-05-30 2015-01-01 # 3: 1 2020-01-01 NA # 4: 2 2012-06-30 2012-06-30 # 5: 2 2013-06-30 NA # 6: 2 1999-01-01 NA 

Thanks Michael

Answer 1

The problem is that cut creates factors, and they are not processed correctly in the data.table by operation (this is an error, and it should be reported - factor levels should be processed in the same way as they are processed in rbind.data.table or rbindlist ). Easy correction of the original expression - conversion to character:

 data[, bucket := as.character(cut(DATE, breaks[B == A, BREAKPOINT], ordered_result = T)) , by = A] # A DATE bucket #1: 1 2012-01-01 NA #2: 1 2015-05-30 2015-01-01 #3: 1 2020-01-01 NA #4: 2 2012-06-30 2012-06-30 #5: 2 2013-06-30 NA #6: 2 1999-01-01 NA