DEV Community

loading...
Cover image for One-liner to sum up numbers from a CSV file

One-liner to sum up numbers from a CSV file

Bartosz Gordon
Originally published at bartoszgordon.com ・2 min read

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Output:

rice,2.49
potatos,1.49
pasta,3.79
Enter fullscreen mode Exit fullscreen mode

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}'
Enter fullscreen mode Exit fullscreen mode

Output:

2.49
1.49
3.79
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Output:

2.49 1.49 3.79
Enter fullscreen mode Exit fullscreen mode

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'
Enter fullscreen mode Exit fullscreen mode

Output:

2.49+1.49+3.79
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Output:

7.77
Enter fullscreen mode Exit fullscreen mode

Discussion (7)

Collapse
netikras profile image
Darius Juodokas

Single command oneliner :)

awk -F, '{if(NR==1)next;total+=$2}END{print total}' shopping.csv
Enter fullscreen mode Exit fullscreen mode
  • 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 ;)

Collapse
netikras profile image
Darius Juodokas

Or a shorter (less verbose) version:

awk -F, 'FNR>1{total+=$2}END{print total}' shopping.csv
Enter fullscreen mode Exit fullscreen mode
Collapse
sfkulyk profile image
Sergii Kulyk • Edited
awk -F, '{sum+=$2}END{print sum}' shopping.csv
Enter fullscreen mode Exit fullscreen mode
cut -d, -f2 a.txt|paste -s -d+|bc
Enter fullscreen mode Exit fullscreen mode
Collapse
moopet profile image
Ben Sinclair

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
Enter fullscreen mode Exit fullscreen mode
Collapse
minzastro profile image
Alexey Mints

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

Collapse
moopet profile image
Ben Sinclair

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.

Collapse
shadowruge profile image
izaias

otimo post, apoie o meu git:
github.com/shadowruge/cadastrocsv