This is a test version of Biostars. For the public version, visit https://www.biostars.org.
Split into separate columns

How to split the chr and bp position into separate cols ? I have chr:bp as:

ID A1 A2 A1_freq P value
7:146019500 T C 0.42 0.334
13:65060537 C G 0.001 0.312 
5:179589868 A G 0.005 0.102

Would like to split ID into separate columns for chr and a separate column for bp ? How to do in linux? Desired output:

CHR BP ID A1 A2 A1_freq P value
7 146019500 7:146019500 T C 0.42 0.334
13 65060537 13:65060537 C G 0.001 0.312 
5 179589868 5:179589868 A G 0.005 0.102

TIA

header id split
• 2,178 views • link
updated by cpad0112 cpad0112 • written by putty • putty 
$ awk -v OFS="\t" '{print $1,$0}' test.txt | sed -re 's/^ID\t/CHR\tBP\t/;s/:/\t/'

CHR BP  ID  A1  A2  A1_freq P   value
7   146019500   7:146019500 T   C   0.42    0.334
13  65060537    13:65060537 C   G   0.001   0.312   
5   179589868   5:179589868 A   G   0.005   0.102
• 0 views • link
written by cpad0112 cpad0112

1 answer

Try csvtk sep:

$ csvtk sep -t -f ID -s : -n CHR,BP -R  test.tsv \
    | csvtk cut -t -f "CHR,BP,A1,A2,A1_freq,P value"
CHR     BP      A1      A2      A1_freq P value
7       146019500       T       C       0.42    0.334
13      65060537        C       G       0.001   0.312
5       179589868       A       G       0.005   0.102
• 0 views • link
written by shenwei356 shenwei356

Thank you for a prompt reply, but i get the error: column "ID" not existed in file: test

tia

• 0 views • link
written by putty • putty

Is it not tab-delimited?

Then use csvtk space2tab first, and replace "P<\tab>value" with "P value".

• 0 views • link
written by shenwei356 shenwei356

Thank you, it works if i don't use the other columns and split only ID....then i merged the two files....

• 0 views • link
written by putty • putty

Log in to answer this question.

More posts like this