[SOLVED] Unix: File have 28 columns separated by space, print column 8 unique values with sum of column 16 to column28

Issue

Sample File.txt:

"Object_Type" "710" "30" "14" "Partner" "Rpt" "Cool" "0ICPRP900" "Des_Space" "HBBREX" "soon_Type" "FY28" "Jan" "50" "USD_US" #Mi #Mi #Mi 0.006641 0 0 0 0 0 0 0 0 0
"Object_Type" "710" "30" "14" "Partner" "Rpt" "Cool" "0ICPRP 900" "Des_Space" "HBBREX" "soon_Type" "FY28" "Jan" "50" "USD_US" #Mi #Mi #Mi 0.01 0 0 0 0 0 0 0 0 0
"Object_Type" "710" "30" "14" "Partner" "Rpt" "Cool" "0ICPRP A&B_900" "Des_Space" "HBBREX" "soon_Type" "FY28" "Jan" "50" "USD_US" #Mi #Mi #Mi 0.641 0 0 0 0 0 0 0 0 0

Looking for output line:

0ICPRP900 0.006641
0ICPRP 900 0.01
0ICPRP A&B_900 0.641

I have tried below command and it's printed column 8 (column not printed after have space special char )

awk '{a[$8]+=$17+$18+$19+$20+$21+$22+$23+$24+$25+$26+$27+$28}END{for (i in a) 
print i,bc a[i]}' File.txt

Output:

"0ICPRP900" 0.006641
"0ICPRP 0.01
"0ICPRP 0.641

Any suggestions how to get unique column8 (column8 with in "") with sum of column 16 to Column 28

Solution

Using any awk:

$ awk -F'"' '{n=split($31,f," "); sum=0; for (i=4; i<=n; i++) sum+=f[i]; print $16, sum}' file
0ICPRP900 0.006641
0ICPRP 900 0.01
0ICPRP A&B_900 0.641

Or using GNU awk for FPAT and gensub():

$ awk -v FPAT='"[^"]*"|[^ ]+' '{sum=0; for (i=19; i<=NF; i++) sum+=$i; print gensub(/"/,"","g",$8), sum}' file
0ICPRP900 0.006641
0ICPRP 900 0.01
0ICPRP A&B_900 0.641

or if by unique column8 you mean you want to sum the values for multiple occurrences of the same $8 value across the input file you can do either of:

any awk:

awk -F'"' '
    {
        n = split($31,f," ")
        for (i=4; i<=n; i++) {
            sum[$16] += f[i]
        }
    }
    END {
        for (key in sum) {
            print key, sum[key]
        }
    }
' file

GNU awk:

awk -v FPAT='"[^"]*"|[^ ]+' '
    {
        for (i=19; i<=NF; i++) {
            sum[gensub(/"/,"","g",$8)] += $i
        }
    }
    END {
        for (key in sum) {
            print key, sum[key]
        }
    }
' file

Answered By - Ed Morton