How do I add items in the same row across records together with awk?

Question

I am trying to become a bit more adept with awk. I made this .txt document, which I called food.txt, and it's irritating me that I can't figure out how to add the prices of products together. Take this txt file for example:

Milk dairy 3.89
Cheese dairy 2.12
Eggs produce 1.28
Yogurt dairy 1.49
Chicken produce 2.19
Muffin pastries 0.49
Cookie pastries 0.99

The first row/field is the item name, second row/field is category, and last row/field is price.

I want to be able to do two things.

First, I want to add all prices in the file together, then find the average. I should have the output of 1.78 (rounded up).

Second, I want to add the prices of all dairy products, and find the average price of the dairy products. In this case, my output should be 2.50.

I am somewhat new to awk, so I am not sure if my txt document needs work or not. I made it myself just for experimentation's sake.

Here is a code I tried out, and I am not sure why it isn't working.

BEGIN{ avg = 0 }

{
   total = 0
     for(i = 3; i <= NF; i++)
       if ($2 == "dairy")
       total = total +$i
   avg += total
}

END{
        print "Total Dairy Price Average =  $" avg/NR
}

I figured that, starting from the third field, if the second field was dairy, it would set total = total+$i. Then avg += total. But when I run this, it gives me something like 1.07, which is way off.

Answer 1

Awk approach:

$ awk '{ cat=$(NF-1); a[cat] += $NF; sum += $NF; b[cat]++ }
       END{ for (cat in a) print cat, a[cat]/b[cat]; print "all avg", sum/NR }' file

The output:

dairy 2.5
produce 1.735
pastries 0.74
all avg 1.77857

Answer 2

First, I want to add all prices in the file together, then find the average. I should have the output of 1.78 (rounded up).

Note that NR contains the current line number while processing the file, in the END clause it will tell you the total number of lines, e.g.:

awk '{ sum+=$3 } END { print sum, sum/NR }'

Or with rounded numbers:

awk '{ sum+=$3 } END { print sum, sum/NR }' OFMT='%.2f'

Second, I want to add the prices of all dairy products, and find the average price of the dairy products. In this case, my output should be 2.50.

You are almost not using any of the built-in goodies that awk comes with, e.g. each statement consists of boolean_expression { actions }. So to only act on lines where $2 == "dairy":

awk '$2 == "dairy" { sum+=$3; count++ } END { print sum, sum/count }'

Answer 3

Since the OP asked for some explanation, here is an attempt to do so. The answer is based on RomanPerekhrest's answer but slightly different.

#!/bin/awk -f

{ 
    products = $2 # Store the second field in products
    a[products] += $NF # Increment each item in the array. $NF denotes the last field. 
    total += $NF # This is the total count of the last field all together resulting in a total of:  12.45
    pr[products]++  #increment by 1 if item found in second field. For dairy this should be 3
} 
END { 
    for (products in a)  # for loop
      print products, a[products] / pr[products]  # print product name, total of products / products found
      printf("All avg: %.2f\n", total/NR); # Print average of all products. So 12.45 / 7 = 1.778571
}

Call the script as follows:

chmod u+x myawkscript.awk && ./myawkscript.awk inputfile 

Answer 4

Total product average

total_number_of_produts=`awk 'END{print NR}' filename`

awk -v t0="$total_number_of_produts" 'BEGIN{sum=0}{sum=sum+$NF}END{print sum/t0}' filename

output
1.777 (1.78)

Dairy product average

da=`awk '/dairy/{print NR}' filename | wc -l`

awk -v t0="$total_number_of_produts" 'BEGIN{sum=0}{sum=sum+$NF}END{print sum/t0}' filename 


output
2.5