Asp Forum - Streaming Spreadsheet in Ruby

ben

11/21/2006 10:30:00 AM

(This posting can be ignored unless you live-or-die by a *nix command
line.)

http://rubyforge.org/pro...

On the other hand, have you ever wanted to do some quick math on a CSV
without waiting to launch Excel or Gnumeric? (Or you're logged in
remotely.) Maybe you'd like to print the average timestamp across all
lines in a log file? Or you might wish "cut" split columns with a
regexp, not just a delimiter?

I just GPL'ed a project of mine for doing spreadsheet style
calculations on the command line. It's probably easiest to explain with
an example. Say you have a sample file called "data.csv", which looks
like this:

Year,Change,TOTAL
2001,34.5,100.1
2002,36.6,101.13
2003,-11,90.5
2004,0,95

And then you call the Streaming Spreadsheet like so:

$ cat data.csv | sss 'b=sum(b)' 'c=sd(c)' 'c1="full total"'

You'll get this on standard out:

Year Change full total
2001 34.5 100.1
2002 36.6 101.13
2003 -11 90.5
2004 0 95
60.1
4.91642400531117

Note that "cell" C1 has been changed, and those last two lines added
with the sum of the B column, and the standard deviation of the C.
Since it's really just a tarted up "eval," more complicated stuff also
works:

$ cat data.csv |sss 'd=(123**2.3).to_i'

On standard out:

Year Change TOTAL
2001 34.5 100.1
2002 36.6 101.13
2003 -11 90.5
2004 0 95
64088

The script is a moderately-clever 350 lines of Ruby (IMHO). For
instance, it takes advantage of a quirk in parsing this sort of thing:

eval("b32:c35")

This string of code ends up trying to call a method named "b32" with
one argument, the symbol ":c35". Perfect for returning a range of cells
via "def method_missing()"!

I hope it's useful for someone else right now, but the project is very
much still in beta.

http://rubyforge.org/pro...

8 Answers

David Kastrup

11/21/2006 11:15:00 AM

ben@somethingmodern.com writes:

> On the other hand, have you ever wanted to do some quick math on a CSV
> without waiting to launch Excel or Gnumeric? (Or you're logged in
> remotely.) Maybe you'd like to print the average timestamp across all
> lines in a log file? Or you might wish "cut" split columns with a
> regexp, not just a delimiter?
>
> I just GPL'ed a project of mine for doing spreadsheet style
> calculations on the command line. It's probably easiest to explain with
> an example. Say you have a sample file called "data.csv", which looks
> like this:
>
> Year,Change,TOTAL
> 2001,34.5,100.1
> 2002,36.6,101.13
> 2003,-11,90.5
> 2004,0,95
>
> And then you call the Streaming Spreadsheet like so:
>
> $ cat data.csv | sss 'b=sum(b)' 'c=sd(c)' 'c1="full total"'
>
> You'll get this on standard out:
>
> Year Change full total
> 2001 34.5 100.1
> 2002 36.6 101.13
> 2003 -11 90.5
> 2004 0 95
> 60.1
> 4.91642400531117

Looks like a case for awk to me...

--
David Kastrup, Kriemhildstr. 15, 44793 Bochum

ben

11/21/2006 12:15:00 PM

Do you really want to learn Awk's esoterica, in 2006? It doesn't do
aggregation well (SUM, MEAN), and "we know Ruby already."

David Kastrup wrote:
> ben@somethingmodern.com writes:
>
> > On the other hand, have you ever wanted to do some quick math on a CSV
> > without waiting to launch Excel or Gnumeric? (Or you're logged in
> > remotely.) Maybe you'd like to print the average timestamp across all
> > lines in a log file? Or you might wish "cut" split columns with a
> > regexp, not just a delimiter?
> >
> > I just GPL'ed a project of mine for doing spreadsheet style
> > calculations on the command line. It's probably easiest to explain with
> > an example. Say you have a sample file called "data.csv", which looks
> > like this:
> >
> > Year,Change,TOTAL
> > 2001,34.5,100.1
> > 2002,36.6,101.13
> > 2003,-11,90.5
> > 2004,0,95
> >
> > And then you call the Streaming Spreadsheet like so:
> >
> > $ cat data.csv | sss 'b=sum(b)' 'c=sd(c)' 'c1="full total"'
> >
> > You'll get this on standard out:
> >
> > Year Change full total
> > 2001 34.5 100.1
> > 2002 36.6 101.13
> > 2003 -11 90.5
> > 2004 0 95
> > 60.1
> > 4.91642400531117
>
> Looks like a case for awk to me...
>
> --
> David Kastrup, Kriemhildstr. 15, 44793 Bochum

