Programming Language that is Spreadsheet/Table Based

[Omar]

I'm looking for a programming language or module that sorta looks and
feels like MS Excel (I love and think in tables), yet has the power and
open-endedness of python or javascript. I'm still pretty new to
python.

any ideas? i've been having some fun with VBA in excel, but I want
something I can save as en exe and call my own creation, y'know?

 

[Fuzzyman]

I'm looking for a programming language or module that sorta looks and
feels like MS Excel (I love and think in tables), yet has the power and
open-endedness of python or javascript. I'm still pretty new to
python.

any ideas? i've been having some fun with VBA in excel, but I want
something I can save as en exe and call my own creation, y'know?

I think you'll have to build it. For .NET there are lots of third party
grids available and a native one as well. You may want to look at the
grid supplied with wxPython.

Spreadsheets are effectively interfaces to functional programming, so
this request isn't as odd as it may seem to some.

You probably want to have a look at the PyCells project which can
handle the dependency tracking you will need: which is harder than you
might think.

Fuzzyman
http://www.voidspace.org.uk/python/index.shtml

 

[MC]

Hi!

You can expand VBA/Excel with Python, with Pywin32 for to make a
dynamic COM server.

 

[James Stroud]

I'm looking for a programming language or module that sorta looks and
feels like MS Excel (I love and think in tables), yet has the power and
open-endedness of python or javascript. I'm still pretty new to
python.

any ideas? i've been having some fun with VBA in excel, but I want
something I can save as en exe and call my own creation, y'know?

More than the implementation, I would be curious about the API you (or
anyone else) might envision. I have spent a lot of time making a "Table"
class over about the last year and a half, but I'm not sure what might
be an intuitive interface for most people. First, I think it should work
like a "sorted" dictionary of lists, but, at the same time, a list of
sorted dictionaries. I also want *shorthand* for selection.

For example, does the output below look like an intuitive interface? Or,
more likely, how many people get squeamish when they see this interface?
Do these squeamish people have any better ideas? This is a taste of how
my Table class currently behaves:

py> print t # dependent on its property t.format
Last First Age
Barker Bob 204
Burnet Carol 64
Danson Ted 54
Cooper Alice 78
py> t.headings()
("Last", "First", "Age")
py> t.get_row(1) # approximately equal clarity with 1d slice
['Burnet', 'Carol', 64]
py> t[1] # implicit selection of "first" dimension
['Burnet', 'Carol', 64]
py> t.get_column('Last') # probably clearer than taking a 1d slice
['Barker', 'Burnet', 'Danson', 'Cooper']
py> # the following is probably the trickiest, should it return a Table
py> # should it be illegal?
py> # should t['Last'] be the way to take the "slice" and get the col?
py> t[None, 'Last'] # 1d slice returns list (2nd dim. explicit)
['Barker', 'Burnet', 'Danson', 'Cooper']
py> t2 = t[1:3, ('First', 'Age')] # 2d slice returns a new Table
py> t2
<__main__.Table instance at 0x404f676c>
py> t2.format
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
AttributeError: 't2' object has no attribute 'format'
py> t2.format = "%10s %4d"
py> print t2
First Age
Carol 64
Ted 54
py> t3 = t[1:3,'First':'Age'] # shorthand to take a swath of columns
py> t3.format = "%10s %4d"
py> print t3
First Age
Carol 64
Ted 54
py> t3 = t[1:3, 0:2] # if we know what column numbers we want instead
py> t3.format = "%10s%10s"
py> print t3
Last First
Burnet Carol
Danson Ted

These latter selections might turn some heads, especially for those who
think a a little too deeply about dictionaries. But, unlike a
dictionary, a Table is ordered. If you disagree with the assumption that
tables are ordered, make a table in excel, save it, close it, open it
again and see that the columns are still in the same order. Do this 1000
times to convince yourself. The table will be in the same order every
time! Amazing!

The idea is that tables are not dictionaries--please resist drawing
paralells. If we strangle ourselves with operating under the dictionary
paradigm, we will never get a Table. Trust me, I've thought about it
more than most. (If you want a Table to act like a dictionary, I can
make a RandomTable class just for you.)

