minijoin

minijoin provides four mutating joins – mjoin_inner_join()/_left_join()/_right_join()/_full_join() – built on base merge(). See the README for the full argument reference; this page compares all four side by side on two small toy data frames with a partial overlap, then covers the by argument in more detail.

Functions

• mjoin_inner_join(x, y, by, ...) – keep only rows with a match in both x and y.
• mjoin_left_join(x, y, by, ...) – keep all rows of x, filling in NA for y’s columns where there’s no match.
• mjoin_right_join(x, y, by, ...) – keep all rows of y, filling in NA for x’s columns where there’s no match.
• mjoin_full_join(x, y, by, ...) – keep all rows of both x and y, filling in NA on whichever side has no match.

minijoin has one internal helper, .mjoin_worker(), which all four join functions delegate to after fixing the right combination of merge()’s all.x/all.y arguments; it also restores x’s original row order, which merge() doesn’t guarantee on its own.

bands <- data.frame(
  band = c("Beatles", "Rolling Stones", "Kinks"),
  founded = c(1960, 1962, 1963)
)
albums <- data.frame(
  band = c("Beatles", "Kinks", "Queen"),
  album = c("Abbey Road", "Arthur", "A Night at the Opera")
)
bands

            band founded
1        Beatles    1960
2 Rolling Stones    1962
3          Kinks    1963

albums

     band                album
1 Beatles           Abbey Road
2   Kinks               Arthur
3   Queen A Night at the Opera

bands and albums share "Beatles" and "Kinks"; "Rolling Stones" exists only in bands, and "Queen" only in albums. The four join types differ only in which of those non-matching rows they keep.

Inner join: only rows matching in both

Neither "Rolling Stones" nor "Queen" appears – an inner join is the most restrictive of the four, keeping only rows with a counterpart on the other side:

mjoin_inner_join(bands, albums, by = "band")

     band founded      album
1 Beatles    1960 Abbey Road
2   Kinks    1963     Arthur

Left join: all rows of x (bands)

"Rolling Stones" is kept, with NA for album, since it has no match in albums. This is the join to reach for when x is your “main” table and you’re enriching it with optional data from y:

mjoin_left_join(bands, albums, by = "band")

            band founded      album
1        Beatles    1960 Abbey Road
2 Rolling Stones    1962       <NA>
3          Kinks    1963     Arthur

Right join: all rows of y (albums)

"Queen" is kept, with NA for founded. A right join is really just a left join with the arguments swapped – mjoin_right_join(x, y, ...) behaves like mjoin_left_join(y, x, ...), with columns in x‘s original order. Note the row order below: matched rows still come first, in bands’ original order, with the unmatched-in-x row ("Queen") appended at the end – minijoin always restores x’s original row order for matched rows, rather than y’s:

mjoin_right_join(bands, albums, by = "band")

     band founded                album
1 Beatles    1960           Abbey Road
2   Kinks    1963               Arthur
3   Queen      NA A Night at the Opera

Full join: all rows of both

Both "Rolling Stones" and "Queen" are kept, each with an NA in the column they don’t have a match for – a full join never drops a row from either side, so it’s the join to reach for when you want to see everything and let missing matches show up as NA rather than disappearing silently:

mjoin_full_join(bands, albums, by = "band")

            band founded                album
1        Beatles    1960           Abbey Road
2 Rolling Stones    1962                 <NA>
3          Kinks    1963               Arthur
4          Queen      NA A Night at the Opera

by is always explicit

Unlike dplyr, there’s no auto-detection of shared column names – by must always be supplied. This is a deliberate simplification, not an oversight, to avoid a class of silent bugs from an unintended shared column name (two data frames that happen to both have an id column with unrelated meanings, for instance).

When the join column has different names on each side, by takes a named character vector instead of a single string, c("x_name" = "y_name") – mirroring dplyr’s own syntax for the same situation:

albums_renamed <- albums
names(albums_renamed)[1] <- "artist"
albums_renamed

   artist                album
1 Beatles           Abbey Road
2   Kinks               Arthur
3   Queen A Night at the Opera

mjoin_inner_join(bands, albums_renamed, by = c("band" = "artist"))

     band founded      album
1 Beatles    1960 Abbey Road
2   Kinks    1963     Arthur

Note that the output keeps x’s column name (band), not y’s (artist) – the join collapses the two into a single column, using whichever name x had.