CODEWITHSUNDEEP
rdata.table
Sam
Sam

Reputation: 1492

Insert all missing rows into data table for a range of values for 2 columns

I am interested in inserting all missing rows into a data table for a new range of values for 2 columns.

Example, dt1[,a] has some values from 1 to 5, as does dt1[,b], but i'd like not only all pair wise combinations to be present in columns a and b, but all combinations to be present in a newly defined range, e.g. 1 to 7 instead. 

# Example data.table
dt1 <- data.table(a=c(1,1,1,1,2,2,2,2,3,3,3,4,4,4,4,4,5,5,5),
b=c(1,3,4,5,1,2,3,4,1,2,3,1,2,3,4,5,3,4,5),
c=sample(1:10,19,replace=T))

setkey(dt1,a,b)

# CJ in data.table will create all rows to ensure all
# pair wise combinations are present (using the nominated columns). 
dt1[CJ(a,b,unique=T)]

The above is great but will only use the max and min in the nominated columns. I'd like the inserted rows to give me all combinations between a new, nominated range, e.g. 1 to 7. There would be 49 rows.

# the following is a temporary workaround
template <- data.table(a1=rep(1:7,each=7),b1=rep(1:7,7))
setkey(template,a1,b1)

full <- dt1[template]

Upvotes: 2

Views: 48

Answers (1)

akrun
akrun

Reputation: 887711

Instead of the already existing values in 'a' column, we can have a range of values to pass into 'CJ' for the 'a'

dt1[CJ(a = 1:7, b, unique = TRUE)]

Upvotes: 2

Related Questions