Quickly calculate the total of a column of numbers

Question

I'm writing down a markdown table which looks like this:

| 13/05/15 | 09:30-16:00 |  6.5 |
| 14/05/15 | 10:00-16:30 |  6.5 |
| 16/05/15 | 15:30-01:00 |  9.5 |
| 21/05/15 | 09:00-16:30 |  7.5 |
| 22/05/15 | 08:30-17:00 |  8.5 |
| 28/05/15 | 09:30-15:30 |  6   |
| 02/06/15 | 09:00-20:00 | 11   |
| 03/06/15 | 08:30-22:30 | 14   |

I'm looking for a way to quickly calculate the total of the third column and insert it in the buffer. The solution I have in mind would make use of visual-block mode (to select all the numbers) and maybe of the expression register (to do the math).

Would this be possible using native Vim commands? If not, is there a plugin that can help me?

Answer 1

I wrote a plugin: https://github.com/sk1418/HowMuch which supports visual selection and does math calculations.

By default the plugin supports three math-expression evaluation engines: Gnu bc, python, and vimscript. You can do the calculations on a certain one or let the plugin automatically choose one for you.

It works with your example like this:

For details please read the README on github.

Answer 2

If you don't want to use plugins or drop to a bash script, you can do something like the following:

• c-V {motions} "ay copy column into "a
• :let @a = substitute(@a, 'c-V c-J', '+', 'g') replace the column newlines with +
• ic-R=c-Ra run the replaced "a through the expression register

Alternatively: make the expression history entry re-usable for further sums of columns

