Question: Collapse duplicate rows and create new columns
1
gravatar for rrdavis
22 months ago by
rrdavis20
United States
rrdavis20 wrote:

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 • 977 views
ADD COMMENTlink modified 22 months ago by zx87549.9k • written 22 months ago by rrdavis20

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

ADD REPLYlink written 22 months ago by Asaf8.5k
1
gravatar for zx8754
22 months ago by
zx87549.9k
London
zx87549.9k wrote:

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
ADD COMMENTlink written 22 months ago by zx87549.9k
Please log in to add an answer.

Help
Access

Use of this site constitutes acceptance of our User Agreement and Privacy Policy.
Powered by Biostar version 2.3.0
Traffic: 1597 users visited in the last hour
_