Notice also that slicing can be done with a tuple or a list, so type
checking is done in the implementation--but lets forget about our
notions of impementation and how type checking is "bad". I want to get
feedback on the API here without regard to how it might be implemented.

In a Table, the first dimension is row and the second is column (i.e.
the data is organized by rows). My Table class has a lot of additional
functionality. But, I'm curious about how people see such a beast
working, though. Please don't criticize unless you have a better idea
about the API of a Table. I want to hear genuine and concrete ideas and
not abstruse pontification about programming or design! Statements of "I
wouldn't do this thing here" should be immediately followed by
"--rather, I would do this other thing for which I've created a concrete
example below."

If you think this interface is genius, well my ego wants to hear about
that as well, but its not terribly necessary.

James

--
James Stroud
UCLA-DOE Institute for Genomics and Proteomics
Box 951570
Los Angeles, CA 90095

http://www.jamesstroud.com/

 

[Gerard Flanagan]

I have spent a lot of time making a "Table"
class over about the last year and a half, but I'm not sure what might
be an intuitive interface for most people. First, I think it should work
like a "sorted" dictionary of lists, but, at the same time, a list of
sorted dictionaries. I also want *shorthand* for selection.

For example, does the output below look like an intuitive interface? Or,
more likely, how many people get squeamish when they see this interface?
Do these squeamish people have any better ideas? This is a taste of how
my Table class currently behaves:

py> print t # dependent on its property t.format
Last First Age
Barker Bob 204
Burnet Carol 64
Danson Ted 54
Cooper Alice 78
py> t.headings()
("Last", "First", "Age")
py> t.get_row(1)
['Burnet', 'Carol', 64]
py> t[1]
['Burnet', 'Carol', 64]
py> t.get_column('Last')
['Barker', 'Burnet', 'Danson', 'Cooper']

+1 from me up to here

py> # the following is probably the trickiest, should it return a Table
py> # should it be illegal?
py> # should t['Last'] be the way to take the "slice" and get the col?
py> t[None, 'Last'] # 1d slice returns list (2nd dim. explicit)
['Barker', 'Burnet', 'Danson', 'Cooper']

I can imagine manipulating columns at the Table creation stage -
insert, append, delete column - but after that I think you would be
dealing with rows more often. Personally, if I needed columns I would
be happier with a list comprehension:

[ (row['Last'], row['Age']) for row in t ] etc.

eg. like:

http://buzhug.sourceforge.net/

py> t2 = t[1:3, ('First', 'Age')] # 2d slice returns a new Table
py> t3 = t[1:3,'First':'Age'] # shorthand to take a swath of columns
py> t3 = t[1:3, 0:2] # if we know what column numbers we want instead

t[1:3][0:2] and so on would be more intuitive to me, possibly
t[1:3]['First':'Age']

Please don't criticize unless you have a better idea
about the API of a Table. I want to hear genuine and concrete ideas and
not abstruse pontification about programming or design! Statements of "I
wouldn't do this thing here" should be immediately followed by
"--rather, I would do this other thing for which I've created a concrete
example below."

chill...

Gerard

 

[James Stroud]

py> # the following is probably the trickiest, should it return a Table
py> # should it be illegal?
py> # should t['Last'] be the way to take the "slice" and get the col?
py> t[None, 'Last'] # 1d slice returns list (2nd dim. explicit)
['Barker', 'Burnet', 'Danson', 'Cooper']

I can imagine manipulating columns at the Table creation stage -
insert, append, delete column - but after that I think you would be
dealing with rows more often. Personally, if I needed columns I would
be happier with a list comprehension:
[ (row['Last'], row['Age']) for row in t ]
etc.

To make a table from list comprehension in this way seems like it would
require some redundancy because a list comprehension only gets you a
list (of rows or lists). It seems if you wanted to work with your 2d
selection of data, then you would want to get a table back:

data = [ (row['Last'], row['Age']) for row in t ]
t2 = Table(('Last','Age'), data)

This seems, to me, to separates selection in the 2 dimensions and makes
it "backwards":

data = [ (row['Last'], row['Age']) for row in t[1:3]]
t2 = Table(('Last','Age'), data)

