2

I like using vim to edit csv files. But I often have to resort to localc when doing actual maths on them. Is there a way to use Math inside a vim Macro? Basicly adding 2 cells together and outputting them to a new third one?

7,6,(13)
8

Yes, you can perform arithmetic expressions in Vim by using the expression register, which you can access from insert mode using CTRL-R.

Another alternative is to use expressions in the replacement of a :substitute command, which can be helpful when performing batch operations.

In your specific case, assuming you have a line with two columns:

7,6

And you want to add a third with the sum:

7,6,13

(I'm assuming you don't want the parens around the result, since then it's still a valid numeric field in CSV.)

So one way to do it using a macro would be:

qa^"xywf,l"yywA,<C-R>=@x+@y<CR><Esc>+q

(Note that <C-R> actually means pressing CTRL-R, <CR> means pressing Enter and <Esc> means pressing Escape.)

Breaking this down:

  • qa: Start recording a macro, save it on register a, so you can later use the @a command to repeat it.
  • ^: Move to the first non-blank character of the line.
  • "xyw: Yank a word into register x, that should be your first number.
  • f,l: Forward to the comma and then move one more character right. This should get you to the start of the second number.
  • "yyw: Yank a word into register y, which will now contain your second number.
  • A: Append at the end of the line.
  • ,: Insert a literal comma.
  • <C-R>=: Insert contents of register =, but the expression register is special in that you'll get prompted for an expression to insert.
  • @x+@y<CR>: That's the expression to evaluate. @x and @y are accessing the x and y registers, since that's where the two numbers were stored. The final <CR> ends the expression, which gets evaluated and its result gets inserted, and you get back to insert mode.
  • <Esc>: Leave insert mode.
  • +: Move to the start of the next line, which might come in handy if you want to repeat this macro to process multiple lines.
  • q: Stop recording.

After recording this macro, you can apply it to further lines with @a.

Another way to use this sequence is with :g/.../normal!, in which case you can add a :execute to be able to escape the special keystrokes such as CTRL-R:

:g/^\d/execute "normal! ^\"xywf,l\"yywA,\<C-R>=@x+@y\<CR>\<Esc>"

(In this case, all characters are typed literally, this one can be used with copy & paste. Vim will know how to interpret "\<C-R>" as equivalent to a CTRL-R keystroke.)

The command above will execute the same explained sequence on all lines starting with a digit (assuming a CSV would have a first line with the headers, that wouldn't match it.)

As mentioned at the start, another possibility is to use :s with an expression in the result. For :s, you can use the submatch() function to access groups matched in the pattern:

:s/\(.*\),\(.*\)/\=submatch(0).','.(submatch(1)+submatch(2))/

Here, starting the replacement with a \= indicates an expression is used and should be evaluated for every match.

The submatch(0) part repeats the whole matched string (to preserve the original columns), then the string concatenation operator . is used to add a comma and the result of the sum of the numbers in the two matched groups.

Note that Vim will convert the expressions to numbers and back to strings as needed, that's expected. (In effect, that's why the string concatenation operator . is distinct from the arithmetic add operator +.)

While these operations are somewhat low-level, they demonstrate some of the possibilities for manipulating numbers in Vim.

| improve this answer | |
7

When I see the not so short vim solution :s/\(.*\),\(.*\)/\=submatch(0).','.(submatch(1)+submatch(2))/ following statement from https://vimways.org/2019/vim-and-the-shell/ comes to my mind:

“Vim is not meant to be a universal answer to all problems.”

There are also dedicated tools you might consider using. A list can be found here:

https://github.com/dbohdan/structured-text-tools#dsv

Pre-installed on most systems is awk. Assuming following file and you want to add a new column based on combining existing columns by a mathematical expression, e.g. +.

data.csv

1,2
4,18

Awk

awk -F',' 'BEGIN{OFS=","}{print $0, $1+$2}' data.csv

with reordering

awk -F',' 'BEGIN{OFS=","}{print $2, $1, $1+$2}' data.csv

Miller

Miller is like awk, sed, cut, join, and sort for name-indexed data such as CSV, TSV, and tabular JSON.

Doing the same as awk would look like:

$ mlr -N --csv put '$3=$1+$2' data.csv

We need to tell Miller that we want again csv format with --csv and the input does not have a headerline with -N.

As quoted Miller is designed for data with name-indexed data:

named_cols.csv

col1,col2
1,2
4,18
$ mlr --csv put '$sum = $col1 + $col2' named_cols.csv

This expression does not rely on the order of your columns and in contrast to an awk equivalent expression does not need to handle the header line separately. IMHO working with named columns is more readable and less error prone.

| improve this answer | |

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.