TableFu – Version: 0.1.1

TableFu is a ruby gem for spreadsheet-style handling of arrays (e.g. filtering, formatting, and sorting by "column" or "row"). In addition, it has the ability to facet — or group — rows according to cell value. It was developed as a backend for its companion project TableSetter.

For example, TableFu can consume a csv file and sort on a column:

csv =<<-CSV
Author,Best Book,Number of Pages,Style
Samuel Beckett,Malone Muert,120,Modernism
James Joyce,Ulysses,644,Modernism
Nicholson Baker,Mezannine,150,Minimalism
Vladimir Sorokin,The Queue,263,Satire
CSV

spreadsheet = TableFu.new(csv) do |s|
  s.sorted_by = {'Best Book' => {'order' => 'ascending'}}
end

Returns:

[["Samuel Beckett", "Malone Muert", "120", "Modernism"], ["Nicholson Baker", "Mezannine", "150", "Minimalism"], ["Vladimir Sorokin", "The Queue", "263", "Satire"], ["James Joyce", "Ulysses", "644", "Modernism"]]

Installation
Usage
Macros / Formatting
Faceting
Manipulation
Links
Credits
License

Installation

TableFu is available as a rubygem:

 gem install table_fu

or from the actual source:

 git clone git://github.com/propublica/table-fu.git
 cd table-fu
 rake install

Usage

The TableFu constructor takes two arguments; a 2 dimensional array or csv (file object or string) and a hash of column options or a block. TableFu will assume that the first row of the array contains the column headers. The simple options are:

sorted_by: the column to sort by, it defaults to no sorting at all.

csv =<<-CSV
Author,Best Book,Number of Pages,Style
Samuel Beckett,Malone Muert,120,Modernism
James Joyce,Ulysses,644,Modernism
Nicholson Baker,Mezannine,150,Minimalism
Vladimir Sorokin,The Queue,263,Satire
CSV

spreadsheet = TableFu.new(csv) do |s|
  s.sorted_by = {'Best Book' => {'order' => 'ascending'}}
end

Returns:

[["Samuel Beckett", "Malone Muert", "120", "Modernism"], ["Nicholson Baker", "Mezannine", "150", "Minimalism"], ["Vladimir Sorokin", "The Queue", "263", "Satire"], ["James Joyce", "Ulysses", "644", "Modernism"]]

columns: the columns to include in the table, useful when reordering and filtering extraneous columns. If no arguments are provided, TableFu will include all columns by default.

spreadsheet = TableFu.new(csv) do |s|
  s.columns = ["Best Book", "Author"]
end

spreadsheet.columns.map do |column|
  spreadsheet.rows[0].column_for(column).to_s
end

Returns:

["Malone Muert", "Samuel Beckett"]

Note that the columns are still accessible directly even if they're not in the columns array.

spreadsheet = TableFu.new(csv) do |s|
  s.columns = ["Best Book", "Author"]
end

spreadsheet.rows[0].column_for('Style').to_s

Returns:

"Modernism"

Macros / Formatting

TableFu allows you to format columns of cells through the use of macros. See TableFu::Formatting for the predefined macros available.

The formatting attribute should be a hash of the form:

{"Column Name" => 'Formatting Method Name'}

# or

{"Meta Column Name" => {"method" => "Method Name", "arguments" => ['Column 1', 'Column 2' ... 'Column N']}}

which will call the macro on the column name with the arguments if specified.

For example, you can use the last_name formatter to extract the last name of a cell containing a person's name:

spreadsheet = TableFu.new(csv) do |s|
  s.formatting = {"Author" => 'last_name'}
end
spreadsheet.rows[0].column_for('Author').to_s

Returns:

"Beckett"

Or you can build a meta column from two others containing an html link like so:

csv = <<-EOF
Website,URL
Propublica,http://www.propublica.org/
EOF

spreadsheet = TableFu.new(csv) do |s|
  s.formatting = {"Link" => {'method' => 'link', 'arguments' => ['Website','URL']}}
  s.columns = ["Link"]
end

spreadsheet.rows[0].column_for('Link').to_s

Returns:

"<a href='http://www.propublica.org/' title='Propublica'>Propublica</a>"

Of course, you can provide your own macros by patching TableFu::Formatting. TableSetter includes rails view helpers directly in TableFu::Formatting.

class TableFu::Formatting
  extend ActionView::Helpers::NumberHelper
end

Faceting

Faceting provides a way to group rows together using a cell value they share in common. Calling TableFu#faceted_by returns an array of table fu instances each with a faceted_on attribute and with only the rows where that value appears.

In this example there are 2 rows where "Modernism" appears in the style column, so calling faceted_on with the argument "Style" returns a TableFu instance with those rows grouped together:

spreadsheet = TableFu.new(csv)
spreadsheet.faceted_by "Style"

Returns:

table.faceted_on => Minimalism, table.rows => [["Nicholson Baker", "Mezannine", "150", "Minimalism"]]
table.faceted_on => Modernism, table.rows => [["Samuel Beckett", "Malone Muert", "120", "Modernism"], ["James Joyce", "Ulysses", "644", "Modernism"]]
table.faceted_on => Satire, table.rows => [["Vladimir Sorokin", "The Queue", "263", "Satire"]]

Manipulation / Output

Deleting Rows

In addition to hiding columns and faceting TableFu can delete rows from the csv. Let's get rid of James Joyce (no one ever finished Ulysses anyway):

spreadsheet = TableFu.new(csv) do |s|
  s.delete_rows! [1]
end

Returns:

[["Samuel Beckett", "Malone Muert", "120", "Modernism"], ["Nicholson Baker", "Mezannine", "150", "Minimalism"], ["Vladimir Sorokin", "The Queue", "263", "Satire"]]

The deleted rows are still available in @deleted_rows for later access:

spreadsheet = TableFu.new(csv) do |s|
  s.delete_rows! [1]
end

Returns:

table.deleted_rows => [["James Joyce", "Ulysses", "644", "Modernism"]]

Pagination

If you want only a chunk of the data, say to paginate your table, you can call only! with the range of values you want to keep:

spreadsheet = TableFu.new(csv) do |s|
  s.sorted_by = {'Style' => {"order" => 'ascending'}}
end

spreadsheet.only!(2..4)
spreadsheet.rows

Returns:

[["Nicholson Baker", "Mezannine", "150", "Minimalism"], ["Vladimir Sorokin", "The Queue", "263", "Satire"]]

Sum

TableFu can also sum a column of values:

spreadsheet = TableFu.new(csv)
spreadsheet.total_for('Number of Pages').to_s

Returns:

Credits

Jeff Larson (Maintainer), Brian Boyer, Scott Klein, Mark Percival, and Charles Brian Quinn.

License

Copyright (c) 2010 ProPublica

Permission is hereby granted, free of charge, to any person obtaining
a copy of this software and associated documentation files (the
"Software"), to deal in the Software without restriction, including
without limitation the rights to use, copy, modify, merge, publish,
distribute, sublicense, and/or sell copies of the Software, and to
permit persons to whom the Software is furnished to do so, subject to
the following conditions:

The above copyright notice and this permission notice shall be
included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.