So a function would be needed to group the selection in a way that
reflects the organization of the table:

t2 = t.subtable((1,3), ('Last','Age'))

But then, since the latter seems a natural consequence of using list
comprehension for selection, how might one distinguish a range of
columns if not by a verbose function name?

t2 = t.subtable_with_column_range((1,3), ('Last','Age'))

The concept of slicing seems to take care of this. Maybe

t2 = t.subtable(slice(1,3), slice('Last','Age'))

But this begins to seem awkward and verbose to boot. Any suggestions on
adapting your idea of list comprehension to generate subtables?

eg. like:

http://buzhug.sourceforge.net/

py> t2 = t[1:3, ('First', 'Age')] # 2d slice returns a new Table
py> t3 = t[1:3,'First':'Age'] # shorthand to take a swath of columns
py> t3 = t[1:3, 0:2] # if we know what column numbers we want instead

t[1:3][0:2] and so on would be more intuitive to me, possibly
t[1:3]['First':'Age']

This looks better than my slicing, and to argue with it I'd have to
break my own rules and point out that it would require making an
intermediate table in the implementation. I am emulating numarray
slicing closely, which itself is probably focused on implementation and
speed.


James

 

[Gerard Flanagan]

py> # the following is probably the trickiest, should it return a Table
py> # should it be illegal?
py> # should t['Last'] be the way to take the "slice" and get the col?
py> t[None, 'Last'] # 1d slice returns list (2nd dim. explicit)
['Barker', 'Burnet', 'Danson', 'Cooper']

I can imagine manipulating columns at the Table creation stage -
insert, append, delete column - but after that I think you would be
dealing with rows more often. Personally, if I needed columns I would
be happier with a list comprehension:
[ (row['Last'], row['Age']) for row in t ]
etc.

To make a table from list comprehension in this way seems like it would
require some redundancy because a list comprehension only gets you a
list (of rows or lists). It seems if you wanted to work with your 2d
selection of data, then you would want to get a table back:

data = [ (row['Last'], row['Age']) for row in t ]
t2 = Table(('Last','Age'), data)

This seems, to me, to separates selection in the 2 dimensions and makes
it "backwards":

data = [ (row['Last'], row['Age']) for row in t[1:3]]
t2 = Table(('Last','Age'), data)

So a function would be needed to group the selection in a way that
reflects the organization of the table:

t2 = t.subtable((1,3), ('Last','Age'))

But then, since the latter seems a natural consequence of using list
comprehension for selection, how might one distinguish a range of
columns if not by a verbose function name?

t2 = t.subtable_with_column_range((1,3), ('Last','Age'))

The concept of slicing seems to take care of this. Maybe

t2 = t.subtable(slice(1,3), slice('Last','Age'))

But this begins to seem awkward and verbose to boot. Any suggestions on
adapting your idea of list comprehension to generate subtables?

What about symmetric 'load' and 'iterrows' methods for the Table class:

t2 = Table()
t2.load( t1.iterrows("LastName", "Age") )

def load(self, iterable):
'''expecting tuples'''
for lname, age in iterable:
self.append( lname, age )

def iterrows(self, *args):
'''returns a generator which itself returns tuples
filtered by the column names in *args (via
operator.itemgetter maybe?)'''


There might be some value in comparing Microsoft's DataSet API. IIRC,
you typically have:

*UI* - DataView - DataSet - DataAdapter - *DATABASE*

A DataSet is a collection of DataTables and has a lot of DB/XML
functionality and so on. A DataView on the other hand is more
lightweight and has mainly filtering and sorting functions and is
designed to be bound to a GUI widget such as a DataGrid.

You might have something along the lines of:

ds = DataSet()
# fill ds from datasource
dt_customers = ds.tables["customers"]
view = DataView( dt_customers )
view.rowfilter = "lastname LIKE 'A*' and Age < 60"

I'm not saying imitate this, but maybe some value in studying the
OO-bondage approach.

All the best

Gerard

 

[James Stroud]

What about symmetric 'load' and 'iterrows' methods for the Table class:

t2 = Table()
t2.load( t1.iterrows("LastName", "Age") )

