Joins, binding, and set operations

Joins

The join functions take the right-hand table as their first argument. The input table is the left-hand table x.

by: names the key columns. A string or list joins on columns with the same name in both tables, and an associative array maps differing names: by: ['school' => 'school_id']. The forms mix, as in ['id', 'code' => 'country_code']. Without by:, the tables join on every column they share.

$schools = DataFrame::fromCsv('schools.csv') |>
    group_by('school') |>
    summarize(avg_score: mean('score'));

$districts = DataFrame::fromRows([
    ['school_id' => 'school-01', 'district' => 'North'],
    ['school_id' => 'school-02', 'district' => 'North'],
    ['school_id' => 'school-03', 'district' => 'South'],
    ['school_id' => 'school-99', 'district' => 'East'],
]);

echo $schools |> inner_join($districts, by: ['school' => 'school_id']);
echo "\n", $schools |> left_join($districts, by: ['school' => 'school_id']) |> head(5);
echo "\n", $districts |> anti_join($schools, by: ['school_id' => 'school']);

$schools = phplyr(DataFrame::fromCsv('schools.csv'))
    ->groupBy('school')
    ->summarize(avg_score: mean('score'))
    ->get();

$districts = DataFrame::fromRows([
    ['school_id' => 'school-01', 'district' => 'North'],
    ['school_id' => 'school-02', 'district' => 'North'],
    ['school_id' => 'school-03', 'district' => 'South'],
    ['school_id' => 'school-99', 'district' => 'East'],
]);

echo phplyr($schools)->innerJoin($districts, by: ['school' => 'school_id']);
echo "\n", phplyr($schools)->leftJoin($districts, by: ['school' => 'school_id'])->head(5);
echo "\n", phplyr($districts)->antiJoin($schools, by: ['school_id' => 'school']);

# A tibble: 3 × 3
  school    avg_score district
  <chr>         <dbl> <chr>   
1 school-01      72.9 North   
2 school-02      72.9 North   
3 school-03      69.0 South   
# A tibble: 5 × 3
  school    avg_score district
  <chr>         <dbl> <chr>   
1 school-01      72.9 North   
2 school-02      72.9 North   
3 school-03      69.0 South   
4 school-04      69.6 <NA>    
5 school-05      73.0 <NA>    
# A tibble: 1 × 2
  school_id district
  <chr>     <chr>   
1 school-99 East

The options follow dplyr 1.1:

• suffix: renames clashing non-key columns, ['.x', '.y'] by default.
• keep: true keeps y’s key columns.
• naMatches: 'never' stops NA keys from matching. By default NA matches NA.
• multiple: is 'all', 'any', 'first', or 'last'.
• unmatched: 'error' throws when rows would be dropped.
• relationship: checks 'one-to-one', 'one-to-many', 'many-to-one', or 'many-to-many'. An unexpected many-to-many join raises dplyr’s warning.

$x = DataFrame::fromRows([['id' => 1, 'v' => 'a'], ['id' => 2, 'v' => 'b'], ['id' => null, 'v' => 'c']]);
$y = DataFrame::fromRows([['id' => 2, 'v' => 'B'], ['id' => 2, 'v' => 'B2'], ['id' => null, 'v' => 'C']]);

// Clashing columns get suffixes; NA keys match each other, as in dplyr.
echo $x |> left_join($y, by: 'id');

// Keep only the first match, and never match NA keys.
echo "\n", $x |> left_join($y, by: 'id', multiple: 'first', naMatches: 'never', suffix: ['x', '_y']);

$x = DataFrame::fromRows([['id' => 1, 'v' => 'a'], ['id' => 2, 'v' => 'b'], ['id' => null, 'v' => 'c']]);
$y = DataFrame::fromRows([['id' => 2, 'v' => 'B'], ['id' => 2, 'v' => 'B2'], ['id' => null, 'v' => 'C']]);

// Clashing columns get suffixes; NA keys match each other, as in dplyr.
echo phplyr($x)->leftJoin($y, by: 'id');

// Keep only the first match, and never match NA keys.
echo "\n", phplyr($x)->leftJoin($y, by: 'id', multiple: 'first', naMatches: 'never', suffix: ['x', '_y']);

# A tibble: 4 × 3
     id v.x   v.y  
  <int> <chr> <chr>
1     1 a     <NA> 
2     2 b     B    
3     2 b     B2   
4    NA c     C    
# A tibble: 3 × 3
     id vx    v_y  
  <int> <chr> <chr>
1     1 a     <NA> 
2     2 b     B    
3    NA c     <NA>

