Separate one column in two column R
How can separate one column in two column in R?
id rs143 rs148 rs149 rs1490
1 02003s NA 11 22 11
2 02003s NA 10 11 22
3 02003s NA 11 11 12
4 02003s NA 10 11 11
5 02003s NA 10 11 11
in result i want this format:
id rs143 rs143.1 rs148 rs148.1 rs149 rs149.1 rs1490 rs1490.1
1 02003s NA NA 1 1 2 2 1 1
2 02003s NA NA 1 0 1 1 2 2
3 02003s NA NA 1 1 1 1 1 2
4 02003s NA NA 1 0 1 1 1 1
5 02003s NA NA 1 0 1 1 1 1
3 answers
A bit simpler version of Kevin's answer, loop through columns, split, assign column names, and finally bind them back together.
res <- do.call(cbind,
list(df[, 1, drop = FALSE],
lapply(colnames(df[, -1]), function(i){
as.data.frame(list(substring(df[, i], 1, 1),
substring(df[, i], 2, 2)),
col.names = c(i, i))
})
)
)
res
# id rs143 rs143.1 rs148 rs148.1 rs149 rs149.1 rs1490 rs1490.1
# 1 02003s <NA> <NA> 1 1 2 2 1 1
# 2 02003s <NA> <NA> 1 0 1 1 2 2
# 3 02003s <NA> <NA> 1 1 1 1 1 2
# 4 02003s <NA> <NA> 1 0 1 1 1 1
# 5 02003s <NA> <NA> 1 0 1 1 1 1
Requires a nice mixture of diverse functions here. This could be done in a single line, but would be complex.
> df
id rs143 rs148 rs149 rs1490
1 02003s NA 11 22 11
2 02003s NA 10 11 22
3 02003s NA 11 11 12
4 02003s NA 10 11 11
5 02003s NA 10 11 11
#Ensure that NAs are encoded as characters
> df[is.na(df)] <- "NA"
#split each value by an empty string delimiter, then re-merge all columns back together
> df2 <- data.frame(df$id, do.call(cbind, lapply(df[,2:ncol(df)], function(x) t(as.data.frame(strsplit(as.character(x), split=""))))), row.names=c(1:nrow(df)))
> df2
df.id X1 X2 X3 X4 X5 X6 X7 X8
1 02003s N A 1 1 2 2 1 1
2 02003s N A 1 0 1 1 2 2
3 02003s N A 1 1 1 1 1 2
4 02003s N A 1 0 1 1 1 1
5 02003s N A 1 0 1 1 1 1
#Now fix the colnames
> index1 <- seq(from=2, to=ncol(df2), by=2)
> index2 <- seq(from=3, to=ncol(df2), by=2)
> colnames(df2)[index1] <- colnames(df[2:ncol(df)])
> colnames(df2)[index2] <- paste(colnames(df[2:ncol(df)]), ".1", sep="")
> df2
df.id rs143 rs143.1 rs148 rs148.1 rs149 rs149.1 rs1490 rs1490.1
1 02003s N A 1 1 2 2 1 1
2 02003s N A 1 0 1 1 2 2
3 02003s N A 1 1 1 1 1 2
4 02003s N A 1 0 1 1 1 1
5 02003s N A 1 0 1 1 1 1
#Restore he NAs
> df2[df2=="N"] <- NA
> df2[df2=="A"] <- NA
df
id rs143 rs148 rs149 rs1490
1 02003s NA 11 22 11
2 02003s NA 10 11 22
3 02003s NA 11 11 12
4 02003s NA 10 11 11
5 02003s NA 10 11 11
df2
df.id rs143 rs143.1 rs148 rs148.1 rs149 rs149.1 rs1490 rs1490.1
1 02003s <NA> <NA> 1 1 2 2 1 1
2 02003s <NA> <NA> 1 0 1 1 2 2
3 02003s <NA> <NA> 1 1 1 1 1 2
4 02003s <NA> <NA> 1 0 1 1 1 1
5 02003s <NA> <NA> 1 0 1 1 1 1
• 1 views
•
link
updated
by
Ram
•
written
by
Kevin Blighe
•
written
by
Kevin Blighe
No, because the NAs have to be encoded as character NAs. Otherwise, lapply with strsplit as the function does not function correctly.
• 1 views
•
link
written
by
Kevin Blighe
test2.txt= data from OP If naming is no problem:
library(stringr)
test=read.csv("test2.txt", stringsAsFactors = F, header = T, sep="\t")
new_test = cbind(test[1], as.data.frame(sapply(test[,-1], function (x)
if (is.integer(x)) {
as.data.frame(str_split_fixed(as.character(x), "", 2))
}
else {
replicate(2, x)
})))
> new_test
id rs143.1 rs143.2 rs148.V1 rs148.V2 rs149.V1 rs149.V2 rs1490.V1 rs1490.V2
1 02003s NA NA 1 1 2 2 1 1
2 02003s NA NA 1 0 1 1 2 2
3 02003s NA NA 1 1 1 1 1 2
4 02003s NA NA 1 0 1 1 1 1
5 02003s NA NA 1 0 1 1 1 1
If naming is important:
df = as.data.frame(cbind(test[1],lapply(test[, -1], function (x)
if (is.integer(x)) {
do.call(rbind, strsplit(as.character(x), ""))
}
else {
replicate(2, x)
})))
names(df)=gsub("\\.1","",names(df))
names(df)=gsub("\\.2","\\.1",names(df))
> df
id rs143 rs143.1 rs148 rs148.1 rs149 rs149.1 rs1490 rs1490.1
1 02003s NA NA 1 1 2 2 1 1
2 02003s NA NA 1 0 1 1 2 2
3 02003s NA NA 1 1 1 1 1 2
4 02003s NA NA 1 0 1 1 1 1
5 02003s NA NA 1 0 1 1 1 1
Log in to answer this question.
More posts like this
-
Understanding Uncommon Genotype Formats in VCF Files Generated by nf-core RNAvar Pipeline
written by ASid •
-
Count columns and delet specific one
written by salvatore_d •
-
MGRA returns error
written by little_more •
-
How to replace number in R
written by Kian •
-
bowtie2 : different MAPQ if -k is set
written by Nicolas Rosewick
-
how to calculate the proportion of SNPs with DP more than 5
written by Ana
-
ComBat on TCGA RNAseq data generates all NAN
written by mforde84
-
Split Seven Rows In Per Column
written by 2011101101 •
-
Id And Ref Are Empty
written by Zhshqzyc •
-
How To Run Phrap With Quality Score Input
written by Pdubois •
You're going to want to use the
strsplit()function with a separator of"". Lots of examples can be found with a few searches.What have you tried?
What is the next step? Maybe consider using plink and read about "compound genotype" flag?
Yes, Its Right. Plink need allelic format of genotype for QC. Can you tell me, whats the right conversion of 0,1,2 codes to allelic format need for PLINK? I assume 0 should be convert to 11 for minor hemozygote, 1 to 10 for hetro and 2 convert to 22 for hemoz. is it right?
https://www.biostars.org/u/39484/: While you accepted @Kevin's answer there are a couple more posted now. Can you test them and see if they work? You can accept more than one answer as correct as long as they work.
Please do the same for your previous posts as well.