A.15 Rearranging data with pivot_wider and pivot_longer

We would like to distance traveled from the previous game to our data frame. To do this we can use this distance matrix.

Code

dm = read.csv('data/distance.matrix.nba.csv')
colnames(dm)[1] = 'loc'
head(dm)

  loc ATL BKN BOS CHA CHI CLE  DAL  DEN DET  GSW  HOU IND  LAC  LAL  MEM  MIA
1 ATL   0 746 936 227 589 555  720 1210 621 2123  701 426 1933 1933  336  606
2 BKN 746   0 190 531 715 405 1372 1630 498 2557 1418 645 2449 2449  954 1090
3 BOS 936 190   0 721 851 549 1549 1766 620 2682 1604 805 2592 2592 1135 1257
4 CHA 227 531 721   0 589 436  929 1357 532 2286  926 428 2117 2117  520  654
5 CHI 589 715 851 589   0 310  803  917 233 1842  941 166 1742 1742  483 1191
6 CLE 555 405 549 436 310   0 1025 1225 115 2152 1115 263 2047 2047  631 1089
  MIL  MIN  NOP NYK  OKC  ORL PHI  PHX  POR  SAS  SAC TOR  UTA WAS
1 669  908  420 748  755  402 663 1588 2168  878 2083 734 1580 543
2 735 1019 1164   5 1326  938  83 2141 2441 1579 2501 344 1970 203
3 856 1121 1354 188 1493 1116 273 2295 2532 1762 2624 429 2094 394
4 659  939  645 533  939  463 448 1779 2285 1101 2241 587 1724 330
5  81  353  829 713  691  987 667 1450 1752 1050 1787 438 1255 597
6 336  630  919 404  950  895 359 1746 2049 1254 2096 189 1565 305

This will be easier to join with our other data frame if we rearrange this so that it has two columns, team1 and team2. We can use pivot_longer to do this.

Code

dm = dm %>% 
  pivot_longer(cols      = - loc, 
               names_to  = 'prev.loc', 
               values_to = 'miles')
head(dm)

# A tibble: 6 × 3
  loc   prev.loc miles
  <chr> <chr>    <int>
1 ATL   ATL          0
2 ATL   BKN        746
3 ATL   BOS        936
4 ATL   CHA        227
5 ATL   CHI        589
6 ATL   CLE        555

The inverse operation is pivot_wider. This gets us back where we started

Code

dm %>%
  pivot_wider(id_cols     = 'loc', 
              names_from  = 'prev.loc', 
              values_from = 'miles') %>% 
  head()

# A tibble: 6 × 31
  loc     ATL   BKN   BOS   CHA   CHI   CLE   DAL   DEN   DET   GSW   HOU   IND
  <chr> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int>
1 ATL       0   746   936   227   589   555   720  1210   621  2123   701   426
2 BKN     746     0   190   531   715   405  1372  1630   498  2557  1418   645
3 BOS     936   190     0   721   851   549  1549  1766   620  2682  1604   805
4 CHA     227   531   721     0   589   436   929  1357   532  2286   926   428
5 CHI     589   715   851   589     0   310   803   917   233  1842   941   166
6 CLE     555   405   549   436   310     0  1025  1225   115  2152  1115   263
# ℹ 18 more variables: LAC <int>, LAL <int>, MEM <int>, MIA <int>, MIL <int>,
#   MIN <int>, NOP <int>, NYK <int>, OKC <int>, ORL <int>, PHI <int>,
#   PHX <int>, POR <int>, SAS <int>, SAC <int>, TOR <int>, UTA <int>, WAS <int>