Non-equi join showing columns in a not so perfect style

Open
#5,593 2 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Assessment

Difficulty
4/5
Estimated time
3-5 days
Newbie friendliness
25/100
Issue type
Feature
Clarity
Mostly clear
Activity status
Stale
Tech stack
r
Domain
databases

Research direction

Start with the non-equi join expression transactions[companies, on = .(company == id, year >= since)] and compare its output with the dplyr example. Determine whether the requested change concerns the displayed join columns, retaining both key columns, or both. Done means establishing the intended output and either identifying existing options or documenting the required behavior; no files or tests are named in the issue.

Written by the indexing model from the issue text.

Description

non-equi joins

In the latest version of dplyr, non-equi joins could be realized by:

library(dplyr)

transactions <- tibble(
  company = c("A", "A", "B", "B"),
  year = c(2019, 2020, 2021, 2023),
  revenue = c(50, 4, 10, 12)
)
transactions
#> # A tibble: 4 × 3
#>   company  year revenue
#>   <chr>   <dbl>   <dbl>
#> 1 A        2019      50
#> 2 A        2020       4
#> 3 B        2021      10
#> 4 B        2023      12

companies <- tibble(
  id = c("A", "B", "B"),
  since = c(1973, 2009, 2022),
  name = c("Patagonia", "RStudio", "Posit")
)

companies
#> # A tibble: 3 × 3
#>   id    since name     
#>   <chr> <dbl> <chr>    
#> 1 A      1973 Patagonia
#> 2 B      2009 RStudio  
#> 3 B      2022 Posit


transactions |>
  inner_join(companies, join_by(company == id, year >= since))
#> # A tibble: 5 × 5
#>   company  year revenue since name     
#>   <chr>   <dbl>   <dbl> <dbl> <chr>    
#> 1 A        2019      50  1973 Patagonia
#> 2 A        2020       4  1973 Patagonia
#> 3 B        2021      10  2009 RStudio  
#> 4 B        2023      12  2009 RStudio  
#> 5 B        2023      12  2022 Posit

Using data.table, I yield:

library(data.table)
#> 
#> Attaching package: 'data.table'
#> The following objects are masked from 'package:dplyr':
#> 
#>     between, first, last
setDT(transactions)
setDT(companies)
transactions[companies, on = .(company == id, year >= since)]
#>    company year revenue      name
#> 1:       A 1973      50 Patagonia
#> 2:       A 1973       4 Patagonia
#> 3:       B 2009      10   RStudio
#> 4:       B 2009      12   RStudio
#> 5:       B 2022      12     Posit

The operation is the same, but the results is not desirable as that of dplyr's, and dplyr also supports to keep all the columns using argument keep = TRUE. How can I get something similar in data.table?

Thanks.

Dominant language
R
Stars
3.9k
Forks
1.1k
Avg merge
14h 4m
Merged PRs (30d)
4

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from Rdatatable/data.table

All issues in Rdatatable/data.table

Similar issues

More R issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.