Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

AWK:Split fields separated by semicolon

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting AWK:Split fields separated by semicolon
# 1  
Old 02-07-2012
AWK:Split fields separated by semicolon
Hi all,


I have a .vcf file which contains 8 coulmns and the data under each column as shown below,
Code:
CHROM       POS     ID     REF    ALT  QUAL    FILTER	INFO	                                                               
1	           3000012         .          A        G	       126	        .       DP=51;VDB=0.0000;AF1=1;AC1=2;DP4=3,0,47,1;MQ=31;FQ=-99;PV4=1,1,0.31,1

Under the 8th cloumn i.e INFO, we had DP=51;VDB=0.000;........PV4-1,1,0.31,1

I want to split the data separated by ";" in the 8th column into separate columns using awk which should look like,
Code:
CHROM     POS           ID     REF   ALT   QUAL    FILTER      DP  VDB     AF1 AC1..................PV4            
  1                   3000012          .    A       G     126            .        51  0.000    1     2                1,1,0.31,1

Any help would be highly appreciated using awk
Last edited by Franklin52; 02-08-2012 at 06:02 AM.. Reason: Please use code tags for code and data samples, thank you
# 2  
Old 02-07-2012
Kind of cumbersome I know, but the first thing we needed to do was clean up the file with sed then use awk.

The file awk.test contains the following:

Code:
1 3000012 . A G 126 . DP=51;VDB=0.0000;AF1=1;AC1=2;DP4=3,0,47,1;MQ=31;FQ=-99;PV4=1,1,0.31,1

Code:
sed -e 's: * :;:g' awk.test \
| sed 's:[A-Z][A-Z][A-Z]=::g' \
| sed -e 's:[A-Z][A-Z][A-Z][0-9]=::'g \
| sed -e 's:[A-Z][A-Z][0-9]=::g' \
| sed -e 's:[A-Z][A-Z]=::g' \
| awk -F\; '{print$1" "$2" "$3" "$4" "$5" "$6" "$7" "$8" "$9" "$10" "$11" "$12" "$13" "$14" "$15}'

The first sed command is cleaning up the white space in the beginning and replacing it with semicolon. The next 4 sed commands are getting rid of the identifier and just leaving it with the value. Once everything is done, the last awk command prints out your results.

Let me know if this helps or if you need more.


That produces this:
Code:
1 3000012 . A G 126 . 51 0.0000 1 2 3,0,47,1 31 -99 1,1,0.31,1

Last edited by Franklin52; 02-08-2012 at 06:03 AM.. Reason: Please use code tags for code and data samples, thank you
# 3  
Old 02-07-2012
Thanks for your answer.

I am sorry that i was not clear with the question.

The file is a tab-delimited text file with 8 columns and the 8th column having the text DP=51;VDB=0.0000;AF1=1;AC1=2;DP4=3,0,47,1;MQ=31;FQ=-99;PV4=1,1,0.31,1

I just need to split the text under INFO into columns, which means the text under INFO should be split into individual coulmns

CHROM POS ID REF ALT QUAL FILTER DP VDB AF1 AC1..................PV4
1 3000012 . A G 126 . 51 0.000 1 2 1,1,0.31,1.
# 4  
Old 02-07-2012
Please find the attached files.


awktest.txt is the original file and test1.txt is the file which was generated by using the commands posted by you in which only the identifiers are removed and the numbers are left alone. I need the numbers into different columns which are tab-separated with their respective headers.

Could you please help further.
awktest.txt (11.9 KB) 
test1.txt (9.9 KB) 
# 5  
Old 02-07-2012
Check if this awk fills your requriment :
Code:
awk 'BEGIN{OFS="\t" } NR == 1 { print } NR > 1 { gsub("[A-Z]|[0-9]=|=","",$8);gsub(";"," ",$8); print $0} ' input

# 6  
Old 02-07-2012
hii thanks !!!!!!!!!

It worked but need a little bit more help.

The awk code removes the alphabets [A-Z] in the 8th column and replaced with nothing. Could it be possible to keep those alphabets as headers to the respective numbers which should look like,

DP VDB AF1 AC1 DP4 MQ FQ PV4
51 0.0000 1 2 3,0,47,1 31 -99 1,1,0.31,1
Last edited by mehar; 02-07-2012 at 04:43 PM..
# 7  
Old 02-09-2012
Hello, check if this code fills your requriment.

I have used "|" as a delimiter since i had issues with tab and aligment.

Also you failed to mention that not all input data has PV4, so i inserted PV4=0 for those who don't have it.

