In this vignette you will learn
how to perform any join operation using resources available in the
data.table
syntax.
It assumes familiarity with the data.table
syntax. If
that is not the case, please read the following vignettes:
vignette("datatable-intro", package="data.table")
vignette("datatable-reference-semantics", package="data.table")
vignette("datatable-keys-fast-subset", package="data.table")
To illustrate how to use the method available with real life examples, let’s simulate a normalized database from a little supermarket by performing the following steps:
data.table
where each product is represented
by a row with some qualities, but leaving one product without
id
to show how the framework deals with missing
values.Products = data.table(
id = c(1:4,
NA_integer_),
name = c("banana",
"carrots",
"popcorn",
"soda",
"toothpaste"),
price = c(0.63,
0.89,
2.99,
1.49,
2.99),
unit = c("unit",
"lb",
"unit",
"ounce",
"unit"),
type = c(rep("natural", 2L),
rep("processed", 3L))
)
Products
# id name price unit type
# <int> <char> <num> <char> <char>
# 1: 1 banana 0.63 unit natural
# 2: 2 carrots 0.89 lb natural
# 3: 3 popcorn 2.99 unit processed
# 4: 4 soda 1.49 ounce processed
# 5: NA toothpaste 2.99 unit processed
data.table
showing the proportion of taxes
to be applied for processed products based on their units.NewTax = data.table(
unit = c("unit","ounce"),
type = "processed",
tax_prop = c(0.65, 0.20)
)
NewTax
# unit type tax_prop
# <char> <char> <num>
# 1: unit processed 0.65
# 2: ounce processed 0.20
data.table
simulating the products received
every Monday with a product_id
that is not present in the
Products
table.set.seed(2156)
ProductReceived = data.table(
id = 1:10,
date = seq(from = as.IDate("2024-01-08"), length.out = 10L, by = "week"),
product_id = sample(c(NA_integer_, 1:3, 6L), size = 10L, replace = TRUE),
count = sample(c(50L, 100L, 150L), size = 10L, replace = TRUE)
)
ProductReceived
# id date product_id count
# <int> <IDat> <int> <int>
# 1: 1 2024-01-08 NA 150
# 2: 2 2024-01-15 1 100
# 3: 3 2024-01-22 6 100
# 4: 4 2024-01-29 1 150
# 5: 5 2024-02-05 2 50
# 6: 6 2024-02-12 1 150
# 7: 7 2024-02-19 2 150
# 8: 8 2024-02-26 2 100
# 9: 9 2024-03-04 1 100
# 10: 10 2024-03-11 3 150
data.table
to show some sales that can take
place on weekdays with another product_id
that is not
present in the Products
table.sample_date = function(from, to, size, ...){
all_days = seq(from = from, to = to, by = "day")
weekdays = all_days[wday(all_days) %in% 2:6]
days_sample = sample(weekdays, size, ...)
days_sample_desc = sort(days_sample)
days_sample_desc
}
set.seed(5415)
ProductSales = data.table(
id = 1:10,
date = ProductReceived[, sample_date(min(date), max(date), 10L)],
product_id = sample(c(1:3, 7L), size = 10L, replace = TRUE),
count = sample(c(50L, 100L, 150L), size = 10L, replace = TRUE)
)
ProductSales
# id date product_id count
# <int> <IDat> <int> <int>
# 1: 1 2024-01-08 7 50
# 2: 2 2024-01-11 2 150
# 3: 3 2024-01-18 1 50
# 4: 4 2024-01-25 1 100
# 5: 5 2024-01-26 3 100
# 6: 6 2024-02-02 3 150
# 7: 7 2024-02-06 2 150
# 8: 8 2024-02-15 7 150
# 9: 9 2024-02-27 1 150
# 10: 10 2024-03-08 1 50
data.table
joining syntaxBefore taking advantage of the data.table
syntax to
perform join operations we need to know which arguments can help us to
perform successful joins.
The next diagram shows a description for each basic argument. In the following sections we will show how to use each of them and add more complexity little by little.
x[i, on, nomatch]
| | | |
| | | \__ If NULL only returns rows linked in x and i tables
| | \____ a character vector o list defining match logict
| \_____ primary data.table, list or data.frame
\____ secondary data.table
Please keep in mind that the standard argument order in data.table is
dt[i, j, by]
. For join operations, it is recommended to pass theon
andnomatch
arguments by name to avoid usingj
andby
when they are not needed.
This the most common and simple case as we can find common elements between tables to combine.
The relationship between tables can be:
In most of the following examples we will perform one to many matches, but we are also going to take the time to explain the resources available to perform many to many matches.
Use this method if you need to combine columns from 2 tables based on one or more references but keeping all rows present in the table located on the right (in the the square brackets).
In our supermarket context, we can perform a right join to see more
details about the products received as this is relation one to
many by passing a vector to the on
argument.
Products[ProductReceived,
on = c(id = "product_id")]
# id name price unit type i.id date count
# <int> <char> <num> <char> <char> <int> <IDat> <int>
# 1: NA toothpaste 2.99 unit processed 1 2024-01-08 150
# 2: 1 banana 0.63 unit natural 2 2024-01-15 100
# 3: 6 <NA> NA <NA> <NA> 3 2024-01-22 100
# 4: 1 banana 0.63 unit natural 4 2024-01-29 150
# 5: 2 carrots 0.89 lb natural 5 2024-02-05 50
# 6: 1 banana 0.63 unit natural 6 2024-02-12 150
# 7: 2 carrots 0.89 lb natural 7 2024-02-19 150
# 8: 2 carrots 0.89 lb natural 8 2024-02-26 100
# 9: 1 banana 0.63 unit natural 9 2024-03-04 100
# 10: 3 popcorn 2.99 unit processed 10 2024-03-11 150
As many things have changed, let’s explain the new characteristics in the following groups:
x
table.i
table.i.
is added to column
names from the right-hand table (table on
i
position).product_id
present on the
ProductReceived
table in row 1 was successfully matched
with missing id
of the Products
table, so
NA
values are treated as any other
value.i
table were kept including:
product_id = 6
.product_id
several
times.If you are following the vignette, you might have found out that we
used a vector to define the relations between tables in the
on
argument, that is really useful if you are
creating your own functions, but another alternative is
to use a list to define the columns to match.
To use this capacity, we have 2 equivalent alternatives:
list
function.list
alias .
.on
argumentIn all the prior example we have pass the column names we want to
match to the on
argument but data.table
also
have alternatives to that syntax.
Products
table from id
to
product_id
and use the keyword .NATURAL
.ProductsChangedName = setnames(copy(Products), "id", "product_id")
ProductsChangedName
# product_id name price unit type
# <int> <char> <num> <char> <char>
# 1: 1 banana 0.63 unit natural
# 2: 2 carrots 0.89 lb natural
# 3: 3 popcorn 2.99 unit processed
# 4: 4 soda 1.49 ounce processed
# 5: NA toothpaste 2.99 unit processed
ProductsChangedName[ProductReceived, on = .NATURAL]
# product_id name price unit type id date count
# <int> <char> <num> <char> <char> <int> <IDat> <int>
# 1: NA toothpaste 2.99 unit processed 1 2024-01-08 150
# 2: 1 banana 0.63 unit natural 2 2024-01-15 100
# 3: 6 <NA> NA <NA> <NA> 3 2024-01-22 100
# 4: 1 banana 0.63 unit natural 4 2024-01-29 150
# 5: 2 carrots 0.89 lb natural 5 2024-02-05 50
# 6: 1 banana 0.63 unit natural 6 2024-02-12 150
# 7: 2 carrots 0.89 lb natural 7 2024-02-19 150
# 8: 2 carrots 0.89 lb natural 8 2024-02-26 100
# 9: 1 banana 0.63 unit natural 9 2024-03-04 100
# 10: 3 popcorn 2.99 unit processed 10 2024-03-11 150
ProductsKeyed = setkey(copy(Products), id)
key(ProductsKeyed)
# [1] "id"
ProductReceivedKeyed = setkey(copy(ProductReceived), product_id)
key(ProductReceivedKeyed)
# [1] "product_id"
ProductsKeyed[ProductReceivedKeyed]
# Key: <id>
# id name price unit type i.id date count
# <int> <char> <num> <char> <char> <int> <IDat> <int>
# 1: NA toothpaste 2.99 unit processed 1 2024-01-08 150
# 2: 1 banana 0.63 unit natural 2 2024-01-15 100
# 3: 1 banana 0.63 unit natural 4 2024-01-29 150
# 4: 1 banana 0.63 unit natural 6 2024-02-12 150
# 5: 1 banana 0.63 unit natural 9 2024-03-04 100
# 6: 2 carrots 0.89 lb natural 5 2024-02-05 50
# 7: 2 carrots 0.89 lb natural 7 2024-02-19 150
# 8: 2 carrots 0.89 lb natural 8 2024-02-26 100
# 9: 3 popcorn 2.99 unit processed 10 2024-03-11 150
# 10: 6 <NA> NA <NA> <NA> 3 2024-01-22 100
Most of the time after a join is complete we need to make some additional transformations. To make so we have the following alternatives:
[]
.j
argument.Our recommendation is to use the second alternative if possible, as it is faster and uses less memory than the first one.
We can also use this alternative to return aggregated results based
columns present in the x
table.
For example, we might interested in how much money we expend buying products each date regardless the products.
So far we have just joined data.table
base on 1 column,
but it’s important to know that the package can join tables matching
several columns.
To illustrate this, let’s assume that we want to add the
tax_prop
from NewTax
to
update the Products
table.
NewTax[Products, on = c("unit", "type")]
# unit type tax_prop id name price
# <char> <char> <num> <int> <char> <num>
# 1: unit natural NA 1 banana 0.63
# 2: lb natural NA 2 carrots 0.89
# 3: unit processed 0.65 3 popcorn 2.99
# 4: ounce processed 0.20 4 soda 1.49
# 5: unit processed 0.65 NA toothpaste 2.99
Use this method if you need to combine columns from 2 tables based on one or more references but keeping only rows matched in both tables.
To perform this operation we just need to add
nomatch = NULL
or nomatch = 0
to any of the
prior join operations to return the same results.
# First Table
Products[ProductReceived,
on = c("id" = "product_id"),
nomatch = NULL]
# id name price unit type i.id date count
# <int> <char> <num> <char> <char> <int> <IDat> <int>
# 1: NA toothpaste 2.99 unit processed 1 2024-01-08 150
# 2: 1 banana 0.63 unit natural 2 2024-01-15 100
# 3: 1 banana 0.63 unit natural 4 2024-01-29 150
# 4: 2 carrots 0.89 lb natural 5 2024-02-05 50
# 5: 1 banana 0.63 unit natural 6 2024-02-12 150
# 6: 2 carrots 0.89 lb natural 7 2024-02-19 150
# 7: 2 carrots 0.89 lb natural 8 2024-02-26 100
# 8: 1 banana 0.63 unit natural 9 2024-03-04 100
# 9: 3 popcorn 2.99 unit processed 10 2024-03-11 150
# Second Table
ProductReceived[Products,
on = .(product_id = id),
nomatch = NULL]
# id date product_id count name price unit type
# <int> <IDat> <int> <int> <char> <num> <char> <char>
# 1: 2 2024-01-15 1 100 banana 0.63 unit natural
# 2: 4 2024-01-29 1 150 banana 0.63 unit natural
# 3: 6 2024-02-12 1 150 banana 0.63 unit natural
# 4: 9 2024-03-04 1 100 banana 0.63 unit natural
# 5: 5 2024-02-05 2 50 carrots 0.89 lb natural
# 6: 7 2024-02-19 2 150 carrots 0.89 lb natural
# 7: 8 2024-02-26 2 100 carrots 0.89 lb natural
# 8: 10 2024-03-11 3 150 popcorn 2.99 unit processed
# 9: 1 2024-01-08 NA 150 toothpaste 2.99 unit processed
Despite both tables have the same information, they present some relevant differences:
id
column of first table has the same information
as the product_id
in the second table.i.id
column of first table has the same information
as the id
in the second table.This method keeps only the rows that don’t match with any row of a second table.
To apply this technique we just need to negate (!
) the
table located on the i
argument.
Products[!ProductReceived,
on = c("id" = "product_id")]
# id name price unit type
# <int> <char> <num> <char> <char>
# 1: 4 soda 1.49 ounce processed
As you can see, the result only has ‘banana’, as it was the only
product that is not present in the ProductReceived
table.
ProductReceived[!Products,
on = c("product_id" = "id")]
# id date product_id count
# <int> <IDat> <int> <int>
# 1: 3 2024-01-22 6 100
In this case, the operation returns the row with
product_id = 6,
as it is not present on the
Products
table.
This method extract keeps only the rows that match with any row in a second table without combining the column of the tables.
It’s very similar to subset as join, but as in this time we are
passing a complete table to the i
we need to ensure
that:
Any row in the x
table is duplicated due row
duplication in the table passed to the i
argument.
All the renaming rows from x
should keep the
original row order.
To make this, you can apply the following steps:
which = TRUE
to save the row numbers related to each matching row of the
x
table.SubSetRows = Products[
ProductReceived,
on = .(id = product_id),
nomatch = NULL,
which = TRUE
]
SubSetRows
# [1] 5 1 1 2 1 2 2 1 3
x
rows to keep.Use this method if you need to combine columns from 2 tables based on one or more references but keeping all rows present in the table located on the left.
To perform this operation, we just need to exchange the order
between both tables and the columns names in the
on
argument.
ProductReceived[Products,
on = list(product_id = id)]
# id date product_id count name price unit type
# <int> <IDat> <int> <int> <char> <num> <char> <char>
# 1: 2 2024-01-15 1 100 banana 0.63 unit natural
# 2: 4 2024-01-29 1 150 banana 0.63 unit natural
# 3: 6 2024-02-12 1 150 banana 0.63 unit natural
# 4: 9 2024-03-04 1 100 banana 0.63 unit natural
# 5: 5 2024-02-05 2 50 carrots 0.89 lb natural
# 6: 7 2024-02-19 2 150 carrots 0.89 lb natural
# 7: 8 2024-02-26 2 100 carrots 0.89 lb natural
# 8: 10 2024-03-11 3 150 popcorn 2.99 unit processed
# 9: NA <NA> 4 NA soda 1.49 ounce processed
# 10: 1 2024-01-08 NA 150 toothpaste 2.99 unit processed
Here some important considerations:
ProductReceived
table as it is the x
table.Products
table as it is the i
table.i.
to any column.i
table were kept as we never
received any banana but row is still part of the results.product_id = 6
is no part of the
results any more as it is not present in the Products
table.One of the key features of data.table
is that we can
apply several operations before saving our final results by chaining
brackets.
So far, if after applying all that operations we want to join new columns without removing any row, we would need to stop the chaining process, save a temporary table and later apply the join operation.
To avoid that situation, we can use special symbols .SD
,
to apply a right join based on the changed table.
NewTax[Products,
on = c("unit", "type")
][, ProductReceived[.SD,
on = list(product_id = id)],
.SDcols = !c("unit", "type")]
# id date product_id count tax_prop name price
# <int> <IDat> <int> <int> <num> <char> <num>
# 1: 2 2024-01-15 1 100 NA banana 0.63
# 2: 4 2024-01-29 1 150 NA banana 0.63
# 3: 6 2024-02-12 1 150 NA banana 0.63
# 4: 9 2024-03-04 1 100 NA banana 0.63
# 5: 5 2024-02-05 2 50 NA carrots 0.89
# 6: 7 2024-02-19 2 150 NA carrots 0.89
# 7: 8 2024-02-26 2 100 NA carrots 0.89
# 8: 10 2024-03-11 3 150 0.65 popcorn 2.99
# 9: NA <NA> 4 NA 0.20 soda 1.49
# 10: 1 2024-01-08 NA 150 0.65 toothpaste 2.99
Sometimes we want to join tables based on columns with
duplicated id
values to later perform some
transformations later.
To illustrate this situation let’s take as an example the
product_id == 1L
, which have 4 rows in our
ProductReceived
table.
ProductReceived[product_id == 1L]
# id date product_id count
# <int> <IDat> <int> <int>
# 1: 2 2024-01-15 1 100
# 2: 4 2024-01-29 1 150
# 3: 6 2024-02-12 1 150
# 4: 9 2024-03-04 1 100
And 4 rows in our ProductSales
table.
ProductSales[product_id == 1L]
# id date product_id count
# <int> <IDat> <int> <int>
# 1: 3 2024-01-18 1 50
# 2: 4 2024-01-25 1 100
# 3: 9 2024-02-27 1 150
# 4: 10 2024-03-08 1 50
To perform this join we just need to filter
product_id == 1L
in the i
table to limit the
join just to that product and set the argument
allow.cartesian = TRUE
to allow combining each row from one
table with every row from the other table.
ProductReceived[ProductSales[list(1L),
on = "product_id",
nomatch = NULL],
on = "product_id",
allow.cartesian = TRUE]
# id date product_id count i.id i.date i.count
# <int> <IDat> <int> <int> <int> <IDat> <int>
# 1: 2 2024-01-15 1 100 3 2024-01-18 50
# 2: 4 2024-01-29 1 150 3 2024-01-18 50
# 3: 6 2024-02-12 1 150 3 2024-01-18 50
# 4: 9 2024-03-04 1 100 3 2024-01-18 50
# 5: 2 2024-01-15 1 100 4 2024-01-25 100
# 6: 4 2024-01-29 1 150 4 2024-01-25 100
# 7: 6 2024-02-12 1 150 4 2024-01-25 100
# 8: 9 2024-03-04 1 100 4 2024-01-25 100
# 9: 2 2024-01-15 1 100 9 2024-02-27 150
# 10: 4 2024-01-29 1 150 9 2024-02-27 150
# 11: 6 2024-02-12 1 150 9 2024-02-27 150
# 12: 9 2024-03-04 1 100 9 2024-02-27 150
# 13: 2 2024-01-15 1 100 10 2024-03-08 50
# 14: 4 2024-01-29 1 150 10 2024-03-08 50
# 15: 6 2024-02-12 1 150 10 2024-03-08 50
# 16: 9 2024-03-04 1 100 10 2024-03-08 50
Once we understand the result, we can apply the same process for all products.
ProductReceived[ProductSales,
on = "product_id",
allow.cartesian = TRUE]
# id date product_id count i.id i.date i.count
# <int> <IDat> <int> <int> <int> <IDat> <int>
# 1: NA <NA> 7 NA 1 2024-01-08 50
# 2: 5 2024-02-05 2 50 2 2024-01-11 150
# 3: 7 2024-02-19 2 150 2 2024-01-11 150
# 4: 8 2024-02-26 2 100 2 2024-01-11 150
# 5: 2 2024-01-15 1 100 3 2024-01-18 50
# 6: 4 2024-01-29 1 150 3 2024-01-18 50
# 7: 6 2024-02-12 1 150 3 2024-01-18 50
# 8: 9 2024-03-04 1 100 3 2024-01-18 50
# 9: 2 2024-01-15 1 100 4 2024-01-25 100
# 10: 4 2024-01-29 1 150 4 2024-01-25 100
# 11: 6 2024-02-12 1 150 4 2024-01-25 100
# 12: 9 2024-03-04 1 100 4 2024-01-25 100
# 13: 10 2024-03-11 3 150 5 2024-01-26 100
# 14: 10 2024-03-11 3 150 6 2024-02-02 150
# 15: 5 2024-02-05 2 50 7 2024-02-06 150
# 16: 7 2024-02-19 2 150 7 2024-02-06 150
# 17: 8 2024-02-26 2 100 7 2024-02-06 150
# 18: NA <NA> 7 NA 8 2024-02-15 150
# 19: 2 2024-01-15 1 100 9 2024-02-27 150
# 20: 4 2024-01-29 1 150 9 2024-02-27 150
# 21: 6 2024-02-12 1 150 9 2024-02-27 150
# 22: 9 2024-03-04 1 100 9 2024-02-27 150
# 23: 2 2024-01-15 1 100 10 2024-03-08 50
# 24: 4 2024-01-29 1 150 10 2024-03-08 50
# 25: 6 2024-02-12 1 150 10 2024-03-08 50
# 26: 9 2024-03-04 1 100 10 2024-03-08 50
# id date product_id count i.id i.date i.count
allow.cartesian
is defaulted to FALSE as this is seldom what the user wants, and such a cross join can lead to a very large number of rows in the result. For example, if Table A has 100 rows and Table B has 50 rows, their Cartesian product would result in 5000 rows (100 * 50). This can quickly become memory-intensive for large datasets.
After joining the table we might find out that we just need to return a single join to extract the information we need. In this case we have 2 alternatives:
id = 2
.ProductReceived[ProductSales[product_id == 1L],
on = .(product_id),
allow.cartesian = TRUE,
mult = "first"]
# id date product_id count i.id i.date i.count
# <int> <IDat> <int> <int> <int> <IDat> <int>
# 1: 2 2024-01-15 1 100 3 2024-01-18 50
# 2: 2 2024-01-15 1 100 4 2024-01-25 100
# 3: 2 2024-01-15 1 100 9 2024-02-27 150
# 4: 2 2024-01-15 1 100 10 2024-03-08 50
id = 9
.ProductReceived[ProductSales[product_id == 1L],
on = .(product_id),
allow.cartesian = TRUE,
mult = "last"]
# id date product_id count i.id i.date i.count
# <int> <IDat> <int> <int> <int> <IDat> <int>
# 1: 9 2024-03-04 1 100 3 2024-01-18 50
# 2: 9 2024-03-04 1 100 4 2024-01-25 100
# 3: 9 2024-03-04 1 100 9 2024-02-27 150
# 4: 9 2024-03-04 1 100 10 2024-03-08 50
If you want to get all possible row combinations regardless of any particular id column we can follow the next process:
AllProductsMix =
ProductsTempId[ProductsTempId,
on = "temp_id",
allow.cartesian = TRUE]
AllProductsMix[, temp_id := NULL]
# Removing type to make easier to see the result when printing the table
AllProductsMix[, !c("type", "i.type")]
# id name price unit i.id i.name i.price i.unit
# <int> <char> <num> <char> <int> <char> <num> <char>
# 1: 1 banana 0.63 unit 1 banana 0.63 unit
# 2: 2 carrots 0.89 lb 1 banana 0.63 unit
# 3: 3 popcorn 2.99 unit 1 banana 0.63 unit
# 4: 4 soda 1.49 ounce 1 banana 0.63 unit
# 5: NA toothpaste 2.99 unit 1 banana 0.63 unit
# 6: 1 banana 0.63 unit 2 carrots 0.89 lb
# 7: 2 carrots 0.89 lb 2 carrots 0.89 lb
# 8: 3 popcorn 2.99 unit 2 carrots 0.89 lb
# 9: 4 soda 1.49 ounce 2 carrots 0.89 lb
# 10: NA toothpaste 2.99 unit 2 carrots 0.89 lb
# 11: 1 banana 0.63 unit 3 popcorn 2.99 unit
# 12: 2 carrots 0.89 lb 3 popcorn 2.99 unit
# 13: 3 popcorn 2.99 unit 3 popcorn 2.99 unit
# 14: 4 soda 1.49 ounce 3 popcorn 2.99 unit
# 15: NA toothpaste 2.99 unit 3 popcorn 2.99 unit
# 16: 1 banana 0.63 unit 4 soda 1.49 ounce
# 17: 2 carrots 0.89 lb 4 soda 1.49 ounce
# 18: 3 popcorn 2.99 unit 4 soda 1.49 ounce
# 19: 4 soda 1.49 ounce 4 soda 1.49 ounce
# 20: NA toothpaste 2.99 unit 4 soda 1.49 ounce
# 21: 1 banana 0.63 unit NA toothpaste 2.99 unit
# 22: 2 carrots 0.89 lb NA toothpaste 2.99 unit
# 23: 3 popcorn 2.99 unit NA toothpaste 2.99 unit
# 24: 4 soda 1.49 ounce NA toothpaste 2.99 unit
# 25: NA toothpaste 2.99 unit NA toothpaste 2.99 unit
# id name price unit i.id i.name i.price i.unit
Use this method if you need to combine columns from 2 tables based on one or more references without removing any row.
As we saw in the previous section, any of the prior operations can
keep the missing product_id = 6
and the
soda (product_id = 4
) as part of the
results.
To save this problem, we can use the merge
function even
thought it is lower than using the native data.table
’s
joining syntax.
merge(x = Products,
y = ProductReceived,
by.x = "id",
by.y = "product_id",
all = TRUE,
sort = FALSE)
# id name price unit type id.y date count
# <int> <char> <num> <char> <char> <int> <IDat> <int>
# 1: 1 banana 0.63 unit natural 2 2024-01-15 100
# 2: 1 banana 0.63 unit natural 4 2024-01-29 150
# 3: 1 banana 0.63 unit natural 6 2024-02-12 150
# 4: 1 banana 0.63 unit natural 9 2024-03-04 100
# 5: 2 carrots 0.89 lb natural 5 2024-02-05 50
# 6: 2 carrots 0.89 lb natural 7 2024-02-19 150
# 7: 2 carrots 0.89 lb natural 8 2024-02-26 100
# 8: 3 popcorn 2.99 unit processed 10 2024-03-11 150
# 9: 4 soda 1.49 ounce processed NA <NA> NA
# 10: NA toothpaste 2.99 unit processed 1 2024-01-08 150
# 11: 6 <NA> NA <NA> <NA> 3 2024-01-22 100
A non-equi join is a type of join where the condition for matching
rows is not based on equality, but on other comparison operators like
<, >, <=, or >=. This allows for more flexible
joining criteria. In data.table
, non-equi joins
are particularly useful for operations like:
It’s a great alternative if after applying a right of inner join:
x
(secondary data.table) in the final table.To illustrate how this work, let’s center over attention on how are the sales and receives for product 2.
ProductSalesProd2 = ProductSales[product_id == 2L]
ProductReceivedProd2 = ProductReceived[product_id == 2L]
If want to know, for example, if can find any receive that took place before a sales date, we can apply the next code.
ProductReceivedProd2[ProductSalesProd2,
on = "product_id",
allow.cartesian = TRUE
][date < i.date]
# id date product_id count i.id i.date i.count
# <int> <IDat> <int> <int> <int> <IDat> <int>
# 1: 5 2024-02-05 2 50 7 2024-02-06 150
What does happen if we just apply the same logic on the list passed
to on
?
As this opperation it’s still a right join, it returns all rows
from the i
table, but only shows the values for
id
and count
when the rules are met.
The date related ProductReceivedProd2
was omited
from this new table.
ProductReceivedProd2[ProductSalesProd2,
on = list(product_id, date < date)]
# id date product_id count i.id i.count
# <int> <IDat> <int> <int> <int> <int>
# 1: NA 2024-01-11 2 NA 2 150
# 2: 5 2024-02-06 2 50 7 150
Now, after applying the join, we can limit the results only show the cases that meet all joining criteria.
Rolling joins are particularly useful in time-series data analysis. They allow you to match rows based on the nearest value in a sorted column, typically a date or time column.
This is valuable when you need to align data from different sources that may not have exactly matching timestamps, or when you want to carry forward the most recent value.
For example, in financial data, you might use a rolling join to assign the most recent stock price to each transaction, even if the price updates and transactions don’t occur at the exact same times.
In our supermarket example, we can use a rolling join to match sales with the most recent product information.
Let’s assume that the price for Bananas and Carrots changes at the first date of each month.
ProductPriceHistory = data.table(
product_id = rep(1:2, each = 3),
date = rep(as.IDate(c("2024-01-01", "2024-02-01", "2024-03-01")), 2),
price = c(0.59, 0.63, 0.65, # Banana prices
0.79, 0.89, 0.99) # Carrot prices
)
ProductPriceHistory
# product_id date price
# <int> <IDat> <num>
# 1: 1 2024-01-01 0.59
# 2: 1 2024-02-01 0.63
# 3: 1 2024-03-01 0.65
# 4: 2 2024-01-01 0.79
# 5: 2 2024-02-01 0.89
# 6: 2 2024-03-01 0.99
Now, we can perform a right join giving a different prices for each product based on the sale date.
ProductPriceHistory[ProductSales,
on = .(product_id, date),
roll = TRUE,
j = .(product_id, date, count, price)]
# product_id date count price
# <int> <IDat> <int> <num>
# 1: 7 2024-01-08 50 NA
# 2: 2 2024-01-11 150 0.79
# 3: 1 2024-01-18 50 0.59
# 4: 1 2024-01-25 100 0.59
# 5: 3 2024-01-26 100 NA
# 6: 3 2024-02-02 150 NA
# 7: 2 2024-02-06 150 0.89
# 8: 7 2024-02-15 150 NA
# 9: 1 2024-02-27 150 0.63
# 10: 1 2024-03-08 50 0.65
If we just want to see the matching cases we just need to add the
argument nomatch = NULL
to perform an inner rolling
join.
ProductPriceHistory[ProductSales,
on = .(product_id, date),
roll = TRUE,
nomatch = NULL,
j = .(product_id, date, count, price)]
# product_id date count price
# <int> <IDat> <int> <num>
# 1: 2 2024-01-11 150 0.79
# 2: 1 2024-01-18 50 0.59
# 3: 1 2024-01-25 100 0.59
# 4: 2 2024-02-06 150 0.89
# 5: 1 2024-02-27 150 0.63
# 6: 1 2024-03-08 50 0.65
As we just saw in the prior section the x
table gets
filtered by the values available in the i
table. Actually,
that process is faster than passing a Boolean expression to the
i
argument.
To filter the x
table at speed we don’t to pass a
complete data.table
, we can pass a list()
of
vectors with the values that we want to keep or omit from the original
table.
For example, to filter dates where the market received 100 units of
bananas (product_id = 1
) or popcorn
(product_id = 3
) we can use the following:
ProductReceived[list(c(1L, 3L), 100L),
on = c("product_id", "count")]
# id date product_id count
# <int> <IDat> <int> <int>
# 1: 2 2024-01-15 1 100
# 2: 9 2024-03-04 1 100
# 3: NA <NA> 3 100
As at the end, we are filtering based on a join operation the code
returned a row that was not present in original table.
To avoid that behavior, it is recommended to always to add the argument
nomatch = NULL
.
ProductReceived[list(c(1L, 3L), 100L),
on = c("product_id", "count"),
nomatch = NULL]
# id date product_id count
# <int> <IDat> <int> <int>
# 1: 2 2024-01-15 1 100
# 2: 9 2024-03-04 1 100
We can also use this technique to filter out any combination of
values by prefixing them with !
to negate the expression in
the i
argument and keeping the nomatch
with
its default value. For example, we can filter out the 2 rows we filtered
before.
ProductReceived[!list(c(1L, 3L), 100L),
on = c("product_id", "count")]
# id date product_id count
# <int> <IDat> <int> <int>
# 1: 1 2024-01-08 NA 150
# 2: 3 2024-01-22 6 100
# 3: 4 2024-01-29 1 150
# 4: 5 2024-02-05 2 50
# 5: 6 2024-02-12 1 150
# 6: 7 2024-02-19 2 150
# 7: 8 2024-02-26 2 100
# 8: 10 2024-03-11 3 150
If you just want to filter a value for a single character
column, you can omit calling the list()
function
pass the value to been filtered in the i
argument.
Products[c("banana","popcorn"),
on = "name",
nomatch = NULL]
# id name price unit type
# <int> <char> <num> <char> <char>
# 1: 1 banana 0.63 unit natural
# 2: 3 popcorn 2.99 unit processed
Products[!"popcorn",
on = "name"]
# id name price unit type
# <int> <char> <num> <char> <char>
# 1: 1 banana 0.63 unit natural
# 2: 2 carrots 0.89 lb natural
# 3: 4 soda 1.49 ounce processed
# 4: NA toothpaste 2.99 unit processed
The :=
operator in data.table is used for updating or
adding columns by reference. This means it modifies the original
data.table without creating a copy, which is very memory-efficient,
especially for large datasets. When used inside a data.table,
:=
allows you to add new columns or
modify existing ones as part of your query.
Let’s update our Products
table with the latest price
from ProductPriceHistory
:
copy(Products)[ProductPriceHistory,
on = .(id = product_id),
j = `:=`(price = tail(i.price, 1),
last_updated = tail(i.date, 1)),
by = .EACHI][]
# id name price unit type last_updated
# <int> <char> <num> <char> <char> <IDat>
# 1: 1 banana 0.65 unit natural 2024-03-01
# 2: 2 carrots 0.99 lb natural 2024-03-01
# 3: 3 popcorn 2.99 unit processed <NA>
# 4: 4 soda 1.49 ounce processed <NA>
# 5: NA toothpaste 2.99 unit processed <NA>
In this operation:
copy
prevent that :=
changes
by reference the Products
table.sProducts
with ProductPriceHistory
based on id
and product_id
.price
column with the latest price from
ProductPriceHistory
.last_updated
column to track when the
price was last changed.by = .EACHI
ensures that the tail
function is applied for each product in
ProductPriceHistory
.Understanding data.table Rolling Joins: https://www.r-bloggers.com/2016/06/understanding-data-table-rolling-joins/
Semi-join with data.table: https://stackoverflow.com/questions/18969420/perform-a-semi-join-with-data-table
Cross join with data.table: https://stackoverflow.com/questions/10600060/how-to-do-cross-join-in-r
How does one do a full join using data.table?: https://stackoverflow.com/questions/15170741/how-does-one-do-a-full-join-using-data-table
Enhanced data.frame: https://rdatatable.gitlab.io/data.table/reference/data.table.html