Compare two files using awk or sed, add two fields in a columns if their previous fields are same

How large are the two files involved in terms of number of records?  Specifically more interested in the size of file2.  Also, do you know how to sort on Solaris, we will need the input file (file1) sorted by field3.

Is there any chance you can provide a larger example of both files for better testing?

~bp

Hi,

Please check the solution attached. I think, it is what you need.

Vinit

#!/bin/ksh
for F1Line in `cat f1`
do
{
F2File1=`echo $F1Line|cut -f2 -d\,`
LineFile2=`grep -w $F2File1 f2|head -1`
F2File2=`echo $LineFile2|cut -f2 -d\,`
if [ $F2File2==$F2File1 ]
then
{
LastVar=`echo $LineFile2|cut -f3 -d\,`
echo $F1Line","$LastVar
}
fi
}
done
 
For Second Requirement
 
awk -F\, '{ if(F3==$3) { F4=F4+$4 } else { if(NR>1) { print F1","F2","F3","F4","F5","F6 } F1=$1;F2=$2;F3=$3;F4=$4;F5=$5;F6=$6 }  END {print F1","F2","F3","F4","F5","F6 }' f1
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:

I have written a code that will give you the first out put you can modify the code to get the second,you should pass the two files as input to the script

#!/bin/sh
var=`wc -l $1`
var=`echo $var | cut -d " " -f 1 `
echo $var 

for a in `seq $var`
do
string1=`head -$a $1 | tail -1`
for b in `seq $var`
do
string2=`head -$b $2 | tail -1`
var1=`echo $string1 | cut -d "," -f 2`
var2=`echo $string2 | cut -d "," -f 2`
var3=`echo $string2 | cut -d "," -f 3`
if [ $var1 == $var2 ]; then
echo $string1,$var3
fi
done
done
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:

Hi all for the first output i found this answer,,,,,,

awk -F, -v OFS="," 'NR==FNR{a[$2]=$3;next}a[$2]{print $0,a[$2]}' file2 file1

can anyone explain me this in detail............

and vinit can you explain me your second output answer

Let me take this one piece at a time and try to explain.

awk

Run the AWK program.

-F,

Sets the seperator character that AWK uses to parse each input record into fields to a comma.
 
-v OFS=","

Sets the AWK special system variable OFS (Output Field Separator) to a comma.  This will be placed between variables on a PRINT statement.

'NR==FNR{a[$2]=$3;next}a[$2]{print $0,a[$2]}'

This is the AWK program itself, more on that below.

file2 file1

These are the two input files to read, one after the other.

Now let's take the actual program apart piece by piece

NR==FNR

FNR is the record number in the current file, NR is the record number relative to all records read in all files.  This statement checks to see if they are equal, so essentially says "Are we processing the first file?"  If so then everything between the following braces is executed.

{a[$2]=$3;

This adds a value to an array named "a" with an index of the second values from the input line (comma delimited) and assigns that array entry a value of the third value from the input line.

So if the input line was "abc,def,ghi,5,jkl,mno" then this will add an entry to "a" like a[def]=ghi

next}

Go back and process the next line, and start checking it at the NR==FNR point.

a[$2]{print $0,a[$2]}

Since this statement follows the last one, we can only get here if we are now processing the second file.  The print statement will output $o (the entire input line (from file2)) then a comma then the value from array a for the second value on this input line.  Building off the array entry we added above, when the second value is "def" on an input line, then "ghi" will be written after the existing contents of the line from file2.

Hope this helps,

~bp

I just noticed a small problem in my last explanation post.  I was thinking the files were listed "file1 file2" on the sample AWK command line, but I see they were reversed, which makes more sense for this example.  So in my explanation file1 should be file2, and vice versa.

~bp