Using awk

UNIX for Beginners Q & A UNIX for Dummies Questions & Answers
1

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. :slight_smile:

Thanks in advance for any assistance you can provide.

2

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

3

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

4

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

5

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:

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:

08715245~6
08715989~3
08764425~2

Thomas

6

nawk -f jon.awk yourDataFile

here's jon.awk:

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

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.

NR > 1 && length($1) > 0 { 
    sum[$1] += $NF 
}
8
  1. If the line is 'blank' - your output will not change as there's no $1 and the value of $NF is '0' which does not change the value of the 'sum' [if there was one]
  2. It's better to use 'FNR > 1' as you may have multiple input files with headers

The body would be:

FNR > 1 { sum[$1] += $NF }

little details...... :wink:

9

Cool, I have only recently started to utilize awk's considerable power so this is useful information.

Thanks for pointing out the little details.

Thomas

10

I want to thank all of you. Just being new to the world of awk, I really appreciate all of the help. This has pointed me in the right direction, and I should be able to wrap this up today. May every lotto ticket you buy be a winning one!

Thanks,

Jonmon

11

This is really helpful in understanding the power of awk and nawk :smiley: :slight_smile: