CODEWITHSUNDEEP
r
Aaron
Aaron

Reputation: 317

R: Comma in string interfering with new column

I am trying to move specific values from a row into a column.

Data Set:

table<- structure(list(V1 = c(1931432L, 1931432L, 1931434L, 1931434L, 1931434L), V2 = c(9548326L, 9548327L, 9551339L, 9551338L, 9551337L), V3 = c("D", "N", "N", "D", "D"), V4 = c(1L, 1L, 1L, 1L, 1L), V5 = c(NA, NA, NA, NA, NA), V6 = c("This belongs in column 1", "This belongs in column 2", "This belongs in column 1", "This belongs in, column 2", "This belongs in column 3")), .Names = c("V1", "V2", "V3", "V4", "V5", "V6"), class = "data.frame", row.names = c(NA, -5L))

Currently my code:

tableNEW <-subset(table, select = c(1,2,6))
tableEND <- aggregate(.~ V1, data=tableNEW, FUN=function(x) paste(sort(x), collapse="|"))

is producing:

   V1     | V2                         | V3
1|1931432 |9548326, 9548327            | "This belongs in column 1", "This belongs in column 2"
2|1931434 |9551339, 9551338, 9551337   | "This belongs in column 1", "This belongs in, column 2", "This belongs in column 3"

Ideally, I am seeking:

   V1     | V2                         | V3                        |V4                        |V5
1|1931432 |9548326, 9548327            | "This belongs in column 1"| "This belongs in column 2"|
2|1931434 |9551339, 9551338, 9551337   | "This belongs in column 1"| "This belongs in, column 2"| "This belongs in column 3"

I have tried other methods that attempt to work after what is being produced, however the bloody comma in [2,V3] magically appears that messes the splitting up. I need to retain the commas inside the text as in the final table.

Is there an efficient method, to combine the collapse step with the column creation?

Upvotes: 0

Views: 51

Answers (2)

Adii_
Adii_

Reputation: 373

Or perhaps try to work on table Like:

table$V6 = gsub(',', '', table$V6)

Rest of code will stay the same.

Upvotes: 0

akrun
akrun

Reputation: 886948

Try

library(reshape2)
res1 <- aggregate(V2~V1, data=tableNEW, FUN=paste, collapse=",")
tableNEW$indx <- with(tableNEW, ave(V1, V1, FUN=seq_along))
res2 <- setNames(merge(res1, dcast(tableNEW, V1~indx, value.var="V6")), paste0("V",1:5))
res2
#       V1                      V2                       V3
#1 1931432         9548326,9548327 This belongs in column 1
#2 1931434 9551339,9551338,9551337 This belongs in column 1
#                         V4                       V5
#1  This belongs in column 2                     <NA>
#2 This belongs in, column 2 This belongs in column 3

Upvotes: 2

Related Questions