table formula help...

table formula help...

[Eric Abrahamsen]

I never seem to use the spreadsheet unless it's some horribly
complicated thing I don't know how to calculate... Can someone lend me a
hand?

I'm calculating payment rates for contributors (actually translators) to
a magazine.

I've got two tables: The first is essentially a table of contents,
listing pieces with their character count (prose), or line count
(poetry), plus who translated it.

The second is a list of translators, with their total character/line
count, and how much they're owed.

I'm having a hell of a time getting the column formulas right:
specifically referencing one table from another. The first table looks
like this:

#+NAME: counts
| Piece          |  Chars | Lines | Translator       |
+----------------+--------+-------+------------------+
| 凤凰           |        |    84 | Austin Woerner   |
| 王血           |   6633 |       | Eric Abrahamsen  |
| 赵氏孤儿       |  16984 |       | Canaan Morse     |
| 山鬼故家       |        |    24 | Lucas Klein      |
| 寂静何其深沉   |        |    10 | Lucas Klein      |

And the second:

#+NAME: payments
#+CONSTANTS: prose=0.7 poetry=10
| Translator       | Total Chars | Total Lines | Payment |
|------------------+-------------+-------------+---------|
| Austin Woerner   |             |             |         |
| Eric Abrahamsen  |             |             |         |
| Canaan Morse     |             |             |         |
| Lucas Klein      |             |             |         |

In this second table, the formula I want for the "Total Chars" column
is:

"Set each row of $2 in table payments to the sum of all numbers in $2 of
remote(counts), if $2 is not empty, and if $4 of remote(counts) is equal
to $1 of table payments."

The formula for "Total Lines" would be exactly the same, but with the
$2s all switched to $3. I can handle adding up the amounts!

Is it possible to iterate over all the rows in another table like this?
Is there a better way I could be arranging my tables?

Thanks for any tips!

Eric

Re: table formula help...

[Thomas S. Dye]

Aloha Eric,

One approach would normalize the =counts= table= and use a SQL query to
generate the payments table.

#+name: counts
| id | Piece | N    | Unit | Translator |
|  1 | foo   | 84   | line | bar        |
|  2 | baz   | 6633 | char | foobar     |

The query would sum over =N= conditional on =Unit= and group by
=Translator=.  I can't write the query off the top of my head.

hth,
Tom

Eric Abrahamsen <eric@ericabrahamsen.net> writes:

> I never seem to use the spreadsheet unless it's some horribly
> complicated thing I don't know how to calculate... Can someone lend me a
> hand?
>
> I'm calculating payment rates for contributors (actually translators) to
> a magazine.
>
> I've got two tables: The first is essentially a table of contents,
> listing pieces with their character count (prose), or line count
> (poetry), plus who translated it.
>
> The second is a list of translators, with their total character/line
> count, and how much they're owed.
>
> I'm having a hell of a time getting the column formulas right:
> specifically referencing one table from another. The first table looks
> like this:
>
> #+NAME: counts
> | Piece          |  Chars | Lines | Translator       |
> +----------------+--------+-------+------------------+
> | 凤凰           |        |    84 | Austin Woerner   |
> | 王血           |   6633 |       | Eric Abrahamsen  |
> | 赵氏孤儿       |  16984 |       | Canaan Morse     |
> | 山鬼故家       |        |    24 | Lucas Klein      |
> | 寂静何其深沉   |        |    10 | Lucas Klein      |
>
> And the second:
>
> #+NAME: payments
> #+CONSTANTS: prose=0.7 poetry=10
> | Translator       | Total Chars | Total Lines | Payment |
> |------------------+-------------+-------------+---------|
> | Austin Woerner   |             |             |         |
> | Eric Abrahamsen  |             |             |         |
> | Canaan Morse     |             |             |         |
> | Lucas Klein      |             |             |         |
>
> In this second table, the formula I want for the "Total Chars" column
> is:
>
> "Set each row of $2 in table payments to the sum of all numbers in $2 of
> remote(counts), if $2 is not empty, and if $4 of remote(counts) is equal
> to $1 of table payments."
>
> The formula for "Total Lines" would be exactly the same, but with the
> $2s all switched to $3. I can handle adding up the amounts!
>
> Is it possible to iterate over all the rows in another table like this?
> Is there a better way I could be arranging my tables?
>
> Thanks for any tips!
>
> Eric
>
>
>

-- 
Thomas S. Dye
http://www.tsdye.com

Re: table formula help...

[Michael Brand]

Hi Eric

On Sat, Dec 6, 2014 at 6:42 AM, Eric Abrahamsen <eric@ericabrahamsen.net> wrote:
> #+NAME: counts
> | Piece          |  Chars | Lines | Translator       |
> +----------------+--------+-------+------------------+
> | 凤凰           |        |    84 | Austin Woerner   |
> | 王血           |   6633 |       | Eric Abrahamsen  |
> | 赵氏孤儿       |  16984 |       | Canaan Morse     |
> | 山鬼故家       |        |    24 | Lucas Klein      |
> | 寂静何其深沉   |        |    10 | Lucas Klein      |
>
> And the second:
>
> #+NAME: payments
> #+CONSTANTS: prose=0.7 poetry=10
> | Translator       | Total Chars | Total Lines | Payment |
> |------------------+-------------+-------------+---------|
> | Austin Woerner   |             |             |         |
> | Eric Abrahamsen  |             |             |         |
> | Canaan Morse     |             |             |         |
> | Lucas Klein      |             |             |         |

Thierry Banel recently announced orgaggregate:
http://lists.gnu.org/archive/cgi-bin/namazu.cgi?idxname=emacs-orgmode&max=100&sort=date:late&query=%2Bsubject:"%5BANN%5D+Aggregate+Table"

For a solution with only built-in functionality see the example
test-org-table/org-lookup-all in the Org source file
testing/lisp/test-org-table.el
http://orgmode.org/w/org-mode.git?p=org-mode.git;a=blob;f=testing/lisp/test-org-table.el

Michael

Re: table formula help...

[Eric Abrahamsen]

