Before getting into the details first the problem: Grab multiple optimized DNA sequences in a MS Excel workbook and format them as a FASTA text file for use with a webapp for rare codon analysis. Prior to seeking my help, users were manually copying the sequences (located in one cell across multiple sheets) into a MS Word document. This was fine for 2-5 sequences, but got seriously tedious and error prone for anything >10.
Lastly, this is all done in Windows (for added craziness).
Round 1: Matlab (aka the 500lb gorilla)Out of the (very expensive) box it has MS Excel read/write capabilities via the functions
Adding the (also expensive) Bioinformatics Toolbox gives FASTA file io via
Using the Matlab Compiler (again, if you've paid for it) this distills nicely into a tidy command line executable.
The problems began (as usual) immediately after deployment.
First, in order to run any compiled Matlab code, users need to install the weighty (~400MB) Matlab Component Runtime (MCR), which in corporate IT-lockdown land is it's own form of enjoyment.
Second, and a horrendous PITA if you ask me, the version of the MCR users need depends on the version of Matlab the code was compiled in. Worse, there is no backward compatibility. In this case, users needed version 7.16 of the runtime to correspond with my Matlab 2011b. However, the program that generated the sequences in the first place (also a Matlab compile job) was made with Matlab 2009a.
It was late in the afternoon, the IT guys were gone, and I didn't want to have to deal with any craziness of conflicting runtimes.
Sorry Matlab, you suck.
Round 2: Python (parsel tongue any one?)There's a lot of hubub about how NumPy/SciPy + matplotlib in distributions like Python(X,Y) can totally replace Matlab - FOR FREE! I've yet to really delve into that module stack. For the task at hand, bare python has all that's needed with a few modules (again ALL FREE)
MS Excel spelunking:
FASTA file acrobatics (and much much more):
As an important note, the python code completes almost instantaneously whereas the Matlab code took at least 5 seconds (about 1 sec per worksheet in the source .xlsx file). I don't know why Matlab takes so long to get/put data from/into an Excel workbook, but I sure hope the Mathworks engineers are working on it. Sure, this is slightly unfair since python is byte-compiled when run, but on a modern PC with 1GHz of multi-core processing power and 3GB of RAM, I expect performance, darn-it.
As a small slight to the Python camp, the xlrd/xlwt modules are only compatible with the older .xls (Microsoft Excel 97-2000) format files and not the newer XML based .xlsx files. So it does require one extra step ... par for the course.
Compiling to an console executable is made easy with Py2Exe.
Deploying is a snap - zip and email everything in the ./dist folder of where you Py2Exe'd your source code.
Of course, getting users that were originally in happy point and click land to work at the C: prompt kinda stopped this awesome train of free and open source progress dead in its tracks.
Round 3: R (statistics will help you find that buried treasure, er p-value)R directly works with MS Excel files? Yup:
FASTA files, Bioinformatics and statistics go hand-in-hand, this is pretty much a given:
As far as speed goes, the R code bested Matlab and was on par with Python. Pretty interesting and consistent with the benchmarks posted by the Julia team.
A small word of warning, the xlsx package uses the Apache POI java library in the background and does run into significant memory cloggery (at least on my workstation) when working with sheets heavily laden with rows and/or columns.
Compiling, well, I'm not completely sure it exists yet (although RCC looks interesting). Of course, who needs to compile if you can just drop this on your webserver behind rApache and a simple webform. There, user command-line aversion solved.