`epix_merge()` has unexpected internal error

[dajmcdon]

library(epiprocess)
#> Loading required package: epidatasets
#> Registered S3 method overwritten by 'tsibble':
#>   method               from 
#>   as_tibble.grouped_df dplyr
#> 
#> Attaching package: 'epiprocess'
#> The following object is masked from 'package:stats':
#> 
#>     filter
buggy_list <- readRDS("~/Desktop/buggy_archives.rds")
x <- buggy_list$x
y <- buggy_list$y
epix_merge(x, y)
#> Warning in as.data.table.list(x, key = key, ...): Item 1 has 2896 rows but
#> longest item has 2906; recycled with remainder.
#> Warning in as.data.table.list(x, key = key, ...): Item 2 has 2896 rows but
#> longest item has 2906; recycled with remainder.
#> Warning in as.data.table.list(x, key = key, ...): Item 3 has 2896 rows but
#> longest item has 2906; recycled with remainder.
#> Warning in as.data.table.list(x, key = key, ...): Item 5 has 2896 rows but
#> longest item has 2906; recycled with remainder.
#> Error in `validate_epi_archive()`:
#> ! `x$DT` must have one row per unique combination of the key variables.
#>   If you have additional key variables other than `geo_value`, `time_value`,
#>   and `version`, such as an age group column, please specify them in
#>   `other_keys`. Otherwise, check for duplicate rows and/or conflicting values
#>   for the same measurement.

^{Created on 2026-01-01 with reprex v2.1.1}

I believe that the error happens at

set(result_dt, , y_nonby_colnames, y_dt[result_dt[, by, with = FALSE], 
        y_nonby_colnames, with = FALSE, on = by, roll = TRUE, 
        nomatch = NA, allow.cartesian = TRUE])

The y_dt[...] seems to create extra rows.

buggy_archives.rds.zip

[dshemetov]

The two buggy archives have stale data.table sorted attributes. They claim to be sorted in this order (which epix_merge needs to work)

r$> attributes(buggy_list$x$DT)$sorted
    attributes(buggy_list$y$DT)$sorted
[1] "geo_value"  "time_value" "version"   
[1] "geo_value"  "time_value" "version"   

but both are clearly sorted by "geo_value", "version", and then "time_value"

r$> buggy_list$x
→ An `epi_archive` object, with metadata:
ℹ Min/max time values: 2020-07-14 / 2023-03-05
ℹ First/last version with update: 2020-11-22 / 2023-03-05
ℹ Versions end: 2023-03-05
ℹ A preview of the table (1444 rows x 4 columns):
Key: <geo_value, time_value, version>
         version time_value geo_value sim_deaths
          <Date>     <Date>    <char>      <num>
   1: 2020-11-22 2020-07-14        pa         20
   2: 2020-11-22 2020-07-15        pa         26
   3: 2020-11-22 2020-07-16        pa         27
   4: 2020-11-22 2020-07-17        pa         20
   5: 2020-11-22 2020-07-18        pa         11
  ---                                           
1440: 2023-03-05 2023-03-01        pa        113
1441: 2023-03-05 2023-03-02        pa         15
1442: 2023-03-05 2023-03-03        pa         14
1443: 2023-03-05 2023-03-04        pa         21
1444: 2023-03-05 2023-03-05        pa         14

r$> buggy_list$y
→ An `epi_archive` object, with metadata:
ℹ Min/max time values: 2020-07-14 / 2023-03-05
ℹ First/last version with update: 2020-11-22 / 2023-03-05
ℹ Versions end: 2023-03-05
ℹ A preview of the table (2158 rows x 4 columns):
Key: <geo_value, time_value, version>
         version time_value geo_value hosps
          <Date>     <Date>    <char> <num>
   1: 2020-11-22 2020-07-14        pa    42
   2: 2020-11-22 2020-07-15        pa    55
   3: 2020-11-22 2020-07-16        pa    42
   4: 2020-11-22 2020-07-17        pa    39
   5: 2020-11-22 2020-07-18        pa    31
  ---                                      
