awk to format an output

awk experts,

I have in put file with time stamp followed by "," separated data. same patern continues. The output need time stamp in first columns and data total in 2nd columns.

Input file

T 9:15
d0,1,3,3
d1,2,1,1
d2,3,1,5
e1,1,1,1
T 9:30
d0,1,1,1
d1,2,3,2
d3,1,2,1
e1,2,1,2

Output:

Time,Total
T 9:15,11
T 9:30,10
 

Select the time stamp and use it for each line until next time stamp. Total is the total of "d" lines for next two columns.

If you don't care about the order of the output lines:

awk -F, '/^T/{x=$0}/^d/{a[x]+=($2+$3)}END{for (i in a){print i","a}}' file
1 Like
awk -F, '/^T/{h=$0} /^d/{m+=$2+$3} /^e/&&m{print h FS m;m=0}' infile
awk -F, '/^T/{h=$0} /^d/{m+=$2+$3} /^e/{print h FS m;m=0}' infile

&&m is so that if there is more than one line that starts with 'e' the line does not get printed more than once.. If that never happens it can be left out.

1 Like

bartus11, Your code gets me good results but it prints in reverse. Here is my data again:

Fri May 13 10:29:16 2011
sd5,1.4,0.4,20.9,0.8,0.0,0.0,7.0,0,0
sd12,1.0,0.5,59.3,10.9,0.0,0.0,1.4,0,0
sd16,0.1,0.0,3.6,0.1,0.0,0.0,0.6,0,0
ed17,10.3,2.5,605.6,207.5,0.0,0.0,3.5,0,1
Fri May 13 10:39:16 2011
sd15,1.0,0.3,0.1,18.5,0.0,0.0,5.6,0,0
sd16,2.0,0.0,0.0,0.0,0.0,0.0,0.0,0,0
sd17,0.0,0.0,0.0,0.1,0.0,0.0,0.8,0,0
Fri May 13 10:49:16 2011
sd11,3.0,0.0,0.0,0.0,0.0,0.0,0.0,0,0
sd12,0.0,1.0,0.0,0.0,0.0,0.0,0.0,0,0
ed13,0.0,2.0,0.0,0.0,0.0,0.0,0.0,0,0
sd14,1.0,0.0,0.0,0.0,0.0,0.0,0.0,0,0

Expected output:

Fri May 13 10:29:16 2011,4.4
Fri May 13 10:39:16 2011,3.3
Fri May 13 10:49:16 2011,5.0

In words: Each line should have time from the line before sd line for first field. 2nd field is total of 2nd and 3rd columns of all sd lines between time line. Select only sd lines.
summary: time,total

If perl is okay, then you can use the following code.

 
perl -F, -lane 'if($#F == 0){$time=$_}else{$hash{$time}+=$F[1]+$F[2] if($F[0]!~/ed/)} END{print $_.",".$hash{$_} for sort keys %hash}' d
Fri May 13 10:29:16 2011,3.4
Fri May 13 10:39:16 2011,3.3
Fri May 13 10:49:16 2011,5

Sorry, I do not have perl. Thanks.

---------- Post updated at 12:48 PM ---------- Previous update was at 11:13 AM ----------

Sorry, I do not have perl.

nawk -F, '/^[A-Z]/ {if(h) print h, s[h];h=$0;next} /^sd/{s[h]+=$2+$3}END{print h, s[h]}' OFS=, myFile

Mr. Moderator,

It worked. Thanks a lot. You did it again. It prints in order now.

Thanks,
-Arv

Ms. Registered User, you're welcome!