>_ IzzyLab

Hands-on task

This task is step 21 of the course “awk and datamash: reports from tables”

awk: pivot on a key pair

medium~5 min

In /root/sales.txt there are three fields: region, product, amount. Write an awk program in the file /root/pivot.awk (handy to do in the Editor tab above the terminal) that sums the amount by the (region, product) pair and prints lines region product amount. Run it with awk -f and write the result to /root/pivot.txt. The program will also be tested on other data. Line order doesn't matter.

What you'll practise:
  • awk
  • compound key
  • aggregation
  • associative arrays
  • END block

Topic breakdown

Pivot aggregation is summing over groups where the group is defined not by one but by several fields. In awk an array is indexed by a string, so a compound key is built by joining the right fields with a separator, and the sum is accumulated in the corresponding cell. In the END block the array is iterated with a for (k in s) loop; the iteration order is undefined, and when stability is needed the output is piped through an external sort. When there's more than one rule, it's handier to keep the program in a separate file and run it with awk -f: you can read it with indentation and comments, and the shell's quotes don't get in the way.

What's checked

  1. sums by the (region, product) pair are correct
  2. pivot.awk works on other data

What it looks like