Turn multiple columns into a single row: Pivot wider by row-CodePudding

I've been trying to get a single row containing every combination of 25 words, but I need some help getting these in the right order.

So far, I have a table looking like this, where each word is repeated 25 times consecutively in Var1, and Var2 matches Var1 with the other 24 words.

Var1     Var2     id
laugh    area     1
laugh    game     2      
laugh    river    3
...
area     laugh    25
area     game     26
area     river    27
...
game     laugh    49
game     area     50
game     river    51
...

I need to convert it to one row, with values alternating from Var1 and Var2 (so combining by row) like this:

V1     V2    V3     V4    V5     V6         V25   V26    V27   V28   V29   V30
laugh  area  laugh  game  laugh  river  ... area  laugh  area  game  area  river  ...

I have tried using:

words %>% pivot_wider(names_from = id, values_from = c(Var1, Var2)

However it gives me the words by column rather than by row, i.e.,

Var2_1 Var2_2 Var2_3     Var2_25  Var2_26 Var2_27     Var1_1 Var1_2 Var1_3
laugh  laugh  laugh  ... area     area    area    ... area   game   river ...

I tried adding names_vary = "slowest" to pivot_wider(), but got the error:

 Error: 1 components of `...` were not used.

We detected these problematic arguments:
* `names_vary`

Did you misspecify an argument?
Backtrace:
 9. tidyr::pivot_wider(...)
 11. ellipsis:::action_dots(...)

CodePudding user response：

library(tidyr)
library(dplyr)
billboard %>% # Replace with words
    select(wk1:wk2) %>% # Replace with Var1:Var2
    pivot_longer(everything()) %>% 
    mutate(
        id = paste0('V', 1:n())
    ) %>% 
    select(-name) %>% 
    pivot_wider(values_from = value, names_from = id)
# A tibble: 1 x 634
     V1    V2    V3    V4    V5    V6    V7    V8    V9   V10   V11   V12   V13   V14   V15   V16   V17   V18   V19   V20   V21   V22   V23   V24   V25   V26
  <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1    87    82    91    87    81    70    76    76    57    34    51    39    97    97    84    62    59    53    76    76    84    84    57    47    50    39
# ... with 608 more variables: V27 <dbl>, V28 <dbl>, V29 <dbl>, V30 <dbl>, V31 <dbl>, V32 <dbl>, V33 <dbl>, V34 <dbl>, V35 <dbl>, V36 <dbl>, V37 <dbl>,
#   V38 <dbl>, V39 <dbl>, V40 <dbl>, V41 <dbl>, V42 <dbl>, V43 <dbl>, V44 <dbl>, V45 <dbl>, V46 <dbl>, V47 <dbl>, V48 <dbl>, V49 <dbl>, V50 <dbl>, V51 <dbl>,
#   V52 <dbl>, V53 <dbl>, V54 <dbl>, V55 <dbl>, V56 <dbl>, V57 <dbl>, V58 <dbl>, V59 <dbl>, V60 <dbl>, V61 <dbl>, V62 <dbl>, V63 <dbl>, V64 <dbl>, V65 <dbl>,
#   V66 <dbl>, V67 <dbl>, V68 <dbl>, V69 <dbl>, V70 <dbl>, V71 <dbl>, V72 <dbl>, V73 <dbl>, V74 <dbl>, V75 <dbl>, V76 <dbl>, V77 <dbl>, V78 <dbl>, V79 <dbl>,
#   V80 <dbl>, V81 <dbl>, V82 <dbl>, V83 <dbl>, V84 <dbl>, V85 <dbl>, V86 <dbl>, V87 <dbl>, V88 <dbl>, V89 <dbl>, V90 <dbl>, V91 <dbl>, V92 <dbl>, V93 <dbl>,
#   V94 <dbl>, V95 <dbl>, V96 <dbl>, V97 <dbl>, V98 <dbl>, V99 <dbl>, V100 <dbl>, V101 <dbl>, V102 <dbl>, V103 <dbl>, V104 <dbl>, V105 <dbl>, V106 <dbl>,
#   V107 <dbl>, V108 <dbl>, V109 <dbl>, V110 <dbl>, V111 <dbl>, V112 <dbl>, V113 <dbl>, V114 <dbl>, V115 <dbl>, V116 <dbl>, V117 <dbl>, V118 <dbl>, ...