This is a test version of Biostars. For the public version, visit https://www.biostars.org.
Collapse duplicate rows and create new columns

Hi,

I have some data that looks like this in R:

##    ID td yr mo dy
## 1  1  0  18  3  2
## 2  1  1  17  3  12
## 3  1  2  16  1  4
## 4  2  2  18  3  7
## 5  2  0  19  2  8

I have some duplicates in column ID (there are sometimes triplicates) and would like the output to have those collapsed and to create new columns with the data being collapsed:

Would like:

##    ID td yr mo dy td yr mo dy td yr mo dy
## 1  1  0  18  3  2 1  17  3  12 2  16  1  4
## 2  2  2  18  3  7  0  19  2  8

I have seen some solutions where they concatenate the data into a new column but would like to keep the data the same as above.

Thanks

r
• 2,739 views • link
updated by zx8754 zx8754 • written by rrdavis • rrdavis

But then you will have a lot of missing data in the table

• 0 views • link
written by Asaf Asaf

1 answer

Here is the start, using base R:

# example data
df1 <- read.table(text = "ID td yr mo dy
1  1  0  18  3  2
2  1  1  17  3  12
3  1  2  16  1  4
4  2  2  18  3  7
5  2  0  19  2  8", header = TRUE)

# split by ID, convert to vector
res <- lapply(split(df1, df1$ID), function(i) c(t(i[ -1 ])))

# set same lengths, then rbind
res <- do.call(rbind, lapply(res, `length<-`, max(lengths(res))))

res
#   [,1] [,2] [,3] [,4] [,5] [,6] [,7] [,8] [,9] [,10] [,11] [,12]
# 1    0   18    3    2    1   17    3   12    2    16     1     4
# 2    2   18    3    7    0   19    2    8   NA    NA    NA    NA
• 0 views • link
written by zx8754 zx8754

If it worked, please consider accepting as answer.

• 0 views • link
written by zx8754 zx8754

Log in to answer this question.

More posts like this