tidyr

Pivoting

pivot_longer() turns columns into rows of names and values. namesPrefix strips a regular expression from the start of each name. namesSep or namesPattern split names into several namesTo columns, and the special name '.value' takes the value column’s name from that part. valuesDropNa: true drops rows whose values are all NA. namesTransform and valuesTransform take a closure, or an array of closures by column. colsVary: 'slowest' keeps each source column’s rows together.

pivot_wider() spreads names and values into columns. idCols identify the rows (by default every other column). valuesFill fills missing cells, valuesFn combines duplicate cells, and namesPrefix, namesSep, namesGlue, and namesSort control the new names. Without valuesFn, duplicate cells turn the value column into list-columns and raise tidyr’s warning. namesVary: 'slowest' groups each name’s value columns together. namesExpand and idExpand add every combination of the namesFrom and idCols values (sorted, factors by level). unusedFn summarizes the columns that are in none of idCols, namesFrom, and valuesFrom.

Both functions build tidyr’s pivot spec in phplyr and pass it to the table’s pivotLongerSpec() or pivotWiderSpec(), so a backend can run the pivot natively. build_longer_spec() and build_wider_spec() return that spec as a table, and pivot_longer_spec() and pivot_wider_spec() run an edited one. The superseded gather() and spread() are also available.

$scores = DataFrame::fromRows([
    ['student' => 'ann', 'wk1' => 10, 'wk2' => null, 'wk3' => 30],
    ['student' => 'bob', 'wk1' => 7, 'wk2' => 8, 'wk3' => 9],
]);

$long = $scores |> pivot_longer(starts_with('wk'), namesTo: 'week', namesPrefix: 'wk', valuesTo: 'points', valuesDropNa: true);
echo $long;

echo "\n", $long |> pivot_wider(namesFrom: 'week', valuesFrom: 'points', namesPrefix: 'week_', valuesFill: 0, namesSort: true);

$scores = DataFrame::fromRows([
    ['student' => 'ann', 'wk1' => 10, 'wk2' => null, 'wk3' => 30],
    ['student' => 'bob', 'wk1' => 7, 'wk2' => 8, 'wk3' => 9],
]);

$long = phplyr($scores)->pivotLonger(starts_with('wk'), namesTo: 'week', namesPrefix: 'wk', valuesTo: 'points', valuesDropNa: true)->get();
echo $long;

echo "\n", phplyr($long)->pivotWider(namesFrom: 'week', valuesFrom: 'points', namesPrefix: 'week_', valuesFill: 0, namesSort: true);

# A tibble: 5 × 3
  student week  points
  <chr>   <chr>  <int>
1 ann     1         10
2 ann     3         30
3 bob     1          7
4 bob     2          8
5 bob     3          9
# A tibble: 2 × 4
  student week_1 week_2 week_3
  <chr>    <int>  <int>  <int>
1 ann         10      0     30
2 bob          7      8      9

Missing combinations and values

complete() adds missing combinations of the given columns and fills other columns with NA, or with values from fill:. A column name expands to its unique values (a factor to its levels), nesting() keeps only combinations that occur, and a named list supplies the values. It works within groups. expand() returns only the combinations, and crossing() and expand_grid() build a new table from lists.

echo DataFrame::fromRows([
    ['shop' => 'north', 'month' => 1, 'sales' => 10],
    ['shop' => 'north', 'month' => 3, 'sales' => 12],
    ['shop' => 'south', 'month' => 2, 'sales' => 7],
]) |>
    complete('shop', month: [1, 2, 3], fill: ['sales' => 0]);

echo phplyr(DataFrame::fromRows([
    ['shop' => 'north', 'month' => 1, 'sales' => 10],
    ['shop' => 'north', 'month' => 3, 'sales' => 12],
    ['shop' => 'south', 'month' => 2, 'sales' => 7],
]))
    ->complete('shop', month: [1, 2, 3], fill: ['sales' => 0]);

# A tibble: 6 × 3
  shop  month sales
  <chr> <int> <int>
1 north     1    10
2 north     2     0
3 north     3    12
4 south     1     0
5 south     2     7
6 south     3     0

fill() carries values down or up over NA within groups (_direction: 'down' | 'up' | 'downup' | 'updown'). drop_na() drops rows with NA in the given columns, and replace_na() replaces NA per column.

