Change SNP file format

Question

[elhamidihay]

I have tried several awk and sed commands to change the format of this SNP file with no success. I have an SNP file with a format that looks like the following:

 ind_1      SNP_1    AA
 ind_1      SNP_2    AB
 ind_1      SNP_3    AA
 ind_2      SNP_1    AA
 ind_2      SNP_2    AA
 ind_3      SNP_1    AB
 ind_3      SNP_2    AA
 ind_3      SNP_3    AB
 ind_3      SNP_4    AA

The desired format:

        SNP_1      SNP_2    SNP_3      SNP_4
ind_1      AA       AB       AA         ??
ind_2      AA       AA       ??         ??
ind_3      AB       AA       AB         AA

Answer 1

[Alex Reynolds]

Here's a Python-based approach:

#!/usr/bin/env python

import sys

d = {}
r = []
c = []

for line in sys.stdin:
    (row, col, val) = line.strip().split('\t')
    if row not in d:
        d[row] = {}
        r.append(row)
    if col not in d[row]:
        d[row][col] = val
    if col not in c:
        c.append(col)

sys.stdout.write("\t%s\n" % ('\t'.join(c)))
for row in r:
    nr = []
    for col in c:
        try:
            nr.append(d[row][col])
        except KeyError:
            nr.append('??')
    sys.stdout.write("%s\t%s\n" % (row, '\t'.join(nr)))

Then:

$ ./condense.py < data.txt
        SNP_1   SNP_2   SNP_3   SNP_4
ind_1   AA      AB      AA      ??
ind_2   AA      AA      ??      ??
ind_3   AB      AA      AB      AA

[elhamidihay]

that worked! thank you

Answer 2

[Pierre Lindenbaum]

use gnu datamash : https://www.gnu.org/software/datamash/examples/#example_transpose

[elhamidihay]

unfortunately datamash did not give me the format i need

[cpad0112]

output:

 $ datamash  crosstab 1,2 unique 3 --filler=??< data.txt 
        SNP_1   SNP_2   SNP_3   SNP_4
    ind_1   AA  AB  AA  ??
    ind_2   AA  AA  ??  ??
    ind_3   AB  AA  AB  AA

Input:

$ cat data.txt 
ind_1   SNP_1   AA
ind_1   SNP_2   AB
ind_1   SNP_3   AA
ind_2   SNP_1   AA
ind_2   SNP_2   AA
ind_3   SNP_1   AB
ind_3   SNP_2   AA
ind_3   SNP_3   AB
ind_3   SNP_4   AA

[Pierre Lindenbaum]

"it doesnt work"