2154: 2023-03-05 2023-03-01        pa   150
2155: 2023-03-05 2023-03-02        pa   154
2156: 2023-03-05 2023-03-03        pa   152
2157: 2023-03-05 2023-03-04        pa   155
2158: 2023-03-05 2023-03-05        pa   157

This is an instance of footgun number 3 from here: #618. It's unfortunately very easy for this to happen if you apply dplyr operations on the data.table directly, because dplyr doesn't play nice with data.table internals.

Until we get a long-term solution, my workaround is to be extra defensive and habitually cast as.data.frame (to shed the attributes) and reconstruct the archive. The main culprit I know about is dplyr::arrange, but it's very possible other dplyr operations can cause similar issues.

library(epiprocess)

buggy_list <- readRDS("buggy_archives.rds")
x <- buggy_list$x
y <- buggy_list$y

x <- x$DT %>% as.data.frame() %>% as_epi_archive()
y <- y$DT %>% as.data.frame() %>% as_epi_archive()

# Now works
epix_merge(x, y)

[JavierMtzRdz]

If it helps, removing duplicates after merging with unique and adjusting how multiple matches are handled with set might resolve this issue. Currently, the default setting (according to the documentation) is "all". Changing it to "last" can prevent mismatches with unique keys during the merge.
Here's an example:

library(epiprocess)
#> Loading required package: epidatasets
#> Registered S3 method overwritten by 'tsibble':
#>   method               from 
#>   as_tibble.grouped_df dplyr
#> 
#> Attaching package: 'epiprocess'
#> The following object is masked from 'package:stats':
#> 
#>     filter
library(data.table)

# Load data
buggy_list <- readRDS("buggy_archives.rds")
x <- buggy_list$x
y <- buggy_list$y

# Setup
new_versions_end <- x$versions_end
x_dt <- x$DT
y_dt <- y$DT
by <- key(x_dt)

x_by_vals <- x_dt[, by, with = FALSE]
y_by_vals <- y_dt[, by, with = FALSE]
x_nonby_colnames <- setdiff(names(x_dt), by)
y_nonby_colnames <- setdiff(names(y_dt), by)

# Merge works correctly now
result_dt <- merge(x_by_vals, y_by_vals,
  by = by,
  all = TRUE,
  allow.cartesian = TRUE
)

set(
  result_dt, , x_nonby_colnames,
  x_dt[result_dt[, by, with = FALSE], x_nonby_colnames,
    with = FALSE,
    on = by,
    roll = TRUE,
    nomatch = NA,
    allow.cartesian = TRUE
  ]
)
set(
  result_dt, , y_nonby_colnames,
  y_dt[result_dt[, by, with = FALSE], y_nonby_colnames,
    with = FALSE,
    on = by,
    roll = TRUE,
    nomatch = NA,
    allow.cartesian = TRUE
  ]
)
setkeyv(result_dt, by)

# Final Validation
tryCatch(as_epi_archive(
  result_dt[],
  other_keys = setdiff(key(result_dt), c("geo_value", "time_value", "version")),
  versions_end = new_versions_end
))
#> Warning in as.data.table.list(x, key = key, ...): Item 1 has 2896 rows but
#> longest item has 2906; recycled with remainder.
#> Warning in as.data.table.list(x, key = key, ...): Item 2 has 2896 rows but
#> longest item has 2906; recycled with remainder.
#> Warning in as.data.table.list(x, key = key, ...): Item 3 has 2896 rows but
#> longest item has 2906; recycled with remainder.
#> Warning in as.data.table.list(x, key = key, ...): Item 5 has 2896 rows but
#> longest item has 2906; recycled with remainder.
#> Error in `validate_epi_archive()`:
#> ! `x$DT` must have one row per unique combination of the key variables.
#>   If you have additional key variables other than `geo_value`, `time_value`,
#>   and `version`, such as an age group column, please specify them in
#>   `other_keys`. Otherwise, check for duplicate rows and/or conflicting values
#>   for the same measurement.


