Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Sum of columns and format the output

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Sum of columns and format the output
# 1  
Old 06-25-2014
Sum of columns and format the output
Input file:
Code:
011100020100 0.00 1 20000 30000 20000 
011110000025 0.00 1 000 240000 10000 
011100020100 0.00 1 200000 2324000 403500 
032200030025 0.00 1 2077500 3077500 250000 
032200030025 0.00 1 2565000 25536400 320000 
022220000005 0.00 1 10000 300000 300000 
022220000005 0.00 1 200050 400000 300000

I Need to sum multiple columns and format the output as shown below.
Note: File is fixed length
step 1:
I want the sum of column 3 (position:65-73) , column 4(position:82-95), column 5(position:98-112),column 6(position:114-127)
based on column 1(position:2-13)
2)I want the output to be look like this:
Output:
Code:
011100020100 0.00 2 $2,200.00 $2,354.000 $4,235.00 
011110000025 0.00 1 $0.00 $240.000 $100.00 
022220000005 0.00 2 $2,100.50 $700.000 $6,000.00 
032200030025 0.00 2 $46,425.00 $28,613.900 $6,000.00

Can anyone please help?

Is there any way i can attach the input and output file. it looks like the input and ouput file postion got changed while posting.
Last edited by vinus; 06-25-2014 at 06:27 PM.. Reason: Please use code tags for code and data
# 2  
Old 06-25-2014
not able to attach the inupt/output files
Last edited by vinus; 06-25-2014 at 06:12 PM..
# 3  
Old 06-25-2014
something to start with:
Code:
awk '{
   idx=$1 OFS $2
   nf=NF
   a[idx]
   for(i=3;i<=NF;i++) 
      v[idx,i]+=$i
}
END {
   for(i in a){
    printf("%s",i)
    for(j=3; j<=nf;j++) 
      printf("%s%d%s", OFS, v[i,j], (j==nf)?ORS:"")
   }
}' myFile

# 4  
Old 06-25-2014
Another solution:

Code:
awk '
BEGIN {
   split("1 13", k)
   t=split("65 73 82 95 98 112 114 127", rng)
}
{
   key=substr($0,k[1],k[2]-k[1]+1)
   keys[key]
   for(i=1;i<t;i+=2) tot[key,i]+=substr($0,rng[i],rng[i+1]-rng[i]+1)
}
END {
  for(key in keys) {
   printf "%s %d", key, tot[key,1]
   tot[key,5]/=10
   for(i=3;i<t;i+=2) printf " $%'\''.2f", tot[key,i]/100
   printf "\n"
  }
}' infile

Input:
Code:
011100020100                            0.00                            1                 20000            30000          20000 
011110000025                            0.00                            1                   000           240000          10000 
011100020100                            0.00                            1                200000          2324000         403500 
032200030025                            0.00                            1               2077500          3077500         250000 
032200030025                            0.00                            1               2565000         25536400         320000 
022220000005                            0.00                            1                 10000           300000         300000 
022220000005                            0.00                            1                200050           400000         300000

Output:
Code:
032200030025  2 $46,425.00 $28,613.90 $5,700.00
011100020100  2 $2,200.00 $2,354.00 $4,235.00
022220000005  2 $2,100.50 $700.00 $6,000.00
011110000025  1 $0.00 $240.00 $100.00

Last edited by Chubler_XL; 06-25-2014 at 07:44 PM..
# 5  
Old 06-25-2014
Quote:
Originally Posted by vinus
Input file:
Code:
011100020100 0.00 1 20000 30000 20000 
011110000025 0.00 1 000 240000 10000 
011100020100 0.00 1 200000 2324000 403500 
032200030025 0.00 1 2077500 3077500 250000 
032200030025 0.00 1 2565000 25536400 320000 
022220000005 0.00 1 10000 300000 300000 
022220000005 0.00 1 200050 400000 300000

I Need to sum multiple columns and format the output as shown below.
Note: File is fixed length
step 1:
I want the sum of column 3 (position:65-73) , column 4(position:82-95), column 5(position:98-112),column 6(position:114-127)
based on column 1(position:2-13)
2)I want the output to be look like this:
Output:
Code:
011100020100 0.00 2 $2,200.00 $2,354.000 $4,235.00 
011110000025 0.00 1 $0.00 $240.000 $100.00 
022220000005 0.00 2 $2,100.50 $700.000 $6,000.00 
032200030025 0.00 2 $46,425.00 $28,613.900 $6,000.00

