Repository navigation
Understanding the value of .I for non-matching rows when using .EACHI #5457
Description
I join two data.tables, use .I in j, and by = .EACHI. When a row in i has no match to x the result is 0. I wish to understand why this is the case.
Some toy data:
d1 = data.table(v = c("A", "B", "C", "A", "C"))
# add column identical (value-wise) to .I
d1[ , i := .I]
d2 = data.table(v = c("D", "A", "G", "C"))
d1
# v i
# 1: A 1
# 2: B 2
# 3: C 3
# 4: A 4
# 5: C 5
d2
# v
# 1: D
# 2: A
# 3: G
3 4: C
Join the two tables on 'v'. In j, call either "i" or .I. Use by = .EACHI ("evaluates j for the groups in 'DT' that each row in i joins to").
When j is "i" (which at least "looks the same" as .I), non-matched rows evaluates to NA. To me, this seems consistent with the default nomatch behaviour: "When a row in i has no match to x, nomatch=NA (default) means NA is returned":
d1[d2, on = .(v), i, by = .EACHI]
# v i
# 1: D NA # unmatched row in `i` evaluates to NA
# 2: A 1
# 3: A 4
# 4: G NA # unmatched row in `i` evaluates to NA
# 5: C 3
# 6: C 5
On the other hand, when j is .I, non-matched rows evaluates to 0:
d1[d2, on = .(v), .I, by = .EACHI]
# v I
# 1: D 0 # unmatched row in `i` evaluates to 0
# 2: A 1
# 3: A 4
# 4: G 0 # unmatched row in `i` evaluates to 0
# 5: C 3
# 6: C 5
From ?.I:
While grouping, it holds for each item in the group, its row location in
x
However, I fail to find documentation on how unmatched rows in i evaluate to 0 when j = .I. Can someone help me understand this seemingly inconsistent behaviour?
R version 4.2.1 (2022-06-23 ucrt)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 10 x64 (build 19044)
Tried on:
data.table_1.14.2 &
data.table 1.14.3 IN DEVELOPMENT built 2022-07-20 18:26:12 UTC
Activity
jangorecki commentedon Sep 6, 2022
Worth to try on devel as well just to ensure it haven't changed since 1.14.2
Henrik-P commentedon Sep 6, 2022
Thanks @jangorecki, I forgot to include that I also attempted with devel version. Updated the post.
Henrik-P commentedon Sep 7, 2022
I just found a related open issue: With by=.EACHI and unmatched i, can we set nomatch= to get .SD[0] instead of .SD[NA]?, with a comment on the same result as here:
venom1204 commentedon Mar 26, 2025
hi @jangorecki , @joshhwuu @tdhock
Is this issue still valid? I'd like to work on it.
From the discussion, it looks like the behavior of .I returning 0 for non-matching rows with .EACHI is expected but not well-documented. My approach would be:
Let me know if this sounds good, and I can start working on it!
tdhock commentedon Aug 20, 2025
sure if you can find some docs to improve go for it
ben-schwen commentedon Aug 22, 2025
So we reuse the space of special symbols for every group.
If there is no match we deliberately write 0 here for
.I, which we could change toNAhttps://github.com/Rdatatable/data.table/blame/c27ec26f0112c62d8c51e8c064409a82e7b0db9c/src/dogroups.c#L218-L236