Michael Brand <michael.ch.brand@gmail.com> writes:

> Hi Eric
>
> On Sat, Dec 6, 2014 at 6:42 AM, Eric Abrahamsen <eric@ericabrahamsen.net> wrote:
>> #+NAME: counts
>> | Piece          |  Chars | Lines | Translator       |
>> +----------------+--------+-------+------------------+
>> | 凤凰           |        |    84 | Austin Woerner   |
>> | 王血           |   6633 |       | Eric Abrahamsen  |
>> | 赵氏孤儿       |  16984 |       | Canaan Morse     |
>> | 山鬼故家       |        |    24 | Lucas Klein      |
>> | 寂静何其深沉   |        |    10 | Lucas Klein      |
>>
>> And the second:
>>
>> #+NAME: payments
>> #+CONSTANTS: prose=0.7 poetry=10
>> | Translator       | Total Chars | Total Lines | Payment |
>> |------------------+-------------+-------------+---------|
>> | Austin Woerner   |             |             |         |
>> | Eric Abrahamsen  |             |             |         |
>> | Canaan Morse     |             |             |         |
>> | Lucas Klein      |             |             |         |
>
> Thierry Banel recently announced orgaggregate:
> http://lists.gnu.org/archive/cgi-bin/namazu.cgi?idxname=emacs-orgmode&max=100&sort=date:late&query=%2Bsubject:"%5BANN%5D+Aggregate+Table"
>
> For a solution with only built-in functionality see the example
> test-org-table/org-lookup-all in the Org source file
> testing/lisp/test-org-table.el
> http://orgmode.org/w/org-mode.git?p=org-mode.git;a=blob;f=testing/lisp/test-org-table.el
>
> Michael

Thanks to you and Thomas! From your answers, it appears I'm asking for
something more complicated than I thought. I'm going to see if I can
manage the solution by re-structuring the tables. Either way, I'll take
a look at the links and see what I can learn.

Thanks again,
Eric

Re: table formula help...

[Thierry Banel]

To elaborate on Michael's first suggestion,
we will first give a name ("work") to the table:

  #+TBLNAME: work
  | Chars | Lines | Translator      |
  |-------+-------+-----------------|
  |       |    84 | Austin Woerner  |
  |  6633 |       | Eric Abrahamsen |
  | 16984 |       | Canaan Morse    |
  |       |    24 | Lucas Klein     |
  |       |    10 | Lucas Klein     |

Then typing C-c C-x i and answering the wizard questions,
we get a new table as follow:

  #+BEGIN: aggregate :table "work" :cols "Translator sum(Chars) sum(Lines)"
  | Translator      | sum(Chars) | sum(Lines) |
  |-----------------+------------+------------|
  | Austin Woerner  | NA         | 84         |
  | Eric Abrahamsen | 6633       | NA         |
  | Canaan Morse    | 16984      | NA         |
  | Lucas Klein     | 2 NA       | 34         |
  #+END:

Whenever you change the "work" table,
you can easily refresh the aggregated table by typing C-c C-c

