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.