Bash Script total sum of a column when another column equals to an input value

Question

I am having a csv file with Header. I would like to create a bash script without using AWK to calculate the sum of let's say the 6th column if the variable on the column 13 equals to an input that I will pass via CMD.

File looks like:

IDENTIF,RIVER,LOCATION,ERECTED,PURPOSE,LENGTH,LANES,CLEAR-G,T-OR-D,MATERIAL,SPAN,REL-L,TYPE
E2,A,25,1819,HIGHWAY,1037,2,N,THROUGH,WOOD,SHORT,S,WOOD
E7,A,27,1840,HIGHWAY,990,2,N,THROUGH,WOOD,MEDIUM,S,WOOD
E16,A,25,1859,HIGHWAY,1030,2,N,THROUGH,IRON,MEDIUM,S-F,SUSPEN

So I would like for example if I execute script.sh WOOD , to print the sum of the 6th columns for the values in column 13 that equal to WOOD.

I have seen many solutions using AWK and solving it immediately. I was hoping how would you do the same without it.

Code I tried:

#!/bin/bash
skip_line = 0
total_length = 0
while IFS=, read -r -a fields 
do
if [ $skip_line eq 0 ]
then
  skip_line = $(($skip_line + 1))
  continue
fi
if [ ${fields[12]} == $1 ]
then
    total_length = $(($total_length + ${fields[5]}))
fi
done < file.csv
echo "$total_length"

The header seems to be missing from the data. Also, why can't you use awk, a very typical and standard UNIX tool? — Jeff Schaller, Nov 06 '20 at 17:20
I am practising on shell scripting and I am very beginner. I''ll update the headers. — user440826, Nov 06 '20 at 17:24
OK. As long as you understand that this is not something you should do with a shell script (it is slow, and very hard for no reason). So, what do you have so far? What have you tried? Which part of this is giving you trouble? — terdon, Nov 06 '20 at 17:33
while IFS=, read -r -a fields; do ...; done < file.csv is the key piece of the solution — glenn jackman, Nov 06 '20 at 17:46
by using -a fields you are passing it as an array to fields? For example $fields[6] will give me the value of the 6th column? — user440826, Nov 06 '20 at 18:08
... @user440826 bash arrays are zero indexed, so the 6th field will be ${fields[5]} — steeldriver, Nov 06 '20 at 18:22

score 0 · Answer 1 · answered Nov 06 '20 at 18:14

0

SMOP.

First, use grep to select the lines, use cut to extract the field, and sum the values.

Untested

#!/bin/bash
pat="$1"
export total=0
grep ",$pat," file.txt |\
  cut -d, -f6 |\
  while read num ; do
    total=$total + $num
  done
echo "$total"

answered Nov 06 '20 at 18:14

waltinator

4,865

i run it as ./script.sh WOOD. It prints +:command not found. – user440826 Nov 06 '20 at 18:20
now it justs prints me zero. – user440826 Nov 06 '20 at 18:36
I've posted my code above too but it hits error – user440826 Nov 06 '20 at 18:38
My script had an error on if condition. I did not let space [ condition ] as it needs to be. But now it says unary expression expected in my first IF condition. – user440826 Nov 06 '20 at 18:44

steeldriver · Answer 2 · 2020-11-07T00:28:11.773

0

Before considering doing it this way, please see Why is using a shell loop to process text considered bad practice?

Nevertheless, your attempt is close (one obvious issue is the whitespace around the = in your total_length assignment).

#!/bin/bash
sum=0
while IFS=, read -a fields; do
  if [[ ${fields[12]} = "$1" ]]; then
    (( sum += fields[5] ))
  fi
done < file.csv
printf 'sum: %d\n' "$sum"

You can shorten the if..then..fi using short-scircuit logic if you prefer ex.

[[ ${fields[12]} = "$1" ]] && (( sum += fields[5] ))

There's no need to skip the header line explicitly since it will fail the ${fields[12]} = "$1" test.

edited Nov 07 '20 at 00:28

answered Nov 06 '20 at 18:52

steeldriver

81,074

owo, yeah it worked! thank you! – user440826 Nov 06 '20 at 18:55
Perfect Explanation! thanks again. – user440826 Nov 06 '20 at 19:00

Bash Script total sum of a column when another column equals to an input value

2 Answers2