command-line tool to sum the values in a column of a CSV file

Question

I am looking for a command-line tool to calculate the sum of the values in a specified column of a CSV file. (Update: The CSV file might have quoted fields, so a simple solution just to break on a delimiter (',') does not work.)

Given the following sample CSV file:

description A,description B,data 1, data 2
fruit,"banana,apple",3,17
veggie,cauliflower,7,18
animal,"fish,meat",9,22

I want to build the sum, for example, over the column data 1 with the result 19.

I have tried to use csvkit for this but didn't get very far. Are there other command-lien tools specialised in this CSV operation?

Answer 1

Miller handles quoted CSVs natively, so the following should work:

mlr --csv --headerless-csv-output stats1 -a sum -f 'data 1'

Answer 2

I put your CSV into a file a.csv and did the summing as follows:

cat a.csv | awk -F, '{ if($3 !~ /data*/  ){ total+= $3} } END { print "sum=" total}'

This is using awk with a comma as a field separator. If field #3 does not match data*, we add the value to total. When done, print out "sum=" and the total value. This is a subset of the Processing Tabular Data awk answer.

An updated version that uses FPAT for to allow the quotes:

cat a.csv | awk -v FPAT='([^,]*)|("[^\"]+\")' '{ if($3 !~ /data*/ ){ total+= $3 } } END {print "sum="total}'

The FPAT is a regular expression for gawk (that won't work for old style awk) that specifies how the fields are defined. In this case, there are two regular expressions, ([^,]*) says zero or more occurrences of zero or more non-comma characters. the ("[\"]+") says one or more occurrences of non-quote characters, contained within quotes.

Answer 3

Using csvsql from the csvkit set of tools, which was what you originally tried to use:

$ csvsql -I --query 'SELECT SUM("data 1") FROM file' file | tail -n +2
19

This inserts the CSV data from file into a database table of the same name, without type inference (-I). It then applies the SQL statement SUM("table 1") on that table to get the sum of the table 1 field.

Since the output will contain a header, we strip it off with a call to tail.

Answer 4

Using GNU awk 5.3 or newer for -k (or equivalently --csv):

awk -k '{n+=$3} END{print n+0}' file

See https://stackoverflow.com/questions/45420535/whats-the-most-robust-way-to-efficiently-parse-csv-using-awk for more information on parsing CSV with awk.

Answer 5

If you're willing to try csvkit again, it's possible using two simple steps. The first will extract just your data 1 column, then the second will output the sum of that column to the command line.

csvcut -c "data 1" file | csvstat --sum

For an enormous file, this won't be as efficient as some of the single step options above.