How to compare using 2 files 
Author Message
 How to compare using 2 files

Hi,
I have two 4 column .csv (comma seperated values) format files for
which I am trying to do the following:

while file1 has more records to look through
   scan file2 to see if $1 $2 & $3 match file1's first 3 fields
      if so, do some math on $4 and write the answer to answer_file
        like so: $1,$2,$3,($answer=$4 + $4_from_file1)

   if no match is found anywhere in file2:
      write the current record (from file1) to answer_file like
         this: $1,$2,$3,$4

Right now, I might get a match, but then the ALL the other lines in
file2 are showing as unmatched and ending up in the answer_file for
each of the iterations through file1... which is wrong!

Do I need to use a shell script for the "outside" loop, in order to
test whether there was a match during the hunt in the awk (inside)
loop, or can I use awk alone?

I feel like the answer is right in front of me... but I'm frazzled.

Sent via Deja.com http://www.*-*-*.com/
Before you buy.



Mon, 30 Dec 2002 03:00:00 GMT  
 How to compare using 2 files

Quote:

>I have two 4 column .csv (comma seperated values) format files for
>which I am trying to do the following:

>while file1 has more records to look through
>   scan file2 to see if $1 $2 & $3 match file1's first 3 fields
>      if so, do some math on $4 and write the answer to answer_file
>        like so: $1,$2,$3,($answer=$4 + $4_from_file1)

>   if no match is found anywhere in file2:
>      write the current record (from file1) to answer_file like
>         this: $1,$2,$3,$4

>Right now, I might get a match, but then the ALL the other lines in
>file2 are showing as unmatched and ending up in the answer_file for
>each of the iterations through file1... which is wrong!

I'll assume you have the usual complement of unix tools available,

Most efficient way to do this would be to use the join command to
create an outer join so that all records from file1 are written to
output along with the 4th field from file2 as a new 5th field in the
output whenever fields 1 through 3 match up in both files. For records
in file1 with no matching records in file2, the 5th field will be
blank. Then pipe the result through awk, add the 5th field to the 4th
field, and print the resulting fields 1 through 4.

For a purely awk solution, load file2 into an array using fields 1
through 3 as the array index, then read through file1 adding the stored
4th field from file2 as needed.

awk 'BEGIN { while ( getline < "file2" ) s[$1,$2,$3] += $4 }
{ if ( ($1,$2,$3) in s ) $4 += s[$1,$2,$3]; print }' file1

Sent via Deja.com http://www.deja.com/
Before you buy.



Mon, 30 Dec 2002 03:00:00 GMT  
 How to compare using 2 files

% while file1 has more records to look through
%    scan file2 to see if $1 $2 & $3 match file1's first 3 fields

[...]

% Right now, I might get a match, but then the ALL the other lines in
% file2 are showing as unmatched and ending up in the answer_file for
% each of the iterations through file1... which is wrong!

How are you scanning file2? If it's like this:
 { for (i = 0; i < 5; i++) d[i] = $i
   while (getline < "file2" > 0) {
      for (i = 1; i < 4; i++) if (d[i] == $i) break
      if (i < 4) break
   }
   if (i < 4) do_the_math()
   else print d[0]
 }

Then your problem is that you need to close("file2") after the while loop.
Otherwise, the next time through, you pick up reading where you left off.

If there's at most one match between the files, and the file size is
reasonable, you could read one file into an array, like so:
 BEGIN { while (getline < "file2" > 0) matches[$1,$2,$3] = $4
 ($1,$2,$3) in matches { do_the_math(matches[$1,$2,$3]); next }
 { print }
--

Patrick TJ McPhee
East York  Canada



Mon, 30 Dec 2002 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Compare multiple files using rexx in SPFPro

2. Using Refinate to compare EDIF files and verify/create BOM

3. Comparing file objects: what is compared?

4. Comparing 2 files to create a third file

5. comparing an input file with an output file

6. Intel versus AMD, compared using iForth 1.12

7. Help comparing the value of a button using the Property Node (I get a variant)

8. using compare operators for own classes

9. using compare operators for own classes

10. Using EXPECT to compare numerous lines

11. Using boolean operators with string compare function

12. BAL file compare: without comments?

 

 
Powered by phpBB® Forum Software