Column to list
June 23, 2012 at 12:07 AM by Dr. Drang
In last night’s post, I mentioned that often need to get a column of values out of a spreadsheet and turn them into a Python list. I’ve been doing that through a combination of copy-and-paste and regex find-and-replace in TextMate. Today I made a bundle with a couple of commands that do all the work in a single step.
The commands were inspired by a comment on that post by Barron Bichon, who said that he pulls in spreadsheet data by saving the spreadsheet as a text file and then reading the values through NumPy’s loadtxt command. For many scripts, that’s the best way to import the data, but since many of my little programs are one-offs, I find that building the information directly into the script often works better than the traditional separation of data and programming.1
What I want for those cases are one-step commands that take the column of values I just copied out of a spreadsheet and paste them into my program as a Python list.
Here’s the first TextMate command, Column to List Bare:
The command itself is a pipeline with a Perl one-liner:
pbpaste | perl -ne 'BEGIN{$s="[";} chomp; $s.="$_, "; END{$s=~s/, $/]/; print $s}'
It’s intended to be used with columns of numbers.
The second TextMate command is Column to List Quoted:
Its command is also a pipeline with a slightly more complicated Perl one-liner:
pbpaste | perl -ne 'BEGIN{$s="[";} chomp; s/"/\\"/g; $s.=qq("$_", ); END{$s=~s/, $/]/; print $s}'
Because it quotes all the items (escaping quotation marks within the items as needed), it’s intended to be used with columns of strings.
Both commands are bound to ⌃⌥⌘C, so I don’t have to remember which is which. When I type the key combo, a popup menu appears to let me choose which command to run,
If I copy this column of numbers from a spreadsheet,
and invoke the Column to List Bare command in TextMate, it inserts this,
[477, 305, 584, 600, 633, 639, 333, 490, 303, 402, 315, 505, 493, 559, 387, 790, 423, 457, 716, 706]
with the items separated by commas and the whole thing enclosed in square brackets.
I suppose I could extend the commands to pipe the result through, say
fmt -w 70
This would break up the list into lines of reasonable length rather having it all in one long line. I’ll see how it works in practice for a while and make the change if it seems warranted.
I started out trying sed to do the conversion because I wanted to be real Unixy, but quickly changed to Perl. Sed blows. I generally don’t mind cryptic syntax, but I just can’t get my head around sed’s. Perl may be slightly more verbose in a one-liner, but at least I have a decent sense ahead of time of what it’s going to do.
If you’re wondering why I didn’t use Python itself for the commands, the answer is simply that Perl is better at one-liners. I saw no point in being a language purist and making my life harder.
-
If you ever see me using the phrase “business logic” in anything other than derisive terms, you have my permission to shoot me. ↩
