generate a report of sales with awk

Question

I trying to create a report of sales for each store using an awk script. The dataset is in csv format and there are 45 stores. Example of the data is something like this:

Store,Store_name,Date,Year,Weekly_Sales,Holiday_Flag,Temperature,Fuel_Price,CPI,Unemployment
1,Store1,05-02-2010,2010,1643690.9,No,42.31,2.572,211.0963582,8.106
1,Store1,12-02-2010,2010,1641957.44,Yes,38.51,2.548,211.2421698,8.106
...
...
45,Store45,12-10-2012,2012,734464.36,No,54.47,4,192.3272654,8.667
45,Store45,19-10-2012,2012,718125.53,No,56.47,3.969,192.3308542,8.667

I'm trying to group the stores and sum individual group records using this code:

#!/usr/bin/awk -f

awk BEGIN {F=","} {a[$2]+=$5;}END{for(i in a)print i", "a[i];}

the output of the code above is the following:

Store1, 2.22403e+08
...
...
Store45, 1.12395e+08

I would like two things: sort it in descending order and to change the number not in scientific notation and with two floating points. Can someone give me some advice?

Answer 1

Use printf to format the output. e.g. printf "%s, %.2f\n", i, a[i]. And pipe into sort to sort the output. For example:

Sort by store name, using GNU sort's -V option for "version" sort (AKA "natural sort"):

$ awk -F, '{a[$2]+=$5;}END{for(i in a)printf "%s, %.2f\n", i, a[i]}' file.csv | sort -V -k1,1
Store1, 3285648.34
Store45, 1452589.89

Sort by total sales:

$ awk -F, '{a[$2]+=$5;}END{for(i in a)printf "%s, %.2f\n", i, a[i]}' file.csv | sort -k2,2
Store45, 1452589.89
Store1, 3285648.34

Answer 2

Using a for (i in a) loop is shuffling the order of the indices of a for output, see https://www.gnu.org/software/gawk/manual/gawk.html#Scanning-an-Array. There are better ways to do that but since your stores are already ordered in your input, you don't need an array at all and can just keep the stores ordered the same way in the output by handling them one at a time as they're read which is also more memory and execution speed efficient as you then don't need to store all the data in memory and then loop through all the stores in the END section.

$ cat tst.awk
BEGIN {
    FS = ","
    ofmt = "%s, %0.2f\n"
}
$2 != store {
    if ( NR > 2 ) {
        printf ofmt, store, tot
    }
    store = $2
    tot = 0
}
{ tot += $5 }
END {
    printf ofmt, store, tot
}

$ awk -f tst.awk file
Store1, 3285648.34
Store45, 1452589.89