TableFu – Version: 0.2.0

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

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

Table of Contents

• Installation
• Usage
• Macros / Formatting
• Faceting
• Manipulation
• Links
• Credits
• License

Installation

TableFu is available as a rubygem:

 gem install table_fu

 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

[["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

["Malone Muert", "Samuel Beckett"]

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

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

"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']}}
 

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

"Beckett"

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"

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

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

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

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

[["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

1177

Links

• TableSetter
  A Sinatra app that uses TableFu to render public Google Spreadsheets in custom HTML.
• TableSetter Generator
  A rails generator that allows you to create a rails app like TableSetter.
• Issues
  Post bug reports and feature requests here.
• API Docs

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.