Ara.T.Howard

11/22/2006 10:58:00 PM

Joel VanderWerf

11/23/2006 2:43:00 AM

ben@somethingmodern.com wrote:
> (This posting can be ignored unless you live-or-die by a *nix command
> line.)

:)

> http://rubyforge.org/pro...

Neat. One of my first ruby programs was a port of a perl program of mine
that was sort of like this, but without builtin aggregation. The
eval("b32:c35") part is clever!

--
vjoel : Joel VanderWerf : path berkeley edu : 510 665 3407

Gregory Brown

11/24/2006 12:07:00 AM

On 11/22/06, ben@somethingmodern.com <ben@somethingmodern.com> wrote:
> (This posting can be ignored unless you live-or-die by a *nix command
> line.)

Ben, this is neat. You might get a lot of implementation details for
free by building this atop Ruport.

But the simplicity is pretty awesome. Cool stuff!

Gregory Seidman

11/24/2006 2:27:00 AM

On Fri, Nov 24, 2006 at 04:05:38AM +0900, ben@somethingmodern.com wrote:
} Do you really want to learn Awk's esoterica, in 2006? It doesn't do
} aggregation well (SUM, MEAN), and "we know Ruby already."
}
} David Kastrup wrote:
[...]
} > Looks like a case for awk to me...
} >
} > --
} > David Kastrup, Kriemhildstr. 15, 44793 Bochum

I've known awk for much longer than Ruby had existed. It is excellent for
line-based, field-based data manipulation. It feels a lot like C, with a
few other niceties (like associative arrays).

The point here is that you use the right tool for the job. For this job,
awk is decidedly the right job.

--Greg

M. Edward (Ed) Borasky

11/24/2006 2:51:00 AM

Gregory Seidman wrote:
> On Fri, Nov 24, 2006 at 04:05:38AM +0900, ben@somethingmodern.com wrote:
> } Do you really want to learn Awk's esoterica, in 2006? It doesn't do
> } aggregation well (SUM, MEAN), and "we know Ruby already."
> }
> } David Kastrup wrote:
> [...]
> } > Looks like a case for awk to me...
> } >
> } > --
> } > David Kastrup, Kriemhildstr. 15, 44793 Bochum
>
> I've known awk for much longer than Ruby had existed. It is excellent for
> line-based, field-based data manipulation. It feels a lot like C, with a
> few other niceties (like associative arrays).
>
> The point here is that you use the right tool for the job. For this job,
> awk is decidedly the right job.
>
> --Greg
>
>
>
>
I used "awk" when it was the only "scripting language" available to me.
As soon as I got my hands on Perl, however, I relished at the thought of
getting rid of a mess composed of a bunch of "the right tools for the
right jobs" -- ksh, awk, sed, cat, pipes, etc. That whole programming
style was a great one when it was new, and when there wasn't anything
better. But once there was *one* tool -- Perl -- that did all the jobs
and looked like a *real* Algol-like programming language, had arrays and
hashes, there was no way I was going back.

And now that there's Ruby, I don't really even want to go back to Perl.

--
M. Edward (Ed) Borasky, FBG, AB, PTA, PGS, MS, MNLP, NST, ACMC(P)
http://borasky-research.blo...

If God had meant for carrots to be eaten cooked, He would have given rabbits fire.

Marc Heiler

11/24/2006 2:58:00 AM

> Looks like a case for awk to me...
>
> The point here is that you use the right tool for the job. For
> this job, awk is decidedly the right job.

Ruby is beauty.
If you prefer awk, stick to it.
But if others want to use Ruby,
let them use Ruby.

--
Posted via http://www.ruby-....

comp.lang.ruby

Streaming Spreadsheet in Ruby

ben