From mboxrd@z Thu Jan 1 00:00:00 1970 From: Thierry Banel Subject: Re: table formula help... Date: Fri, 12 Dec 2014 22:04:31 +0100 Message-ID: <548B585F.6000404@free.fr> References: <87ppbxfi5f.fsf@ericabrahamsen.net> <54838805.8090705@free.fr> <8761do6t01.fsf@ericabrahamsen.net> <5484CD5C.9070604@free.fr> <5487471D.5070807@free.fr> <5487793C.1080800@free.fr> <5488CF51.8000006@free.fr> Mime-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Return-path: Received: from eggs.gnu.org ([2001:4830:134:3::10]:56299) by lists.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1XzXOB-0000Su-8K for emacs-orgmode@gnu.org; Fri, 12 Dec 2014 16:04:40 -0500 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1XzXO9-0000vB-Ti for emacs-orgmode@gnu.org; Fri, 12 Dec 2014 16:04:39 -0500 Received: from smtp6-g21.free.fr ([2a01:e0c:1:1599::15]:44739) by eggs.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1XzXO9-0000v3-JF for emacs-orgmode@gnu.org; Fri, 12 Dec 2014 16:04:37 -0500 In-Reply-To: List-Id: "General discussions about Org-mode." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org Sender: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org To: Michael Brand Cc: Org Mode Le 12/12/2014 18:15, Michael Brand a =C3=A9crit : > Hi Thierry > > >> Here is how the spreadsheet handles those cases (without modifiers): >> | | | | sum | mean | prod | >> |---+---+---+-----+-----------+------| >> | 1 | 2 | 3 | 6 | 2 | 6 | >> | | 2 | 3 | 5 | 1.6666667 | 6 | >> | | | 3 | 3 | 1 | 3 | >> | | | | 0 | 0 | 1 | <--- see >> #+TBLFM: $4=3Dvsum($1..$3)::$5=3Dvmean($1..$3)::$6=3Dvprod($1..$3) > - Isn't the above table content from a different TBLFM with a mode > string EN for vmean?: > > #+TBLFM: $4=3Dvsum($1..$3)::$5=3Dvmean($1..$3);EN::$6=3Dvprod($1..$3) Absolutely. I should not write mails so late in the night. > > - All columns without mode string (Org >=3D 8.0): > > | | | | vsum | vmean | vprod | vmin | vmax | > |---+---+---+------+-----------+-------+------+------| > | 1 | 2 | 3 | 6 | 2 | 6 | 1 | 3 | > | | 2 | 3 | 5 | 2.5 | 6 | 2 | 3 | > | | | 3 | 3 | 3 | 3 | 3 | 3 | > | | | | 0 | vmean([]) | 1 | inf | -inf | > #+TBLFM: $4=3Dvsum($1..$3)::$5=3Dvmean($1..$3)::$6=3Dvprod($1..$3)::$= 7=3Dvmin($1..$3)::$8=3Dvmax($1..$3) > > - All columns with mode string EN (Org >=3D 8.0): > > | | | | vsum | vmean | vprod | vmin | vmax | > |---+---+---+------+-----------+-------+------+------| > | 1 | 2 | 3 | 6 | 2 | 6 | 1 | 3 | > | | 2 | 3 | 5 | 1.6666667 | 0 | 0 | 3 | > | | | 3 | 3 | 1 | 0 | 0 | 3 | > | | | | 0 | 0 | 0 | 0 | 0 | > #+TBLFM: $4=3Dvsum($1..$3);EN::$5=3Dvmean($1..$3);EN::$6=3Dvprod($1..= $3);EN::$7=3Dvmin($1..$3);EN::$8=3Dvmax($1..$3);EN > >> This is correct. Orgaggregate should behave in a similar way. >> Fortunately in its latest version it does. > Ok, I see the similarity in the case for sum of "no input" with which > I now agree. And for vmean on zero-length input: - Spreadsheet without modifiers: vmean([]) - Orgaggregate: Empty They agree, in this zero-case both return a special value. >> * Summary >> Modifiers are lacking in orgaggregate for it to be fully consistent wi= th >> the spreadsheet. If someone knowns how to add them easily... > I would try an approach like > > #+TBLNAME: test > | Item | Value | > |------+-------| > | a | | > | a | 2 | > > #+BEGIN: aggregate :table test :cols ("Item" "2 * vsum(Value) + 3 * > vmean(Value); EN") > | Item | What column header here? How to specify? | > |------+------------------------------------------| > | a | 7 | > #+END > > that has a syntax more towards TBLFM with a Calc expression. I dreamed about such a syntax when designing orgaggregate in the first place. But I dismissed it as it was going too far in terms of re-inventing the wheel. > It would > not need a mapping of the aggregation function like in > orgtbl-aggregate-apply-calc-*-function and would go through these > steps: > > 1) Collect list from aggregated input column "Value": > > =3D> '("" "2") > > 2) Convert list to Calc vector depending on mode string, see also > test-org-table/references/mode-string-EN and > test-org-table/org-table-make-reference/mode-string-EN with their > siblings: > > (org-table-make-reference '("" "2") t t nil) =3D> "[0,2]" > > 3) Detach Calc expression from mode string and replace input header > "Value" (possibly several and different input headers per output > header) with Calc vector: > > "2 * vsum(Value) + 3 * vmean(Value); EN" =3D> > "2 * vsum([0,2]) + 3 * vmean([0,2])" > > 4) Delegate everything else to Calc, just as org-table-eval-formula > does: > > (calc-eval "2 * vsum([0,2]) + 3 * vmean([0,2])") =3D> "7" > > It is the same that happens already without orgaggregate as > > | Value | > |-------| > | | > | 2 | > |-------| > | 7 | > #+TBLFM: @>$1 =3D 2 * vsum(@I..@II) + 3 * vmean(@I..@II); EN > > where the table formula debugger logs: > > Orig: 2 * vsum(@I..@II) + 3 * vmean(@I..@II) > $xyz-> 2 * vsum(@I..@II) + 3 * vmean(@I..@II) > @r$c-> 2 * vsum([0,2]) + 3 * vmean([0,2]) > $1-> 2 * vsum([0,2]) + 3 * vmean([0,2]) > Result: 7 > > Mode strings other than "E" and "N" for orgaggregate should then not > be too far away, see also org-table-eval-formula. > > Michael > Seems doable. Would tie the spreadsheet and orgaggregate seamlessly. Very appealing! Are you willing to help me implement those steps? Thierry