UNIX for Dummies Questions & Answers

Hello all,

I'm relatively new to the UNIX world (I've been lost in the Microsofa world!) and I haven't used awk yet, but I think it would be useful in grouping and aggregating data. I'm not sure how to proceed, and would welcome any suggestions.

I have three fields in a delimited file. The file is sorted by a business id, then by the an item id, and then the third field is a quantity. What I would like to do is aggregate the quantity by the business id. I was told that awk would be a very useful tool to do this, but I'm not really sure how to proceed.

My script extracts data from various flat files (all fixed-string) and formats & sorts the files, but this is the last step and I'm not sure how to proceed.

Thanks in advance for any assistance you can provide.

put in several lines of the input file and what your desired output should look like ... and "uname -a" output ...

Thanks,

Here is what some of the sample data looks like:

bus_id itm_id qty
08715245~228~ 00002.00
08715245~123~ 00002.00
08715245~011~ 00002.00
08715989~028~ 00001.00
08715989~333~ 00001.00
08715989~441~ 00001.00
08764425~001~ 00001.00
08764425~212~ 00001.00

would like to have out look like:

bus_id sum_qty


08715245~6
08715989~3
08764425~2

Again, I really appreciate any suggestions that you may have.

Thanks,

jonmon

Give us more info on the matter so we can help you.

It's pretty clear, he wants to sum up the third column, grouping on the first column.

Assuming your data is sorted, here is one way to do it:

Code:

nawk -F\~ 'BEGIN {
             LAST_ACCT=""
             SUM=0
          }
     NR > 1 {
         if (LAST_ACCT!=$1) {
             if (LAST_ACCT != "")
                 printf ("%s~%s\n", LAST_ACCT, SUM) 
             LAST_ACCT=$1
             SUM=$3
         } else {
             SUM+=$3
         }
}' your_data_file

Output:

Code:

08715245~6
08715989~3
08764425~2

Thomas

nawk -f jon.awk yourDataFile

here's jon.awk:

Code:

BEGIN {
  FS=OFS="~"
}
{
   sum[$1] += $NF
}
END {
   for (i in sum)
      print i, sum[i]
}

Much nicer, I didn't realize that awk supported associative arrays.

However, If the data file has column headings and any blank lines, you may want to add a filter so you don't get meaningless lines in your output.

Code:

NR > 1 && length($1) > 0 { 
    sum[$1] += $NF 
}