calculate totals in shell

wolfipa · 03-31-2008, 09:30 AM

Hi,

another question from me.
I have a csv file with e.g. the following values:

Code:

Name;Initials;Zip Code;Totals
Jim;J D;12345;5
Jack;J K;42345;10
Jim;;12345;6
Jack;;42345;11

And I want to create a file with the total sum of "Totals". e.g:

Code:

Name;Totals
Jim;11
Jack;21

Any idea doing this easily?

Simon Bridge · 03-31-2008, 09:48 AM

One strategy would be:
Sort categories are the text up to the first ;
you need to strip the lines in each category of everything up to and including the third ;
then you can just add the results in each category and cat the finished lines to a new txt file.

Sorted?
Look at grep, sed and awk. Though perl is supposed to be good at this.
You will also have course notes on these.

Alternatively - you can tell us why this is not homework?

theNbomr · 03-31-2008, 12:37 PM

This smells like homework, but...
First, this is technically not a CSV file, but still it does use an easily recognizable delimiter, the ';'. So in awk, if one were to make the semicolon the field separator, FS, then for each record, you would simply print $1 & $4. The whole thing is a one-liner.
--- rod.

osor · 03-31-2008, 03:14 PM

Quote:

Originally Posted by theNbomr

then for each record, you would simply print $1 & $4.

It’s a little more complicated than that. For whatever language you use, you will need an associative array (i.e., hash) whose key is $1 and to whose value you add $4 after each line.

theNbomr · 03-31-2008, 04:57 PM

Well, actually, in awk $1 & $4 are all you need to print.

Code:

awk 'BEGIN{ FS=";" } //{print $1";"$4}' wolfipa.dat

(original posters data in 'wolfipa.dat')

I suppose in some sense $1, $2... are associative arrays. But there it is for you.
This is one step above 'hello world' for awk programs.

--- rod.

osor · 03-31-2008, 05:17 PM

Quote:

Originally Posted by theNbomr

Well, actually, in awk $1 & $4 are all you need to print.

Code:

awk 'BEGIN{ FS=";" } //{print $1";"$4}' wolfipa.dat

(original posters data in 'wolfipa.dat')

Except this is not what the OP wants.

Something along the lines of this (if you want to stay with awk) should do it:

Code:

awk '
BEGIN{ FS=";" }
NR==1{ print $1";"$4; next }
{ a[$1]+=$4 }
END{ for (i in a) print i";"a[i] }
' wolfipa.dat

theNbomr · 03-31-2008, 09:06 PM

Oops. Just re-read the OP's question. You are quite right. And I'd say you've seized upon the minimal solution. Still small enough to be a one-liner, IMHO.
--- rod.

Simon Bridge · 04-02-2008, 06:12 PM

Got that Wolfie? Homework complete