reformat in vim for a nice column layout

Question

I have this dataset in a csv file

1.33570301776, 3.61194e-06, 7.24503e-06, -9.91572e-06, 1.25098e-05, 0.0102828, 0.010352, 0.0102677, 0.0103789, 0.00161604, 0.00167978, 0.00159998, 0.00182596, 0.0019804, 0.0133687, 0.010329, 0.00163437, 0.00191202, 0.0134425 
1.34538754675, 3.3689e-06, 9.86066e-06, -9.12075e-06, 1.18058e-05, 0.00334344, 0.00342207, 0.00332897, 0.00345504, 0.00165532, 0.00170412, 0.00164234, 0.00441903, 0.00459294, 0.00449357, 0.00339737, 0.00166596, 0.00451926, 0.00455153
1.34808186291, -1.99011e-06, 6.53026e-06, -1.18909e-05, 9.52337e-06, 0.00158065, 0.00166529, 0.0015657, 0.0017022, 0.000740644, 0.00078635, 0.000730052, 0.00219736, 0.00238191, 0.00212762, 0.00163783, 0.000750669, 0.00230171, 0.00217917

As you can see, the numbers are formatted differently and misaligned. Is there a way in vim to quickly align the columns properly, so that the result is this

1.33570301776,  3.61194e-06, 7.24503e-06, -9.91572e-06, 1.25098e-05, 0.0102828,  0.010352,   0.0102677,  0.0103789, 0.00161604, 0.00167978, 0.00159998, 0.00182596, 0.0019804,  0.0133687,  0.010329,   0.00163437, 0.00191202, 0.0134425 
1.34538754675,  3.3689e-06,  9.86066e-06, -9.12075e-06, 1.18058e-05, 0.00334344, 0.00342207, 0.00332897, 0.00345504,0.00165532, 0.00170412, 0.00164234, 0.00441903, 0.00459294, 0.00449357, 0.00339737, 0.00166596, 0.00451926, 0.00455153
1.34808186291, -1.99011e-06, 6.53026e-06, -1.18909e-05, 9.52337e-06, 0.00158065, 0.00166529, 0.0015657,  0.0017022, 0.000740644,0.00078635, 0.000730052,0.00219736, 0.00238191, 0.00212762, 0.00163783, 0.000750669,0.00230171, 0.00217917

That would be great to copy and paste sections with ctrl-v. Any hints?

Zeus

Answer 1

If you're on some kind of UNIX (Linux, etc), you can cheat and filter it through the column(1) command.

:%!column -t

The above will parse on delimiters inside string literals which is wrong, so you will likely need pre-processing steps and specifying the delimiter for this file for example:

%!sed 's/","/\&/' | column -t -s '&'

Profusive

For more control, I inserted tabs (could be an unused symbol) in the file to mark columns using substitution, and then ran :%!column -t -s "^I" to tabulate (C-v, tab inserts a literal tab character - ^I) – Landman

Because my files didn't have a space I had to use :%!column -t -s ','. It removes the commas, so they're not technicaly csv files anymore. But arranges them beautifully, which is what I needed. – Courtund

Great tip! Just adding, it works for visual selection too with :'<,'>!column -t – Encyst

The command column -t -s '&' deletes the separator character which is a problem when using the & in a LaTeX Table as separator. – Otto

also use the option ":set nowrap" for very wide files that go off the screen – Marquesan

Anyone know of a solution to work with quoted columns with commas in the column? example "2151 Main ST Houston, TX" – Urbain

@Thomas, you can do something like this to deal with latex tables... :s/&/;;&/g :!column -t -s ';;' – Hopping

@metrix, here is a very clumsy workaround: 1) convert spaces inside delimiters into a different character, 2) then run column as described in the post, 3) then replace your delimiters back to spaces. An example command in visual mode for step #1 is: '<,'>s/"\(\w\+\) \(\w\+\)"/"\1_\2"/g – Stockmon