• ctrl-V {motions} y put column into yank register ""
• ictrl-R=eval(substitute(@", '\n', '+', 'g'))

Repeating for another column:

• ctrl-V {motion} y (unchanged)
• ictrl-R=<CR> or if you did something else with the expression register, cycle through the history with the up arrow key (or with ctrl-P if you remapped it):
  ictrl-R=<up>...<up><CR>

Answer 3

:r!awk '{sum+=$6} END {print "Total: "sum}' %

Explanation:

:r ........... read (put result in this file)
! ............ external command
awk .......... external tool
{sum+=$6} .... sixth field (awk considers spaces as field separator)
END .......... at the end
{print "Total: "sum} --> string "Total: " plus your result
% ............ current file

Here's a function that uses Vim's expression register instead of awk:

" This function requires you to select the numbers
fun! SumVis()
    try
        let l:a_save = @a
        norm! gv"ay
        let @a = substitute(@a,'[^0-9. ]','+','g')
        exec "norm! '>o"
        exec "norm! iTotal \<c-r>=\<c-r>a\<cr>"
     finally
        let @a = l:a_save
     endtry
endfun
vnoremap <leader>s :<C-u>call SumVis()<cr>

Using the mapping above, all you have to do after loading the function is select the numbers you want to sum and use <leader>s to sum up the selected area.

Function explanation:

It uses try/finally/endtry structure to capture errors.

let l:a_save = @a ............................. if we have register 'a' we save it temporarily
norm! gv"ay ................................... gv --> reselects and captures selection to 'register a'
let @a = substitute(@a,'[^0-9. ]','+','g') .... removes all but numbers, dots and spaces from 'register a' and puts '+' among the numbers
exec "norm! '>o"  ............................. opens new line below selection. see :h '>
exec "norm! iTotal: \<c-r>=\<c-r>a\<cr>" ...... insert "Total: " plus 'expression register result
let @a = l:a_save ............................. restores original 'a' register content

If you want to try this function, do the following: Copy this function in your browser and run this command on vim :@+ this will allow you to use :call SumVis() normally.

:@+ ......... loads `+` register making the function available

It needs you to make a visual block selection with ctrl+v, unselect and finally call the function. Or you can use the suggested map which by itself removes the selection before calculating.

Answer 4

My csv plugin allows this. Use the :SumCol command and be sure to read the documentation.

Answer 5

Making a plugin or coding this in vimscript seems a little heavy. I believe in a plugin-free vim, and good composition with external tools.

Here is a 1-time command, based on user2571881's, that works even if the buffer has not been saved.

:%!awk -F '|' '{print; sum+=$4}; END {print "Total: "sum}'

If you want to save this command for future use, you might want to name it:

:command! -range=% -nargs=1 SumColumn <line1>,<line2>!awk -F '|' '{print; sum+=$('<args>' + 1)} END {print "Total: "sum}'

It works with visual selection. If you select a few rows and go into command mode, vim will prefix your command with :'<,'>, which is the line range for the visual selection. So you can run:

:'<,'>SumColumn 3

and it will only sum the 3rd column of the selected rows. By default the range is %, so

:SumColumn 3

will sum the 3rd column of all lines.

EDIT: If you want to be able to specify other field separators and default the column counted to the last one, you can cover the command in bash and handle the arguments with it, like this:

:command! -range=% -nargs=* SumColumn <line1>,<line2>!bash -c 'awk -F ${2:-|} "{print; sum+=\$(${1:-NF - 2} + 1)} END {print \"Total: \"sum}"' sumcolumn <args>

Now,

:SumColumn

will count the last column of a table with "|" field separators,

:SumColumn 3

will count the 3rd column of a table with "|" field separators, and

:SumColumn 3 +

will count the 3rd column of a table with "+" field separators.

Answer 6

If the columns are properly aligned, this can be done with a simple oneliner.

1. first select the column in block-wise visual mode as other answers have demonstrate -> CTRL-V + move the cursor
2. yank the selection with y
3. type: :echo eval(join(split(@", '\_s\+'), '+')) which splits the text yanked on spaces and new lines, rejoin the element with + character, and evaluate the string.
4. another way to proceed: replace newlines with + and evaluate: :echo eval(substitute(@", "\n", '+', 'g')) -- eval() is the closest thing to reduce we have.

If not, you'll have to use other tricks to count fields. For instance, split(getline('.'), "[ \t|]\\+") can be used to split the columns from a row in your array. From there, it becomes as simple as:

1. select your lines in visual mode
2. :echo eval(join(map(getline("'<", "'>"), { -> split(v:val, "[ \t|]\\+")[2] }), '+'))

In order to get rid of the magical values (field number - 1, and +), it can become a command

:command! -range=% -nargs=+ OnField 
    \ echo { field, what -> eval(join(map(getline(<line1>, <line2>), { -> split(v:val, "[ \t|]\\+")[field-1] }), what))}(<f-args>)

Which can be used with:

:OnField  3 +
:2,5OnField  3 +
:'<,'>Onfield 3 *   " after line-wise selection
....

Note: Here I use lambdas from Vim 7.4.1xxx

Answer 7

vmap ++ from plugin vmath by Damian Conway

1. Install plugin from github (only 178 sloc) e.g.

   $ wget https://raw.githubusercontent.com/thoughtstream/Damian-Conway-s-Vim-Setup/master/plugin/vmath.vim -P ~/.vim/pack/manual/start/damians-tools/plugin
   

2. Add mapping to your vimrc

   vmap <silent><expr>  ++  VMATH_YankAndAnalyse()
   

   However, I would suggest to use something else, e.g. gA

3. Move to the third colum 2f| and select the column in visual-block mode <C-V>G$
4. Press ++ (or your chosen mapping)
5. Results are shown and stored in registers (sum in s)
6. Insert sum from register s, e.g. with "sp

For a presentation of this plugin see the YouTube video Damian Conway, "More Instantly Better Vim" - OSCON 2013 (starting at minute 29).

Answer 8

External cli tool csvstat from csvkit

:!csvstat -d '|' -H -c 4 --sum %
69.5

Short explanation of the options

• -d DELIMITER Delimiting character of the input CSV file. Here |.
• -H Specify that the input CSV file has no header row.
• -c COLUMNS A comma separated list of column indices or names to be examined. Defaults to all columns.
• --sum Only output sums.

This tool also provides min, max, mean, median, stdev (standard deviation), count unique values, list of frequent values.

Insert into file with

<C-r>=system("csvstat -d '|' -H -c 4 --sum FILENAME 2> /dev/null")  

Installation

On macOS csvkit is available via homebrew and on Debian/Ubuntu and similar it can be installed with $ sudo apt install csvkit.

Answer 9

This is a way to do it: First, add the following to your .vimrc:

command! -range Add
    \ silent exec "norm gv\"ay" |
    \ let array = split(@a, "[\\n\\t ]\\+") |
    \ let sum = 0.00 |
    \ for item in array |
    \     let sum += str2float(item) |
    \ endfor |
    \ echo sum

then reload vim, and highlight a column of numbers in visual selection mode, and then type:

:'<,'>Add

and it will print out the sum of the numbers in the column.

Answer 10

I just did it this way:

• thru the block selection copied all values, as a column to a new buffer
• quickly edited the text to construct a mathematical expression
• copy/pasted it to the default system calculator

Answer 11

ctrl-v for block select, select the column, paste below G p

select the pasted text, press I, now you are in insert mode, type + press esc esc to escape. now each line has a + select it again press J

select the line with V type command :!bc, it will show as :'<,'>!bc

you got the sum