Using AWK to sum up two column for each block

Question

I would like to calculate for each block/("team") a summary of $4 & $5 in awk

Sample input:

ABC Team
1109;BB; EE;B;M
1108;GG; KK;B;M
1104;KK; CC;F;S
1103;LL; JJ;B;XL

CBS Team
600;AA; AA;O;
597;AA; MM;O;
599;BB; JJ;B;M
593;DD; SS;B;S

UMG Team
7341;BB; TT;F;M
7339;FF; AA;B;M
7338;FF; SS;B;M
7336;GG; UU;B;XL

Desired output

ABC Team
1109;BB; EE;B;M
1108;GG; KK;B;M
1104;KK; CC;F;S
1103;LL; JJ;B;XL
B;M = 2
B;XL = 1
F;S = 2

CBS Team
600;AA; AA;O;
597;AA; MM;O;
599;BB; JJ;B;M
593;DD; SS;B;S
O;  = 2
B;M = 1
B;S = 1

UMG Team
7341;BB; TT;F;M
7339;FF; AA;B;M
7338;FF; SS;B;M
7336;GG; UU;B;XL
F;M = 1
B;M = 2
B;XL = 1

This is the code I came up with, but does not work or have not found any code leading to the desired output. Can anybody help?

awk -F; "{if(NF>3) {a[$4 $5]++}} {if(NF==0) {for (pair in a) print pair, a[pair];a=0; pair=0}}1"

Edit - here's the above code formatted legibly by gawk -o-:

{
        if (NF > 3) {
                a[$4 $5]++
        }
}

{
        if (NF == 0) {
                for (pair in a) {
                        print pair, a[pair]
                }
                a = 0
                pair = 0
        }
}

1 {
        print
}

Why does ABC Team show a predicted F;S = 2 output? Should that be F;S = 1? Also, CBS Team you add across rows to count O; = 2 values. How do you know these Os can be added together? Is it possible that they have different 'partners' and thus should not be summed? — jubilatious1
– jubilatious1, Commented Jun 23, 2022 at 3:10

Ed Morton · Accepted Answer · 2022-06-23 00:31:09Z

$ cat tst.awk
BEGIN { FS=SUBSEP=";"; OFS=" = " }
NF == 0 { prt(); delete sum }
{ print }
NF > 3 { sum[$4,$5]++ }
END { prt() }

function prt(   key) {
    for ( key in sum ) {
        print key, sum[key]
    }
}

$ awk -f tst.awk file
ABC Team
1109;BB; EE;B;M
1108;GG; KK;B;M
1104;KK; CC;F;S
1103;LL; JJ;B;XL
B;XL = 1
F;S = 1
B;M = 2

CBS Team
600;AA; AA;O;
597;AA; MM;O;
599;BB; JJ;B;M
593;DD; SS;B;S
B;S = 1
O; = 2
B;M = 1

UMG Team
7341;BB; TT;F;M
7339;FF; AA;B;M
7338;FF; SS;B;M
7336;GG; UU;B;XL
B;XL = 1
F;M = 1
B;M = 2

At least start out by writing your code using multiple lines and indenting, preferably stored in a file so you avoid any OS-specific quoting issues and any mistakes in environment variable usage, and it's much easier to understand, debug, and enhance while you're trying to get it working. Cram it all into one line after it works if you find that useful.

Stack Exchange Network

Using AWK to sum up two column for each block

1 Answer 1

You must log in to answer this question.

Hot Network Questions

Using AWK to sum up two column for each block

1 Answer 1

You must log in to answer this question.

Related

Hot Network Questions