A reminder for Windows users: column and vim are both available via git bash. – Fertilization

Awesome! (Stating the obvious) to minimize unintended alterations of your source file, work on a copy. – Piero

It's awesome, but is there a way to avoid removing delimiter? – Obellia

Answer 2

Sometimes we want to align just two columns. In that case, we don't need any plugins and can use pure Vim functionality like this:

1. Choose a separator. In OP's post this is a comma, in my example this is =.
2. Add spaces before/after it. I use s/=/= ...spaces... / in visual selection for this.
3. Locate to the longest word and place cursor after it.
4. Remove all the extra whitespace using dw and vertical movement.

Example of this technique demonstrated below:

I don't find myself needing to align things often enough to install another plugin, so this was my preferred way of accomplishing it - especially that it doesn't require much thinking.

Apheliotropic

How did you make that nice gif? – Zeus

blog.bahraniapps.com/gifcam unfortunately, looks like it's a Windows-only tool. – Apheliotropic

When you finished aligning the upper part, how did you move the cursor to the middle of screen and then start aligning the lower part? – Prehensible

I suppose with either 15↓ or 16G. – Apheliotropic

After :s/=/ =/ it is much better to use Ctrl + V to select the right column, then align it using << and . to repeat. Found it here: https://mcmap.net/q/160309/-align-text-on-an-equals-sign-in-vim – Admirable

Or creaete a macro to do de job @Admirable – Scoop

After one dw, it would be a lot faster (and less error-prone) to go with repeated j. or k.. – Metaplasm

Simplest then would be: '<,'>s/^\(\S*\)/\=printf("%-48s", submatch(1)) One could expand it to be: '<,'>s/^\(\S*\)\s*=\s*/\=printf("%-48s= ", submatch(1)) and it would align even if line is foo=bar. – To get format width one could do: max(map(getline("'<", "'>"), "match(v:val, '=')")) So as a one-liner: let w = max(map(getline("'<", "'>"), "match(v:val, '=')")) | '<,'>s/^\(\S*\)\s*=\s*/\=printf("%-".w."s= ", submatch(1)) – Homework

Answer 3

As sunny256 suggested, the column command is a great way of doing this on Unix/Linux machines, but if you want to do it in pure Vim (so that it can be used in Windows as well), the easiest way is to install the Align plugin and then do:

:%Align ,
:%s/\(\s\+\),\s/,\1/g

The first line aligns the entries on the commas and the second moves the comma so that it's flush with the preceding value. You may be able to use AlignCtrl to define a custom mapping that does the whole lot in one go, but I can never remember how to use it...

Edit

If you don't mind two spaces between entries and you want to do this in one command, you can also do:

:%Align ,\zs

Stacked

Works perfectly in a LaTeX Table: :'<,'>Align & – Otto

It can accomplished also with :%Align! lP0 \s (l = left aligned, P0 = 0 padding after the separator). – Undersize

Answer 4

This is a great answer using vim macros: https://mcmap.net/q/160310/-align-text-only-on-first-separator-in-vim - basically, you start recording a macro, format the first column, stop recording then repeat the macro for all remaining lines.

Copy/pasted from that answer:

qa0f:w100i <Esc>19|dwjq4@a

Note the single space after the 100i, and the <Esc> means "press escape"--don't type "<Esc>" literally.

Translation:

qa         -- record macro in hotkey a
0          -- go to beginning of line
f:         -- go to first : symbol
w          -- go to next non-space character after the symbol
100i <Esc> -- insert 100 spaces
19|        -- go to 19th column (value 19 figured out manually)
dw         -- delete spaces until : symbol
j          -- go to next line
q          -- stop recording macro
4@a        -- run the macro 4 times (for the remaining 4 lines)

Dumas

Answer 5

We now also have the fabulous EasyAlign plugin, written by junegunn.

Demonstration GIF from its README:

Apheliotropic

Answer 6