def load(self, iterable):
'''expecting tuples'''
for lname, age in iterable:
self.append( lname, age )

def iterrows(self, *args):
'''returns a generator which itself returns tuples
filtered by the column names in *args (via
operator.itemgetter maybe?)'''


There might be some value in comparing Microsoft's DataSet API. IIRC,
you typically have:

*UI* - DataView - DataSet - DataAdapter - *DATABASE*

A DataSet is a collection of DataTables and has a lot of DB/XML
functionality and so on. A DataView on the other hand is more
lightweight and has mainly filtering and sorting functions and is
designed to be bound to a GUI widget such as a DataGrid.

You might have something along the lines of:

ds = DataSet()
# fill ds from datasource
dt_customers = ds.tables["customers"]
view = DataView( dt_customers )
view.rowfilter = "lastname LIKE 'A*' and Age < 60"

I'm not saying imitate this, but maybe some value in studying the
OO-bondage approach.

Thank you, this is very good stuff to think about.

James

 

[Hendrik van Rooyen]

Gerard Flanagan wrote:
8<----------------------------------------------

Thank you, this is very good stuff to think about.

James

I can't really add to the above train of thought...

And I don't know if this will help - but if you want to think, here is a skewed,
simple view:

If I were to try and do something like this, I would start by defining a spread
sheet like dict to keep everything in, using (column,row) tuples as keys, and
build on top of that.

This would give you the ability to keep *anything* in a cell, and the database
like structures you are thinking of would live on top of this underlying access
structure, with the (column,row) being a direct *pointer* to an element in one
fast step.

I would then also use other dicts to describe what is kept in the "columns" -
something simple like:

header_dict = {0:['name of person','Name','Enter your name'],1:['age of person
since birth','Age','Enter your birthday'], ... }

where the first item in the list is a help string, the second a column header
for printing, the third the string to use as prompt for a GUI, etc... - you
could even keep and enforce type if you wanted to - must be int, must be date,
column width for printing, validation data and rules, - whatever.

In a sense this is a sort of inverted way of looking at a file - instead of
having an underlying dict with (column, row) keys, you could alternatively have
"loose" column dicts only to keep the data in ( note that I have been writing
(column, row) consistently, instead of the more conventional (row,column).) -
this would make adding or deleting columns almost trivial.

You *could* also have references to Python functions or class methods living
alongside the data in the cells, and then things can get hairy - for an age
entry, for instance, the cell can look like:

Data[(column,row)] = # (or Age[row] in the alternative
approach)

[(1947,01,24),self.BirthdayChecker,self.PresentChooser,self.LetterWriter, ...]

where the first entry, the tuple, represents the data (could have been a list
or dict, of course ) and the rest are methods or functions to use, in this
particular instance. You could instead have these function references in the
age column's header dict entry, for those of them that have applicability across
all rows of the column, just like the idea of type enforcement or validation
above.

For a kind of row, or row type, you need then simply keep a list of column
numbers that are required for this "record", with a different list defining a
different record type - Gold members need these column entries filled in, while
Silver members need only those... simple, but then you need a record type
column...

This sort of thing organises the stuff, but the code to use it becomes a bit
impenetrable, as you have to write a kind of crawling process to access the
structure to do what is required, but it is all "table driven" and can be very
flexible. Its not a weekend project though, and when you are finished its a
kind of super relational database...

HTH

- Hendrik

 

[dug]

I think that Gnumeric lets you do some python stuff.

Douglas

 

[Ravi Teja]

I'm looking for a programming language or module that sorta looks and
feels like MS Excel (I love and think in tables), yet has the power and
open-endedness of python or javascript. I'm still pretty new to
python.
PyCells
http://pycells.pdxcb.net/
http://pycells.pdxcb.net/wiki/index.php?title=Basic_Tutorial

any ideas? i've been having some fun with VBA in excel
but I want something I can save as en exe and call my own creation, y'know?

You can also do Excel automation using Python.
http://www.markcarter.me.uk/computing/python/excel.html

There are many packaging tools for Python. Py2exe is the most popular.
Although in Excel's case, it would be difficult to make stand alone.