# This line deletes the corrupted index on 'x'.
# Probably the root cause, as merge() works nicely after this.
key(x_dt)
#> [1] "geo_value"  "time_value" "version"
setkey(x_dt, NULL)
key(x_dt)
#> NULL

x_by_vals <- x_dt[, by, with = FALSE]
y_by_vals <- y_dt[, by, with = FALSE]
x_nonby_colnames <- setdiff(names(x_dt), by)
y_nonby_colnames <- setdiff(names(y_dt), by)

# Merge works correctly now
result_dt <- merge(x_by_vals, y_by_vals,
  by = by,
  all = TRUE,
  allow.cartesian = TRUE
)

set(
  result_dt, , x_nonby_colnames,
  x_dt[result_dt[, by, with = FALSE], x_nonby_colnames,
    with = FALSE,
    on = by,
    roll = TRUE,
    nomatch = NA,
    allow.cartesian = TRUE
  ]
)
set(
  result_dt, , y_nonby_colnames,
  y_dt[result_dt[, by, with = FALSE], y_nonby_colnames,
    with = FALSE,
    on = by,
    roll = TRUE,
    nomatch = NA,
    allow.cartesian = TRUE
  ]
)
setkeyv(result_dt, by)

# Final Validation
tryCatch(as_epi_archive(
  result_dt[],
  other_keys = setdiff(key(result_dt), c("geo_value", "time_value", "version")),
  versions_end = new_versions_end
))
#> → An `epi_archive` object, with metadata:
#> ℹ Min/max time values: 2020-07-14 / 2023-03-05
#> ℹ First/last version with update: 2020-11-22 / 2023-03-05
#> ℹ Versions end: 2023-03-05
#> ℹ A preview of the table (2383 rows x 5 columns):
#> Key: <geo_value, time_value, version>
#>       geo_value time_value    version sim_deaths hosps
#>          <char>     <Date>     <Date>      <num> <num>
#>    1:        pa 2020-07-14 2020-11-22         20    42
#>    2:        pa 2020-07-14 2021-06-06         21    42
#>    3:        pa 2020-07-15 2020-11-22         26    55
#>    4:        pa 2020-07-15 2021-06-06         25    55
#>    5:        pa 2020-07-16 2020-11-22         27    42
#>   ---                                                 
#> 2379:        pa 2023-03-01 2023-03-05        113   150
#> 2380:        pa 2023-03-02 2023-03-05         15   154
#> 2381:        pa 2023-03-03 2023-03-05         14   152
#> 2382:        pa 2023-03-04 2023-03-05         21   155
#> 2383:        pa 2023-03-05 2023-03-05         14   157

# However it modifies the original dt
key(buggy_list$x$DT)
#> NULL

# A propose solution
# Load data
buggy_list <- readRDS("buggy_archives.rds")
x <- buggy_list$x
y <- buggy_list$y

# Setup
new_versions_end <- x$versions_end
x_dt <- x$DT
y_dt <- y$DT
by <- key(x_dt)

x_by_vals <- x_dt[, by, with = FALSE]
y_by_vals <- y_dt[, by, with = FALSE]
x_nonby_colnames <- setdiff(names(x_dt), by)
y_nonby_colnames <- setdiff(names(y_dt), by)

# Merge works correctly now
result_dt <- merge(x_by_vals, y_by_vals,
  by = by,
  all = TRUE,
  allow.cartesian = TRUE
)

# Remove duplicated rows resulting from the corrupted index

result_dt <- unique(result_dt)

# Rolling Join with mult="last"

