Find fields from one file in the other file


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Find fields from one file in the other file
# 1  
Old 02-13-2014
Find fields from one file in the other file

I need to find (basically, grep) matching lines ignoring the first field from file1:
Code:
field1 field2 field3 field4 field5 field6
id1    carrot  white small  
id2    carrot
id3    potato  black
id4    potato

in file2:

Code:
field1 field2 field3 field4 field5 field6
num1 carrot  white  small tasty xxx
num2 carrot  black  big     bad  yyy
num3 potato  yellow large   good  zzzz
num4 potato  black   small bad  aaaa
num5 potato  green   medium nasty bbbb
num6 carrot  white   big     good   cccc

and print ids from file1 in file2 into file3:
Code:
field1 field2 field3 field4 field5 field6 fieldx fieldy
num1 carrot  white  small tasty xxx  id1 id2 
num2 carrot  black  big     bad  yyy id2 
num3 potato  yellow large   good  zzzz id4
num4 potato  black   small bad  aaaa id3 id4
num5 potato  green   medium nasty bbbb id4
num6 carrot  white   big     good   cccc id2

I was trying to write something like this:
Code:
> while read line
> do
> awk '{$1=""; print $0}' $line > $line.1
> grep $line.1 file2 | awk '{print $1}' > $line.otu_id
> awk '{print $line "\t" $line.otu_id}' >> file3
> done < file1

but don't know how to use variables right. Could you please help?

Thank you!
# 2  
Old 02-13-2014
So, On which field (of which file) do you need join?
# 3  
Old 02-13-2014
i need to grep a line without first field from file1 (basically, fields 2-6) and find them in file2. join won't work because it would find exact matches, and I need everything: if only field2 matches file2 or fields2-6 match file2. So when it finds all the matches it needs to put all ids to the line in file2. I need something like grep. Hope it's more clear now.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Splitting the file based on two fields - Fixed length file

Hi , I am having a scenario where I need to split the file based on two field values. The file is a fixed length file. ex: AA0998703000000000000190510095350019500010005101980301 K 0998703000000000000190510095351019500020005101480 ... (4 Replies)
Discussion started by: saj
4 Replies

2. Shell Programming and Scripting

awk to update file with sum of matching fields in another file

In the awk below I am trying to add a penalty to a score to each matching $1 in file2 based on the sum of $3+$4 (variable TL) from file1. Then the $4 value in file1 is divided by TL and multiplied by 100 (this valvue is variable S). Finally, $2 in file2 - S gives the updated $2 result in file2.... (2 Replies)
Discussion started by: cmccabe
2 Replies

3. Shell Programming and Scripting

Speed : awk command to count the occurrences of fields from one file present in the other file

Hi, file1.txt AAA BBB CCC DDD file2.txt abc|AAA|AAAabcbcs|fnwufnq bca|nwruqf|AAA|fwfwwefwef fmimwe|BBB|fnqwufw|wufbqw wcdbi|CCC|wefnwin|wfwwf DDD|wabvfav|wqef|fwbwqfwfe i need the count of rows of file1.txt present in the file2.txt required output: AAA 2 (10 Replies)
Discussion started by: mdkm
10 Replies

4. Shell Programming and Scripting

Looping over a file to count common fields from another file

Hi, I would like to know how can I get the number of rows in file1 that: - the 1st and 2nd field should be the same (text) - the 3rd field should be less or equal (numeric) when comparing to file2. So for each row of file1, I would like to have the number of rows in file2 that follow the... (5 Replies)
Discussion started by: fadista
5 Replies

5. Shell Programming and Scripting

wanted to find both link file and ordinary file using single find command

find . -type fl o/p is only the ordinary file. where in it wont give the link files. (2 Replies)
Discussion started by: nikhil jain
2 Replies

6. UNIX for Dummies Questions & Answers

Match the amount fields in the source file vs trigger file

Hello, I have to write a script to compare the sum of the amount fields in a source file and the amount field in another file. details are: based on the name of the source file (say SALES as an example), a file already available in a path will be grabbed (say SALES_ParmFile) and this file... (4 Replies)
Discussion started by: vijaylak
4 Replies

7. Shell Programming and Scripting

Extracting Some fields from current file to another file

Hi, I have multiple files in a directory all I am trying to do is to read the files in the directory and extract data from 2nd field till 10th field and put it in a new files. The files are pipe delimited. The new file will have the same name as the old file but the prefix of PRE_oldfilename. ... (1 Reply)
Discussion started by: simi28
1 Replies

8. Shell Programming and Scripting

Find out if few fields in a file are null

Hi, I've a pipe delimited file where I want to find out a number of lines where 1st 2nd and last field are null using awk/sed. Is it possible? Thanks (5 Replies)
Discussion started by: rudoraj
5 Replies

9. UNIX for Dummies Questions & Answers

Extract some common fields from 1 file that are presnt in another file

I have 2 files FILEA 720646363*PHILIPPINES 117183970*USA 116274291*USA 107940983*USA 107395824*USA 106632425*USA 105861926*USA 105208607*USA 053077046*USA 065428026*ENGLAND FILEB 001125236 001408905 002316511 002521094 020050725 035018308 052288735 (1 Reply)
Discussion started by: unxusr123
1 Replies

10. Shell Programming and Scripting

Find null fields in file

Hi All, I have some csv files out of which i want to find records which have empty values in either the 14th or 16th fields. The following is a sample. $cut -d',' -f14,16 SPS* | head -5 VOIP_ORIG_INFO,VOIP_DEST_INFO sip:445600709315@sip.com,sip:999@sip.com... (2 Replies)
Discussion started by: rahulrathod
2 Replies
Login or Register to Ask a Question