0

I am pulling my hair out trying to get correctly get my data from wide to long using pivot_longer.

My data currently looks like this:

# A tibble: 1 x 11
  Player    completetion.rank~ completion.rank.n~ ypc.rank.2020 ypc.rank.not2020 ypc.td.2020 ypc.td.not2020 ypc.int.2020 ypc.int.not2020
  <chr>                  <dbl>              <dbl>         <dbl>            <dbl>       <dbl>          <dbl>        <dbl>           <dbl>
1 Tom Brady                  1              0.375             1            0.375           1          0.312         0.25           0.375
# ... with 2 more variables: ypc.sack.2020 <dbl>, ypc.sack.not2020 <dbl>

In the end, I would like the data to be organized like this:

enter image description here

Lastly, here is a reproducible example of the data:

structure(list(Player = "Tom Brady", completetion.rank.2020 = 1, 
    completion.rank.not2020 = 0.375, ypc.rank.2020 = 1, ypc.rank.not2020 = 0.375, 
    ypc.td.2020 = 1, ypc.td.not2020 = 0.3125, ypc.int.2020 = 0.25, 
    ypc.int.not2020 = 0.375, ypc.sack.2020 = 0, ypc.sack.not2020 = 0.625), row.names = c(NA, 
-1L), groups = structure(list(Player = "Tom Brady", .rows = structure(list(
    1L), ptype = integer(0), class = c("vctrs_list_of", "vctrs_vctr", 
"list"))), row.names = c(NA, -1L), class = c("tbl_df", "tbl", 
"data.frame"), .drop = TRUE), class = c("grouped_df", "tbl_df", 
"tbl", "data.frame"))

Thanks in advance. The whole pivot_longer, pivot_wider stuff throws me for a loop everytime I try to figure it out.

bcongelio
  • 81
  • 9

1 Answers1

2

You can use regex to do this -

tidyr::pivot_longer(df, 
                    cols = -Player, 
                    names_to = c('name', '.value'), 
                    names_pattern = '(.*)\\.(.*)')

#  Player    name              `2020` not2020
#  <chr>     <chr>              <dbl>   <dbl>
#1 Tom Brady completetion.rank   1     NA    
#2 Tom Brady completion.rank    NA      0.375
#3 Tom Brady ypc.rank            1      0.375
#4 Tom Brady ypc.td              1      0.312
#5 Tom Brady ypc.int             0.25   0.375
#6 Tom Brady ypc.sack            0      0.625

Basically everything until the last . is captured in name column and the rest is used to create new column.

Ronak Shah
  • 377,200
  • 20
  • 156
  • 213
  • That works quite well ... thank you! Realized I misspelled `completion` in the first one. Corrected the spelling and it fixed the double `completion.rank` issue and the `NA` values. – bcongelio Jul 29 '21 at 13:36