Matching 2 files


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Matching 2 files
# 1  
Old 07-01-2009
Matching 2 files

Hi,

I am able to modify files (one file) but I have a hard time working with multiple files simultaneously.

So I have two files. Basically I want to match two files based on certain columns. Both files are tab-seperated.

File1 looks something like this:
Code:
num1  89  george
num4  78  bob

File 2 is a much larger file and looks somethign liek this:
Code:
num1  3    lion
num1  13  tiger
num1  84  bear
num4  3    house
num4  19  dog
num4  77  mouse

So what I want to do is match column 1 from Files1 and 2. I also want to substract column 2 from both files. So if they are a match, I want to print (into a seperate file) the row with the smallest difference for column 2.

So for my example above, the output file would look like this

num1 5 george bear
num4 1 bob mouse


thanks

Last edited by Yogesh Sawant; 07-05-2009 at 11:29 AM.. Reason: added code tags
# 2  
Old 07-02-2009
What have you tried so far?
# 3  
Old 07-02-2009
See if this helps.
Code:
while read value1
do
a=`echo $value1 | cut -d ' ' -f1`
b=`echo $value1 | cut -d ' ' -f2`
c=`echo $value1 | cut -d ' ' -f3`
while read value2
do
a1=`echo $value2 | cut -d ' ' -f1`
b1=`echo $value2 | cut -d ' ' -f2`
c1=`echo $value2 | cut -d ' ' -f3`
if [ "$a" = "$a1" ]
then
b2=`expr $b - $b1`
echo $a $b2 $c $c1 >>outfile
fi
done<file2
done <file1
sort -o outfile2 -2 outfile
while read out1
do
x=`echo $out1 | cut -d ' ' -f1`
grep $x outfile2 | head -1 >>finalout
grep -v $x outfile2 >tmp
mv tmp outfile2
done<outfile2


Last edited by Yogesh Sawant; 07-05-2009 at 11:28 AM.. Reason: added code tags
# 4  
Old 07-02-2009
Try this...


Code:
while read value1
do
        a=`echo $value1 | cut -d ' ' -f1`
        b=`echo $value1 | cut -d ' ' -f2`
        c=`echo $value1 | cut -d ' ' -f3`
        x=`grep "$a" file2|sort -nr -k2,2|head -1|cut -d' ' -f1`
        y=`grep "$a" file2|sort -nr -k2,2|head -1|cut -d' ' -f2`
        z=`grep "$a" file2|sort -nr -k2,2|head -1|cut -d' ' -f3`
        Diff=`expr $b - $y`
        echo "$a $Diff $c $z" >>outfile
done <file1

Note:
file1 - your first file
file2 - your second file
outfile - the result file
# 5  
Old 07-02-2009
I had the solution ready, but wanted you to give it a try...
Code:
find_corresponding_least_val()
{
   cat file2 | grep "$_key" | awk '{
      if((val-$2) < _diff) {
         _diff=val-$2; args=$3
      }
      else
         _diff=val-$2
   }
      END {
         printf("%s\t%s\t%s\n", key, _diff, args)
   }' key="$_key" val="$_val"
}

while read line
do
   _key=`echo $line | awk '{print $1}`
   _val=`echo $line | awk '{print $2}`
   _args=`echo $line | awk '{print $3}`
   find_corresponding_least_val $_key $_val
done < file1

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Delete all files if another files in the same directory has a matching occurrence of a specific word

he following are the files available in my directory RSK_123_20141113_031500.txt RSK_123_20141113_081500.txt RSK_126_20141113_041500.txt RSK_126_20141113_081800.txt RSK_128_20141113_091600.txt Here, "RSK" is file prefix and 123 is a code name and rest is just timestamp of the file when its... (7 Replies)
Discussion started by: kridhick
7 Replies

2. UNIX for Dummies Questions & Answers

Matching the rows in 2 files

I have a file like this AFF3 BCL2 AGTRAP BRAF AHRR NCOA2 AKAP9 BRAF And second input file like this chromosome start end gene chr1 38177326 38664955 AFF3 chr4 148077060 148088064 AGTRAP chr13 74211117 74292309 AHRR chr5 3928185 ... (4 Replies)
Discussion started by: raj_k
4 Replies

3. Shell Programming and Scripting

matching two files

Hello, I need to match one file with another. The best would be to give an example. File 1 A 1 B 2 C 3 D 4 E 5 F 6 File 2 A 5 6 2 B 3 2 1 F 4 2 9 (1 Reply)
Discussion started by: gisele_l
1 Replies

4. UNIX for Dummies Questions & Answers

Matching corresponding columns in two different files

Hi to all, I have two separated files: FILE1 "V1" "V2" "V3" Mary James Nicole Robert Francisco Sophie Nancy Antony Matt Josephine Louise Rose Mark Simon Charles FILE2 "V1" "V2" "V3"... (2 Replies)
Discussion started by: eleonoral
2 Replies

5. Shell Programming and Scripting

AWK help, matching 2 files into one

I'm newbie with AWK. What I'm trying to do is matching file1 and file2 into a file3 with records listed in columns with pipe as delimiter. The thing is the file1 has thousands of records while file2 has very few. But I want the file3 to show all records in file1 and with data from file2 to be... (2 Replies)
Discussion started by: jmeasel7
2 Replies

6. Shell Programming and Scripting

Delete all files if another files in the same directory has a matching occurence of a specific word

Hello, I have several files in a specific directory. A specific string in one file can occur in another files. If this string is in other files. Then all the files in which this string occured should be deleted and only 1 file should remain with the string. Example. file1 ShortName "Blue... (2 Replies)
Discussion started by: premier_de
2 Replies

7. Shell Programming and Scripting

Matching 2 files

Hi I am able to match two files (fileA and B) based on the first column using this line. awk -F"/t" 'NR == FNR { A = $0; next } A { print $0 FS A }' However I now need to match the two files (files A and B) based on two columns. On top of that, for those that dont match, I want it to... (1 Reply)
Discussion started by: phil_heath
1 Replies

8. Shell Programming and Scripting

Not matching files

Hi I need to check list of files aganinst a.txt and return those files which are not available in a.txt ls -lrt file1.txt file2.txt file3.txt a.txt ===== file1.txt file2.txt Expecting o/p file3.txt (3 Replies)
Discussion started by: akil
3 Replies

9. Shell Programming and Scripting

compare two files and to remove the matching lines on both the files

I have two files and need to compare the two files and to remove the matching lines from both the files (4 Replies)
Discussion started by: shellscripter
4 Replies

10. Shell Programming and Scripting

Matching and combining two files

Hi, How can I match the first two fields of file2 against the first two fields of file1 and where they match combine the two lines. If the name (example-Aidan Rielly) is in file1 but not in file2 then just write the info from file1 to the combined output file. If the name (example-Silvia... (5 Replies)
Discussion started by: p3t3r
5 Replies
Login or Register to Ask a Question