title: A DSL in Python for tabular analysis and processing?
body: <p>I am carrying around the idea to create a restricted form of a language for tabular analysis. Thequestion is if there is something like the following already around or if there are better ideas todo it. The key requirement is that the user must be able to employ naked script files without thenecessity to produce a full Python program. OTOH elements of the script language are compiled at runtime andshould offer a possibility to formulate complex conditions and calculations (mostly arithmetic and string operations). The declarative way to formulate programs in the language (see below) prohibits the direct use of Python syntax as the vehicle for the language (see the implications of the @PART function) - at least I think so.
  Is there a better/ more clever/ more elegant way to achieve my goals than to program around in a semi-parsed mixture of Python and clumsy self-defined syntax like I did below?</p>
  
  <p>Below I try to make my ideas clear by an example. The input table is produced by another program part and looks like this on start of the script interpretation:</p>
  
  <pre><code># First,Last,Department,Hourly Wage
  [ ('Greg','Foo','HR',100),
    ('Judy','Bar','EE',51),
    ('Jake','Baz','HR',75),
    ('Lila','Bax','HR',49),
    ('Norm','Fob','EE',49) ]
  </code></pre>
  
  <p>Following is the 'script' file itself. This will be a file for itself in a production system. The program code is currently laid down as an array of Python strings - maybe not even that in a final version.</p>
  
  <pre><code># A program to produce per department the average hourly rate, separated for the higher and lower 50% of earners: 
  [ "@SORT(2,-3)", 
       "@SET({max},@MAX({3}))",
       "@PART({2}!={^2} or {3}&lt;{max}/2)",
          "@SET({dep},@FIRST({2}))",
          "@PRINT({dep},float(@SUM({3}))/@CNT({3}))"
  ]
  </code></pre>
  
  <p>I will try to explain step by step what the script shall do:</p>
  
  <pre><code>"@SORT(2,-3)" 
  </code></pre>
  
  <p>sorts the table after column 2 (ascending), then column 3 (descending). We get</p>
  
  <pre><code>[ ('Judy','Bar','EE',51),
    ('Norm','Fob','EE',49),
    ('Greg','Foo','HR',100),
    ('Jake','Baz','HR',75),
    ('Lila','Bax','HR',49),
  ]
  
  "@SET({max},@MAX({3}))" 
  </code></pre>
  
  <p>takes the maximum of column 3 and puts it into a dynamic, local variable max</p>
  
  <pre><code>"@PART({2}!={^2} or {3}&lt;{max}/2)" 
  </code></pre>
  
  <p>is a bit more complex. @PART partitions the current table
  into several sub-tables by evaluating the given expression for each row and cutting in front of the row if true.
  Here we want to cut at department-borders (column 2). {^2} is an upward reference, meaning the element in column 2
  from the previous row. This syntax is necessary as I deem the ability to partition tables on conditions more complex
  than "row differs from previous row in X" very important (imagine you want to partition the table in 10k-income classes)
  so I need the expressive power of a (restricted) Python expression in the argument to PART. Also this has the implication
  that the expression can not be evaluated for the first row as there is no predecessor, so the PART-function
  will simply go over it.
  After this function we have the following tables:</p>
  
  <pre><code>[ ('Judy','Bar','EE',51) ] # Department EE
  
  [ ('Norm','Fob','EE',49) ] # Norm Fob is in the same department but earns less than half of the maximum
  
  [ ('Greg','Foo','HR',100), # New department HR
    ('Jake','Baz','HR',75) ]
  
  [ ('Lila','Bax','HR',49) ] # HR dept. but less than half of the best earner
  </code></pre>
  
  <p>From here on, the functions in the script will work on each sub-table separately. A PART function more or less
  starts a loop over all resulting sub-tables and every following function (including more PARTs) is executed on
  a subtable in isolation.</p>
  
  <pre><code>"@SET({dep},@FIRST({2}))",
  "@PRINT({dep},float(@SUM({3}))/@CNT({3}))"
  </code></pre>
  
  <p>@FIRST({2}) takes the value of column 2 of the first row simply. @SUM({3}) takes the sum of the whole column 3
  and @CNT({3}) is counting the number of rows which have column 3 unequal to None.
  I picture the functions outcome roughly here:</p>
  
  <pre><code>[ ('Judy','Bar','EE',51) ]
          "@SET({dep},@FIRST({2}))"  --&gt; {dep} = "EE"
          "@PRINT({dep},float(@SUM({3}))/@CNT({3}))"  --&gt; output  "EE 51"
  
  [ ('Norm','Fob','EE',49) ]
          "@SET({dep},@FIRST({2}))", --&gt; {dep} = "EE"
          "@PRINT({dep},float(@SUM({3}))/@CNT({3}))"  --&gt; output  "EE 49"
  
  [ ('Greg','Foo','HR',100), 
    ('Jake','Baz','HR',75) ]
          "@SET({dep},@FIRST({2}))", --&gt; {dep} = "HR"
          "@PRINT({dep},float(@SUM({3}))/@CNT({3}))"  --&gt; output  "HR 87.5"
  
  [ ('Lila','Bax','HR',49) ] 
          "@SET({dep},@FIRST({2}))", --&gt; {dep} = "HR"
          "@PRINT({dep},float(@SUM({3}))/@CNT({3}))"  --&gt; output  "HR 49"
  </code></pre>
  
  <p>I should add that I'd prefer to keep the solution small, i.e. no use of non-standard Python packages like pyparsing etc.</p>