0

I have a csv file as text.csv and want to calculate the total salary of all employees. The data in it is as follows:

Id,name,salary,designation
12,joe,thomas,253637,IT,admin
25,miacheal,364829,software engineer
46,mike,silva,adam, 374829479, senior, software, engineer
85,amber johnson,3728291,IT
24,Austin barclay, 3701830,software engineer

I have tried using

awk -F "," '{ SUM_SALARY += $3 } END { print SUM_SALARY }'

but the total sum is incorrect. The real data has 5.5 million entries.

  • 1
    The salary field of line #2 is "thomas", which is a string, not a number. Please clarify whether the data above is intentionally messed up; if not, please correct it. – agc Mar 22 '17 at 5:54
1

Assuming there's a migrating salary field, (that wanders from field 3 to 5 and back), two GNU greps can isolate the salaries, and pass them to numsum:

grep -o '[, ][[:digit:]]*,' test.csv | grep -o '[[:digit:]]*' | numsum 

Output:

382878066
  • @PremJoshi, Re "no such command": numsum can be found in Debian's num-utils package, or it can be had from the num-utils homepage. – agc Mar 22 '17 at 20:55
  • 1
    Lacking GNU grep, a single sed would do as well: sed '2,$s/^[^0-9]*[0-9]*[^0-9]*\([0-9]*\).*$/\1/' or similar. num-utils seems really neat! – Fox Mar 23 '17 at 17:15
  • seen elsewhere: instead of numsum just say paste -sd+ -|bc (which will work fine for non-extreme inputs) – fgeorgatos Jul 7 '17 at 18:02
1

Your name fields contain commas but are not quoted, so awk will see differing types of values in the $3 column for each line. This would be the case even if they were quoted, but in the case shown here even a real CSV parser won't help. Here is what I did to get around this:

awk 'BEGIN { FS=",,*"; print 0 }
     (NR > 1) { gsub("[^0-9,]",""); printf "%d+\n", $2 }
     END { print "pq" }' < text.csv \
| dc

This first uses gsub to remove everything that is not a digit or comma within each line, then prints the value of the new second column, where columns are considered to be separated by one or more commas. The BEGIN and END blocks make the data work with the format dc expects.


I used dc because I don't know if awk acts as a general-purpose, arbitrary-precision calculator. If it does, you could instead do:

awk -F ',,*' '(NR > 1){ gsub("[^0-9,]",""); SUM_SALARY += $2 } END { print SUM_SALARY }' < text.csv

This case works much like the earlier one, except that awk is doing the arithmetic instead of dc. This also uses the -F switch to set the field separator, to avoid an otherwise unused BEGIN block.

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.