Sum and calculate number in table


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Sum and calculate number in table
# 1  
Old 07-03-2014
Sum and calculate number in table

Hello everyone,

I have some problem in calculation number using awk.

input file format :
Code:
   S1    1 : 0.003 0.031
   S2    1 : 0.020 0.095
   S3    4 : 0.088 0.012
   S4    2 : 0.010 0.090
   S5    2 : 0.244 0.066
   S6    3 : 0.249 0.751
   S7    3 : 0.010 0.990
   S8    3 : 0.230 0.566

Expected result :
Code:
1    0.012  0.063
4    0.088  0.012
2    0.127  0.078
3    0.163  0.769

Normally, I used this code below but it is not completely. I have to sum value which start in column 4 to up (some file has value in column 4 to 5 or 4 to 8) but based on column 2 and count in column 2.
Code:
awk '{a[$2]+=$4; b[$2]++;} END {for(i in a) print i"\t"a[i]/b[i];}' inputfile

Could you please suggest me?
Thanks in advance.
# 2  
Old 07-03-2014
Code:
awk '{a[$2] += $4;
  b[$2] += $5}
END {for(x in a)
    {print x "\t" a[x] "\t" b[x]}}' inputfile

If you want to maintain the input order
Code:
awk '{if(!($2 in b))
    {a[++n] = $2};
  b[$2] += $4;
  c[$2] += $5}
END {for(i = 1; i <= n; i++)
    print a[i] "\t" b[a[i]] "\t" c[a[i]]}' inputfile

This User Gave Thanks to SriniShoo For This Post:
# 3  
Old 07-03-2014
Thank you very much SriniShoo. It worked Smilie.
However, I have many files which each file contain value column more than 4 to 70 column. I will try to adjust this code for them.
# 4  
Old 07-03-2014
For more fields per line try
Code:
awk     '       {B[$2]++
                 for (i=4; i<=NF; i++) A[$2, i]+=$i
                 N=NF}
         END    {for (j in B) {printf "%s ", j
                         for (k=4; k<=N; k++) printf " %8.3f", A [j,k]/B[j]
                         printf "\n"
                        }
                }
        ' file

This User Gave Thanks to RudiC For This Post:
# 5  
Old 07-03-2014
Thank you very much RudiC. Wow, it worked in many column. Smilie
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How to sum values on specific line number?

I have a file that looks like this: HP ColorPlotter Z-6100 ACMARTIN IP 192.168.x.x "VIRTUAL HP ( C9468A ) PART 1 of 2 (REAL CARTRIDGE 1)" "VIRTUAL HP ( C9468A ) PART 2 of 2 (REAL CARTRIDGE 1)" 181 181 "VIRTUAL HP ( C9471A ) PART 1 of 2 (REAL CARTRIDGE 2)" "VIRTUAL HP ( C9471A ) PART 2... (4 Replies)
Discussion started by: SysAdminRialto
4 Replies

2. Shell Programming and Scripting

awk to calculate difference of split and sum the difference

In the awk I am trying to subtract the difference $3-$2 of each matching $4 before the first _ (underscore) and print that value in $13. I think the awk will do that, but added comments. What I am not sure off is how to add a line or lines that will add sum each matching $13 value and put it in... (2 Replies)
Discussion started by: cmccabe
2 Replies

3. Shell Programming and Scripting

Help with calculate the total sum of record in column one

Input file: 101M 10M10D20M1I70M 10M10D39M4I48M 10M10D91M 10M10I13M2I7M1I58M 10M10I15M1D66M Output file: 101M 101 0 0 10M10D20M1I70M 100 1 10 10M10D39M4I48M 97 4 10 10M10D91M 101 0 10 10M10I13M2I7M1I58M 88 13 0 10M10I15M1D66M 91 10 1 I'm interested to count how many total of... (6 Replies)
Discussion started by: perl_beginner
6 Replies

4. Homework & Coursework Questions

Calculate sum of the field

Hi All, I need to calculat the sum of the particular field in text file. And the datatype of the field in file is decimal(18,2). If the file is small, then I am facing any problem. But the file is huge, then the result is converted into exponential format. I tried using various command thr... (0 Replies)
Discussion started by: lathanandhini
0 Replies

5. Programming

Sum a number in different ways.

Hello, I need some help to correct my algorithm steps to sum a number. I am confused... please input some steps Example: Let's say a number 5. Now we can get a list of numbers: 1, 2, 3, 4 (less than 5 in sorted order). so the sum could be: 1+4; 2+3 ( 1+1+1+1+1 or 1+2+2 is not a solution). ... (1 Reply)
Discussion started by: sureshcisco
1 Replies

6. Shell Programming and Scripting

Help with calculate total sum of same data problem

Long list of input file: AGDRE1 0.1005449050 AGDRE1 2.1005443435 AGDRE1 1.2005449050 AGDRE1 5.1005487870 AASFV3 50.456304789 AASFV3 2.3659706549 AASFV3 6.3489807860 AASFV3 3.0089890148 RTRTRS 5.6546403546 . . Desired output file: AGDRE1 8.5021829410 AASFV3 62.180245240... (2 Replies)
Discussion started by: perl_beginner
2 Replies

7. Solaris

calculate sum size of files by date (arg list too long)

Hi, I wanted a script to find sum of files for a particular date, below is my script ls -lrt *.req | nawk '$6 == "Aug"' | nawk '$7 == "1"'| awk '{sum = sum + $5} END {print sum}' However, i get the error below /usr/bin/ls: arg list too long How do i fix that. Many thanks before. (2 Replies)
Discussion started by: beginningDBA
2 Replies

8. Shell Programming and Scripting

Calculate total sum from a file

The file content is dynamic and using this format: name1 number1 name2 number2 name3 number3 name4 number4 .................... Need a smooth way to calculate the sum of all the numbers in that file (number1 + number2 + number3 + number4........ = total ) (11 Replies)
Discussion started by: TehOne
11 Replies

9. Shell Programming and Scripting

calculate number of strings in a variable

Hi all I have a variable called "variable" and is of the form variable ="AAA BBB CCC DDD" {basically it has values separated by spaces} What is the simplest way to check if "variable" has more that one value in its list? Thanks. (9 Replies)
Discussion started by: felixmat1
9 Replies

10. Shell Programming and Scripting

How to calculate a sum of certain records?

Hi, i have a file where the records are like this. vt100 2048 D402 MG0010 0 586 262144 D403 MG0011 1000 486 8192 D404 MG0012 270 386 8192 A423 CC0177 40 586 65536 A424 CC0182 670 486 16384 A423 CC0183 100 486 16384 A425 CC0184 80 65000 4096 B407 EE1027 80 I want firstly, to count how... (10 Replies)
Discussion started by: sickboy
10 Replies
Login or Register to Ask a Question

Featured Tech Videos