Transposing column with non-unique identifiers in R dataframe

Question

I have a dataset

df <- data.frame(sp = rep(c("A","B","C","D"), each = 10), val = rnorm(40,6,0.1))

sp  val
A   6
A   6.1
A   5.9
...
D   6.2

I want to "transpose" the rows such that the final dataframe looks like

   sp  1    2     3    4 .....10
   A   6   6.1     ...
   B
   C
   D

I can't use spread as it's not unique columns. transpose and pivot wide didn't work either.

Answer 1

We could do it this way:

library(tidyr)
library(dplyr)

df %>% 
  group_by(sp) %>% 
  mutate(row_id = row_number()) %>% 
  pivot_wider(names_from = row_id, values_from = val)

  sp      `1`   `2`   `3`   `4`   `5`   `6`   `7`   `8`   `9`  `10`
  <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 A      6.10  6.05  6.02  5.94  6.14  5.94  6.22  6.15  5.98  5.90
2 B      5.93  6.03  5.98  5.97  5.90  6.00  5.92  5.83  5.96  6.09
3 C      5.94  6.06  5.84  5.99  6.05  6.03  6.01  5.94  5.92  5.90
4 D      6.01  5.91  5.95  5.97  6.18  5.93  6.02  6.01  5.90  5.99