Login or Register to Ask a Question and Join Our Community


UNIX for Dummies Questions & Answers

Lookup field in map file

 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Lookup field in map file
# 1  
Old 05-07-2013
Lookup field in map file
Hi,

I have two questions which I would massively appreciate help with.

1.

I am trying to insert a field into a file similar to the vlookup function in excel. In column 2 is a gene id for which i would like to insert the full name in the adjacent column. I have a map file (map.file) which has two columns - in column 1 is the gene ID and column 2 is the full gene name

Code:
inputfile.txt
Func	Gene	ExonicFunc	AAChange	Conserved	SegDup	1000g2010nov_ALL	1000g2011may_ALL	1000g2012feb_ALL
exonic	GABRD	synonymous SNV	c.C816T:p.S272S	660;Name=lod=640		0.15	0.34	0.19
exonic	PRKCZ	synonymous SNV	c.T264C:p.D88D	343;Name=lod=33		0.66	0.73	0.63
exonic	PRKCZ	synonymous SNV	c.A318G:p.P106P	389;Name=lod=51		0.21	0.36	0.24

map.file
Gene ID  Full Gene name
GABRD  Gamma-aminobutyric acid receptor subunit delta
PRKCZ    protein kinase c, zeta

desired output

Func	Gene	GeneName ExonicFunc	AAChange	Conserved	SegDup	1000g2010nov_ALL	1000g2011may_ALL	1000g2012feb_ALL
exonic	GABRD  Gamma-aminobutyric acid receptor subunit delta	synonymous SNV	c.C816T:p.S272S	660;Name=lod=640		0.15	0.34	0.19
exonic	PRKCZ protein kinase c, zeta	synonymous SNV	c.T264C:p.D88D	343;Name=lod=33		0.66	0.73	0.63
exonic	PRKCZ protein kinase c, zeta	synonymous SNV	c.A318G:p.P106P	389;Name=lod=51		0.21	0.36	0.24

The second query I have is that i want to be able to filter the file so that the output only contains those fields where for example column 7 <0.5 and column 8 <0.3 and column 9 <0.2 (sometimes there is just a blank in the field which effectively equals 0)

Is there a way to do this so that the filtering is done based on the column header rather than the column number as I have different files which have a different arrangement of columns but with the same headers.

Sorry for the complicated question.

Thanks
# 2  
Old 05-07-2013
If you religiously used tab as your field separator and only used spaces as data in fields, everything that you're asking for could be done (and has been done several times in this forum). But, when you sometimes use one or two spaces as a field separator AND use spaces as data within a field (as you have done in your examples above), there is no way to programmatically determine where one field ends and the next begins AND there is no way to determine whether a field is empty in the middle of the line or missing at the end of the line.

If you can clean up the source of your data so that you have a consistent field separator, please repost sample input files and the describe (in English) and provide sample output files to more fully describe how you would identify fields to be processed and we may be able to help you.
 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk to print field from lookup file in output

The below awk uses $3 and $4 in search as the min and max, then takes each $2 value in lookup and compares it. If the value in lookupfalls within the range in searchthen it prints the entire line in lookup/ICODE]. What I can't seem to figure out is how to print the matching $5 from search on that... (4 Replies)
Discussion started by: cmccabe
4 Replies

2. Shell Programming and Scripting

Lookup field values in two fixed format file in UNIX - not working

I have 2 fixed length files input#1 & input#2. I want to match the rows based on the value in position 37-50 in both files (pos 37-50 will have same value in both files). If any matching record is found then cut the value against company code & Invoice number from input file #1 (position 99 until... (3 Replies)
Discussion started by: Lingaraju
3 Replies

3. Shell Programming and Scripting

File field to replace lookup from another file

Hi All, I don't know how to fast do this field replace that need lookup from another file to form the update result:confused: I want to do it by general shell script Can anyone help to solve it ? Thanks for your kindly reply in advance. CK (0 Replies)
Discussion started by: ckwong99
0 Replies

4. UNIX for Dummies Questions & Answers

Help with AWK - Compare a field in a file to lookup file and substitute if only a match

I have the below 2 files: 1) Third field from file1.txt should be compared to the first field of lookup.txt. 2) If match found then third field, file1.txt should be substituted with the second field from lookup.txt. 3)Else just print the line from file1.txt. File1.txt:... (4 Replies)
Discussion started by: venalla_shine
4 Replies

5. UNIX for Advanced & Expert Users

cannot find map file

Hi, all: My writed network device driver works fine when the transmitted file is under several MegaBytes, but above this size, especially dozens of or hundreds of MegaBytes, the kernel panic ocurres! I check the kern.log and find this error : 522 Nov 14 19:35:32 liklstar-server kerneNov 14... (2 Replies)
Discussion started by: liklstar
2 Replies

6. Shell Programming and Scripting

Append 1st field from a file into 2nd field of another file

Hi, I've internally searched through forums for about 2+ hours. Unfortunately, with no luck. Although I've found some cases close to mine below, but didn't help so much. Actually, I'm in short with time. So I had to post my case. Hoping that you can help. I have 2 files, FILE1 ... (1 Reply)
Discussion started by: amurib
1 Replies

7. Shell Programming and Scripting

Appending 1st field in a file into 2nd field in another file

Hi, I've internally searched through forums for about 2+ hours. Unfortunately, with no luck. Although I've found some cases close to mine below, but didn't help so much. Actually, I'm in short with time. So I had to post my case. Hoping that you can help. I have 2 files, FILE1 ... (0 Replies)
Discussion started by: amurib
0 Replies

8. Solaris

Mail issue solution query- host map: lookup (domain): deferred

Hi all I had a mail issue earlier today where I was not receiving any emails from the servers of one of our clients. The mail queue just showed this: -----Q-ID----- --Size-- -----Q-Time----- ------------Sender/Recipient----------- o8S7eSpp020274* 5858 Tue Sep 28 10:42... (0 Replies)
Discussion started by: notreallyhere
0 Replies

9. Shell Programming and Scripting

Script to map to .csv file

Hello Guys, I want to write a script which will search for all the *.c,*.h,*.txt in the file. The result will be again search in a .csv file. If that .c file or .h or .txt is existing in the .csv,then put a * mark corresponding to the row in .csv file. ---------- Post updated at 05:19 AM... (2 Replies)
Discussion started by: suvenduperl
2 Replies

10. UNIX for Advanced & Expert Users

Clueless about how to lookup and reverse lookup IP addresses under a file!!.pls help

Write a quick shell snippet to find all of the IPV4 IP addresses in any and all of the files under /var/lib/output/*, ignoring whatever else may be in those files. Perform a reverse lookup on each, and format the output neatly, like "IP=192.168.0.1, ... (0 Replies)
Discussion started by: choco4202002
0 Replies
Login or Register to Ask a Question