Tried four different methods to convert long to wide but all result in errors

Question

My data looks like this

newdata

APPLICATION	USER	APPLICANT
25428X.	P991.	A1
25428X.	P929.	A2
26619L.	P929.	A1
26619L.	P991.	A2

This is what I am looking for

APPLICATION	A1	A2
25428X.	P991.	P929.
26619L.	P929.	P991.

I have tried the following but have faced issues with all of them: First try

library(tidyr)
wide<-newdata %>% spread(USER, APPLICANT, -c(APPLICATION))

Error: Each row of output must be identified by a unique combination of keys

Keys are shared for 944218 rows: 71988, ...*

Second try

reshape (newdata, idvar="APPLICANT", timevar="APPLICATION", direction="wide")

This code just does not load

Third try

library(tidyr) 
newdata_wide<-spread(newdata, APPLICANT, USER)

Error: Each row of output must be identified by a unique combination of keys Keys are shared for 944410 rows: 71988, ...

Fourth try

pivot_wider(newdata, names_from="APPLICANT", values_from="USER", id_cols="APPLICATION")

Warning message: values are not uniquely identified; output will contain list-cols

Answer 1

# Your data
dat <- structure(list(APPLICATION = c("25428X.", "25428X.", "26619L.", 
"26619L."), USER = c("P991.", "P929.", "P929.", "P991."), APPLICANT = c("A1", 
"A2", "A1", "A2")), class = "data.frame", row.names = c(NA, -4L
))

# Transform to wider form
dat %>% pivot_wider(names_from = APPLICANT, values_from = USER)

# A tibble: 2 × 3
  APPLICATION A1    A2   
  <chr>       <chr> <chr>
1 25428X.     P991. P929.
2 26619L.     P929. P991.

In base R using reshape:

newdat <- reshape(dat, 
v.names = "USER", 
timevar = "APPLICANT", 
idvar ="APPLICATION",
 direction = "wide")

colnames(newdat)[2:3] <- c("A1", "A2")

newdat
  APPLICATION    A1    A2
1     25428X. P991. P929.
3     26619L. P929. P991.