Also, Tabularize is quite good http://vimcasts.org/episodes/aligning-text-with-tabular-vim/

Greataunt

The Tabularize command (or short Tab) works well for csv files separated by , and markdown tables separated by pipes |, but to make it work for tsv files, you need to use :Tab / followed by CTRL + V then TAB, as explained #6952172 – Saporific

Mastapiece. Archive if server goes down: web.archive.org/web/20210309173605/http://vimcasts.org/episodes/… – Theme

Answer 7

You could use the csv.vim plugin.

:%ArrangeColumn

However, this will not do exactly what you have asked: it will right adjust the contents of cells, whereas you have your values aligned by the decimal point or by the first digit.

The plugin has many other useful commands for working with CSV files.

Rhodian

Answer 8

also if you have very long columns it can be handy to disable default wrapping

:set nowrap
:%!column -t

(note in debian you also have a further option for column -n which if you want to split multiple adjacent delimiters)

Marquesan

much better with nowrap. I included your suggestion, thanks 'command CSV set nowrap | %!column -t -s ','`. – Recreant

also, note the -n option to disable the merging of empty columns. https://mcmap.net/q/86263/-view-tabular-file-such-as-csv-from-command-line-closed – Recreant

Answer 9

I just wrote tablign for this purpose. Install with

pip3 install tablign --user

Then simply mark the table in vim and do

:'<,'>:!tablign

Coherence

Answer 10

Here’s a pure Vim script answer, no plugins, no macros:

It might be most clear to start out with my problem’s solution as an example. I selected the lines of code I wanted to affect, then used the following command (recall that entering command mode from visual mode automatically prepends the “'<,'>”, so it acts on the visual range):

:'<,'>g``normal / "value<0d>D70|P`

Except I did NOT actually type “<0d>”. You can enter unprintable characters on the command line by pressing ctrl-v, then the key you want to type. “<0d>” is what is rendered on the command line after I typed ‘ctrl-v enter’. Here, it’s parsed by the “normal” command as the exit from “/” search mode. The cursor then jumps to “ value” in the current line.

Then we simply [D]elete the rest of the line, jump to column 70 (or whatever you need in your case), and [P]ut what we just deleted. This does mean we have to determine the width of the widest line, up to our search. If you haven’t put that information in your statusline, you can see the column of the cursor by entering the normal mode command ‘g ctrl-g’. Also note that jumping to a column that doesn’t exist requires the setting 'virtualedit'!

I left the search term for the :g(lobal) command empty, since we used a visual block and wanted to affect every line, but you can leave off using a visual selection (and the “'<,'>”) and put a search term there instead. Or combine a visual selection and a search term to narrow things more finely/easily.

Here’s something I learned recently: if you mess up on a complex command mode command, undo with ‘u’ (if it affected the buffer), then press “q:” to enter a special command history buffer that acts much like a conventional buffer. Edit any line and press enter, and the changed command is entered as a new command. Indispensable if you don’t want to have to stress over formulating everything perfectly the first time.

Banting

Answer 11

Pretty old question, but I've recently availed myself of an excellent vim plugin that enables table formatting either on the fly or after-the-fact (as your use case requires):

https://github.com/dhruvasagar/vim-table-mode

Tiffany

Answer 12

I made a cli tool written in Perl.

You can find it here: https://github.com/bas080/colcise

Hiero

Answer 13

I have this in my .vimrc.

command! CSV set nowrap | %s/,/,|/g | %!column -n -t -s "|" 

This aligns the columns while keeping the comma, which may be needed later for correct reading. For example, with Python Pandas read_csv(..., skipinitialspace=True), thanks Pandas guys for this smart option, otherwise in vim %s/,\s\+/,/g. It may be easier if your column has the option --output-separator I guess, my doesn't and I'm not sure why (my man page for column says 2004, on ubuntu 18.04, not sure ubuntu will get a new version). Anyway, this works for me, and comment if you have any suggestions.

Recreant