Calculations in .csv via SED AWK
in [Shell]
|
From: nag on 20 Mar 2010 23:11 On Mar 4, 10:23 am, Ed Morton <mortons...(a)gmail.com> wrote: > On 3/3/2010 12:18 PM, rbala...(a)gmail.com wrote: > > > > > > > Calculations in .csv via SED AWK > > > Hello is there a way to make easy calculations in .csv files through > > sed awk? > > > In my case I want to do the following: > > date;cat;amount > > 01.01.2010;CAT1;60 > > 01.01.2010;CAT1;5 > > 01.01.2010;CAT2;7 > > 01.01.2010;CAT2;50 > > 01.02.2010;CAT2;1 > > 01.02.2010;CAT2;2 > > > I need a summarization and grouping by month. > > > month;cat;sumamount > > 01;CAT1;65 > > 01;CAT2;57 > > 01;CAT2;3 > > ITYM: > > 02;CAT2;3 > > > > > maybe there is a way to process this step by step in a loop, but for > > me is interesting if there is a way to use some basic lowlevel tools > > for this task. > > > Regards Randolf Balasus > > $ cat file > date;cat;amount > 01.01.2010;CAT1;60 > 01.01.2010;CAT1;5 > 01.01.2010;CAT2;7 > 01.01.2010;CAT2;50 > 01.02.2010;CAT2;1 > 01.02.2010;CAT2;2 > > $ awk -F'[.;]' 'NR==1{print "month;cat;sumamount"; next} > {s[$2";"$4]+=$5} END{for (i in s) print i";"s[i]}' file > month;cat;sumamount > 01;CAT1;65 > 01;CAT2;57 > 02;CAT2;3 > > Regards, > > Ed. ============================================================== some modifications to Mr.Morton's code with due respect to him... awk 'BEGIN{FS="[.;]";SUBSEP=";"} NR==1{print"month;cat;sumamount";next}{ {s[$2,$4]+=$5}END{ for (i in s) print i";"s[i]}' input |