Preventing Excel from wrongly interpreting Strings in CSV files as Exponential numbers

MS Excel and Apple Numbers both attempt to guess the format of the contents of each cell when they open up a CSV file. Most of the time this is fine but in some cases it causes a big problem.

If you have a string with numbers and the letter E then both programs will treat this as a number in exponential notation.

For example, the string 229E10 will be converted to 2.29E+12

What makes it worse is that you cannot change it back to the original text using the cell format options.

I run into this problem with strings that represent microtiter plate and well assignments. For example, 229E10 refers to plate 229 row E column 10. 

I just want the spreadsheet program to take what I give it as text. Putting the string in single or double quotes can work but then the value remains in quotes in the spreadsheet.

The best way to do this seems to be to use double quotes around the string AND prefix that with an equals sign - like this:

="229E10"

For reasons that I do not understand, Excel will strip the extra formatting off the string and treat the contents as plain text. This also works for arbitrary strings, such as ="ABC"

Installing OpenCV Ruby gem on mac OS X

In my previous post I showed how to Install the OpenCV computer vision library on Mac OS X Lion.

I want to use the Ruby interface to the library and of the various project forks I went with https://github.com/ruby-opencv/ruby-opencv

The github page has several alternate ways to install the interface, but installing it as a gem is the most mainstream. Even so, you need to do a bit more work than usual. First create an install directory for the 'raw' gem and clone it from github:

$ mkdir opencv_install
$ cd opencv_install/
$ git clone git://github.com/ruby-opencv/ruby-opencv.git
$ cd ruby-opencv/
$ git checkout master
$ bundle install
$ rake gem
$ gem install pkg/opencv-*.gem -- --with-opencv-dir=/usr/local/Cellar/opencv/2.4.2

Your opencv directory may not be identical to mine but this is where homebrew should place it. For me, that was all I needed to do before copying the examples from the github page and trying things out ! Here is my version of the basic image display program:

#!/usr/bin/env ruby
require "opencv"
abort "Usage: #{$0} " unless ARGV.length == 1
image = OpenCV::IplImage.load(ARGV[0])
window = OpenCV::GUI::Window.new("preview")
window.show(image)
OpenCV::GUI::wait_key

When you run the script with an image file, you can quit by typing 'q' in the resulting image window. Now for the fun stuff. Here is my version of the github page face detection script. Face detection uses 'haarcascade' classifier files which live in your OpenCV library directory. With my Homebrew installation these are found in /usr/local/Cellar/opencv/2.4.2/share/OpenCV/haarcascades

Get yourself an image file with a few faces (looking directly at the camera) to test it on.

#!/usr/bin/env ruby
require "opencv"
abort "Usage: #{$0} source_image_file  dest_image_file" unless ARGV.length == 2
classifiers_dir = "/usr/local/Cellar/opencv/2.4.2/share/OpenCV/haarcascades"
data = File.join(classifiers_dir, "haarcascade_frontalface_alt.xml")
detector = OpenCV::CvHaarClassifierCascade::load(data)
image = OpenCV::IplImage.load(ARGV[0])
detector.detect_objects(image).each do |region|
  color = OpenCV::CvColor::Red
  image.rectangle! region.top_left, region.bottom_right, :color => color
end
image.save_image(ARGV[1])

Make the file executable, run the script and then view the output file

$ chmod a+x findface.rb
$ ./findface.rb smiths.jpg output.jpg
$ open output.jpg

And you should something along the lines of this:

Pretty amazing, if you ask me - Not perfect but three out of four ain't bad !
Now on to some real experiments with OpenCV - I can't wait...
 

Installing the OpenCV library on Mac OS X Lion using Homebrew

OpenCV (Open Source Computer Vision) is a remarkable library of functions for real time computer vision.

I'm interested in using it on to recognize buildings in satellite/aerial photographs - I'll explain more if I get the idea to work.

The first step is to get OpenCV up and running on my Mac (OS X Lion 10.7.4). The library has a lot of dependencies that need to be installed but thankfully there is a Homebrew recipe for doing all of the heavy lifting... almost... I ran into a few problems but here is what I needed to do.

In principle all you need is:

$ brew update
$ brew install opencv

But I got two errors:
1: brew wanted me to update my version of Xcode from 4.2 to 4.3
On Lion you update Xcode via the App store and it turns out that 4.4 is available. So I installed that and reran 'brew install opencv'
2: brew then complained that the Python package 'numby' (Numerical Python) was not installed. Brew does not install Python dependencies but suggested that I do this with:

$ easy_install numby

I did that but I still got the error.

I've got two versions of python installed (via homebrew)

$ python --version
Python 2.7.3
$ python3 --version
Python 3.2.3

