Replace period in a tab delimited file to a number


 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Replace period in a tab delimited file to a number
# 1  
Old 07-16-2015
Linux Replace period in a tab delimited file to a number

I have a file like this.
It is tab delimited.
Unfortunately, the missing data was filled in with a period "." (see the leading lines 1-5 columns)
I want to substitute the periods for misisng data with an integer "-999".
however, I do not want the global replace to change the other periods seen in the file.
for e.g, c.17_18del or p.P6fs should stay without change.
awk or sed preferred, but also willing to try other simple solutions.
Have only rudimentary bash programming skills.
Attached an example file.
Thanks
~ GH
Code:
1kgp_all    1kgp_amr    1kgp_eur    esp6500siv2_aa    esp6500siv2_all    esp6500siv2_ea    cosmic70    AAChange.ensGene    AAChange.refGene    FS
.    .    .    .    .    .    ID    EN134250:ENST00000256646:exon1:c.17_18del:p.P6fs    NOTCH2:NM_001200001:exon1:c.17_18del:p.P6fs,NOTCH2:NM_024408:exon1:c.17_18del:p.P6fs    22.816
.    .    .    .    .    .    .    EN168614:ENST00000281815:exon13:c.G604T:p.E202X,EN168614:ENST00000338347:exon14:c.G1810T:p.E604X    UNKNOWN    1.684
.    .    .    .    .    .    .    EN211725:ENST00000390372:exon2:c.T302A:p.L101X    .    5.782
.    .    .    .    .    .    .    EN211721:ENST00000390368:exon2:c.273_274del:p.E91fs    .    85.008
.    .    .    .    .    .    .    EN211721:ENST00000390368:exon2:c.266_267del:p.T89fs    .    93.895
0.0103834    0.0173    0.0249    0.0057    0.0226    0.0312    ID    .    .    0.867
.    .    .    .    .    .    .    .    .    6.763
0.0181709    0.0231    0.0308    0.0098    0.0229    0.0297    .    EN140798:ENST00000311303:exon22:c.G3071A:p.W1024X    ABCC12:NM_033226:exon22:c.G3071A:p.W1024X    1.154
0.0171725    0.0115    0.0398    0.0093    0.0225    0.0293    .    EN177558:ENST00000324675:exon1:c.G693A:p.W231X    FAM187B:NM_152481:exon1:c.G693A:p.W231X    3.217
0.0173722    0.0303    0.0467    0.008    0.0354    0.0494    .    EN141959:ENST00000403390:exon2:c.C154T:p.R52X    PFKL:NM_001002021:exon4:c.C163T:p.R55X    0


Last edited by Don Cragun; 07-16-2015 at 02:29 AM.. Reason: Change ICODE tags to CODE tags.
# 2  
Old 07-16-2015
For the future, please use CODE tags, not icode for multiple lines, as you agreed with the forum rules.

A simple:
Code:
sed s,"\.\t","-999\t",g /tmp/example2.txt

Should do the trick, replacing the dot with -999.

hth

Last edited by sea; 07-16-2015 at 02:10 AM.. Reason: Code fix
# 3  
Old 07-16-2015
Would that work?
Code:
perl -pe 's/\.(\s)/-999$1/g' example2.txt

# 4  
Old 07-16-2015
Quote:
Originally Posted by sea
For the future, please use CODE tags, not icode for multiple lines, as you agreed with the forum rules.

A simple:
Code:
sed s,"\.\t","-999\t",g /tmp/example2.txt

Should do the trick, replacing the dot with -999.

hth
it did not work correctly.
It changed a bunch of columns with
Code:
 ./. to ./-999

Same with the perl solution.
I guess, my example did not have fields with "./."
# 5  
Old 07-16-2015
Nope it did not.

Well, you might do a 'first' run to replace the ./. by something like . .
Like:
Code:
sed s,"\./\.","\.\t\.",g /tmp/example2.txt

