CODEWITHSUNDEEP
rbioinformatics
user3293338
user3293338

Reputation: 491

Create a new column which name was from a field of a table

For example, I have a table as following:(let's call it a)

SNP         ID    ALLE1    ALLE2
SNPNAME1    1      A        A
SNPNAME2    1      A        G
SNPNAME3    1      G        G
...

I want to write a function to create a new table from above:

ID SNPNAME1  SNPNAME2  SNPNAME3...
1    AA        AG         GG
...

So my idea was to create a NULL object b first, then I add a new column "ID" to it which I can do as following:

b$ID=NA

Then I try to add a new column which name is from a[1,]$SNP, I tried to this by using the following statement: 

b$a[1,]$SNP=NA

However I can't. Then I tried to use

b$get(a[1,]$SNP)=NA

or

c=quote(a[1,]$SNP)
b$eval(c)=NA

But all above didn't now work. Can anyone tell me how to do this? Thank you.

Upvotes: 1

Views: 70

Answers (4)

Jake Burkhead
Jake Burkhead

Reputation: 6545

Here's a data.table solution.

library(data.table)

DT <- data.table(a)
DT[, setNames(as.list(paste0(ALLE1, ALLE2)), SNP), by = ID]

##    ID SNPNAME1 SNPNAME2 SNPNAME3
## 1:  1       AA       AG       GG

Using Paul's data

DT <- data.table(df)
DT[, structure(as.list(paste0(var1, var2)), names = as.character(name)), by = ID]

##    ID spam1 spam2 spam3 spam4 spam5 spam6 spam7 spam8 spam9 spam10
## 1:  1    AA    AA    GA    GG    AG    GA    GA    AG    GG     AA
## 2:  2    AA    AA    GA    GA    GA    AA    AG    GG    GG     GG
## 3:  3    AG    AG    AG    GA    AA    AG    GG    GA    AG     AA
## 4:  4    GA    GG    GA    AA    AG    GG    AA    AA    GG     AG
## 5:  5    AG    GA    GG    AG    AA    AG    AA    AA    GG     GA

Upvotes: 2

IRTFM
IRTFM

Reputation: 263451

Whenever I see a reshape2 answer I always try to see if there is a fairly straightforward base R solution. In this case (using Pauls's data) using tapply() with I() seems to rearrange the strings in a tabular arrangement (if you first prevent `transform from making a factor):

 df = transform(df, comb_var = paste(var1, var2, sep = ''),stringsAsFactors=FALSE)
 with(df, tapply(comb_var, list(ID, name), I))
#--------------------
  spam1 spam10 spam2 spam3 spam4 spam5 spam6 spam7 spam8 spam9
1 "AA"  "GA"   "GG"  "GG"  "GA"  "AG"  "AA"  "GG"  "GG"  "AG" 
2 "AA"  "AG"   "AA"  "AA"  "AA"  "GG"  "GG"  "AA"  "AA"  "GG" 
3 "GA"  "GA"   "GA"  "AG"  "AA"  "AG"  "GA"  "GG"  "AG"  "AG" 
4 "GG"  "AA"   "GG"  "GG"  "AA"  "GA"  "GA"  "GG"  "AA"  "AA" 
5 "AG"  "GA"   "AG"  "GG"  "GA"  "GA"  "AG"  "AA"  "GG"  "GG"

Upvotes: 1

Roland
Roland

Reputation: 132969

DF <- read.table(text="SNP         ID    ALLE1    ALLE2
SNPNAME1    1      A        A
SNPNAME2    1      A        G
SNPNAME3    1      G        G", header=TRUE)

library(reshape2)

DFm <- melt(DF, id=c("SNP", "ID"))
dcast(DFm, ID~SNP, value.var="value", fun.aggregate=paste, collapse="")
#  ID SNPNAME1 SNPNAME2 SNPNAME3
#1  1       AA       AG       GG

Upvotes: 1

Paul Hiemstra
Paul Hiemstra

Reputation: 60974

There is no need to build an object yourself. First let's make some example data that I think is representative for your situation:

df = data.frame(name = paste('spam', rep(1:10, 5), sep = ''), 
                ID = rep(1:5, each = 10),
                var1 = sample(c('A', 'G'), 50, replace = TRUE),
                var2 = sample(c('A', 'G'), 50, replace = TRUE))

amd combine the var columns:

df = transform(df, comb_var = paste(var1, var2, sep = ''))
head(df)
   name ID var1 var2 comb_var
1 spam1  1    A    G       AG
2 spam2  1    G    G       GG
3 spam3  1    G    G       GG
4 spam4  1    A    G       AG
5 spam5  1    A    G       AG
6 spam6  1    G    A       GA

And then use dcast to perform the transformation:

library(reshape2)
dcast(df, ID ~ name, value.var = 'comb_var')
  ID spam1 spam10 spam2 spam3 spam4 spam5 spam6 spam7 spam8 spam9
1  1    AG     GA    GG    GG    AG    AG    GA    GG    GA    GG
2  2    AA     GA    AG    GA    GA    AG    AG    AA    GG    AG
3  3    GG     AG    AG    AG    GA    GG    GA    GA    AA    AG
4  4    AA     AA    GA    GA    GA    GA    AA    GA    AG    AA
5  5    AG     AA    GA    AA    GG    GG    GG    GA    GG    GG

Upvotes: 1

Related Questions