Bartosz Gordon

Posted on Jun 7, 2021 • Originally published at bartoszgordon.com

One-liner to sum up numbers from a CSV file

#linux #bash #cli

This is the shopping.csv CSV file that we will be working with throughout this blog post.

item,price
rice,2.49
potatos,1.49
pasta,3.79

The goal is to create a one-line bash script that prints the sum of the prices in the file above, which is 7.77.

Step: 1. Print the file content

$ cat shopping.csv

This command prints the file content without modifications.
It gives us a way to redirect - pipe - the output to another command in the next step.

Output:

item,price
rice,2.49
potatos,1.49
pasta,3.79

Step: 2. Cut the first line

There are many ways to achieve it, but we use the tail +n syntax.

In our case, it takes all the lines until the end of the file, starting from the second line.

$ cat shopping.csv | tail +2

Output:

rice,2.49
potatos,1.49
pasta,3.79

Step: 3. Cut the first column

awk splits each line by a separator defined by the option -F.
In our case, it's the comma.
Then it prints the second column from every row.

$ cat shopping.csv | tail +2 | awk -F , '{print $2}'

Output:

2.49
1.49
3.79

Step: 4. Concatenate the prices

xargs is used to "squash" the lines into a single string, separating them by space.

$ cat shopping.csv | tail +2 | awk -F , '{print $2}' | xargs

Output:

2.49 1.49 3.79

Step: 5. Replace spaces with pluses

The sed expression replaces the escaped space with + in the entire string (globally - g).

$ cat shopping.csv | tail +2 | awk -F , '{print $2}' | xargs | sed -e 's/\ /+/g'

Output:

2.49+1.49+3.79

Step: 6. Perform the calculation

bc is a simple calculator that you can use interactively or by piping an equation into it.

$ cat shopping.csv | tail +2 | awk -F , '{print $2}' | xargs | sed -e 's/\ /+/g' | bc

Output:

7.77

Top comments (8)

Darius Juodokas • Jun 8 '21

Single command oneliner :)

awk -F, '{if(NR==1)next;total+=$2}END{print total}' shopping.csv

if(NR==1) // if Number_of_Row is 1, i.e. if it's the first row
next // continue, i.e. skip the row and go to the next one
total+=$2 // create a global variable total if not exists (initialized with '0') and add 2nd column value to it.

Repeat above for all the lines

END{print total} // print the global variable total to the console.

I used to hate awk scripts - they are so awkward to read... But awk embeds so many shell utilities that it's really worth to learn it ;)

Darius Juodokas • Jun 8 '21

Or a shorter (less verbose) version:

awk -F, 'FNR>1{total+=$2}END{print total}' shopping.csv

Sergii Kulyk • Jun 16 '21 • Edited

awk -F, '{sum+=$2}END{print sum}' shopping.csv

cut -d, -f2 a.txt|paste -s -d+|bc

Ben Sinclair • Jun 8 '21

I like this, because there are loads of ways you could do it.

You could use cut -d, -f2 instead of the awk. You could use tr "\n" "+" instead of the sed to transform one character (though you might have to fudge that).

You can get rid of the cat and use tail +2 shopping.csv.

You can use grep to do almost all the heavy lifting if you want, because you can tell it to only print the matching text with -o, which has the added benefit of skipping the first line entirely so you no longer need the cat or the tail.

I'd go with this combo, which uses paste to join the lines:

grep -o "[0-9.]\+" shopping.csv | paste -s -d+ | bc

Andrea Borruso • Sep 14 '22

Using the great Miller is

mlr --c2n stats1 -f price -a sum input.csv

Alexey Mints • Jun 9 '21

How to use 6 commands, where one can use just 1...

Ben Sinclair • Jun 15 '21

Using six commands demonstrates six commands, and how to chain things together to get what you want.

If you don't know something exists, or you haven't gotten around to learning (say) awk, then the tools you have can almost always be strung together to get the same result.

That's what makes us, as users, so powerful.