Can anyone please help?

Is there any way i can attach the input and output file. it looks like the input and ouput file postion got changed while posting.
Of course, your sample input is absolutely different from your description of fixed length records and I don't understand how you would expect to get $6,000.00 from adding 250000 cents to 320000 cents, but the following seems to come closer to what you requested (with two of your input columns being cents, one of your columns being mils and keeping the 2nd column intact, and sorting the output on the 1st column):
Code:
awk  '
{	f4[$1] += $4
	f5[$1] += $5
	f6[$1] += $6
	c[$1]++
}
END {	for(i in f4)
		printf("%s 0.00 %d %s %s %s\n", i, c[i], df(2, f4[i]),
			df(3, f5[i]),df(2, f6[i]))
}
function df(dp, a,	c, s) {
	c = a % (dp == 2 ? 100 : 1000)
	a /= (dp == 2 ? 100 : 1000)
	s = ""
	while(a > 999) {
		s = sprintf(",%03d%s", a % 1000, s)
		a /= 1000
	}
	return sprintf("$%d%s.%0*d", a, s, dp, c)
}' file | sort

which produces the output:
Code:
011100020100 0.00 2 $2,200.00 $2,354.000 $4,235.00
011110000025 0.00 1 $0.00 $240.000 $100.00
022220000005 0.00 2 $2,100.50 $700.000 $6,000.00
032200030025 0.00 2 $46,425.00 $28,613.900 $5,700.00

from your sample input.
# 6  
Old 06-25-2014
Perhaps fields 2 and 3 should also be summed rather than assuming they are all 0.00 and 1.00 respectively:

Code:
awk '
{
   t2[$1] += $2
   t3[$1] += $3
   t4[$1] += $4 / 100;
   t5[$1] += $5 / 1000;
   t6[$1] += $6 / 100;
}
END {
  f=" $%'\''.2f"
  for(i in t2)
   printf "%s %.2f %d" f f f "\n", i, t2[i], t3[i], t4[i], t5[i], t6[i]
}' infile | sort

These 2 Users Gave Thanks to Chubler_XL For This Post:
Don Cragun vinus
# 7  
Old 06-25-2014
Quote:
Originally Posted by Chubler_XL
Perhaps fields 2 and 3 should also be summed rather than assuming they are all 0.00 and 1.00 respectively:

Code:
awk '
{
   t2[$1] += $2
   t3[$1] += $3
   t4[$1] += $4 / 100;
   t5[$1] += $5 / 1000;
   t6[$1] += $6 / 100;
}
END {
  f=" $%'\''.2f"
  for(i in t2)
   printf "%s %.2f %d" f f f "\n", i, t2[i], t3[i], t4[i], t5[i], t6[i]
}' infile | sort

Good idea, but this still only prints 2 decimal places in the field 5 output. Perhaps one more tweak (fixing the number of decimal places printed for field and reducing the number of divisions from three per input line to three total):
Code:
awk -v sq="'" '
{
	t2[$1] += $2
	t3[$1] += $3
	t4[$1] += $4
	t5[$1] += $5
	t6[$1] += $6
}
END {
	for(i in t2)
		printf("%s %.2f %d $%"sq".2f $%"sq".3f $%"sq".2f\n", i, t2[i],
			t3[i], t4[i] / 100, t5[i] / 1000, t6[i] / 100)
}' file | sort

producing:
Code:
011100020100 0.00 2 $2,200.00 $2,354.000 $4,235.00
011110000025 0.00 1 $0.00 $240.000 $100.00
022220000005 0.00 2 $2,100.50 $700.000 $6,000.00
032200030025 0.00 2 $46,425.00 $28,613.900 $5,700.00

Last edited by Don Cragun; 06-25-2014 at 10:18 PM.. Reason: Add parens.
This User Gave Thanks to Don Cragun For This Post:
vinus
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Group by columns and add sum in new columns

