From mboxrd@z Thu Jan 1 00:00:00 1970 From: Michael Brand Subject: Re: table formula help... Date: Fri, 12 Dec 2014 18:15:25 +0100 Message-ID: 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]:54609) by lists.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1XzToN-0005kG-Ll for emacs-orgmode@gnu.org; Fri, 12 Dec 2014 12:15:28 -0500 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1XzToL-0005li-Or for emacs-orgmode@gnu.org; Fri, 12 Dec 2014 12:15:27 -0500 Received: from mail-qc0-x22d.google.com ([2607:f8b0:400d:c01::22d]:32995) by eggs.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1XzToL-0005lX-Ju for emacs-orgmode@gnu.org; Fri, 12 Dec 2014 12:15:25 -0500 Received: by mail-qc0-f173.google.com with SMTP id i17so5836104qcy.32 for ; Fri, 12 Dec 2014 09:15:25 -0800 (PST) In-Reply-To: <5488CF51.8000006@free.fr> 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: Thierry Banel Cc: Org Mode Hi Thierry On Wed, Dec 10, 2014 at 11:55 PM, Thierry Banel wrot= e: > Le 10/12/2014 22:06, Michael Brand a =C3=A9crit : >> If this function would follow its docstring by having "(if (cdr vec)" >> also for sum, min, max and prod then the user could benefit from >> adding "E" and/or "N" or not in the mode string of the TBLFM: > > To further process the aggregations? Yes, in the TBLFM of the result table. > 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) - 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=3D= vmin($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. > * Summary > Modifiers are lacking in orgaggregate for it to be fully consistent with > 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. 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