hth
# 6  
Old 07-16-2015
Wont that shift the fields when adding an additional tab in between?
Will it work if I put an underscore or do I need to breakup that field?
Thanks for your help
# 7  
Old 07-16-2015
Code:
 perl -pe 's/(^|\s)\.(\s)/$1-999$2/g' example2.txt

 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Replace a column in tab delimited file with column in other tab delimited file,based on match

Hello Everyone.. I want to replace the retail col from FileI with cstp1 col from FileP if the strpno matches in both files FileP.txt ... (2 Replies)
Discussion started by: YogeshG
2 Replies

2. Shell Programming and Scripting

Replace a number in the last line of a delimited file.

Hi all, I am fairly new to UNIX and I was wondering if you could provide me with some help! Lets say i have a file as below : Line 1 Line 2 Line 3 ABC|12|4|2 Now the number 4 in bold, this number will represent the number of row there is in the file excluding the header and footer... (10 Replies)
Discussion started by: Stinza
10 Replies

3. UNIX for Dummies Questions & Answers

Need to convert a pipe delimited text file to tab delimited

Hi, I have a rquirement in unix as below . I have a text file with me seperated by | symbol and i need to generate a excel file through unix commands/script so that each value will go to each column. ex: Input Text file: 1|A|apple 2|B|bottle excel file to be generated as output as... (9 Replies)
Discussion started by: raja kakitapall
9 Replies

4. Shell Programming and Scripting

How to make tab delimited file to space delimited?

Hi How to make tab delimited file to space delimited? in put file: ABC kgy jkh ghj ash kjl o/p file: ABC kgy jkh ghj ash kjl Use code tags, thanks. (1 Reply)
Discussion started by: jagdishrout
1 Replies

5. Shell Programming and Scripting

Help with converting Pipe delimited file to Tab Delimited

I have a file which was pipe delimited, I need to make it tab delimited. I tried with sed but no use cat file | sed 's/|//t/g' The above command substituted "/t" not tab in the place of pipe. Sample file: abc|123|2012-01-30|2012-04-28|xyz have to convert to: abc 123... (6 Replies)
Discussion started by: karumudi7
6 Replies

6. UNIX for Dummies Questions & Answers

tab delimited file that is not tab delimited.

Hi Forum I have a tab delimited file that opens well in Openoffice calc (excel). But when I perform any operation in command line, it reads the file incorrectly. When I 'save As' the same file in office as tab delimited then it works fine. The file that I think is tab delimited is actually... (8 Replies)
Discussion started by: imlearning
8 Replies

7. UNIX for Advanced & Expert Users

merge two tab delimited file with exact same number of rows in unix/linux

Hi I have two tab delimited file with different number of columns but same number of rows. I need to combine these two files in such a way that row 1 in file 2 comes adjacent to row 1 in file 1. For example: The content of file1: field1 field2 field3 a1 a2 a3 b1 b2 b3... (2 Replies)
Discussion started by: mary271
2 Replies

8. UNIX for Advanced & Expert Users

Problem while counting number of fields in TAB delimited file

I'm facing a strange problem, please help me out. Here we go. I want to count number of fields in particular file. filename and delimiter character will be passed through parameter. On command prompt if i type following i get 27 as output (which is correct) cat customer.dat | head -1 | awk... (12 Replies)
Discussion started by: vikanna
12 Replies

9. UNIX for Dummies Questions & Answers

Converting Space delimited file to Tab delimited file

Hi all, I have a file with single white space delimited values, I want to convert them to a tab delimited file. I tried sed, tr ... but nothing is working. Thanks, Rajeevan D (16 Replies)
Discussion started by: jeevs81
16 Replies

10. Shell Programming and Scripting

Converting Tab delimited file to Comma delimited file in Unix

Hi, Can anyone let me know on how to convert a Tab delimited file to Comma delimited file in Unix Thanks!! (22 Replies)
Discussion started by: charan81
22 Replies
Login or Register to Ask a Question