awk and count sum ?


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting awk and count sum ?
# 1  
Old 05-11-2012
awk and count sum ?

I have a input.txt file which have 3 fields separate by a comma
place, os and timediff in seconds
Code:
tampa,win7,	2575
tampa,win7,	157619
tampa,win7,	3352
dallas,vista,604799
greenbay,winxp,	14400
greenbay,win7 ,	518400
san jose,winxp,	228121
san jose,winxp,	70853
san jose,winxp,	193514
san jose,winxp,	176290
san jose,winxp,	110999
san jose,winxp,	110940
new york,win7,    136290
carolina,win7 , 	604799
carolina,win7 , 	604799

How do we count sum of the seconds for each OS in each place ?
if total seconds in each place has more than (7 *24 * 3600) then use (7 *24 * 3600)
then write to a new file name output.txt
Code:
Place,OS,Total,Percent
tampa,win7,          (2575+157619+3352)         ,   (2575+157619+3352)/ (7 *24 * 3600) * 100
tampa ,unknown,       ((7 *24 * 3600) - (2575+157619+3352)) ,    ((7 *24 * 3600) - (2575+157619+3352)) / (7 *24 * 3600) * 100
dallas      ,     vista        ,  604799                   ,      (604799)/(7 *24 * 3600) * 100
dallas       ,    unknown   ,     ((7 *24 * 3600) - 604799) ,       ((7 *24 * 3600)- 604799)) / (7 *24 * 3600) * 100
greenbay     ,    win7     ,      518400  ,                  (518400)/(7 *24 * 3600) * 100
greenbay    ,     XP     ,        14400  ,                  (14400)/(7 *24 * 3600) * 100
greebbay  ,  unknown,       ((7 *24 * 3600) - (518400+14400)) ,        ((7 *24 * 3600) - (518400+14400)) / (7 *24 * 3600) * 100
....

Thanks
# 2  
Old 05-11-2012
Quote:
if total seconds in each place has more than (7 *24 * 3600) then use (7 *24 * 3600)
Both "tampa,win7" and "tampa,unknown" have 2576+157619+3352 seconds here. One of them ends up being subtracted from 7*24*3600, one doesn't. Why?

And wouldn't the numbers always end up negative if you always did that when they were greater than 7*24*3600?
# 3  
Old 05-11-2012
This one i want to count like
Tampa , win7, sum total, 65%
Tampa, unknown, (7 * 24 *3600 is one week - sum total win7), 35%

It should not have any location a system can run more than amount of one week, so it cannot be negative.
# 4  
Old 05-11-2012
But why are some subtracted, and some not?
# 5  
Old 05-11-2012
It should have every thing, i just show you the sample.
# 6  
Old 05-11-2012
Quote:
Originally Posted by Corona688
But why are some subtracted, and some not?

I believe that "unknown" isn't in the input file, but is to be assumed to be the difference between the expected total (a week's worth of seconds) and the sum. So, after summing the location-type combinations, if that total isn't greater than a week's seconds, an unknown type is added to the output which is the difference.

@sabercats: I'm still not clear about how to handle sums that are larger than a week's worth of seconds. It seemed odd just to cap it. The code below caps location/type at a week's worth of seconds, and prints the unknown line only when the cap isn't reached. You can tweek it to always put out an unknown line if needed.

Code:
awk  -F , '
    {
        loc[$1];
        if( !seen[$1,$2]++ )
            type[$1] = type[$1] $2 ",";
        tsum[$1,$2] += $3+0;
    }

    END {
        wsec = 7 * 86400;
        for( l in loc )
        {
            sum = 0;
            sub( ",$", "", type[l] );
            split( type[l], t, "," );
            for( i = 1; i <= length( t ); i++ )
            {
                v =  tsum[l,t[i]] < wsec ? tsum[l,t[i]] : wsec;
                printf( "%s,%s,%d,%.2f%%\n", l, t[i], v, 100 * (v/wsec) );
                sum += v;
            }
            if( (diff = wsec - sum) > 0 )
                printf( "%s,%s,%d,%.2f%%\n", l, "unknown", diff, 100 * (diff/wsec) );
        }
    }
