Skip to contents

Left anti-join

Source: R/fjoin_functions.R
fjoin_left_anti.Rd

The anti-join of x in a join of x and y, i.e. the rows of x that do not join. The alias fjoin_anti can be used instead.

Usage

fjoin_left_anti(
  x = NULL,
  y = NULL,
  on,
  match.na = FALSE,
  mult.x = "all",
  mult.y = "all",
  select = NULL,
  do = !(is.null(x) && is.null(y)),
  show = !do
)

fjoin_anti(
  x = NULL,
  y = NULL,
  on,
  match.na = FALSE,
  mult.x = "all",
  mult.y = "all",
  select = NULL,
  do = !(is.null(x) && is.null(y)),
  show = !do
)

Arguments

x, y

data.frame-like objects (plain, data.table, tibble, sf, list, etc.) or else both omitted for a mock join statement with no data. See Details.

on

A character vector of join predicates, e.g. c("id", "col_x == col_y", "date > date", "cost <= budget").

match.na

Whether to allow equality matches between NAs or NaNs. Default FALSE.

mult.x

When a row of x has multiple matching rows in y, which to accept: "all" (the default), "first", or "last".

mult.y

When a row of y has multiple matching rows in x, which to accept: "all" (the default), "first", or "last". Can be combined with mult.x.

select

Character vector of non-join columns to be selected from x. NULL (the default) selects all columns. Join columns are always selected.

do

Whether to execute the join. If FALSE, show is set to TRUE and the data.table code for the join is printed to the console instead. Default is TRUE unless x and y are both omitted/NULL, in which case a mock join statement is produced. See Details.

show

Whether to print the data.table code for the join to the console. Default is the opposite of do. If x and y are both omitted/NULL, mock join code is displayed.

Value

A data.frame, data.table, (grouped) tibble, sf, or sf-tibble, or else NULL if do is FALSE. See Details.

Details

Details are as for fjoin_inner except for arguments controlling the order and prefixing of output columns, which do not apply. Output class is determined by x.

See also

See the package-level documentation fjoin for related functions.

Examples

# ---------------------------------------------------------------------------
# Semi- and anti-joins: basic usage
# ---------------------------------------------------------------------------

# data frames
x <- data.table::fread(data.table = FALSE, input = "
country   pop_m
Australia  27.2
Brazil    212.0
Chad        3.0
")

y <- data.table::fread(data.table = FALSE, input = "
country forest_pc
Brazil       59.1
Chad          3.2
Denmark      15.8
")

# full join with `indicate = TRUE` for comparison
fjoin_full(x, y, on = "country", indicate = TRUE)
#>   .join   country pop_m forest_pc
#> 1     1 Australia  27.2        NA
#> 2     3    Brazil 212.0      59.1
#> 3     3      Chad   3.0       3.2
#> 4     2   Denmark    NA      15.8

fjoin_semi(x, y, on = "country")
#>   country pop_m
#> 1  Brazil   212
#> 2    Chad     3
fjoin_anti(x, y, on = "country")
#>     country pop_m
#> 1 Australia  27.2
fjoin_right_semi(x, y, on = "country")
#>   country forest_pc
#> 1  Brazil      59.1
#> 2    Chad       3.2
fjoin_right_anti(x, y, on = "country")
#>   country forest_pc
#> 1 Denmark      15.8

# ---------------------------------------------------------------------------
# `mult.x` and `mult.y` support
# ---------------------------------------------------------------------------

# data frames
events <- data.table::fread(data.table = FALSE, input = "
event_id event_ts
       1       10
       2       20
       3       40
")

reactions <- data.table::fread(data.table = FALSE, input = "
reaction_id reaction_ts
          1          30
          2          50
          3          60
")
NULL # section break
#> NULL

# for each event, the next reaction, provided there was no intervening event (1:1)
fjoin_full(
  events,
  reactions,
  on = c("event_ts < reaction_ts"),
  mult.x = "first",
  mult.y = "last",
  indicate = TRUE
)
#>   .join event_id event_ts reaction_id reaction_ts
#> 1     1        1       10          NA          NA
#> 2     3        2       20           1          30
#> 3     3        3       40           2          50
#> 4     2       NA       NA           3          60

fjoin_semi(
  events,
  reactions,
  on = c("event_ts < reaction_ts"),
  mult.x = "first",
  mult.y = "last"
)
#>   event_id event_ts
#> 1        2       20
#> 2        3       40

fjoin_anti(
  events,
  reactions,
  on = c("event_ts < reaction_ts"),
  mult.x = "first",
  mult.y = "last"
)
#>   event_id event_ts
#> 1        1       10