Convert rows to columns delimited by each SNP ID

Question

[selplat21]

I have a file I'm working with where each row begins with a SNP ID and is followed by 510 mean genotypes.

These are space delimited like so:

Chr1_25 0 0 0 0.1 
Chr1_33 0.2 0 0 1
Chr5_44 1.2 2 0 2 
Chr7_87 0 0 0 0 

I want to convert each row into tab delimited columns like so:

Chr1_25             Chr1_33       ....
0                   0.2
0                   0
0                   0
0.1                 1

Answer 1

[selplat21]

I was able to solve this with the following:

conda install bioconda -c csvtk 
grep -w -f candidate.snps Test.bimbam | csvtk transpose | sed -e '2,3d' >> candidate.list

candidate.snps is a list of snp_ids (rs......). This provides a transposed matrix with snpID as the header and dosages for all individuals as the rows.

Answer 2

[Trivas]

You're looking to transpose your file. This is straight forward to do in R using t() and likely easy in Python but if you want to do it by command line it's probably a little more convuluted. Here's a stack overflow link that goes through how to do it using awk https://stackoverflow.com/questions/1729824/an-efficient-way-to-transpose-a-file-in-bash

[selplat21]

So I was able to locate posts like this, but I consistently get the output as a single vector:

Chr1_25
0                   
0                   
0                
0.1       
Chr1_33
0.2
0
0
1

[selplat21]

while read i; do grep -w $i Test.bimbam | cut -d, --output-delimiter $'\t' -f1,4- \
| awk '
{ 
    for (i=1; i<=NF; i++)  {
        a[NR,i] = $i
    }
}
NF>p { p = NF }
END {    
    for(j=1; j<=p; j++) {
        str=a[1,j]
        for(i=2; i<=NR; i++){
            str=str" "a[i,j];
        }
        print str
    }
}' ; done < candidate.snps 

[selplat21]

I have tried transposing with R but am getting the same solution:

I made an Rscript transpose.R

#!/usr/bin/env Rscript
input <- file("stdin", "r")
x <- readLines(input)
x <- as.data.frame(x)
x1 <- t(x)
write(x1, "")

I then do the following to my bimbam file:

while read i; do grep -w $i Test.bimbam | cut -d, --output-delimiter $'\t' -f1,4- | transpose.R ; done < candidate.snps 

I am getting the same solution:

Chr1_25
0
0
0
0.1
Chr1_33
...
...
...

The original bimbam is in this format

Chr1_25,A,T,0,0,0,0.1
Chr1_33,C,G,......