15

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?

15

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:

enter image description here

For details please read the README on github.

  • It would be helpful if you'd include the keystrokes required to select, sum and insert in your answer. – pdoherty926 Dec 4 '17 at 16:32
  • @pdoherty926 For details please read the README on github. Even if I put the keystrokes I pressed for this problem here, I don't see how helpful it could be, it is just 3 or 4 key-combinations. If my script is really needed by someone, he/she will check the details anyway. – Kent Dec 4 '17 at 16:46
11

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>
  • 1
    For some reason I only managed to use your solution with double quotes " instead single quotes ' on the substitute command. Do you know if is there any reason for that ? – vappolinario Sep 10 '15 at 19:52
  • @vappolinario it works both ways for me, so I'm afraid I don't know, sorry. – Hovercouch Sep 10 '15 at 20:17
  • @Hovercouch Could you elaborate on the third step? How, exactly, would one go about running the replacement through the expression register? – pdoherty926 Dec 4 '17 at 16:09
  • How about making a map: ` nnoremap <c-s> :s/$/\=eval(substitute(@0, '[^0-9]', '+', 'g'))/<cr>` – SergioAraujo Jul 3 at 17:35
9
: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

I have been trying a function that works here:

" This function requires you 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 map above included, all you have to do after loadin 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 extructure to capture errors.

let l:a_save = @a .......... if whe have register 'a' we save it temporarelly
norm! gv"a  ................................... 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 bellow 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 avaiable

It needs you 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.

7

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

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.

  • How can one deal with other possible field separators? Just for making the solution more generic. – SergioAraujo Sep 15 '17 at 15:59
  • @user2571881, I've edited the answer, showing that. – JoL Sep 15 '17 at 16:32
  • @JoL adding functions like SumColumn to vimrc means you simply have your 'plugins' in your vimrc. Hopefully, you are good at maintaining this with time. For me plugins provide documentation, separation into meaningful parts, taking advantage of others ingenuity. I contribute to upstream which improves amazing plugins which no one has time to create all of them on their own (except tpope). Do you not use vim-surround, vim-fugitive, vim-easy-align/vim-lion, vim-unimpaired, vim-commentary, ultisnips or ft-specific ones such as vim-go, vim-rails, vimtex? – Hotschke Jul 26 '18 at 10:54
  • @Hotschke When I got here, I saw the question and thought, "well, just pipe through awk." But then, I saw the accepted answer was, "hey, download this hundreds of LOC plugin and install it." Third answer was, "hey, download this thousands of LOC plugin and install it." It's overkill and bloat. Even if you needed to sum columns more than once in your life, it's overkill. My answer is meant to show how you can do this in a single no-plugins, no-nonsense command if you only need to do this once, and how you can make a simple command with parameters out of it if you need to do this often. – JoL Jul 26 '18 at 15:31
  • @Hotschke To answer your question, I used to install every plugin under the sun that looked remotely cool, but then my vim was incredibly slow (read "a little laggy" which is intolerable for an editor). On looking more into vim docs, I realized that I didn't really need the plugins. Many of the stock features were good enough, and, for those that vim didn't have, the shell was the way to go. Foundationally (ignoring exceptions it's made), as per the Unix philosophy, vim is an editor that interoperates well with other OS tooling. I believe that's the way to best utilize it. No plugins since. – JoL Jul 26 '18 at 15:32
1

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

1

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).

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.