Code:
BEGIN {
OFS="|"
print "CHROM|POS|ID|REF|ALT|QUAL|FILTER|DP|VDB|AF1|AC1|DP4|MQ|FQ|PV4|FORMAT|tmbul1.txt"
}
NR>1 {
datafix = match($8,/PV4=/)
if ( datafix ) {
        gsub("[A-Z]|[0-9]=|=","",$8)
        gsub(";","|",$8)
        print $0
        }
else
        {
        gsub($8,$8";PV4=0",$8)
        gsub("[A-Z]|[0-9]=|=","",$8)
        gsub(";","|",$8)
        print $0
        }
}

Save it as script.awk and run again input file
Code:
awk -f script.awk awktest.txt

There is probably a nicer way, but i'm still in the process of mastering the tool.

Regards
Peasant.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

How to extract fields from a CSV i.e comma separated where some of the fields having comma as value?

can anyone help me!!!! How to I parse the CSV file file name : abc.csv (csv file) The above file containing data like abv,sfs,,hju,',',jkk wff,fst,,rgr,',',rgr ere,edf,erg,',',rgr,rgr I have a requirement like i have to extract different field and assign them into different... (4 Replies)
Discussion started by: J.Jena
4 Replies

2. Shell Programming and Scripting

awk to add plus or minus to fields and split another field

In the tab-delimited input below I am trying to use awk to -10 from $2 and +10 to $3. Something like awk -F'\t' -v OFS='\t' -v s=10 '{split($4,a,":"); print $1,$2-s,$3+s,a,$5,$6} | awk {split(a,b,"-"); print $1,$2-s,$3+s,b-s,b+s,$5,$6}' input should do that. I also need to -10 from $4... (2 Replies)
Discussion started by: cmccabe
2 Replies

3. Shell Programming and Scripting

Replace semicolon within double quotes in a file with semicolon delimiter

Hello Team, Could you please help me with the below question? I have a file with the following properties 1) File Delimiter is ; 2) Text columns are within double quotes 3) Numeric columns will not have double quotes 4) File has total 6 columns Please see a sample record from file ... (3 Replies)
Discussion started by: sam99
3 Replies

4. Shell Programming and Scripting

awk print - fields separated with comma's need to ignore inbetween double quotes

I am trying to re-format a .csv file using awk. I have 6 fields in the .csv file. Some of the fields are enclosed in double quotes and contain comma's inside the quotes. awk is breaking this into multiple fields. Sample lines from the .csv file: Device Name,Personnel,Date,Solution... (1 Reply)
Discussion started by: jxrst
1 Replies

5. Shell Programming and Scripting

awk split lines without knowing the number of fields a-priori

I want to use awk to split fields and put them into a file but I don't know the number of fields for example, in the following line Ports: 22/filtered/tcp//ssh///, 53/open/tcp//tcpwrapped///, 111/filtered/tcp//rpcbind///, 543/filtered/tcp//klogin///, 544/filtered/tcp//kshell///,... (3 Replies)
Discussion started by: esolvepolito
3 Replies

6. Shell Programming and Scripting

grep lines separated with semicolon

Hello, I would like to kindly ask you for help. I have a file with some lines in one row separated by semicolon. I need to find out, if the line I have in different variable is included in this file. e.g I have a file foo.txt with lines A=hello there;hello world;hello there world In... (6 Replies)
Discussion started by: satin1321
6 Replies

7. Shell Programming and Scripting

awk to split one field and print the last two fields within the split part.

Hello; I have a file consists of 4 columns separated by tab. The problem is the third fields. Some of the them are very long but can be split by the vertical bar "|". Also some of them do not contain the string "UniProt", but I could ignore it at this moment, and sort the file afterwards. Here is... (5 Replies)
Discussion started by: yifangt
5 Replies

8. Shell Programming and Scripting

Compare files with fields separated with semicolon

Dear experts I have files like ABD : 5869 events, relative ratio : 1.173800E-01 , sum of ratios : 1.173800E-01 VBD : 12147 events, relative ratio : 2.429400E-01 , sum of ratios : 3.603200E-01 SDF : 17000 events, relative ratio : 3.400000E-01 , sum of ratios : 7.003200E-01 OIP: 14984... (9 Replies)
Discussion started by: Alkass
9 Replies

9. Shell Programming and Scripting

Compare Tab Separated Field with AWK to all and print lines of unique fields.

Hi. I have a tab separated file that has a couple nearly identical lines. When doing: sort file | uniq > file.new It passes through the nearly identical lines because, well, they still are unique. a) I want to look only at field x for uniqueness and if the content in field x is the... (1 Reply)
Discussion started by: rocket_dog
1 Replies

10. Shell Programming and Scripting

Extract semicolon separated delimiter

The log reads as follows. fname1;lname1;eid1;addr;pincode1; fname2;lname2;eid2;addr2;pincode2; fname3;lname3;eid3;addr3;pincode3; fname4;lname4;eid;addr4;pincode4; how do i extract only fname and save it in an array similarly for lname and so on i tried reading a file and cutting each... (5 Replies)
Discussion started by: vkca
5 Replies
Login or Register to Ask a Question