File manipulation using AWK

Shell Programming and Scripting
1

Hi All,
I have a file having content,
$ cat data1.txt
20060620 142 62310 959400 A 5.00
20060620 142 62310 959400 B 3.00
20060620 143 62310 959401 A 7.00
20060620 143 62310 959401 B 4.00
20060620 144 62310 959402 A 8.00
20060620 144 62310 959402 B 9.00
20060620 145 62310 959403 A 1.00
20060620 146 62310 959404 B 2.00

I need a awk file or one liner command, that will give me the output as below

20060620 142 62310 959400 A 8.00
20060620 142 62310 959400 B
20060620 143 62310 959401 A 11.00
20060620 143 62310 959401 B
20060620 144 62310 959402 A 17.00
20060620 144 62310 959402 B
20060620 145 62310 959403 A 1.00
20060620 146 62310 959404 B 2.00

Here the field separator is " ". For any two consecutive records, where $2 are same, $6 will be updated by adding rec1.$6+rec2.$6 for $5 = 'A' (first record) where as rec2.$6 will be substited by null( here $5 will be 'B' only, i.e second record). The records will remain unchange if $2 has one record only.
NOTE: For any value of $2, there can be maximum two records.

Thanks in advance,

Regards,
rin..

2

This should work

#! /bin/sh
while read line
do
key=`echo $line | awk '{print $2}'`
var=`echo $line | awk '{print $6}'`

awk -v key=${key} -v var=${var} 'key==$2 && var!=$6 {var2=var+$6;print $1,$2,$3,$4,$5,var2}' $1
done<$1>tempfile

sort -k 2,5 tempfile | awk '{if(NR%2!=0) print $0; else print $1,$2,$3,$4,$5}' > outputfile
rm -f tempfile

3

Thanks Buddy
This gave me the output as
20060620 142 62310 959400 A 8
20060620 142 62310 959400 B
20060620 143 62310 959401 A 11
20060620 143 62310 959401 B
20060620 144 62310 959402 A 17
20060620 144 62310 959402 B

But the last two lines are not here ( the lines without a pair). Can you please help me out in getting my toatal output?

4

This logic currently only adds only lines with multiple entries in the file. Will need extra logic to add remaining lines. A bit short of time, so may not be able to help you soon.

5

nawk -f rinku.awk data1.txt

rinku.awk:

prev == "" { prev = $0; f2=$2; flast=$NF; next }
$2 != f2 { print prev; prev = $0 ; f2=$2; flast=$NF; next}
{
   match(prev, "[^ ][^ ]*$")
   printf("%s%.2f\n", substr(prev, 1, RSTART-1), $NF + flast)
   NF--
   $1=$1
   print; prev = ""
}
END {
  print prev
}
6

#!/bin/sh

while read line
do
key=`echo $line | awk '{print $2}'`
var=`echo $line | awk '{print $6}'`
if [ `grep -c "$key" $1` -eq 1 ]; then
echo $line >> ofile
fi
awk -v key=${key} -v var=${var} 'key==$2 && var!=$6 {var2=var+$6;print $1,$2,$3,$4,$5,var2}' $1
done<$1>tempfile

sort -k 2,5 tempfile | awk '{if(NR%2!=0) print $0; else print $1,$2,$3,$4,$5}' >> ofile
rm -f tempfile

This one doesn't sorts data currently but shows all the rows. You can use
sort -k2 ofile to get the desired result.

7

Thanks Dude.
It worked..