Question: How do I find the sum of integer column in a CSV file?
I have a CSV text file cities-population.csv containing city name, state abbreviation and population. These are the first 10 lines:
$ head -n 10 cities-population.csv
city,state,population
Abbeville,AL,2378
Adamsville,AL,4178
Addison,AL,669
Akron,AL,236
Alabaster,AL,34748
Albertville,AL,23710
Alexander City,AL,14515
Aliceville,AL,2024
Allgood,AL,557
I want to find the sum of all the numbers in the third column. The assumption is that the first two columns do not have a , comma other than the comma delimiter.
Solution 1: Use perl
Yes, I still use Perl along with bash. I usually use Perl for more complex regular expressions, but I'm showing you how to use regexes in this example.
perl -ne '/(.+?),(.+?),(\d+)/;$s+=$3; END {print "$s\n"}' cities-population.csv
OUTPUT:
$ perl -ne '/(.+?),(.+?),(\d+)/;$s+=$3; END {print "$s\n"}' cities-population.csv
215859657
How does this work?
perl -ne is to run the Perl interpreter and not print the file to the terminal. The -n prevents echoing the file contents to the terminal.
/(.+?),(.+?),(\d+)/ is the regular expression to capture all three columns. The third column will be accessed as $3.
$s+=$3; The sum of each match is added to $s.
END signifies the end of the matches.
{print "$s\n"}' prints the final value of sum $s.
Solution 2: Use awk
The awk version is equally simple.
awk -F, '{s+=$3}; END {print s}' cities-population.csv
Related Posts
If you have any questions, please contact me at arulbOsutkNiqlzziyties@gNqmaizl.bkcom. You can also post questions in our Facebook group. Thank you.