The problem is going through the hundreds of thousands of potential combinations by hand.  Picalo itself doesn’t do this (yet), but here’s a script that makes quick work of it.  Note that the algorithm is *exponential*.  It takes about 1 minute for my laptop to go through 8 numbers (which results in almost 20,000 possible combinations).  If you have hundreds of numbers, prepare to wait a while.

The script has dummy numbers in it and seeks for a value of $7.07.  It comes up with 5 potential matches.  Readers should easily be able to use this script with their own data.  I’ll include it as a detectlet in future version of Picalo.

# written by Conan Albrecht
 
# to be included in a future version of Picalo
 
# released under the LGPL
 
# import the PIcalo libraries
 
from picalo import *
 
# import commonly-needed built-in libraries
 
import string, sys, re, random, os, os.path, urllib
 
import itertools
 
# the goal we want to find
 
goal_value = 7.07
 
# create our table of numbers
 
# this would normally be done by importing the data
 
# in other words, delete this part here.  i'm only creating
 
# it to make the script self-sufficient.
 
nums = Table([("Num", number, "#.00")])
 
nums.append(15.50)
 
nums.append(42.33)
 
nums.append(-33.11)
 
nums.append(5.67)
 
nums.append(22.31)
 
nums.append(-15.23)
 
nums.append(15.55)
 
nums.append(14.88)
 
nums.append(-7.33)
 
# create a table to store the results in
 
results = Table([
 
  ('Formula', str),
 
  ('Answer', number),
 
  ('DiffFromGoal', number),
 
])
 
try:
 
  comb = {}  # use a dict for the has_key below (to remove duplicates efficiently)
 
  pos_neg = [ [ abs(n), -1*abs(n) ] for n in nums.column('Num') ]
 
  prod = list(itertools.product(*pos_neg))
 
  combmap = {}
 
  for pi, p in enumerate(prod):
 
    show_progress('Finding all possible combinations...', float(pi) / float(len(prod)))
 
    for i in range(1, len(p)+1):
 
      for c in itertools.combinations(p, i):
 
        if not comb.has_key(c):
 
          comb[c] = None
 
# go through and fill out the results table
 
for i, c in enumerate(comb.keys()):
 
  show_progress('Checking combinations...', float(i) / float(len(comb)))
 
  formula = ' + '.join([ '(%s)' % num for num in c])
 
  total = sum(c)
 
  results.append(
 
    formula,
 
    total,
 
    abs(goal_value - total)
 
  )
 
# sort the results
 
show_progress('Sorting...', .99)
 
Simple.sort(results, True, 'DiffFromGoal')
 
finally:
 
  clear_progress()

There are lots of other changes as well.  Those interested can read the README file.  Enjoy.

The workbook is the result of some training I did for the a client two weeks ago.  It wanted exercises for the group to go through.  After the training, I took the exercises and sample data sets and formally put them into the workbook.  Note that the datasets were entirely generated and have no relation to the client.

The workbook is still missing a few items.  I also found some bugs in Picalo while working through it.  I also need to edit the text to ensure grammar is correct.  But for now, it’s 95 percent there and is up on the web site in the documentation section.

P.S. For those users waiting for PyTables integration, it’s still coming.  I want to crank through these bugs I found, then I’ll get back to work on it.  I’m hoping the Christmas break will give me some time to get it pushed forward quite a bit.

What is PyTables? It’s a Python library for holding data. It’s built upon the widely-used numarray framework.  Both projects (PyTables and numarray) are written in C, so they’ll speed up Picalo’s data structures considerably.  PyTables includes a disk-caching system, so Picalo should be able to load any size table without killing memory or the processor.  Overall, this integration should bring Picalo into the “full production” arena.

I’m excited about doing it and will jump on the implementation of it ASAP.