set(
  result_dt, , x_nonby_colnames,
  x_dt[result_dt[, by, with = FALSE], x_nonby_colnames,
    with = FALSE,
    on = by,
    roll = TRUE,
    nomatch = NA,
    allow.cartesian = TRUE,
    mult = "last"
  ]
)
set(
  result_dt, , y_nonby_colnames,
  y_dt[result_dt[, by, with = FALSE], y_nonby_colnames,
    with = FALSE,
    on = by,
    roll = TRUE,
    nomatch = NA,
    allow.cartesian = TRUE,
    mult = "last"
  ]
)
setkeyv(result_dt, by)

# Final Validation
as_epi_archive(
  result_dt[],
  other_keys = setdiff(key(result_dt), c("geo_value", "time_value", "version")),
  versions_end = new_versions_end
)
#> → An `epi_archive` object, with metadata:
#> ℹ Min/max time values: 2020-07-14 / 2023-03-05
#> ℹ First/last version with update: 2020-11-22 / 2023-03-05
#> ℹ Versions end: 2023-03-05
#> ℹ A preview of the table (2248 rows x 5 columns):
#> Key: <geo_value, time_value, version>
#>       geo_value time_value    version sim_deaths hosps
#>          <char>     <Date>     <Date>      <num> <num>
#>    1:        pa 2020-07-14 2020-11-22         20    42
#>    2:        pa 2020-07-15 2020-11-22         26    55
#>    3:        pa 2020-07-16 2020-11-22         27    42
#>    4:        pa 2020-07-17 2020-11-22         20    39
#>    5:        pa 2020-07-18 2020-11-22         11    31
#>   ---                                                 
#> 2244:        pa 2023-03-01 2023-03-05        113   150
#> 2245:        pa 2023-03-02 2023-03-05         15   154
#> 2246:        pa 2023-03-03 2023-03-05         14   152
#> 2247:        pa 2023-03-04 2023-03-05         21   155
#> 2248:        pa 2023-03-05 2023-03-05         14   157

Another alternative is to clear the index before using epix_merge.

buggy_list <- readRDS("buggy_archives.rds")
x <- buggy_list$x
y <- buggy_list$y
# Extract and copy the DT
x_dt_fixed <- copy(x$DT)
# Clear the corrupted index
setkey(x_dt_fixed, NULL)
# Restore the keys
setkeyv(x_dt_fixed, key(x$DT))
# Update the archive object
x$DT <- x_dt_fixed
# Merge
res <- epix_merge(x, y)
res
#> → An `epi_archive` object, with metadata:
#> ℹ Min/max time values: 2020-07-14 / 2023-03-05
#> ℹ First/last version with update: 2020-11-22 / 2023-03-05
#> ℹ Versions end: 2023-03-05
#> ℹ A preview of the table (2383 rows x 5 columns):
#> Key: <geo_value, time_value, version>
#>       geo_value time_value    version sim_deaths hosps
#>          <char>     <Date>     <Date>      <num> <num>
#>    1:        pa 2020-07-14 2020-11-22         20    42
#>    2:        pa 2020-07-14 2021-06-06         21    42
#>    3:        pa 2020-07-15 2020-11-22         26    55
#>    4:        pa 2020-07-15 2021-06-06         25    55
#>    5:        pa 2020-07-16 2020-11-22         27    42
#>   ---                                                 
#> 2379:        pa 2023-03-01 2023-03-05        113   150
#> 2380:        pa 2023-03-02 2023-03-05         15   154
#> 2381:        pa 2023-03-03 2023-03-05         14   152
#> 2382:        pa 2023-03-04 2023-03-05         21   155
#> 2383:        pa 2023-03-05 2023-03-05         14   157

^{Created on 2026-01-05 with reprex v2.1.1}

[brookslogan]

Regarding Dmitry's point, I view this as it is a bug to use dplyr ops on a data.table. You should as.data.frame first before performing any dplyr. Fixing up after the fact might work but I'm afraid that sometimes it might not.

Without thinking too much about it, I would also be wary of tweaking join options to try to fix. Data.table is working with corrupt objects and I'd be nervous depending on any behavior there. Fix is to avoid corrupt objects in the first place.