Not able to calculate sum of multiple column where file data in different format .csv

Question

I have a scenario where i want to calculate sum of column

SAL|CAL|TAG|VAL
12.11"|"1.1"|"2.1"|"1.1
13.11"|"1.1"|"2.2"|"2.2
14.11"|"1.1"|"2.4"|"2.4

My below code not giving output giving output as 0.00

I need output for this column CAL VAL

awk -F'|' '{T+=$2} END { printf "%.2f\n", T }' demo.txt

output :

6.7 5.7

The issue is that (outside of the header line) your delimiter is "|" not | - when you use | as the delimiter, the field values become strings like "1.1" that evaluate numerically to zero — steeldriver, Jan 01 '20 at 16:36
Does this answer your question? How to create a function to sum the required multiple column — markgraf, Jan 01 '20 at 17:34

score 0 · Accepted Answer · answered Jan 01 '20 at 17:29

Either the delimiters in your file are inconsistent (plain | in the header line but "|" thereafter) or the delimiter is consistently | but the field quoting is inconsistent (missing open " on the first field and closing " on the last).

Either way, when you set -F'|', the values of $2 are parsed as strings, like "1.1" which get converted to a numeric value of zero when you try to perform addition on them.

If your system's version of awk supports regular expressions for the field separator, you could instead use -F'"?\\|"?' which allows the (literal) | to have optional leading and trailing quotes:

$ awk -F'"?\\|"?' '{T+=$2} END { printf "%.2f\n", T }' demo.txt
3.30

super code worked like charm – genip26057 Jan 01 '20 at 17:38 — genip26057, Jan 01 '20 at 17:38

score 0 · Answer 2 · answered Jan 01 '20 at 17:47

From the expected output, you seem to be adding TAG and VAL columns.

I note your input is not valid CSV. You can not-quote fields, like 12.11, or you can quote fields, like "12.11". Half-quoting them is not valid.

Two ways to do this, including avoiding adding "numeric" values from the column headings. (None of this is tested.)

(a) Define a field separator to match the reality.

awk '-F"[|]"' 'NR == 1 { next; }
{ T3 += $3; T4 += $4; }
END { printf ("T3 %.2f T4 %.2f\n", T3, T4); }'

(b) Fix each field before you use it.

awk '-F|' 'NR == 1 { next; }
{ gsub ("\042", "", $3); T3 += $3; }
{ gsub ("\042", "", $4); T4 += $4; }
END { printf ("T3 %.2f T4 %.2f\n", T3, T4); }'

If this is an example, and you are planning to do this for more columns, I would probably make T into an array, and the addition into a function:

awk '-F|' 'NR == 1 { next; }
function Sum (f) {
    gsub ("\042", "", $(f)); T[f] += $(f);
}
{ Sum(2); Sum(3); Sum(5); Sum(11); }

END {
    for (f = 1; f < 20; ++f)
        if (f in T) printf ("T%d %.2f\n", f, T[f]);
}'

Not able to calculate sum of multiple column where file data in different format .csv

2 Answers2

Related