0

I have a scenario where I want to calculate the sum of multiple columns containing floating point data.

My below code works perfect with integer values but, not with floating paint values.

Code works perfect for this data

ID|NAME|SAL|COST|PER|TAG

1|A|10|10|20|10|

1|B|10|15|20|10|

1|C|10|17|25|80|

1|D|115|110|20|100|

1|E|10|10|10|10|

code :

#!/bin/bash
FILE="$1"
COLUMNS="${@:2}"
for col in $COLUMNS; do
  colnum=$(awk -v RS='|' '/'$col'/{ print NR; exit}' $FILE)
  awk '{FS="|"}{s+='$colnum'}END{print "'$col' ", s}' $FILE
done | column -t

How do I achieve sum for below data using above code?

ID|NAME|SAL|COST|PER|TAG

    1|A|9.234|123.12|20.123|67.1|

    1|B|10.12|153.234|20.90|190.34|

    1|C|8.234|17.01|25.777|80.09|

    1|D|11.2|11.222|10.1|10.00000|

    1|E|16.23|10.1|145.22|11.77278|

New code not working:

#!/bin/bash
FILE="$1"
COLUMNS="${@:2}"
for col in $COLUMNS; do
            awk -F'|' '{T+=$col} END { printf "%.10f\n", T }' $FILE
    done | column -t
1
0

Start by not doing this in the shell, but in something like awk. The shell does not know how to do floating point arithmetics.

Your script seems to be getting a set of parameters that corresponds to the filename to parse and the names of the fields to consider.

You can do this a bit more efficiently than to call awk twice for each column.

BEGIN { OFS = FS = "|" }

FNR == 1 {
        # Handle the header row. Assuming "cols" is a FS-separated
        # string of column names that we want to sum, pick out the
        # column numbers that we want to process and put them in the
        # array "col". This also converts the "cols" string into a
        # corresponding "header" array.

        nf = split(cols, header)
        for (i = 1; i <= NF; ++i)
                for (j = 1; j <= nf; ++j)
                        if ($i == header[j])
                                col[j] = i
        next
}

{
        # Sum each column that we have enumerated in the "col" array.

        for (i = 1; i <= nf; ++i)
                sum[i] += $(col[i])
}

END {
        # Output a two row table containing only the headers that we
        # have summed over and the accumulated sums.

        $0 = ""

        for (i = 1; i <= nf; ++i)
                $i = header[i]
        print

        for (i = 1; i <= nf; ++i)
                $i = sum[i]
        print
}

Running this on your first file with:

$ awk -v cols="SAL|COST|PER|TAG" -f script.awk file1
SAL|COST|PER|TAG
155|162|95|210

Listing the same column twice gives the sum twice:

$ awk -v cols="SAL|SAL" -f script.awk file1
SAL|SAL
155|155

On the second file:

$ awk -v cols="SAL|COST|PER|TAG" -f script.awk file2
SAL|COST|PER|TAG
55.018|314.686|222.12|359.303

With post-processing using column -t:

$ awk -v cols="SAL|COST|PER|TAG" -f script.awk file2 | column -s '|' -t
SAL     COST     PER     TAG
55.018  314.686  222.12  359.303

Putting this into a simple script:

#!/bin/sh

infile=$1
shift

IFS='|'

awk -v cols="$*" -f script.awk "$infile" |
column -s '|' -t

You would use this as

$ ./script.sh file1 PER TAG
PER  TAG
95   210

You could also use a CSV-aware tool, like the tools in CSVkit, to do the work for you.

csvstat is able to compute some basic "stats" of a CSV file. Assuming your files are properly formatted CSV files (they are not, as each data row has a trailing |-delimiter), the sum for each column can be had with

$ csvstat --sum file1
  1. ID: None
  2. NAME: None
  3. SAL: 155
  4. COST: 162
  5. PER: 95
  6. TAG: 210
$ csvstat --sum file2
  1. ID: None
  2. NAME: None
  3. SAL: 55.018
  4. COST: 314.686
  5. PER: 222.12
  6. TAG: 359.30278

Or, for just a single column:

$ csvstat --sum -c 'SAL' file2
55.018
15
  • can you tell me how to make my script work i have updated the question ... added newly created code at the end ... where as your solution is really good .... is it possible to do with my code with liitle chnages pls look question updated
    – genip26057
    Jan 1 '20 at 7:15
  • @genip26057 You're using $col in the awk code, but this would refer to column number col, and there's no col variable in the program. Therefore, $col is the same as $0, which is the complete line.
    – Kusalananda
    Jan 1 '20 at 7:20
  • @genip26057 Also see unix.stackexchange.com/questions/50044/…
    – Kusalananda
    Jan 1 '20 at 7:21
  • @can you modify the provide code to make it work ...pls
    – genip26057
    Jan 1 '20 at 7:21
  • @genip26057 Note that you have not indicated in the slightest way what you expect that a user give as arguments to the script. In the first coed it seems to be column names, in the second, it's a (single) column number?
    – Kusalananda
    Jan 1 '20 at 7:23
0

My solution will work like charm

For this DATA set 1 - tested on this

ID|NAME|SAL|COST|PER|TAG
1"|"A"|"50.1123"|"10.1"|"25.22"|"10.2"|"
2"|"B"|"50.11"|"15.45"|"25.1"|"10.1118"|"

For this DATA set 2 - tested on this

ID|NAME|SAL|COST|PER|TAG
1|A|9.234|123.12|20.123|67.1
2|B|10.12|153.234|20.90|190.34
3|C|8.234|17.01|25.777|80.09
4|D|11.2|11.222|10.1|10.00000
5|E|16.23|10.1|145.22|11.77278

Effective solution

FILE_NAME="$1"
COLUMN_NAME="$2"

alpha=( $(awk -F"|" 'NR==1{for(i=1;i<=NF;i++){if ($i ~ /'$COLUMN_NAME'/){print i;}}}' $FILE_NAME) )

for each in "${alpha[@]}"
do
      #echo "$each"
      awk -F'"?\\|"?' '{T+=$('$each')} END { printf "%.4f\n", T }' $FILE_NAME
done

Run like this

bash script.sh DEMO.txt 'SAL|COST|PER|TAG'

output 1 :

100.2223
25.5500
50.3200
20.3118

output 2 :

55.0180
314.6860
222.1200
359.3028
3
  • This input data is different from what's in the question. When providing a dataset in a question, make sure that it is representative of the actual data that you are working on.
    – Kusalananda
    Jan 1 '20 at 17:58
  • @Kusalananda previously i was not known what will be the DATA set .... i got with a requirement now ... so whole process got changed ... this is also another way we can achieve
    – genip26057
    Jan 1 '20 at 18:28
  • @Kusalananda even i tested with another DATA set it work like charm ... updated the answer please have a look
    – genip26057
    Jan 1 '20 at 18:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.