# cumulative totals with shifted column

Apologies in advance since I am sure this is an easy problem. Nevertheless, here goes.

``````cat blah.txt
aa+2
bb+4
cc+10
dd+31
``````

I would like to process blah.txt to produce the folowing:

``````aa+0
bb+2
cc+6
dd+16
``````

The notion here is that the first column might be a list of songs, and the second their start times.

My hunch was to do this using awk. I thought of two arrays with numeric indices storing \$1 and the running total of \$2. My idea was to shift the numeric index for the running totals by 1.

``````tail -r blah.txt |
awk -F "+" '{ for(i=0;i<=NR;i++) arr[i+1]+=\$2; farr[i]=\$1 } END
{ for(i=NR+1;i>1;i--) {if (i==NR) {print farr[NR] FS 0 }
else { print farr[i] FS arr[i]}}}'
``````

This isn't concise, and what's more doesn't work. I am befuddled by the failure of the array making above all.

Anyway, 'nuf said, could some kind person please put me out of my misery ?

Tom

-
If `-r` is to get `head` you can also do: `awk -F+ 'NR>10{exit}{pr...` – Runium Apr 14 '13 at 10:29

Here you go:

``````\$ awk -F+ '{sum+=\$2;printf("%s+%d\n",\$1,sum-\$2);}' blah.txt
aa+0
bb+2
cc+6
dd+16
``````

Edit1: here is a slightly simpler way, thanks to Sukminder

``````\$ awk -F+ '{printf("%s+%d\n",\$1,sum);sum+=\$2}' blah.txt
``````

Edit2: And a slightly more concise, thanks to Bernhard:

``````\$ awk -F+ '{print \$1,sum;sum+=\$2}' OFS="+" blah.txt
``````

Edit3: but the former doesn't display 0 on the first line, so here is a corrected and somewhat packed version that shows the shorter way to answer Tom's question (until some new comment suggests a better one) :

``````\$ awk -F+ '{print\$1,s+0;s+=\$2}' OFS=+ blah.txt
``````
-
Great thing, I guess that knowledge of toolkit is no less important than knowing regular expressions. +1 – TNW Apr 14 '13 at 9:59
that is sweet ! – Tom Apr 14 '13 at 10:11
Or, put the addition at end. `nawk -F+ '{printf("%s+%d\n",\$1,sum);sum+=\$2}' blah.txt` – Runium Apr 14 '13 at 10:12
@Sukminder, thanks, answer updated. – jlliagre Apr 14 '13 at 15:31
@Sukminder That would be less portable though, not the (useless) BEGIN clause but the GNU specific options. – jlliagre Apr 14 '13 at 15:46

The best way to do that is to learn and use regular expressions, cause in the future it will save you a lot of trouble doing that kind of thing.

``````cat blah.txt | gawk 'match(\$0, /([^0-9]*)([0-9]+)/, ary) {print ary[1] ary[2]-2}'
``````

You'll have to substitute last 2 by your shift variable. You should note the usage of `gawk`. As far as I know, regular `awk` can't extract groups from regular expressions.

What this does do? It does a match on `\$0`, putting results in `ary`, using regular expression `/([^0-9]*)([0-9]+)/`, which matches: `([^0-9]*)` - 0 or more characters which aren't numbers and puts it at first array index (parentheses are responsible for that thing called grouping), then `([0-9]+)` matches nonzero length number (and puts it as second element of array).

Of course this can be made more elaborate, along with some error checking, special cases of matching etc. - but I encourage you to explore this (namely regular expressions) on your own.

-
i am familiar w regular expressions so will try to get to grips with your solution: thanks for taking the time to set out your solution. – Tom Apr 14 '13 at 10:26
This way I could refresh and extend my miserable awk knowledge, so I can't say it wasn't worth it ;) – TNW Apr 14 '13 at 11:52