Difficulties with pivot_wider()

[rdr]

I'm close to solving a problem, but am missing something critical.

I have a tidy data set that looks like this:

tibble::tribble(
   ~year, ~age,   ~tm,  ~lg, ~metric, ~value,
  "2013", "20", "BOS", "AL",    "ba",   0.25,
  "2013", "20", "BOS", "AL",   "obp",   0.32,
  "2013", "20", "BOS", "AL",   "slg",  0.364,
  "2014", "21", "BOS", "AL",    "ba",   0.24,
  "2014", "21", "BOS", "AL",   "obp",  0.297,
  "2014", "21", "BOS", "AL",   "slg",  0.362,
  "2015", "22", "BOS", "AL",    "ba",   0.32,
  "2015", "22", "BOS", "AL",   "obp",  0.355,
  "2015", "22", "BOS", "AL",   "slg",  0.421
  )

I want to send this data to gt() for a nicely formatted table, but first I need to pivot_wider() and have been running into difficulties. My goal is to show the metric per row and the year value in columns, like this:

        2013   2014  2015
ba      .250   .240  .320
obp     .320   .297  .355
slg     .364   .362  .421

But when I pivot_wider(names_from = year) I get

 age   tm    lg    metric `2013` `2014` `2015`
  <chr> <chr> <chr> <chr>   <dbl>  <dbl>  <dbl>
1 20    BOS   AL    ba      0.25  NA     NA    
2 20    BOS   AL    obp     0.32  NA     NA    
3 20    BOS   AL    slg     0.364 NA     NA    
4 21    BOS   AL    ba     NA      0.24  NA    
5 21    BOS   AL    obp    NA      0.297 NA    
6 21    BOS   AL    slg    NA      0.362 NA    
7 22    BOS   AL    ba     NA     NA      0.32 
8 22    BOS   AL    obp    NA     NA      0.355
9 22    BOS   AL    slg    NA     NA      0.421

What I'd like to do, somehow, is consolidate these 9 rows into 3. I know there are ways to do this, but I suspect I'm missing some key point to using pivot_wider() effectively.

Thanks for any guidance.

-- Robert

[FJCC]

The age column varies along with the year and that causes the output you see. Try dropping the age column.

library(tibble)
library(tidyr)
library(dplyr, warn.conflicts = FALSE)
DF <- tibble::tribble(
  ~year, ~age,   ~tm,  ~lg, ~metric, ~value,
  "2013", "20", "BOS", "AL",    "ba",   0.25,
  "2013", "20", "BOS", "AL",   "obp",   0.32,
  "2013", "20", "BOS", "AL",   "slg",  0.364,
  "2014", "21", "BOS", "AL",    "ba",   0.24,
  "2014", "21", "BOS", "AL",   "obp",  0.297,
  "2014", "21", "BOS", "AL",   "slg",  0.362,
  "2015", "22", "BOS", "AL",    "ba",   0.32,
  "2015", "22", "BOS", "AL",   "obp",  0.355,
  "2015", "22", "BOS", "AL",   "slg",  0.421
)
DF %>% select(-age) %>% 
  pivot_wider(names_from = "year")
#> # A tibble: 3 x 6
#>   tm    lg    metric `2013` `2014` `2015`
#>   <chr> <chr> <chr>   <dbl>  <dbl>  <dbl>
#> 1 BOS   AL    ba      0.25   0.24   0.32 
#> 2 BOS   AL    obp     0.32   0.297  0.355
#> 3 BOS   AL    slg     0.364  0.362  0.421

^{Created on 2020-09-09 by the reprex package (v0.3.0)}

[rdr]

No sooner did I read the first 3 words of your reply that I realized my mistake. Thank you for the help!

-- Robert