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

user_18986363 0
Viewed 29

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

r
1 Answers
# 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.
user_148121700
Related