pivot_wider cant manage simple syntax.

[RYann]

Hello folks.

I am trying to pivot a very simple table from long format to a wide format.
I am getting these errors for some time now and there must be an easy way to solve it which I am not aware of.

Here is my code:

df <- data.frame(
  Pain=c(sample(7:12,5,T),
         sample(4:9,5,T),
         sample(1:6,5,T)),
  Expectation=factor(rep(c("Increase","Neutral","Decrease"),each=5)
))

This is fake data about the placebo effect of a medicine on pain perception. I generate it so the ones who expect high pain will report high levels of pain.

My code (that does not work):

df %>% pivot_wider(names_from = Expectation,
                   values_from = Pain)

Now,
in this case it is very simple to just write the code in a wide manner (either tribble or regular tibble), but this is not very helpful in future case.

The error I get from R looks like they're aware of the difficulties but still is not very helpful:

Pivot_longer syntax works like a charm though.

wide_df %>%  pivot_longer(1:3,
                        names_to = "Expectation",
                        values_to = "Pain")

[JackDavison]

The error is quite descriptive; the values in "Pain" aren't uniquely identified. There is nothing that means that I=8, N=7, D=3 are related and should be in the same row as each other, same for 9-4-6, same for 10-6-4, etc.

You need another column which can tell pivot wider where to put all of your values. For example:

library(tidyverse, warn.conflicts = FALSE)

df <- data.frame(
  Pain=c(sample(7:12,5,T),
         sample(4:9,5,T),
         sample(1:6,5,T)),
  Expectation=factor(rep(c("Increase","Neutral","Decrease"),each=5)
  ))

df %>%
  group_by(Expectation) |> 
  mutate(id = row_number()) |> 
  pivot_wider(names_from = Expectation,
              values_from = Pain)
#> # A tibble: 5 × 4
#>      id Increase Neutral Decrease
#>   <int>    <int>   <int>    <int>
#> 1     1        7       7        3
#> 2     2        9       8        2
#> 3     3        7       7        4
#> 4     4        8       4        4
#> 5     5       11       7        2

^{Created on 2022-07-02 by the reprex package (v2.0.1)}

[RYann]

@JackDavison

df %>%
  group_by(Expectation) |> 
  mutate(id = row_number()) |> 
  pivot_wider(names_from = Expectation,
              values_from = Pain)

Thank you. I tried also generating this ID col myself but then ofcourse i had to make 15 ids which didnt help.
That seems like a good solution, I still argue that the previous syntax should have been accepted, as it is the most trivial one. R should be able to recognise which value from 'pain' goes where since in the long format it matches a unique 'Expectation' value.

Again, Thank you!