Reshaping data frame in R according one column

Question

I have a data frame like this:

TargetID        Gene
cg26365299        HOXA9
cg26476852        HOXA9
cg26492446      BHLHE23
cg26521404        HOXA9
cg26531174         CDX1
cg26595643         VAX1

And I want it into this shape

Gene         TargetID
HOXA9        cg26365299;cg26476852;cg26521404
BHLHE23      cg26492446
CDX1         cg26531174
VAX1         cg26595643

I tried with dcast but it doesn't work

score 4 · Answer 1 · answered Nov 22 '13 at 16:31

4

Use aggregate. Consider df is your data.frame:

> aggregate(TargetID~Gene, data=df, paste0, collapse=";")
     Gene                         TargetID
1 BHLHE23                       cg26492446
2    CDX1                       cg26531174
3   HOXA9 cg26365299;cg26476852;cg26521404
4    VAX1                       cg26595643

answered Nov 22 '13 at 16:31

Jilber Urbina

53,125
10
108
134

1

Thanks!! It works perfectly!! – user976991 Nov 22 '13 at 16:34
@user976991 glad to be useful, you can accept the answer if it helps you to solve your problem/question. – Jilber Urbina Nov 22 '13 at 16:49

Henrik · Answer 2 · 2013-11-22T19:41:29.493

1

Another possibility.

ll <- lapply(unstack(df), paste0, collapse = ";")
data.frame(TargetID = names(ll), Gene = unlist(ll), row.names = NULL)

#   TargetID                             Gene
# 1  BHLHE23                       cg26492446
# 2     CDX1                       cg26531174
# 3    HOXA9 cg26365299;cg26476852;cg26521404
# 4     VAX1                       cg26595643

edited Nov 22 '13 at 19:41

answered Nov 22 '13 at 17:16

Henrik

61,039
13
131
152

score 0 · Answer 3 · answered Nov 22 '13 at 16:48

Another option using plyr:

ddply(df,.(Gene),summarise,TargetID=paste(TargetID,collapse=";"))
  Gene                         TargetID
1 BHLHE23                       cg26492446
2    CDX1                       cg26531174
3   HOXA9 cg26365299;cg26476852;cg26521404
4    VAX1                       cg26595643

Reshaping data frame in R according one column

3 Answers3