4

This is a "Small" portion of the file car_sales.txt that I am trying to extract information from (the full file has about 700 lines, with more vehicle brands than are listed here):

first_name,last_name,price_paid,brand,year Mann,Mathers,20500.79,Chevy,2012 Doug,Samual,21000.12,Dodge,2015 Walter,Gray,17000.87,Dodge,2010 Jessica,Garnet,17350.00,MINI,2009 Paula,Raymond,45300.87,BMW,2015 Willie,Reynolds,64950.05,BMW,2015 Sam,Collins,70200.35,Lexus,2014 Katy,Martinez,29580.84,Chevy,2012 Nicole,Davis,31650.60,Chevy,2009 Brenda,Gray,12400.56,Dodge,2012 Samantha,Fernandez,27900.21,MINI,2015 Eric,Woods,68900.85,BMW,2009 George,Luke,33453.91,BMW,2011 Mildred,Takey,46820.80,Lexus,2012

I would like to output the "brand" and "price_paid" columns ( and find the average price paid per brand, for all the cars), sorted (a-z), and first line the "header" removed. This is the output I am looking for (from the example listed above):

BMW,53151.4 Chevy,27244.1 Dodge,16800.5 Lexus,58510.6 MINI,22625.1

Now I have been working on this trying to figure this out with no luck for 2 days (I am new at this), best I could come up with is this:

sed '1d' car_sales.txt |awk -F ',' '/Chevy/{print $3}' $1|awk '{total += $1; count ++}END{print "Chevy," total/count}'

Now clearly that is "Not" what I am looking for; If I needed the average output for just "one single" Brand/Price_Paid, then yeah it would work, I would just input the single "Pattern" I'm looking for and then I would get the Average price paid.

However I am looking for a way to catch and output the Average Price paid for ALL the brands in the car_sales.txt file. And there are plenty of more brands then just the 5 in the portion I listed (about 50+ brands).

I have read my 3 books that I have and scanned for hours online, and for the life of me I can't figure it out. Maybe I'm not even looking in the right place, I thought awk would be the answer but it so massive. Many thanks for the help in advance.

I then got an Idea thinking that I had figured a way to make it happen, and starting writing this script. Logically it seemed like it would work in my head, the thought was I would use the output from the first function as the output to the 2nd. Alas this didn't work either, I thought I was on the right track but no.

#!/bin/bash

#This will output the car "brand"
function brand {
        sed '1d' $1| cut -d ',' -f 4 |sort|uniq 
}

#The output of function "brand", will be the pattern for function "average"
function average {
    awk -F ',' '/'"$names"'/{print $3}' $1|awk '{total += $1; count ++}END{print "'$names'" "," total/count}'

}

brand $1
names=$(brand)
average $1 $names
  • I think the second script/attempt would need a for loop in the average function, replacing the first $1 file argument to awk with the for loop iterator (because average gets a list of filename args and the script only handled $1). By default the for loop iterates over the positional args ($1, $2, etc), you could use for i; do awk 'script' "$i"; done. Also, the script can run under POSIX sh if you take out the function keywords and place parenthesis after the function names (for example brand() { ... }). sh is much smaller, faster, and more universal than bash. – RobertL Nov 30 '15 at 19:54
5

Since awk arrays are indexed by strings, you can use one array to keep the total price for that brand so far, and use another array to keep the count of records seen for that brand.

Because "brand" is field 4, you can index the arrays in awk like this:

total_price[$4] += $3        # accumulate total price for this brand
count[$4] += 1               # increment count of records for this brand

At the end, loop through the keys to the arrays, and format the output while calculating the averages.

Since POSIX awk contains no sort function, pipe the output of the awk command to the standard Unix sort command.

Please try this:

Script

#!/bin/sh

#first_name,last_name,price_paid,brand,year
#print for each brand, the average price paid

awk -F, '
    NR == 1 {
        next                        # skip header
    }
    {
        price_paid[$4] += $3        # accumulate total price for this brand       
        count[$4] += 1              # increment count of records for this brand
    }
    END {
        for (brand in price_paid) {
            printf "%s,%7.2f\n", brand, price_paid[brand] / count[brand]
        }
    }
' < "${1:?filename required}" | sort

Annotation/Explananation

  1. Invoke the awk command, setting the Field Separator to comma (,) and passing everything between the single quote on this line and the next single quote several lines below, as the script:

    awk -F, '
    
  2. Skip Header: If the current record number is 1, then skip all processing on the current line (the first line), and get the next line of input:

        NR == 1 {
            next                        # skip header
        }
    
  3. Accumulate Price Total Per Brand (this is executed on every line):
    The arrays price_paid and count are indexed by the brand string.
    Add the current price paid ($3) to the price_paid total for this brand.
    Increment the count of records for this brand:

        {
            price_paid[$4] += $3        # accumulate total price for this brand    
            count[$4] += 1              # increment count of records for this brand
        }
    
  4. Print the Output Table: After all input is processed, step through the keys (brand) to the price_paid array, and for each brand, print the brand and the average of price_paid for that brand:

        END {
            for (brand in price_paid) {
                printf "%s,%7.2f\n", brand, price_paid[brand] / count[brand]
            }
       }
    
  5. Terminate the script argument, redirect input from the filename parameter, and pipe the output of the awk command to the sort command:

    ' < "${1:?filename required}" | sort
    

The single quote (') terminates the script argument to awk.
< "${1:?filename required}" redirects the standard input of awk from the filename specified by the first command line parameter to the script. If there is no parameter, then the shell will print an error message containing "filename required" and exit with error status.

  • Great answer it works and very concise. I wonder why my second script attempted/listed in the original posting didn't work. – Jaymes Deen Nov 30 '15 at 14:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.