Dear Experts, I have input file which is comma separated, has 4 columns like below, BRAND,COUNTRY,MODEL,COUNT NIKE,USA,DUMMY,5 NIKE,USA,ORIGINAL,10 PUMA,FRANCE,DUMMY,20 PUMA,FRANCE,ORIGINAL,15 ADIDAS,ITALY,DUMMY,50 ADIDAS,ITALY,ORIGINAL,50 SPIKE,CHINA,DUMMY,1O And expected output add... (2 Replies)
Discussion started by: ricky1991
2 Replies

2. Shell Programming and Scripting

Evaluate 2 columns, add sum IF two columns satisfy the condition

HI All, I'm embedding SQL query in Script which gives following output: Assignee Group Total ABC Group1 17 PQR Group2 5 PQR Group3 6 XYZ Group1 10 XYZ Group3 5 I have saved the above output in a file. How do i sum up the contents of this output so as to get following output: ... (4 Replies)
Discussion started by: Khushbu
4 Replies

3. Shell Programming and Scripting

How to format output in columns by appending multi lines one by one?

Hi, I need to display output in below format Customer : Apr 24 16:31 Customer_Name_111121.txt |---Space---|Apr 24 16:32 Customer_Name _111121. txt |---Space---|Apr 24 16:34 Customer_Name_111112. txt |---Space---|Apr 24 16:35 Customer_Name _222223. txt |---Space---|Apr 24 16:37... (8 Replies)
Discussion started by: ketanraut
8 Replies

4. Shell Programming and Scripting

Get the SUM of TWO columns SEPARATELY by doing GROUP BY on other columns

My File looks like: "|" -> Field separator A|B|C|100|1000 D|E|F|1|2 G|H|I|0|7 D|E|F|1|2 A|B|C|10|10000 G|H|I|0|7 A|B|C|1|100 D|E|F|1|2 I need to do a SUM on Col. 5 and Col.6 by grouping on Col 1,2 & 3 My expected output is: A|B|C|111|11100 (2 Replies)
Discussion started by: machomaddy
2 Replies

5. Shell Programming and Scripting

Evaluate 2 columns, add sum IF two columns match on two rows

Hi all, I know this sounds suspiciously like a homework course; but, it is not. My goal is to take a file, and match my "ID" column to the "Date" column, if those conditions are true, add the total number of minutes worked and place it in this file, while not printing the original rows that I... (6 Replies)
Discussion started by: mtucker6784
6 Replies

6. Shell Programming and Scripting

Format output into columns, variables with multiple entries

Hello all, I've got a script that collects data on file systems and prints out specific data about each. I've formatted headers w/ printf like so. printf "\033 and I had the content of the varibles printed out beneath those columns like so: printf... (5 Replies)
Discussion started by: awreneau
5 Replies

7. Shell Programming and Scripting

Format the value of sum

I have a list of values ( in Kb) I have the following code to sum up the values and convert the total to GB cat list 701368101370 101370101370 801554101370 701636101370 101757101370 101876101370 901951101370 And this is the output of my script awk '{ s += $1 } END {... (3 Replies)
Discussion started by: Sara_84
3 Replies

8. UNIX for Dummies Questions & Answers

Sum of all columns in all files in one output file

If I have say 4 files like this: File1: 1 3 4 7 7 0 5 7 5 9 1 2 7 4 8 File2: 1 4 6 2 5 7 1 2 3 6 0 3 0 3 8 File3: (5 Replies)
Discussion started by: cosmologist
5 Replies

9. Shell Programming and Scripting

Sum of three columns - in 4N columns file

Hi All, happy new year. I have a file with 4xN columns like 0.0000e+00 0.0000e+00 7.199E+07 7.123E+07 6.976E+07 6.482E+07 5.256E+07 2.523E+07 0.0000e+00 0.0000e+00 8.641E+07 8.550E+07 8.373E+07 7.780E+07 6.309E+07 3.028E+07... (8 Replies)
Discussion started by: f_o_555
8 Replies

10. Shell Programming and Scripting

scripting/awk help : awk sum output is not comming in regular format. Pls advise.

Hi Experts, I am adding a column of numbers with awk , however not getting correct output: # awk '{sum+=$1} END {print sum}' datafile 2.15291e+06 How can I getthe output like : 2152910 Thank you.. # awk '{sum+=$1} END {print sum}' datafile 2.15079e+06 (3 Replies)
Discussion started by: rveri
3 Replies
Login or Register to Ask a Question