Formatting file with Awk?

UNIX for Advanced & Expert Users
1

I have a file in CSV format (2 columns ID and Number of Items):

AB1 ,,10
AB2 ,,20
AB2 ,, 30
AB3 ,, 10
AB4 ,, 20
AB4 ,, 30
AB4 ,, 40
AB5 ,, 50
AB6 ,, 10
AB7 ,, 20
AB7 ,, 30
AB7 ,, 40
......

This file is produced daily i would like to get it in the following format, so all duplicates codes are summed together:

AB1 ,,10
AB2 ,,50
AB3 ,, 10
AB4 ,, 90
AB5 ,, 50
AB6 ,, 10
AB7 ,, 90
......

I believe some use of awk -F will be required, could some assist me please

2 
#  awk '{FS=",";t[$1]+=$3}END{for (i in t){print i ",, " t}}' file1
AB1 ,, 10
AB2 ,, 50
AB3 ,, 10
AB4 ,, 90
AB5 ,, 50
AB6 ,, 10
AB7 ,, 90
3

Thanks, that works a treat. Could someone please explain in detail as to what is being done here for (i in t){print i ",, " t[i].

# awk '{FS=",";t[$1]+=$3}END{for (i in t){print i ",, " t[i]}}' file1

I understand that the awk is reading each line from file1 and that the columns are serated by ',' character.
I think the t[$1] with the {for (i in t){print i ",, " t[i]} is somehow grouping together all the same codes and corresponding values and +=$3 is totalling them, ut would like a better clarification of how the ode is actually doing this

4

bump!!

edit by bakunin: Sorry to put it so bluntly, but i think it is utter gall to "bump" a thread (where you have already gotten a sufficient answer, as you yourself admitted) after 11(!) hours. 11 hours - this is the time passed between this post and your last one.

Apart from the fact that bumping up threads is against the rules - who do you think we are? Do you think we have nothing better to do than to sit and wait if you want have something explained? We are NOT your first level support, we have NO obligation to help you at all, let alone within a certain time frame.

5 
awk '{FS=",";   ### cols are seperated by commas.

t[$1]+=$3} ## set up an array t where each element is referenced by $1, and add the value of $3 to the relevant $1 element

END  ### after we've looped over all lines.. 

{for (i in t) ### for every entry in t i.e. all the elements labelled with the 1 entries


{print i ",, " t}}' file1 ### print out the label, two commas, then the contents of the labelled element.
6

thanks

7

Please try this..

nawk 'NR==1{FS="," ;v=$1;Total=0}$1!=v{print v",," Total} ;Total=0; k=0 ;v=$1}
$1==v{Total=Total+$3}' input > Total

Regards