' input-file >output-file

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Awk: count unique elements in a field and sum their occurence across the entire file

Hi, Sure it's an easy one, but it drives me insane. input ("|" separated): 1|A,B,C,A 2|A,D,D 3|A,B,B I would like to count the occurence of each capital letters in $2 across the entire file, knowing that duplicates in each record count as 1. I am trying to get this output... (5 Replies)
Discussion started by: beca123456
5 Replies

2. Shell Programming and Scripting

Shell script count lines and sum numbers from multiple files

I want to count the number of lines, I need this result be a number, and sum the last numeric column, I had done to make this one at time, but I need to make this for a crontab, so, it has to be an script, here is my lines: It counts the number of lines: egrep -i String file_name_201611* |... (5 Replies)
Discussion started by: Elly
5 Replies

3. Shell Programming and Scripting

Script Shell: Count The sum of numbers in a file

Hi all; Here is my file: V1.3=4 V1.4=5 V1.1=3 V1.2=6 V1.3=6 Please, can you help me to write a script shell that counts the sum of values in my file (4+5+3+6+6) ? Thank you so much for help. Kind regards. (3 Replies)
Discussion started by: chercheur111
3 Replies

4. Shell Programming and Scripting

Why sum of recs in awk don't match total rec count?

I'm using awk to determine if a field starting in position 604 for length of 10 is not equal to ALL spaces. It's searching several files which are in the current directory. The below awk indicates that there are 84 records on all files where this field IS NOT equal to ALL spaces ( there are 10... (2 Replies)
Discussion started by: mjf
2 Replies

5. Shell Programming and Scripting

Count char, sum and change

Hello, I have some problem in counting char of word, sum and change. I'm not sure shell script can do this. Input data: Sam1 BB BB AA AA BB BB BB Sam2 BB BB AA AA AB AB AB Sam3 BB BB BB AA BB BB BB Sam4 AB AB AB AB AB AB AA Sam5 BB BB AA AA BB BB -- If I count in column 2, B is 9... (3 Replies)
Discussion started by: awil
3 Replies

6. Shell Programming and Scripting

sum divided by count

Dear friends, I'm stuck with the task below, I would be thankful for all your replies. INPUT : Date Price Volume 20110601 73052811.61 2845833 20110602 61489062.96 9909230 20110603 72790724.65 1108927 20110606 48299507.20 7435881 20110607 ... (5 Replies)
Discussion started by: hernand
5 Replies

7. Shell Programming and Scripting

awk count characters, sum, and divide by another column

Hi All, I am another biologist attempting to parse a large txt file containing several million lines like: tucosnp 56762 T Y 228 228 60 23 .CcCcc,,..c.c,cc,,.C... What I need to do is get the frequency of periods (.) plus commas (,) in column 9, and populate this number into another... (1 Reply)
Discussion started by: peromhc
1 Replies

8. UNIX for Dummies Questions & Answers

how to count number of rows and sum of column using awk

Hi All, I have the following input which i want to process using AWK. Rows,NC,amount 1,1202,0.192387 2,1201,0.111111 3,1201,0.123456 i want the following output count of rows = 3 ,sum of amount = 0.426954 Many thanks (2 Replies)
Discussion started by: pistachio
2 Replies

9. Shell Programming and Scripting

Getting Sum, Count and Distinct Count of a file

Hi all this is a UNIX question. I have a large flat file with millions of records. col1|col2|col3 1|a|b 2|c|d 3|e|f 3|g|h footer**** I am supposed to calculate the sum of col1 1+2+3+3=9, count of col1 1,2,3,3=4, and distinct count of col1 1,2,3=c3 I would like it if you avoid... (4 Replies)
Discussion started by: singhabhijit
4 Replies

10. Shell Programming and Scripting

count of sum of row60 files same patter

i have 60 files that have same pattern tgt_abc1.dat tgt_abc2.dat i want to calculate sum of row count of these files and sum these up and populate that in third file. how can i do that?? example tgt_abc1.dat 2000 tgt_abc2.dat 4000 so want to populate in file xyz.dat 6000 (1 Reply)
Discussion started by: er_zeeshan05
1 Replies
Login or Register to Ask a Question