For our purposes you only want to deal with Python 2 - version 3 should work but the homebrew package seems to want version 2

Given the problem with easy_install, I went with a manual installation
1: Download the tar file for the latest stable version of numby
http://sourceforge.net/projects/numpy/files/NumPy/1.6.2/
2: Unpack the file into a temporary directory and cd into it
3: Run the python setup script

$ python setup.py install

Sit back and relax as this takes a while. Everything went smoothly for me and hopefully for you too.


All being well you can now run

$ brew install opencv

Again this takes a while - about 9 minutes on my box. You will get a message at the end of the output telling you to add the following to your .bash_profile

export PYTHONPATH="/usr/local/lib/python2.7/site-packages:$PYTHONPATH"

OpenCV has an excellent interface to Python, but I work primarily in Ruby so the next step for me is to get an OpenCV ruby gem set up and start to do some image processing. The next post on this blog will cover that.

Variable substitution in Ruby sub/gsub replacement strings

With the sub and gsub methods in Ruby you can extract components of the matching string and use them in the replacement string.

For example, with input string 'Hello World' this replacement:

mystring.sub(/(Wo)rld/, '\1bble') -> Hello Wobble

Note the single quotes - if you use double quotes this happens:

mystring.sub(/(Wo)rld/, "\1bble") -> Hello \u0001bble

You can use double quotes if you double escape the 'sequence' \1

mystring.sub(/(Wo)rld/, "\\1bble") -> Hello Wobble

This is very important if you want to include a ruby variable in the replacement string

s = "Mr."
mystring.sub(/(Wo)rld/, "#{s} \\1bble") -> Hello Mr. Wobble

Just remember 'double quotes' means 'double escapes'....

Simple Example of How to Show / Hide Divs using JQuery

Here is a simple example of how to use JQuery to show or hide divs by clicking a link within a HTML page. You could make this even more minimal but I think this version gives you the simplest explanation.

I use this to embed comments in pages that I want to show to a client. When they are visible, the client gets to see my rationale for designing the page a certain way. But by clicking a 'Hide Comments' link, the client can see the final page design.

Clicking on the 'Hide Comments' link makes a 'Show Comments' link appear, and vice versa.

You can see the test page in its entirety at this github gist: https://gist.github.com/fa1782bd47872f4c98d6

Within the body of the page, each comment is entered in a div (or paragraph) block with the class 'comment'

At the top of the page are two links with '#' as the target and with the div ids of 'show-comments' and 'hide-comments' respectively.

In the Head of the page you need to include the JQuery library and then you create your custom script. This has three components:

The first defines the action taken when the Hide Comments link is clicked. All divs with class 'comment' and the Hide Comments link are hidden and the Show Comments link is shown.

  jQuery('#hide-comments').click(
    function() {
      jQuery('.comment, #hide-comments').hide();
      jQuery('#show-comments').show();
    }
  );

The second component implements the reverse action when the Show Comments link is clicked. Now, all divs with class 'comment' are displayed along with the Hide Comments link. The Show Comments link is hidden.

  jQuery('#show-comments').click(
    function() {
      jQuery('.comment, #hide-comments').show();
      jQuery('#show-comments').hide();
    }
  );

Finally the Show Comments link is hidden when the page is loaded.


You could do this with a single show/hide comments link. You would update the Javascript to use the 'toggle()' function instead of show()/hide() and you would update the text of the link as appropriate.

Setting up Apache Solr on Mac OS X

Apache Solr is an incredibly flexible and capable text search engine that you can hook into other applications.

For a beginner it can appear to be very daunting due to the number of configuration options and when you start to read the documentation this feeling tends not to improve. But if you are using Mac OS X (a recent version) and have the homebrew package manager installed then the process is not too bad, at least to get you up and running.

These steps will help you get Solr installed and then load in data extracted from HTML, PDF and Word files.

In my configuration I have Mac OS X Lion and current versions of Java and homebrew.

1: Install Solr with homebrew - (brew update is always a good idea before a complex install)
$ brew update
$ brew install solr

This installs the software in /usr/local/Cellar/solr/3.6.0 (or whatever your version is)

2: Go to the example directory and start up Solr
$ cd /usr/local/Cellar/solr/3.6.0/libexec/example
$ java -jar start.jar
This will start up the Solr server using Jetty as the servlet container, which is just fine for our testing. Do not worry about using Tomcat etc until you are comfortable with a working Solr set up.
It is usually not good form to work inside a distribution directory - but for initial testing you should do this.
You will see a load of verbose output from Java in your terminal window, aside from any real errors, just ignore this.

3: Verify that the server is running
Browse to http://localhost:8982/solr/admin
You should the Administration interface with a gray background. There is not a lot you can do as you have not yet indexed any data, but this shows that you are running.

