Combining 2 files - add (some fields), use larger number, etc.

[arunrr]

Hello,

I need to combine File1 and File2 to result in File3...

File1:

SR Tendulkar,1989-2011,453,18111,200*,48,95,20
RT Ponting,1995-2011,370,13686,164,30,82,20
ST Jayasuriya,1989-2011,445,13430,189,28,68,34
Inzamam-ul-Haq,1991-2007,378,11739,137*,10,83,20
JH Kallis,1996-2011,317,11372,139,17,84,16

File2:

SR Tendulkar,1989-2011,184,15183,248*,51,63,14
R Dravid,1996-2011,160,13094,270,36,62,8
RT Ponting,1995-2011,158*,12640,257,39,58,16
JH Kallis,1995-2011,147,12005,201*,40,55,11
BC Lara,1990-2006,131,11953,400*,34,48,17

File3:

SR Tendulkar,1989-2011,637,33294,248*,99,158,34
RT Ponting,1995-2011,528*,26326,257,69,140,36
JH Kallis,1995-2011,464,23377,201*,57,139,27
ST Jayasuriya,1989-2011,445,13430,189,28,68,34
R Dravid,1996-2011,160,13094,270,36,62,8
BC Lara,1990-2006,131,11953,400*,34,48,17
Inzamam-ul-Haq,1991-2007,378,11739,137*,10,83,20

Rule for combination...
If player (field 1) is in both files, combine as following...
Field 2: Use the larger range (ex. 1995-2010 over 1997-2009)
Field 3: Add the 2 numbers. If * exists with either number, Keep *
Field 5: Use larger number. Keep * only if exists with larger #
Field 4,6,7,8: Add the 2 numbers

If player (field 1) is not in both files, simply duplicate row in File3.

Hope the above is clear. Thanks in advance for the help.
Arun

 

[PHV]

What have you tried so far and
Tip: man awk

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[arunrr]

Thanks for the tip!

File1 is really about 60 lines and File2 about 80 lines. I extracted only the names that were in both file and came up with 38 names. Note this is a change from the initial request. Then,

> resultfile
while read NAME
do
grep -h "$NAME" File1 File2 | awk 'ORS=NR%2?",":RS' | awk -F, -vOFS=',' '{$9="REMOVEME";sub(/REMOVEME,/,"")}1' | awk -F, '{if($2>$9) YY=$2; else YY=$9; if($5>$11) XX=$5; else XX=$11; {print $1","YY","$3+$10","$4+$11","XX","$6+$13","$7+$14","$8+$15}
}' >> resultfile
done < filewithnames

Combining the 2 lines into one line, removing Field 9, etc.

Handling of field 2 and 9 is incorrect. Need the following...

If $2=(1994-2009) and $9=(1994-2010), then YY=$9
If $2=(1994-2009) and $9=(1992-2007), then YY=(1992-2009)
...Basically the resulting range of years must encompass both ranges specified in field 2 and 9

Handling of * is incorrect. Need as specified in initial request.

Thanks.

 

[arunrr]

This is done, albeit not as I originally intended...

http://www.bcci.com/

-> Records -> Batting: ALL

Year range ($2) and handling of "*" needs work.

Thanks
AR