Swivel Code Blog

is it tsv or csv?

When you upload a file to Swivel, one of the things we need to figure out is whether the file is a TSV or CSV file.  We had implemented this in various ways in the past to varying degrees of success.  What we really lacked was a good corpus to inform our decision.  And we like to use data to inform our decisions here of course.  So, I went and grabbed a decent chunk of self-reported tsvs (3,000 or so) and some csvs (100 or so) and tried two different algorithms, both very simple and very naive:

def csv? f
  File.open f do |x| x.gets[/.+,/] end
end

def tsv? f
  File.open f do |x| x.gets[/.+\t/] end
end

and then I ran it against my corpus to find:

                         csv?                            tsv?
 tp | fp      89 ( 78.8%) |  252 (  7.4%)   3303 ( 97.2%) |    2 (  1.8%)
 ---+---    --------------+-------------    --------------+-------------
 tn | fn    3147 ( 92.6%) |   24 ( 21.2%)    111 ( 98.2%) |   96 (  2.8%)

tp = true positive, tn = true negative, fp = false positive, fn = false negative.

What this boils down to is a check for a comma on the first line of the file is right in the positive case ~78% of the time and right at identifying non-csv files ~92% of the time, but mistakenly calling a csv file not-a-csv-file ~21% of the time.  On the other hand, checking for a tab character on the first line is much more consistent at identifying TSV vs. CSV (only wrong ~1-3% of the time).

And, just to check, but i tried checking for a tab on either the first or second lines of the file:

def tsv2? f
  File.open f do |x|
    s1, s2 = x.gets, x.gets
    s1[/.+\t/] && (!s2 || s2[/.+\t/])
  end
end

this yielded:

             tsv2?

3265 ( 96.1%) |    1 (  0.9%)

--------------+-------------

 112 ( 99.1%) |  134 (  3.9%)

which has the effect you'd expect: it increased the false negative rate by allowing more files in (even ones that really weren't TSVs).

Conclusion: if you need to determine if a file is a TSV or a CSV, checking for a tab character on the first line of the file is a very good measure.

Numerical Sorting in Javascript with Prototype

Much of programming involves managing arrays (lists) in some way, and there's broad consensus about the basic tools for creating and manipulating them. Arrays can be joined, pushed, popped, spliced, and reversed using much the same syntax in most major programming languages, with a few exceptions like INTERCAL and Python that use more eccentric terminology to do the same things.

For those of us used to Ruby and the syntactic sugar rush provided by methods like Enumerable.collect, .inject, .reject, and .select switching gears to get to grips with Javascript can induce the sorts of headaches caused by brutally mixed metaphors. Given Javascript's dynamic nature and its symbiosis with the DOM, we might expect more from the Javascript Array object than we get.

For example, sorting an array is one of those basic operations we expect to be available.

We even expect the language to do a little bit of magic, as Ruby does, determining what data type(s) the array's elements are and sorting accordingly when the array consists of all numbers or strings:

[3,1,21,2].sort
	=> [1,2,3,21]

["badger","antelope","bison","wallaby"].sort
	=> ["antelope", "badger", "bison", "wallaby"]

Ruby, of course, gives us its customary sort and sort! depending whether we want to change the array.

Javascript goes to great lengths to make sorting not work as you might expect. First, Array.sort() both changes the array, and returns the sorted version -- if you need to preserve the original order of your array, you'll need to copy it before sorting. Then, even though Javascript has no difficulty in determining the data types in arrays like the examples here, it insists on sorting them alphabetically.

var a = [3,1,21,2];
alert(a.sort());
	=> [1,2,21,3]
alert(a);
	=> [1,2,21,3]

This is even though it knows full well that these are numeric values:

alert(typeof(a[0]));
	=> number

And to think some people say unkind things about Javascript.

Even with the Prototype library's indispensible Enumerable mixin, Javascript is still playing catch-up, but at least we can recreate the functionality we want.

Prototype's Enumerable.sortBy() method takes an iterator -- an anonymous function applied to each element in the Enumerable -- and sorts the array by the iterated elements. It's a useful function for playing all kinds of tricks. Sort by length-of-word, for example:

alert( ["badger","antelope","bison","wallaby"].sortBy(function(element) {return(element.length);}) );
	=> ["bison","badger","wallaby","antelope"]

This method does not change the original array, as Javascript's native .sort() does. Another valuable feature is that it *does* automatically sort according to the elements' data type, so it is not necessary for the iterator to transform numeric values via parseInt or parseFloat (even though it might feel that it should be).

Now we can implement a numerical sort easily, if not too prettily:

alert([3,1,21,2].sortBy(function(element) { return(element);}) );
	=> [1,2,3,21]

It seems a lot of palaver for what we're used to getting for free, but there it is.