emacs-orgmode@gnu.org archives
 help / color / mirror / code / Atom feed
From: Thierry Banel <tbanelwebmin@free.fr>
To: Org Mode <emacs-orgmode@gnu.org>
Subject: Re: table formula help...
Date: Wed, 10 Dec 2014 23:55:13 +0100	[thread overview]
Message-ID: <5488CF51.8000006@free.fr> (raw)
In-Reply-To: <CALn3zog0eocBkU3iJeYehMfQXx6DVDRCYAe=C6bbQN_=Qgjovg@mail.gmail.com>

Le 10/12/2014 22:06, Michael Brand a écrit :
> Hi Thierry
>
> On Tue, Dec 9, 2014 at 11:35 PM, Thierry Banel <tbanelwebmin@free.fr> wrote:
>
>> On output, empty cells are generated when the aggregation function does
>> not have enough input. For instance, =mean= needs at least one value,
>> otherwise a division by zero happens.
> The above "not enough input" contradicts with "no input" from the
> docstring of orgtbl-aggregate-apply-calc-1arg-function:
>
>     Empty value is returned when all input values are empty.

My mistake.Fixed to:
  "Empty value is returned when not enough non-empty input is available"

Thanks

> 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?

> #+TBLNAME: test
> | Item | Value_1 | Value_2 |
> |------+---------+---------|
> | a    |       2 |         |
> | a    |       2 |       2 |
> | b    |       2 |       2 |
> | b    |         |       2 |
> | c    |         |       2 |
> | c    |         |       2 |
> | d    |         |       1 |
> | d    |         |      -1 |
>
> #+BEGIN: aggregate :table test :cols ("Item" "sum(Value_1)" "sum(Value_2)")
> | Item | sum(Value_1) | sum(Value_2) |   |   |    |
> |------+--------------+--------------+---+---+----|
> | a    |            4 |            2 | 3 | 3 | >  |
> | b    |            2 |            4 | 3 | 3 | <  |
> | c    |              |            4 | 4 | 2 | NA |
> | d    |              |            0 | 0 | 0 | NA |
> #+TBLFM: $4 = vmean($2..$3) :: $5 = vmean($2..$3); EN :: $6 = if("$2"
> == "nan" || "$3" == "nan", string("NA"), if($2 > $3, string(">"),
> if($2 < $3, string("<"), string("eq")))); E
> #+END
>
> The current orgaggregate for comparison:
>
> | Item | sum(Value_1) | sum(Value_2) |   |   |    |
> |------+--------------+--------------+---+---+----|
> | a    |            4 |            2 | 3 | 3 | >  |
> | b    |            2 |            4 | 3 | 3 | <  |
> | c    |            0 |            4 | 2 | 2 | <  |
> | d    |            0 |            0 | 0 | 0 | eq |
>
> One could still get the current behavior by adding the column formula
> ~$2 = if("$2" == "nan", 0, $0); E~ etc. for the aggregated columns.
>
> What do you think?
>
> Michael
>

Well... Many different topics here.First of all, empty input & empty
output are unrelated. So let us take them one at a time.

* Input
In the spreadsheet formulas, we have those modifiers:
- E = keep empty fields when counting input vector size.
- N = replace non-numbers (including empties) by zero.

The first version of orgaggregate behaved as thought it had the EN
modifiers.The latest version behaves as thought it had no modifiers at all.

For the shake of consistency, it would be nice to have specifiers in
orgaggregate. E & N, of course, but also p7 (precision 7 digits), %.3
(three decimal places after dot), F (fraction), and so on.

* Output
If a function is able to compute a result, then it should give it. If
for any reason it is not able to compute a result, it should tell it in
some way. Right now, orgaggregate signals problems by leaving a blank
output. But it could be anything else: nan, NA, #ERROR, vmean([]), 1/0,
whatever.

The =sum= aggregation is always able to return a value, even for a zero
length input vector, in which case the sum is zero. On zero length
vectors, =prod= gives one. The =mean= aggregation has a hard time
telling what is the mean of a zero length input vector.

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=vsum($1..$3)::$5=vmean($1..$3)::$6=vprod($1..$3)

This is correct. Orgaggregate should behave in a similar way.
Fortunately in its latest version it does.

* Summary
Modifiers are lacking in orgaggregate for it to be fully consistent with
the spreadsheet. If someone knowns how to add them easily...

Regards
Thierry

  reply	other threads:[~2014-12-10 22:55 UTC|newest]

Thread overview: 34+ messages / expand[flat|nested]  mbox.gz  Atom feed  top
2014-12-06  5:42 table formula help Eric Abrahamsen
2014-12-06  7:37 ` Thomas S. Dye
2014-12-06  8:05 ` Michael Brand
2014-12-06  8:32   ` Eric Abrahamsen
2014-12-06 22:49   ` Thierry Banel
2014-12-07  2:05     ` Eric Abrahamsen
2014-12-07  3:25     ` Eric Abrahamsen
2014-12-07  9:20       ` Thierry Banel
2014-12-07  9:40         ` Michael Brand
2014-12-07 10:02           ` Thierry Banel
2014-12-07 10:26             ` Michael Brand
2014-12-07 14:51               ` Thierry Banel
2014-12-07 16:13               ` Thierry Banel
2014-12-07 16:48                 ` Michael Brand
2014-12-08 21:12                   ` Thierry Banel
2014-12-08 22:32                     ` Thierry Banel
2014-12-10 21:08                       ` Michael Brand
2014-12-08  3:52             ` Eric Abrahamsen
2014-12-07  9:39       ` Michael Brand
2014-12-07  9:55         ` Thierry Banel
2014-12-07 21:57         ` Thierry Banel
2014-12-08 18:02           ` Michael Brand
2014-12-08 21:57             ` Thierry Banel
2014-12-09  5:54               ` Michael Brand
2014-12-09 18:12                 ` Thierry Banel
2014-12-09 19:01             ` Thierry Banel
2014-12-09 22:35               ` Thierry Banel
2014-12-10 21:06                 ` Michael Brand
2014-12-10 22:55                   ` Thierry Banel [this message]
2014-12-12 17:15                     ` Michael Brand
2014-12-12 21:04                       ` Thierry Banel
2014-12-13 18:27                         ` Michael Brand
2015-01-25 22:21       ` Thierry Banel
2015-01-26  2:48         ` Eric Abrahamsen

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

  Avoid top-posting and favor interleaved quoting:
  https://en.wikipedia.org/wiki/Posting_style#Interleaved_style

  List information: https://www.orgmode.org/

* Reply using the --to, --cc, and --in-reply-to
  switches of git-send-email(1):

  git send-email \
    --in-reply-to=5488CF51.8000006@free.fr \
    --to=tbanelwebmin@free.fr \
    --cc=emacs-orgmode@gnu.org \
    /path/to/YOUR_REPLY

  https://kernel.org/pub/software/scm/git/docs/git-send-email.html

* If your mail client supports setting the In-Reply-To header
  via mailto: links, try the mailto: link
Be sure your reply has a Subject: header at the top and a blank line before the message body.
Code repositories for project(s) associated with this public inbox

	https://git.savannah.gnu.org/cgit/emacs/org-mode.git

This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox;
as well as URLs for read-only IMAP folder(s) and NNTP newsgroup(s).