Reshaping with tidyr
Summarize to one row per school and band, pivot the bands into columns, split the school id, and pivot back.
Inputs
Runningtidyr's reshaping functions move data between long and wide layouts. Here the schools data is summarized to one row per school and study-time band, pivot_wider() turns the bands into columns, separate_wider_delim() splits the school id, and pivot_longer() turns the band columns back into rows.
$long = $schools |>
mutate(band: case_when([col('study_hours')->lt(4), 'low'], [col('study_hours')->lt(8), 'mid'], default: 'high')) |>
group_by('school', 'band') |>
summarize(students: n(), mean_score: mean('score', naRm: true));
$wide = $long |>
pivot_wider(idCols: 'school', namesFrom: 'band', valuesFrom: 'mean_score', namesPrefix: 'band_') |>
separate_wider_delim('school', '-', names: ['kind', 'number'], colsRemove: false);
$back = $wide |>
pivot_longer(starts_with('band_'), namesTo: 'band', namesPrefix: 'band_', valuesTo: 'mean_score');$long = phplyr($schools)
->mutate(band: case_when([col('study_hours')->lt(4), 'low'], [col('study_hours')->lt(8), 'mid'], default: 'high'))
->groupBy('school', 'band')
->summarize(students: n(), mean_score: mean('score', naRm: true))
->get();
$wide = phplyr($long)
->pivotWider(idCols: 'school', namesFrom: 'band', valuesFrom: 'mean_score', namesPrefix: 'band_')
->separateWiderDelim('school', '-', names: ['kind', 'number'], colsRemove: false)
->get();
$back = phplyr($wide)
->pivotLonger(starts_with('band_'), namesTo: 'band', namesPrefix: 'band_', valuesTo: 'mean_score')
->get();One row per school and band
| school | band | students | mean_score | mean_gain |
|---|---|---|---|---|
| school-01 | high | 7 | 80.65 | 8.25 |
| school-01 | low | 4 | 64.96 | 15.89 |
| school-01 | mid | 7 | 69.72 | 8.14 |
| school-02 | high | 8 | 84.23 | 9.55 |
| school-02 | low | 8 | 65.02 | 14.98 |
| school-02 | mid | 8 | 69.46 | 16.40 |
| school-03 | high | 5 | 77.96 | 12.88 |
| school-03 | low | 3 | 52.25 | 14.15 |
| school-03 | mid | 7 | 69.75 | 12.41 |
| school-04 | high | 13 | 75.05 | 4.96 |
| school-04 | low | 1 | 51.86 | 10.51 |
| school-04 | mid | 8 | 62.99 | 6.83 |
| school-05 | high | 9 | 79.47 | 10.53 |
| school-05 | low | 6 | 67.44 | 15.23 |
| school-05 | mid | 5 | 67.92 | 8.11 |
| school-06 | high | 15 | 83.18 | 10.84 |
| school-06 | low | 4 | 61.69 | 15.16 |
| school-06 | mid | 8 | 72.67 | 13.37 |
| school-07 | high | 9 | 90.18 | 23.98 |
| school-07 | low | 4 | 74.32 | 27.38 |
16 more rows not shown.
Mean final score, one column per band
| school | kind | number | band_high | band_low | band_mid |
|---|---|---|---|---|---|
| school-01 | school | 01 | 80.65 | 64.96 | 69.72 |
| school-02 | school | 02 | 84.23 | 65.02 | 69.46 |
| school-03 | school | 03 | 77.96 | 52.25 | 69.75 |
| school-04 | school | 04 | 75.05 | 51.86 | 62.99 |
| school-05 | school | 05 | 79.47 | 67.44 | 67.92 |
| school-06 | school | 06 | 83.18 | 61.69 | 72.67 |
| school-07 | school | 07 | 90.18 | 74.32 | 79.36 |
| school-08 | school | 08 | 71.28 | 57.49 | 64.41 |
| school-09 | school | 09 | 81.14 | 68.89 | 73.23 |
| school-10 | school | 10 | 81.27 | 63.87 | 70.82 |
| school-11 | school | 11 | 80.05 | 62.10 | 73.01 |
| school-12 | school | 12 | 76.84 | 57.65 | 64.77 |
Every school and band combination has a value.
Mean final score by school and band
Back to long with pivot_longer()
| school | band | mean_score |
|---|---|---|
| school-01 | low | 64.96 |
| school-01 | mid | 69.72 |
| school-01 | high | 80.65 |
| school-02 | low | 65.02 |
| school-02 | mid | 69.46 |
| school-02 | high | 84.23 |
| school-03 | low | 52.25 |
| school-03 | mid | 69.75 |
| school-03 | high | 77.96 |
| school-04 | low | 51.86 |
| school-04 | mid | 62.99 |
| school-04 | high | 75.05 |
| school-05 | low | 67.44 |
| school-05 | mid | 67.92 |
| school-05 | high | 79.47 |
| school-06 | low | 61.69 |
| school-06 | mid | 72.67 |
| school-06 | high | 83.18 |
| school-07 | low | 74.32 |
| school-07 | mid | 79.36 |
16 more rows not shown.
Script: site/vignettes/reshaping.php in phpstats/phplyr@d319dc5.