To install the orgtbl-aggregate module,
add those lines to your .emacs:

  (require 'package)
  (add-to-list 'package-archives '("melpa" .
"http://melpa.milkbox.net/packages/") t)
  (package-initialize)

Then type M-x package-list-packages
and install orgtbl-aggregate

Have fun
Thierry



Le 06/12/2014 09:05, Michael Brand a écrit :
> Hi Eric
>
> On Sat, Dec 6, 2014 at 6:42 AM, Eric Abrahamsen <eric@ericabrahamsen.net> wrote:
>> #+NAME: counts
>> | Piece          |  Chars | Lines | Translator       |
>> +----------------+--------+-------+------------------+
>> | 凤凰           |        |    84 | Austin Woerner   |
>> | 王血           |   6633 |       | Eric Abrahamsen  |
>> | 赵氏孤儿       |  16984 |       | Canaan Morse     |
>> | 山鬼故家       |        |    24 | Lucas Klein      |
>> | 寂静何其深沉   |        |    10 | Lucas Klein      |
>>
>> And the second:
>>
>> #+NAME: payments
>> #+CONSTANTS: prose=0.7 poetry=10
>> | Translator       | Total Chars | Total Lines | Payment |
>> |------------------+-------------+-------------+---------|
>> | Austin Woerner   |             |             |         |
>> | Eric Abrahamsen  |             |             |         |
>> | Canaan Morse     |             |             |         |
>> | Lucas Klein      |             |             |         |
> Thierry Banel recently announced orgaggregate:
> http://lists.gnu.org/archive/cgi-bin/namazu.cgi?idxname=emacs-orgmode&max=100&sort=date:late&query=%2Bsubject:"%5BANN%5D+Aggregate+Table"
>
> For a solution with only built-in functionality see the example
> test-org-table/org-lookup-all in the Org source file
> testing/lisp/test-org-table.el
> http://orgmode.org/w/org-mode.git?p=org-mode.git;a=blob;f=testing/lisp/test-org-table.el
>
> Michael
>
>

Re: table formula help...

[Eric Abrahamsen]

Thierry Banel <tbanelwebmin@free.fr> writes:

> To elaborate on Michael's first suggestion,
> we will first give a name ("work") to the table:
>
>   #+TBLNAME: work
>   | Chars | Lines | Translator      |
>
>   |-------+-------+-----------------|
>   |       |    84 | Austin Woerner  |
>   |  6633 |       | Eric Abrahamsen |
>   | 16984 |       | Canaan Morse    |
>   |       |    24 | Lucas Klein     |
>   |       |    10 | Lucas Klein     |
>
> Then typing C-c C-x i and answering the wizard questions,
> we get a new table as follow:
>
>   #+BEGIN: aggregate :table "work" :cols "Translator sum(Chars) sum(Lines)"
>   | Translator      | sum(Chars) | sum(Lines) |
>
>   |-----------------+------------+------------|
>   | Austin Woerner  | NA         | 84         |
>   | Eric Abrahamsen | 6633       | NA         |
>   | Canaan Morse    | 16984      | NA         |
>   | Lucas Klein     | 2 NA       | 34         |
>   #+END:
>
> Whenever you change the "work" table,
> you can easily refresh the aggregated table by typing C-c C-c

Whoa, that's really intense, going to play with it for a bit...

Thanks!

> To install the orgtbl-aggregate module,
> add those lines to your .emacs:
>
>   (require 'package)
>   (add-to-list 'package-archives '("melpa" .
> "http://melpa.milkbox.net/packages/") t)
>   (package-initialize)
>
> Then type M-x package-list-packages
> and install orgtbl-aggregate
>
> Have fun
> Thierry
>
>
>
> Le 06/12/2014 09:05, Michael Brand a écrit :
>> Hi Eric
>>
>> On Sat, Dec 6, 2014 at 6:42 AM, Eric Abrahamsen <eric@ericabrahamsen.net> wrote:
>>> #+NAME: counts
>>> | Piece          |  Chars | Lines | Translator       |
>>> +----------------+--------+-------+------------------+
>>> | 凤凰           |        |    84 | Austin Woerner   |
>>> | 王血           |   6633 |       | Eric Abrahamsen  |
>>> | 赵氏孤儿       |  16984 |       | Canaan Morse     |
>>> | 山鬼故家       |        |    24 | Lucas Klein      |
>>> | 寂静何其深沉   |        |    10 | Lucas Klein      |
>>>
>>> And the second:
>>>
>>> #+NAME: payments
>>> #+CONSTANTS: prose=0.7 poetry=10
>>> | Translator       | Total Chars | Total Lines | Payment |
>>> |------------------+-------------+-------------+---------|
>>> | Austin Woerner   |             |             |         |
>>> | Eric Abrahamsen  |             |             |         |
>>> | Canaan Morse     |             |             |         |
>>> | Lucas Klein      |             |             |         |
>> Thierry Banel recently announced orgaggregate:
>> http://lists.gnu.org/archive/cgi-bin/namazu.cgi?idxname=emacs-orgmode&max=100&sort=date:late&query=%2Bsubject:"%5BANN%5D+Aggregate+Table"
>>
>> For a solution with only built-in functionality see the example
>> test-org-table/org-lookup-all in the Org source file
>> testing/lisp/test-org-table.el
>> http://orgmode.org/w/org-mode.git?p=org-mode.git;a=blob;f=testing/lisp/test-org-table.el
>>
>> Michael
>>
>>

Re: table formula help...

[Eric Abrahamsen]

[-- Attachment #1: Type: text/plain, Size: 2305 bytes --]

Thierry Banel <tbanelwebmin@free.fr> writes:

> To elaborate on Michael's first suggestion,
> we will first give a name ("work") to the table:
>
>   #+TBLNAME: work
>   | Chars | Lines | Translator      |
>
>   |-------+-------+-----------------|
>   |       |    84 | Austin Woerner  |
>   |  6633 |       | Eric Abrahamsen |
>   | 16984 |       | Canaan Morse    |
>   |       |    24 | Lucas Klein     |
>   |       |    10 | Lucas Klein     |
>
> Then typing C-c C-x i and answering the wizard questions,
> we get a new table as follow:
>
>   #+BEGIN: aggregate :table "work" :cols "Translator sum(Chars) sum(Lines)"
>   | Translator      | sum(Chars) | sum(Lines) |
>
>   |-----------------+------------+------------|
>   | Austin Woerner  | NA         | 84         |
>   | Eric Abrahamsen | 6633       | NA         |
>   | Canaan Morse    | 16984      | NA         |
>   | Lucas Klein     | 2 NA       | 34         |
>   #+END:
>
> Whenever you change the "work" table,
> you can easily refresh the aggregated table by typing C-c C-c
>
> To install the orgtbl-aggregate module,
> add those lines to your .emacs:
>
>   (require 'package)
>   (add-to-list 'package-archives '("melpa" .
> "http://melpa.milkbox.net/packages/") t)
>   (package-initialize)
>
> Then type M-x package-list-packages
> and install orgtbl-aggregate
>
> Have fun
> Thierry

Okay, having fun! This seems like exactly what I was after. A couple of
things:

I've attached a patch replacing some [a-z] regexps with [:word:], so
that column names can be written in scripts other than ascii (my tables
above are actually all in Chinese). I hope that's acceptable.

I've changed blank fields to "0" so that I don't get the "NA" strings.

Now I'd like to do more calculations based on the rows in the sum(Chars)
and sum(Lines) columns.

Ideally I could add a fourth column to the aggregate table, calculated
from rows in the previous two. Is there any way to make a column
specification that refers to columns in the same table? The fourth
column would be defined as:

(sum(prod($2 0.7) prod($3 10))

Where $2 and $3 refer to cells in the aggregated table.

I made that up -- I don't even know if it's valid calc syntax. But
that's the idea. Or perhaps I should be making a third table based on
this second one?

Thanks again!

Eric


[-- Warning: decoded text below may be mangled, UTF-8 assumed --]
[-- Attachment #2: 0001-Modify-regexps-to-find-word-instead-of-a-zA-Z.patch --]
[-- Type: text/x-diff, Size: 1686 bytes --]

From a5e50914e431420a39e544b3a05b72169828e269 Mon Sep 17 00:00:00 2001
From: Eric Abrahamsen <eric@ericabrahamsen.net>
Date: Sun, 7 Dec 2014 10:56:04 +0800
Subject: [PATCH] Modify regexps to find [:word:] instead of a-zA-Z

* orgtbl-aggregate.el (orgtbl-to-aggregated-table-parse-spec): Allow
  column names in scripts other than ASCII.
---
 orgtbl-aggregate.el | 6 +++---
 1 file changed, 3 insertions(+), 3 deletions(-)

diff --git a/orgtbl-aggregate.el b/orgtbl-aggregate.el
index b228545..af8fc12 100644
--- a/orgtbl-aggregate.el
+++ b/orgtbl-aggregate.el
@@ -273,7 +273,7 @@ or 0 for the special 'hline column."
     (cond
      ((string-match "^count()$" column)
       'count)
-     ((string-match "^\\([a-z]+\\)(\\([a-zA-Z0-9_$]+\\))$" column)
+     ((string-match "^\\([[:word:]]+\\)(\\([[:word:]0-9_$]+\\))$" column)
       (setq id (intern (match-string 1 column)))
       (unless (memq id validid1)
 	(error
@@ -285,7 +285,7 @@ or 0 for the special 'hline column."
 	     table
 	     t)))
      ((string-match
-       "^\\([a-z]+\\)(\\([a-zA-Z0-9_$]+\\)[*,]\\([a-zA-Z0-9_$]+\\))$"
+       "^\\([[:word:]]+\\)(\\([[:word:]0-9_$]+\\)[*,]\\([[:word:]0-9_$]+\\))$"
        column)
       (setq id (intern (match-string 1 column)))
       (unless (memq id validid2)
@@ -297,7 +297,7 @@ or 0 for the special 'hline column."
 	(list id
 	      (orgtbl-to-aggregated-table-colname-to-int a table t)
 	      (orgtbl-to-aggregated-table-colname-to-int b table t))))
-     ((string-match "^\\([a-zA-Z0-9_$]+\\)$" column)
+     ((string-match "^\\([[:word:]0-9_$]+\\)$" column)
       (orgtbl-to-aggregated-table-colname-to-int
        (match-string 1 column)
        table
-- 
2.1.3

Re: table formula help...

[Thierry Banel]

Le 07/12/2014 04:25, Eric Abrahamsen a écrit :
>
> Okay, having fun! This seems like exactly what I was after. A couple of
> things:
>
> I've attached a patch replacing some [a-z] regexps with [:word:], so
> that column names can be written in scripts other than ascii (my tables
> above are actually all in Chinese). I hope that's acceptable.

Not only it is acceptable, it is also very welcome!
Your patch has been pushed to GitHub along with an updated unittests.org
(https://github.com/tbanel/orgaggregate).
Wait for a couple of hours for Melpa to refresh
(http://melpa.org).

> I've changed blank fields to "0" so that I don't get the "NA" strings.
>
> Now I'd like to do more calculations based on the rows in the sum(Chars)
> and sum(Lines) columns.
>
> Ideally I could add a fourth column to the aggregate table, calculated
> from rows in the previous two. Is there any way to make a column
> specification that refers to columns in the same table? The fourth
> column would be defined as:
>
> (sum(prod($2 0.7) prod($3 10))
>
> Where $2 and $3 refer to cells in the aggregated table.

It would be great of course. However I don't know how to do that
cleanly. If we want total flexibility for the formulas, we will end up
duplicating the native features of Org spreadsheet.

> I made that up -- I don't even know if it's valid calc syntax. But
> that's the idea. Or perhaps I should be making a third table based on
> this second one?

This would be the easiest for the time being.

You can also add columns and formulas to the 2nd table in the usual way
(Alt-Shift-RightArrow, then C-c =). But beware that those additions will
be cleared by an aggregate refresh.

> Thanks again!
>
> Eric
>

You are welcome
Thierry

Re: table formula help...

[Michael Brand]

Hi Thierry

On Sun, Dec 7, 2014 at 4:25 AM, Eric Abrahamsen <eric@ericabrahamsen.net> wrote:
> Thierry Banel <tbanelwebmin@free.fr> writes:
>> [...]
>>   #+BEGIN: aggregate :table "work" :cols "Translator sum(Chars) sum(Lines)"
>>   | Translator      | sum(Chars) | sum(Lines) |
>>
>>   |-----------------+------------+------------|
>>   | Austin Woerner  | NA         | 84         |
>>   | Eric Abrahamsen | 6633       | NA         |
>>   | Canaan Morse    | 16984      | NA         |
>>   | Lucas Klein     | 2 NA       | 34         |
>>   #+END:
>> [...]
> I've changed blank fields to "0" so that I don't get the "NA" strings.
>> [...]

I suggest that orgaggregate leaves such fields empty instead of "NA".
This way the user gets a choice how to deal with them by adding e. g.
EN or not as TBLFM format specifier:

| Translator      | sum(Chars) | sum(Lines) |
|-----------------+------------+------------|
| Austin Woerner  |            |         84 |
| Eric Abrahamsen |       6633 |            |
| Canaan Morse    |      16984 |            |
| Lucas Klein     |            |         34 |
|-----------------+------------+------------|
|                 |    5904.25 |         59 |
#+TBLFM: @>$2 = vmean(@I..@II); EN :: @>$3 = vmean(@I..@II)

Re: table formula help...

[Michael Brand]

Hi Thierry

On Sun, Dec 7, 2014 at 10:20 AM, Thierry Banel <tbanelwebmin@free.fr> wrote:
> Le 07/12/2014 04:25, Eric Abrahamsen a écrit :
>> [...]
>> (sum(prod($2 0.7) prod($3 10))
>>
>> Where $2 and $3 refer to cells in the aggregated table.
>
> It would be great of course. However I don't know how to do that
> cleanly. If we want total flexibility for the formulas, we will end up
> duplicating the native features of Org spreadsheet.

I suggest that orgaggregate lets the user specify a TBLFM to be
inserted and updated. Missing target columns are added automatically,
it would result in

#+BEGIN: aggregate :table "work" :cols "Translator sum(Chars) sum(Lines)"
| Translator      | sum(Chars) | sum(Lines) |          |
|-----------------+------------+------------+----------|
| Austin Woerner  |            |         84 |   840.00 |
| Eric Abrahamsen |       6633 |            |  4643.10 |
| Canaan Morse    |      16984 |            | 11888.80 |
| Lucas Klein     |            |         34 |   340.00 |
#+TBLFM: $4 = $2 * 0.7 + $3 * 10; %.2f
#+END:

Re: table formula help...

[Thierry Banel]

Le 07/12/2014 10:39, Michael Brand a écrit :
> Hi Thierry
>
>
> I suggest that orgaggregate leaves such fields empty instead of "NA".
> This way the user gets a choice how to deal with them by adding e. g.
> EN or not as TBLFM format specifier:
>
> | Translator      | sum(Chars) | sum(Lines) |
> |-----------------+------------+------------|
> | Austin Woerner  |            |         84 |
> | Eric Abrahamsen |       6633 |            |
> | Canaan Morse    |      16984 |            |
> | Lucas Klein     |            |         34 |
> |-----------------+------------+------------|
> |                 |    5904.25 |         59 |
> #+TBLFM: @>$2 = vmean(@I..@II); EN :: @>$3 = vmean(@I..@II)
>

I didn't knew about the EN specifier. Thanks Michael for pointing to it.
Your suggestion is quite interesting. I will take a look.
We could also handle empty fields as zero.
Let us think about the best to do.

Re: table formula help...

[Thierry Banel]

Le 07/12/2014 10:40, Michael Brand a écrit :
>
> I suggest that orgaggregate lets the user specify a TBLFM to be
> inserted and updated. Missing target columns are added automatically,
> it would result in
>
> #+BEGIN: aggregate :table "work" :cols "Translator sum(Chars) sum(Lines)"
> | Translator      | sum(Chars) | sum(Lines) |          |
> |-----------------+------------+------------+----------|
> | Austin Woerner  |            |         84 |   840.00 |
> | Eric Abrahamsen |       6633 |            |  4643.10 |
> | Canaan Morse    |      16984 |            | 11888.80 |
> | Lucas Klein     |            |         34 |   340.00 |
> #+TBLFM: $4 = $2 * 0.7 + $3 * 10; %.2f
> #+END:
>

I do agree, Michael, this is probably the way to go.
Maybe the TBLFM should be taken care of for any kind of dynamic blocks,
not only aggregate (columnview, clocktable, propview, invoice,
transpose, and any future dynamic block).

Regards
Thierry

Re: table formula help...

[Michael Brand]

Hi Thierry

On Sun, Dec 7, 2014 at 11:02 AM, Thierry Banel <tbanelwebmin@free.fr> wrote:
> Maybe the TBLFM should be taken care of for any kind of dynamic blocks,
> not only aggregate (columnview, clocktable, propview, invoice,
> transpose, and any future dynamic block).

Yes, see e. g. ":formula" in
http://orgmode.org/manual/The-clock-table.html

    Content of a `#+TBLFM' line to be added and evaluated.

and

    If you do not specify a formula here, any existing formula
    below the clock table will survive updates and be evaluated.

Michael

Re: table formula help...

[Thierry Banel]

Le 07/12/2014 11:26, Michael Brand a écrit :
> Hi Thierry
>
> On Sun, Dec 7, 2014 at 11:02 AM, Thierry Banel <tbanelwebmin@free.fr> wrote:
>> Maybe the TBLFM should be taken care of for any kind of dynamic blocks,
>> not only aggregate (columnview, clocktable, propview, invoice,
>> transpose, and any future dynamic block).
> Yes, see e. g. ":formula" in
> http://orgmode.org/manual/The-clock-table.html
>
>     Content of a `#+TBLFM' line to be added and evaluated.
>
> and
>
>     If you do not specify a formula here, any existing formula
>     below the clock table will survive updates and be evaluated.
>
> Michael
>

Excellent!
I've looked at org-clock.el, and understood the handling of TBLFMT.
I will mimic it in aggregate.

Thanks, Michael, for pointing to this relevant spot.
Thierry

Re: table formula help...

[Thierry Banel]

Le 07/12/2014 11:26, Michael Brand a écrit :
> Hi Thierry
>
> On Sun, Dec 7, 2014 at 11:02 AM, Thierry Banel <tbanelwebmin@free.fr> wrote:
>> Maybe the TBLFM should be taken care of for any kind of dynamic blocks,
>> not only aggregate (columnview, clocktable, propview, invoice,
>> transpose, and any future dynamic block).
> Yes, see e. g. ":formula" in
> http://orgmode.org/manual/The-clock-table.html
>
>     Content of a `#+TBLFM' line to be added and evaluated.
>
> and
>
>     If you do not specify a formula here, any existing formula
>     below the clock table will survive updates and be evaluated.
>
> Michael
>
The new features (:formula parameter and TBLFM survival) have been pushed to https://github.com/tbanel/orgaggregate. The unittests.org file has been updated. The http://melpa.org repository will reflect the change shortly.

This was easy to implement following the org-clock.el example.

Thanks, Michael, for this useful improvement.
Thierry

Re: table formula help...

[Michael Brand]

Hi Thierry

On Sun, Dec 7, 2014 at 5:13 PM, Thierry Banel <tbanelwebmin@free.fr> wrote:
> The new features (:formula parameter and TBLFM survival) have been
> pushed to https://github.com/tbanel/orgaggregate. The unittests.org
> file has been updated. The http://melpa.org repository will reflect
> the change shortly.

Cool, thank you. As a hint for the user you could add something like
"@<$4 = string("header") etc. to the TBLFMs in the unittests.org.

Michael

Re: table formula help...

[Thierry Banel]

Le 07/12/2014 10:39, Michael Brand a écrit :
> Hi Thierry
>
> I suggest that orgaggregate leaves such fields empty instead of "NA".
> This way the user gets a choice how to deal with them by adding e. g.
> EN or not as TBLFM format specifier:
>
> | Translator      | sum(Chars) | sum(Lines) |
> |-----------------+------------+------------|
> | Austin Woerner  |            |         84 |
> | Eric Abrahamsen |       6633 |            |
> | Canaan Morse    |      16984 |            |
> | Lucas Klein     |            |         34 |
> |-----------------+------------+------------|
> |                 |    5904.25 |         59 |
> #+TBLFM: @>$2 = vmean(@I..@II); EN :: @>$3 = vmean(@I..@II)
>

Done.
Probably this can be discussed further.
For the time being, there is no longer any "NA".

Re: table formula help...

[Eric Abrahamsen]

Thierry Banel <tbanelwebmin@free.fr> writes:

> Le 07/12/2014 10:40, Michael Brand a écrit :
>>
>> I suggest that orgaggregate lets the user specify a TBLFM to be
>> inserted and updated. Missing target columns are added automatically,
>> it would result in
>>
>> #+BEGIN: aggregate :table "work" :cols "Translator sum(Chars) sum(Lines)"
>> | Translator      | sum(Chars) | sum(Lines) |          |
>> |-----------------+------------+------------+----------|
>> | Austin Woerner  |            |         84 |   840.00 |
>> | Eric Abrahamsen |       6633 |            |  4643.10 |
>> | Canaan Morse    |      16984 |            | 11888.80 |
>> | Lucas Klein     |            |         34 |   340.00 |
>> #+TBLFM: $4 = $2 * 0.7 + $3 * 10; %.2f
>> #+END:
>>
>
> I do agree, Michael, this is probably the way to go.
> Maybe the TBLFM should be taken care of for any kind of dynamic blocks,
> not only aggregate (columnview, clocktable, propview, invoice,
> transpose, and any future dynamic block).
>
> Regards
> Thierry

I thought of the same thing when I first started playing, but my plans
to provide a "quick patch" foundered when I looked at the code. Who knew
that, when I got up in the morning, it would all be implemented and
waiting in Melpa! Thanks so much to you both! I think this really
improves the usefulness of this package.

Eric

Re: table formula help...

[Michael Brand]

Hi Thierry

On Sun, Dec 7, 2014 at 10:57 PM, Thierry Banel <tbanelwebmin@free.fr> wrote:
> Done.
> Probably this can be discussed further.

I hope that there are more opinions than only mine.

> For the time being, there is no longer any "NA".

Good. My opinion is about to replace it with what.

https://github.com/tbanel/orgaggregate#empty-and-malformed-input-cells
says:

    An input cell may be empty. In this case, it is silently replaced
    by zero. In an output cell, if the computed result is zero, it not
    output, leaving a blank cell. This allows for empty input cells to
    result in empty output cells.

I understand the intention very well (the proof is in the references
at the bottom ;-) ). Nevertheless I find the compromise goes too far
when in the following example the sum and mean for a0 and b0 are
empty. I would prefer 0 there even when for the time being it is at
the cost of that c results in a sum and mean of 0 too.

It would mean to remove the above "In an output cell, if the computed
result is zero, it not output, leaving a blank cell. [...]". Or - when
you want to bother with the implementation - to change it into "If all
input cells of a computation are empty then the result cell is left
empty.".

#+TBLNAME: original
| Item | Value |
|------+-------|
| a2   |     1 |
| a2   |     1 |
| a0   |    -1 |
| a0   |     1 |
| b2   |     2 |
| b2   |       |
| b0   |     0 |
| b0   |       |
| c    |       |
| c    |       |

#+BEGIN: aggregate :table original :cols "Item sum(Value) mean(Value)"
| Item | sum(Value) | mean(Value) |
|------+------------+-------------|
| a2   |          2 |           1 |
| a0   |            |             |
| b2   |          2 |           1 |
| b0   |            |             |
| c    |            |             |
#+END

Could you please add this example or something in the same sense to
the unittests.org before any other change?

https://github.com/tbanel/orgaggregate#empty-and-malformed-input-cells
continues:

    The empty cell handling may be changed in the futur. For instance,
    we may want to compute an average aggregation ignoring empty cells
    (right now, empty cells contribute to the average by pulling it
    toward zero).

As I understand orgaggregate already uses Calc vectors. Maybe then it
could use and benefit from org-table-make-reference which has the
necessary arguments and asks for a Lisp list? See
testing/lisp/test-org-table.el:
- The application of the mode string variations for TBLFM are in
  test-org-table/references/mode-string-*.
- The same variations for org-table-make-reference are in
  test-org-table/org-table-make-reference/mode-string-*

Michael

Re: table formula help...

[Thierry Banel]

Le 07/12/2014 17:48, Michael Brand a écrit :
> Hi Thierry
>
> On Sun, Dec 7, 2014 at 5:13 PM, Thierry Banel <tbanelwebmin@free.fr> wrote:
>> The new features (:formula parameter and TBLFM survival) have been
>> pushed to https://github.com/tbanel/orgaggregate. The unittests.org
>> file has been updated. The http://melpa.org repository will reflect
>> the change shortly.
> Cool, thank you. As a hint for the user you could add something like
> "@<$4 = string("header") etc. to the TBLFMs in the unittests.org.
>
> Michael
>

Good suggestion.
What stops me now is that the @<$4 formula (or @1$4) does not work,
probably because of a bug.
More investigation is needed...

Re: table formula help...

[Thierry Banel]

Le 08/12/2014 19:02, Michael Brand a écrit :
>
> Good. My opinion is about to replace it with what.
>
> https://github.com/tbanel/orgaggregate#empty-and-malformed-input-cells
> says:
>
>     An input cell may be empty. In this case, it is silently replaced
>     by zero. In an output cell, if the computed result is zero, it not
>     output, leaving a blank cell. This allows for empty input cells to
>     result in empty output cells.
>
> I understand the intention very well (the proof is in the references
> at the bottom ;-) ). Nevertheless I find the compromise goes too far
> when in the following example the sum and mean for a0 and b0 are
> empty. I would prefer 0 there even when for the time being it is at
> the cost of that c results in a sum and mean of 0 too.

You instantly found the weakness of the current design!

> It would mean to remove the above "In an output cell, if the computed
> result is zero, it not output, leaving a blank cell. [...]".

I'm inclined to agree with you. Dropping the /zero output becomes blank/
feature would be the best short-term compromise.

>  Or - when
> you want to bother with the implementation - to change it into "If all
> input cells of a computation are empty then the result cell is left
> empty.".

Yes, this is the correct specification. Testing that *all* inputs are blank.
Unfortunately, implementing this is a lot of work, because we need to
create new data structures to remember whether input fields are blank.

> #+TBLNAME: original
> | Item | Value |
> |------+-------|
> | a2   |     1 |
> | a2   |     1 |
> | a0   |    -1 |
> | a0   |     1 |
> | b2   |     2 |
> | b2   |       |
> | b0   |     0 |
> | b0   |       |
> | c    |       |
> | c    |       |
>
> #+BEGIN: aggregate :table original :cols "Item sum(Value) mean(Value)"
> | Item | sum(Value) | mean(Value) |
> |------+------------+-------------|
> | a2   |          2 |           1 |
> | a0   |            |             |
> | b2   |          2 |           1 |
> | b0   |            |             |
> | c    |            |             |
> #+END
>
> Could you please add this example or something in the same sense to
> the unittests.org before any other change?

Good idea. I'll do that.

> https://github.com/tbanel/orgaggregate#empty-and-malformed-input-cells
> continues:
>
>     The empty cell handling may be changed in the futur. For instance,
>     we may want to compute an average aggregation ignoring empty cells
>     (right now, empty cells contribute to the average by pulling it
>     toward zero).
>
> As I understand orgaggregate already uses Calc vectors.

Yes.

> Maybe then it
> could use and benefit from org-table-make-reference which has the
> necessary arguments and asks for a Lisp list? See
> testing/lisp/test-org-table.el:
> - The application of the mode string variations for TBLFM are in
>   test-org-table/references/mode-string-*.
> - The same variations for org-table-make-reference are in
>   test-org-table/org-table-make-reference/mode-string-*
>

Definitely interesting. Someone else has already bumped into the empty
cells thing.

> Michael

Thierry

Re: table formula help...

[Thierry Banel]

Le 08/12/2014 22:12, Thierry Banel a écrit :
> Le 07/12/2014 17:48, Michael Brand a écrit :
>> Hi Thierry
>>
>> On Sun, Dec 7, 2014 at 5:13 PM, Thierry Banel <tbanelwebmin@free.fr> wrote:
>>> The new features (:formula parameter and TBLFM survival) have been
>>> pushed to https://github.com/tbanel/orgaggregate. The unittests.org
>>> file has been updated. The http://melpa.org repository will reflect
>>> the change shortly.
>> Cool, thank you. As a hint for the user you could add something like
>> "@<$4 = string("header") etc. to the TBLFMs in the unittests.org.
>>
>> Michael
>>
> Good suggestion.
> What stops me now is that the @<$4 formula (or @1$4) does not work,
> probably because of a bug.
> More investigation is needed...
>
>
>
>

I found this thread in The List started by Dima Kogan which is closely
related to the @1$4 issue:
http://comments.gmane.org/gmane.emacs.orgmode/91268
He submitted a patch.

Re: table formula help...

[Michael Brand]

Hi Thierry

On Mon, Dec 8, 2014 at 10:57 PM, Thierry Banel <tbanelwebmin@free.fr> wrote:
> You instantly found the weakness of the current design!

The reason follows very shortly. ;-)

> Definitely interesting. Someone else has already bumped into the empty
> cells thing.

It was me:
http://thread.gmane.org/gmane.emacs.orgmode/63559/focus=63975

Michael

Re: table formula help...

[Thierry Banel]

Le 09/12/2014 06:54, Michael Brand a écrit :
> Hi Thierry
>
> On Mon, Dec 8, 2014 at 10:57 PM, Thierry Banel <tbanelwebmin@free.fr> wrote:
>> You instantly found the weakness of the current design!
> The reason follows very shortly. ;-)
>
>> Definitely interesting. Someone else has already bumped into the empty
>> cells thing.
> It was me:
> http://thread.gmane.org/gmane.emacs.orgmode/63559/focus=63975
>
> Michael
>

Excellent. With your help, the aggregate package will reach the next level.

Re: table formula help...

[Thierry Banel]

Le 08/12/2014 19:02, Michael Brand a écrit :
> #+TBLNAME: original
> | Item | Value |
> |------+-------|
> | a2   |     1 |
> | a2   |     1 |
> | a0   |    -1 |
> | a0   |     1 |
> | b2   |     2 |
> | b2   |       |
> | b0   |     0 |
> | b0   |       |
> | c    |       |
> | c    |       |
>
> #+BEGIN: aggregate :table original :cols "Item sum(Value) mean(Value)"
> | Item | sum(Value) | mean(Value) |
> |------+------------+-------------|
> | a2   |          2 |           1 |
> | a0   |            |             |
> | b2   |          2 |           1 |
> | b0   |            |             |
> | c    |            |             |
> #+END
>
> Could you please add this example or something in the same sense to
> the unittests.org before any other change?
>

Done under the title "* Test zero output".
There is also a new "* Test empty inputs" were several empty input cases
are tested against all the available aggregations (sum, mean, max, corr,
and so on).

I will work on a new and clean design for handling empty inputs.
It shouldn't be so difficult after all.
Stay tuned.

Have fun
Thierry

Re: table formula help...

[Thierry Banel]

Ok, done.
A clean design has been implemented for handling empty cells.

Basically, empty input cells are ignored, and therefore they do not
participate in the aggregation. (However, for aggregation using two
columns (=corr(p,q)= for example), if a pair of cells contains both an
empty and a non-empty cell, then the empty one is replaced by zero.)

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.

Thanks to Michael Brand for his insight and suggestions.

Source code and documentation here:
  https://github.com/tbanel/orgaggregate

Melpa installation:
  (require 'package)
  (add-to-list 'package-archives
    '("melpa" . "http://melpa.milkbox.net/packages")
    t)
  (package-initialize)
  M-x package-list-packages
  install orgtbl-aggregate

Have fun
Thierry

Re: table formula help...

[Michael Brand]

Hi Thierry

On Tue, Dec 9, 2014 at 11:35 PM, Thierry Banel <tbanelwebmin@free.fr> wrote:
> A clean design has been implemented for handling empty cells.

Very good.

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

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:

#+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

Re: table formula help...

[Michael Brand]

Hi Thierry

On Mon, Dec 8, 2014 at 11:32 PM, Thierry Banel <tbanelwebmin@free.fr> wrote:
> Le 08/12/2014 22:12, Thierry Banel a écrit :
>> Le 07/12/2014 17:48, Michael Brand a écrit :
>>> Cool, thank you. As a hint for the user you could add something like
>>> "@<$4 = string("header") etc. to the TBLFMs in the unittests.org.
>>
>> Good suggestion.
>> What stops me now is that the @<$4 formula (or @1$4) does not work,
>> probably because of a bug.
>> More investigation is needed...
>
> I found this thread in The List started by Dima Kogan which is closely
> related to the @1$4 issue:
> http://comments.gmane.org/gmane.emacs.orgmode/91268
> He submitted a patch.

An additional, more convenient and more natural way to add headers for
a TBLFM column formula could be to add support for this (matches the
TBLFM of the example of my other post from today):

    :cols ("Item" "sum(Value_1)" "sum(Value_2)" "Mean of non-empty"
"Mean of all" "Compare sums")

Michael

Re: table formula help...

[Thierry Banel]

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

Re: table formula help...

[Michael Brand]

Hi Thierry

On Wed, Dec 10, 2014 at 11:55 PM, Thierry Banel <tbanelwebmin@free.fr> wrote:
> Le 10/12/2014 22:06, Michael Brand a écrit :
>> 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=vsum($1..$3)::$5=vmean($1..$3)::$6=vprod($1..$3)

- Isn't the above table content from a different TBLFM with a mode
  string EN for vmean?:

  #+TBLFM: $4=vsum($1..$3)::$5=vmean($1..$3);EN::$6=vprod($1..$3)

- All columns without mode string (Org >= 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=vsum($1..$3)::$5=vmean($1..$3)::$6=vprod($1..$3)::$7=vmin($1..$3)::$8=vmax($1..$3)

- All columns with mode string EN (Org >= 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=vsum($1..$3);EN::$5=vmean($1..$3);EN::$6=vprod($1..$3);EN::$7=vmin($1..$3);EN::$8=vmax($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":

   => '("" "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) => "[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" =>
   "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])") => "7"

It is the same that happens already without orgaggregate as

| Value |
|-------|
|       |
|     2 |
|-------|
|     7 |
#+TBLFM: @>$1 = 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

Re: table formula help...

[Thierry Banel]

Le 12/12/2014 18:15, Michael Brand a écrit :
> 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=vsum($1..$3)::$5=vmean($1..$3)::$6=vprod($1..$3)
> - Isn't the above table content from a different TBLFM with a mode
>   string EN for vmean?:
>
>   #+TBLFM: $4=vsum($1..$3)::$5=vmean($1..$3);EN::$6=vprod($1..$3)

Absolutely. I should not write mails so late in the night.

>
> - All columns without mode string (Org >= 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=vsum($1..$3)::$5=vmean($1..$3)::$6=vprod($1..$3)::$7=vmin($1..$3)::$8=vmax($1..$3)
>
> - All columns with mode string EN (Org >= 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=vsum($1..$3);EN::$5=vmean($1..$3);EN::$6=vprod($1..$3);EN::$7=vmin($1..$3);EN::$8=vmax($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 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.

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":
>
>    => '("" "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) => "[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" =>
>    "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])") => "7"
>
> It is the same that happens already without orgaggregate as
>
> | Value |
> |-------|
> |       |
> |     2 |
> |-------|
> |     7 |
> #+TBLFM: @>$1 = 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

Re: table formula help...

[Michael Brand]

Hi Thierry

On Fri, Dec 12, 2014 at 10:04 PM, Thierry Banel <tbanelwebmin@free.fr> wrote:
> Le 12/12/2014 18:15, Michael Brand a écrit :
> Seems doable.
> Would tie the spreadsheet and orgaggregate seamlessly.
> Very appealing!
>
> Are you willing to help me implement those steps?

Where necessary I try to help.

I would break it down to these commits and take a larger break
after 3):

1) Prepare for Calc syntax: Adapt the "(case fun [...]" of
   orgtbl-aggregate-apply-calc-*-function to move from :cols "sum(x)"
   etc. to :cols "vsum(x)" etc.

   #+BEGIN: aggregate :table test :cols ("Item" "vsum(x)")
   | Item | vsum(x) |
   |------+---------|

2) Add a separate target column header: The "=" is only a suggestion
   how to separate the target column header from the formula.

   #+BEGIN: aggregate :table test :cols ("Item" "Header = vsum(x)")
   | Item | Header |
   |------+--------|

3) Unleash complete Calc syntax: Make use of (org-table-make-reference
   with KEEP-EMPTY and NUMBERS constantly nil and use calc-eval

   #+BEGIN: aggregate :table test :cols ("Item" "Header = 2 * vsum(x)
+ 3 * vmean(y)")
   | Item | Header |
   |------+--------|

4) Add the mode strings "E" and "N": Parse and pass them to KEEP-EMPTY
   and NUMBERS of org-table-make-reference.

   #+BEGIN: aggregate :table test :cols ("Item" "Header = 2 * vsum(x)
+ 3 * vmean(y); EN")
   | Item | Header |
   |------+--------|

5) Someday add more mode strings.

Michael

Re: table formula help...

[Thierry Banel]

[-- Attachment #1: Type: text/html, Size: 1503 bytes --]

Re: table formula help...

[Eric Abrahamsen]

Thierry Banel <tbanelwebmin@free.fr> writes:

> Le 07/12/2014 04:25, Eric Abrahamsen a écrit :
>
>     
> Now I'd like to do more calculations based on the rows in the sum(Chars)
> and sum(Lines) columns.
>
>
> The new version of Aggregate supports adding new columns in the
> aggregated table. This can be achieved in two ways:
> - the :formula parameter adds a spreadsheet formula to the aggregated
> table
> - a #+TBLFM: line in the aggregated table survives refreshes.
>
> Documentation here:
> https://github.com/tbanel/orgaggregate#spreadsheet-formulas
>
> This feature was borrowed from the "clock table"
> http://orgmode.org/manual/The-clock-table.html
> Thanks to Michael Brand for pointing to it.

Very nice!