4: Load some data into Solr
$ cd exampledocs
$ java -jar post.jar *.xml
Note that the Solr server MUST be running before you try and load the documents.

5: Run your first query from the admin page
Enter 'video' in the Query form and hit 'Search'. You should see the contents of an XML file returned to you with 3 documents.
This demonstrates that the server is working, that you can index XML documents and query them. Solr does not provide you with a nice search interface. The intent is that your Rails, etc application sends queries and then parses out the XML results for display back to the user.

6: Extract text from other document types
What I want to use Solr for is to search text extracted from web pages, Word documents, etc. But this where the Solr documents started to really let me down.
This parsing is done by calling Apache Tika which is a complex software package with the sole aim of text extraction. The interface between Solr and Tika used to be called SolrCell and is now called the ExtractingRequestHandler. Don't worry about any of that for now! The Solr distribution has everything you need already in place - you just need to know how to use it...


Do not set up a custom Solr home directory for now. That is what the documentation suggests, it makes perfect sense but nothing will work if you do.


Save a few HTML files that contain a good amount of text into a temporary directory somewhere.

To load the contents of a file into the server and have it parse the text you need to use 'curl' to POST the data to a specific URL on the server. In this example my file is called index.html and I have cd'ed to the directory containing the file
$ curl "http://localhost:8983/solr/update/extract?literal.id=doc1&commit=true&uprefix=attr_&fmap.content=attr_content" -F "myfile=@index.html"

There are several things to note with this URL...
The server URI is http://localhost:8983/solr/update/extract
The parameters are
literal.id=doc1
commit=true
uprefix=attr_
fmap.content=attr_content

Literal.id provides a unique identifier for this this document in the index (doc1 in this case)
uprefix=attr_   adds the prefix 'attr_' to the name of each tag in the source document
fmap.content=attr_content  specifies that the main text content of the page should be given the tag attr_content
commit=true actually commits the parsed data to the index

And then note how the file to be parsed is specified... as a quoted string passed with the -F flag to curl. The quoted string consists of a name for the uploaded file (myfile) followed by the path to the file preceded by the Ampersand character (@).

You can ignore what these mean for now with the exception that each document you load must have a unique document ID.

7: Verify that the text has been indexed
Go to the Admin interface and click 'Full Interface'. Set the number of rows returned to large number (50) and then search with the query *:*   - this will return all the records in the index.

You should get back an XML page with the example data plus the text derived from your HTML file at the bottom.

8: Try loading other data types
Tika knows how to parse Word, Excel, PDF and other files.
Be aware that there may not be much text in certain files. This is especially true of PDF files which although they appear to have text when displayed, may have that text stored as an image.

9: Explore the example directory

Everything I've shown here should 'just work' as long as you worked in the example directory. In there you will see a 'solr' subdirectory which contains conf and data directories. The data directory is where the index files reside. In the conf directory you will see a solrconfig.xml file. This is the main location for specifying the various components that your Solr installation uses. The default values happen to work fine until you create your own 'Solr Home' directory elsewhere.

The problem is that the solrconfig.xml file contains various relative paths (like ../../lib) which will not work if you create a solr directory in an arbitrary location. Knowing this you can update those paths pretty easily, but if you created a custom solr home then you are in for a lot of frustration trying to figure out why nothing works. I went through that process - you shouldn't have to...

That should be enough to getting you started with Solr. The next steps are to link it to your web application, to load in a lot more data and to move it to a production servlet container like Tomcat.

Installing Apache tomcat on Mac OS X Lion using homebrew

It is hard to overestimate how much I am grateful for the homebrew Mac OS X package manager.

Today I needed to setup Apache Tomcat on a OS X Lion machine. I already have homebrew installed and have used it to install a bunch of stuff.

All that is needed is:
$ brew install tomcat

I'll admit that this failed the first time that I tried it but running 'brew update' and then 'brew install tomcat' sorted that out - I guess if you've not used it for a while you should run 'brew update' as it is still evolving quite rapildly.

That installs the code into /usr/local/Cellar/tomcat/7.0.28

To start/stop the server from the command line you use the catalina shell script
$ /usr/local/Cellar/tomcat/7.0.28/bin/catalina run
[...]
$ /usr/local/Cellar/tomcat/7.0.28/bin/catalina stop


When it is running you can go to http://localhost:8080 and see a default tomcat information page with lots more info.


For me that default port is a problem as it is also the default for my nginx installation. But changing it is easy enough. Edit /usr/local/Cellar/tomcat/7.0.28/libexec/conf/server.xml and replace the instances of 808 with your preferred port. Rerun catalina and then go to your preferred URL.


For my purposes I want to start and stop the server manually but check the documents for how to link it to an instance of Apache.