A row of x that matches several rows of y appears once per match, so a join can return more rows than x has. The result keeps x’s row order, with several matches in y’s order. Unmatched rows of x get NA in y’s columns in a left or full join. multiple: and relationship: limit or check the matches.

Joins keep x’s grouping, except for a grouping column that the join renames.

join_by()

join_by() builds dplyr’s join specifications. A string is an equality on the same column name, ['a' => 'b'] maps names, and a comparison of two col() expressions compares x’s column (left) with y’s (right). Every condition must hold.

join_by('id', col('t')->ge(col('start')), col('t')->lt(col('end')))
join_by('id', between('t', 'start', 'end'))        // start and end are y's columns
join_by('id', closest(col('t')->ge(col('start'))))  // the latest start at or before t
join_by(within('lo', 'hi', 'start', 'end'))         // x's interval inside y's
join_by(overlaps('lo', 'hi', 'start', 'end'))

For inequality, rolling, and overlap joins, keep defaults to dplyr’s behavior: equality keys are merged and the columns in other conditions are kept from both tables. keep: false is an error for these joins. Database tables run them in SQL, except right and full rolling joins, which load the rows.

nest_join() keeps every row of x and adds a list-column (name:, default 'data') holding each row’s matching rows of y as a table.

Binding

bind_rows() stacks tables under the input one, matching columns by name and filling missing columns with NA. _id: adds a column with each row’s source. bind_cols() puts tables side by side and renames duplicate names as name...position.

$a = DataFrame::fromRows([['id' => 1, 'v' => 'a']]);
$b = DataFrame::fromRows([['id' => 2, 'w' => true]]);

echo $a |> bind_rows($b, _id: 'source');
echo "\n", $a |> bind_cols(DataFrame::fromRows([['id' => 10]]));

$a = DataFrame::fromRows([['id' => 1, 'v' => 'a']]);
$b = DataFrame::fromRows([['id' => 2, 'w' => true]]);

echo phplyr($a)->bindRows($b, _id: 'source');
echo "\n", phplyr($a)->bindCols(DataFrame::fromRows([['id' => 10]]));

# A tibble: 2 × 4
  source    id v     w    
  <chr>  <int> <chr> <lgl>
1 1          1 a     NA   
2 2          2 <NA>  TRUE 
# A tibble: 1 × 3
  id...1 v     id...3
   <int> <chr>  <int>
1      1 a         10

Set operations and row updates

union(), union_all(), intersect(), setdiff(), and symdiff() compare whole rows. setequal() returns true when both tables hold the same distinct rows.

The rows_*() functions modify the input table using another table’s rows, matched on by: (y’s first column by default):

• rows_insert() adds rows with new keys; conflict: 'ignore' skips existing keys.
• rows_append() adds all rows.
• rows_update() overwrites values for matching keys.
• rows_patch() fills only NA values for matching keys.
• rows_upsert() updates matching keys and inserts new ones.
• rows_delete() removes matching keys.

unmatched: 'ignore' allows y keys that x lacks.

$stock = DataFrame::fromRows([
    ['sku' => 'A1', 'qty' => 5],
    ['sku' => 'B2', 'qty' => null],
    ['sku' => 'C3', 'qty' => 0],
]);
$delivery = DataFrame::fromRows([['sku' => 'B2', 'qty' => 12], ['sku' => 'D4', 'qty' => 3]]);

echo $stock |> rows_upsert($delivery, by: 'sku');
echo "\n", $stock |> rows_patch($delivery, by: 'sku', unmatched: 'ignore');
echo "\n", $stock |> setdiff(DataFrame::fromRows([['sku' => 'C3', 'qty' => 0]]));

$stock = DataFrame::fromRows([
    ['sku' => 'A1', 'qty' => 5],
    ['sku' => 'B2', 'qty' => null],
    ['sku' => 'C3', 'qty' => 0],
]);
$delivery = DataFrame::fromRows([['sku' => 'B2', 'qty' => 12], ['sku' => 'D4', 'qty' => 3]]);

echo phplyr($stock)->rowsUpsert($delivery, by: 'sku');
echo "\n", phplyr($stock)->rowsPatch($delivery, by: 'sku', unmatched: 'ignore');
echo "\n", phplyr($stock)->setdiff(DataFrame::fromRows([['sku' => 'C3', 'qty' => 0]]));

# A tibble: 4 × 2
  sku     qty
  <chr> <int>
1 A1        5
2 B2       12
3 C3        0
4 D4        3
# A tibble: 3 × 2
  sku     qty
  <chr> <int>
1 A1        5
2 B2       12
3 C3        0
# A tibble: 2 × 2
  sku     qty
  <chr> <int>
1 A1        5
2 B2       NA