"partial" pivot wide in r

Question

I have a data frame that looks like this:

Time Y
1 2
1 3
1 2
2 5
2 7
2 5
3 10
3 9
3 8

And I'd like to create something that looks like

Time R1 R2 R3
1 2 3 2
2 5 7 5
3 10 9 8

I have to keep the time column for further regression analysis. I can accomplish this by using Time2=Time and using Time2 for names_from argument. However, I get errors along the lines of:

> foo
  Time  Y Time2
1    1  2     1
2    1  3     1
3    1  2     1
4    2  5     2
5    2  7     2
6    2  5     2
7    3 10     3
8    3  9     3
9    3  8     3
> pivot_wider(foo, names_from=Time2, values_from=Y)
# A tibble: 3 x 4
   Time `1`       `2`       `3`      
  <dbl> <list>    <list>    <list>   
1     1 <dbl [3]> <NULL>    <NULL>   
2     2 <NULL>    <dbl [3]> <NULL>   
3     3 <NULL>    <NULL>    <dbl [3]>
Warning message:
Values in `Y` are not uniquely identified; output will contain list-cols.
* Use `values_fn = list(Y = list)` to suppress this warning.
* Use `values_fn = list(Y = length)` to identify where the duplicates arise
* Use `values_fn = list(Y = summary_fun)` to summarise duplicates 

I'm not sure what I'm doing wrong.

Answer 1

It is just a warning message as there are duplicates for the 'Time2'. Instead, we can create a sequence column based on either 'Time' or 'Time2'

library(dplyr)
library(tidyr)
library(data.table)
foo %>% 
    mutate(rn = str_c('R', rowid(Time))) %>%
    pivot_wider(names_from = rn, values_from = Y)
# A tibble: 3 x 4
#   Time    R1    R2    R3
#  <int> <int> <int> <int>
#1     1     2     3     2
#2     2     5     7     5
#3     3    10     9     8

Answer 2

Maybe the following base R options can help

• Using unstack:

p <- unstack(foo,Y~Time)
res <- data.frame(Time = gsub("X","",names(p)),
                  `colnames<-`(unname(t(p)),
                               paste0("R",seq(ncol(p)))))

• Using reshape:

res <- reshape(within(foo,ID <- ave(Y,Time,FUN = seq_along)),
               direction = "wide",
               idvar = "Time",
               timevar = "ID")
res <- setNames(res,gsub("^Y\\.","R",names(res)))

such that

> res
  Time R1 R2 R3
1    1  2  3  2
2    2  5  7  5
3    3 10  9  8

Data

foo <- structure(list(Time = c(1L, 1L, 1L, 2L, 2L, 2L, 3L, 3L, 3L), 
    Y = c(2L, 3L, 2L, 5L, 7L, 5L, 10L, 9L, 8L)), class = "data.frame", row.names = c(NA, 
-9L))