echo DataFrame::fromRows([
    ['quarter' => 'Q1', 'year' => 2024, 'sales' => 10],
    ['quarter' => 'Q2', 'year' => null, 'sales' => null],
    ['quarter' => 'Q3', 'year' => null, 'sales' => 14],
]) |>
    fill('year') |>
    replace_na(['sales' => 0]);

echo phplyr(DataFrame::fromRows([
    ['quarter' => 'Q1', 'year' => 2024, 'sales' => 10],
    ['quarter' => 'Q2', 'year' => null, 'sales' => null],
    ['quarter' => 'Q3', 'year' => null, 'sales' => 14],
]))
    ->fill('year')
    ->replaceNa(['sales' => 0]);

# A tibble: 3 × 3
  quarter  year sales
  <chr>   <int> <int>
1 Q1       2024    10
2 Q2       2024     0
3 Q3       2024    14

Splitting and combining

tooFew and tooMany handle pieces that don’t fit, as in tidyr.

$t = DataFrame::fromRows([
    ['id' => 1, 'name' => 'Ada Lovelace', 'tags' => 'math,code'],
    ['id' => 2, 'name' => 'Grace Hopper', 'tags' => 'navy'],
]);

echo $t |> separate_wider_delim('name', ' ', names: ['first', 'last']);
echo "\n", $t |> select('id', 'tags') |> separate_longer_delim('tags', ',');
echo "\n", $t |> select('id', 'name') |> unite('label', 'id', 'name', sep: ': ');

$t = DataFrame::fromRows([
    ['id' => 1, 'name' => 'Ada Lovelace', 'tags' => 'math,code'],
    ['id' => 2, 'name' => 'Grace Hopper', 'tags' => 'navy'],
]);

echo phplyr($t)->separateWiderDelim('name', ' ', names: ['first', 'last']);
echo "\n", phplyr($t)->select('id', 'tags')->separateLongerDelim('tags', ',');
echo "\n", phplyr($t)->select('id', 'name')->unite('label', 'id', 'name', sep: ': ');

# A tibble: 2 × 4
     id first last     tags     
  <int> <chr> <chr>    <chr>    
1     1 Ada   Lovelace math,code
2     2 Grace Hopper   navy     
# A tibble: 3 × 2
     id tags 
  <int> <chr>
1     1 math 
2     1 code 
3     2 navy 
# A tibble: 2 × 1
  label          
  <chr>          
1 1: Ada Lovelace
2 2: Grace Hopper

List-columns

A list element is a PHP array. A list is R’s unnamed list or vector, and an array with string keys is a named list.

chop, pack, unpack, and extract share names with PHP built-ins, so import them with use function Phplyr\{chop, pack, unpack, extract};.

use function Phplyr\chop;

$people = DataFrame::fromColumns([
    'id' => [1, 2, 3],
    'info' => [
        ['name' => 'ann', 'langs' => ['php', 'r'], 'city' => ['name' => 'Oslo']],
        ['name' => 'bob', 'langs' => ['sql']],
        null,
    ],
]);

echo $people |> hoist('info', town: ['city', 'name']), "\n";

echo $people |> unnest_wider('info') |> select('id', 'langs') |> unnest_longer('langs', indicesInclude: true), "\n";

echo DataFrame::fromColumns(['g' => ['a', 'a', 'b'], 'x' => [1, 2, 3]]) |> chop('x');

use function Phplyr\chop;

$people = DataFrame::fromColumns([
    'id' => [1, 2, 3],
    'info' => [
        ['name' => 'ann', 'langs' => ['php', 'r'], 'city' => ['name' => 'Oslo']],
        ['name' => 'bob', 'langs' => ['sql']],
        null,
    ],
]);

echo phplyr($people)->hoist('info', town: ['city', 'name']), "\n";

echo phplyr($people)->unnestWider('info')->select('id', 'langs')->unnestLonger('langs', indicesInclude: true), "\n";

echo phplyr(DataFrame::fromColumns(['g' => ['a', 'a', 'b'], 'x' => [1, 2, 3]]))->chop('x');

# A tibble: 3 × 3
     id town  info            
  <int> <chr> <list>          
1     1 Oslo  <named list [3]>
2     2 <NA>  <named list [2]>
3     3 <NA>  <NULL>          
# A tibble: 3 × 3
     id langs_id langs
  <int>    <int> <chr>
1     1        1 php  
2     1        2 r    
3     2        1 sql  
# A tibble: 2 × 2
  g     x        
  <chr> <list>   
1 a     <int [2]>
2 b     <int [1]>