very long table calc expressions ?

very long table calc expressions ?

[Uwe Brauer]

Hi

Take the following example 


#+TBLNAME: data
 | Name | Cual 1 |
 |------+--------|
 | A    | NT     |
 | B    | NT     |
 | C    | MH     |
 | D    | AP     |
 | E    | MH     |
 | F    | SS     |
 | G    | NP     |
 | H    | NP     |
 | I    | NP     |
 |      | NT     |
 |      | AP     |
 |      | AP     |
 |      | AP     |
 |      | AP     |
 |      | SB     |


#+TBLNAME: stat-final2
|    | Frequency |
|----+-----------|
| SS |         1 |
| AP |         5 |
| NT |         3 |
| SB |         1 |
| MH |         2 |
| NP |         3 |
#+TBLFM: @>$2='(length (org-lookup-all "NP" '(remote(data,@2$2..@>I$2)) nil))::@>>$2='(length (org-lookup-all "MH" '(remote(data,@2$2..@>I$2)) nil))::@>>>$2='(length (org-lookup-all "SB" '(remote(data,@2$2..@>I$2)) nil))::@>>>>$2='(length (org-lookup-all "NT" '(remote(data,@2$2..@>I$2)) nil))::@>>>>>$2='(length (org-lookup-all "AP" '(remote(data,@2$2..@>I$2)) nil))::@>>>>>>$2='(length (org-lookup-all "SS" '(remote(data,@2$2..@>I$2)) nil))


Are there any rules to break this very long expression?

Thanks

Uwe Brauer 

Re: very long table calc expressions ?

[Thierry Banel]

Le 19/06/2018 19:21, Uwe Brauer a écrit :
>
> Hi
>
> Take the following example
>
>
> #+TBLNAME: data
>   | Name | Cual 1 |
>   |------+--------|
>   | A    | NT     |
>   | B    | NT     |
>   | C    | MH     |
>   | D    | AP     |
>   | E    | MH     |
>   | F    | SS     |
>   | G    | NP     |
>   | H    | NP     |
>   | I    | NP     |
>   |      | NT     |
>   |      | AP     |
>   |      | AP     |
>   |      | AP     |
>   |      | AP     |
>   |      | SB     |
>
>
> #+TBLNAME: stat-final2
> |    | Frequency |
> |----+-----------|
> | SS |         1 |
> | AP |         5 |
> | NT |         3 |
> | SB |         1 |
> | MH |         2 |
> | NP |         3 |
> #+TBLFM: @>$2='(length (org-lookup-all "NP" '(remote(data,@2$2..@>I$2)) nil))::@>>$2='(length (org-lookup-all "MH" '(remote(data,@2$2..@>I$2)) nil))::@>>>$2='(length (org-lookup-all "SB" '(remote(data,@2$2..@>I$2)) nil))::@>>>>$2='(length (org-lookup-all "NT" '(remote(data,@2$2..@>I$2)) nil))::@>>>>>$2='(length (org-lookup-all "AP" '(remote(data,@2$2..@>I$2)) nil))::@>>>>>>$2='(length (org-lookup-all "SS" '(remote(data,@2$2..@>I$2)) nil))
>
>
> Are there any rules to break this very long expression?
>
> Thanks
>
> Uwe Brauer
>
>
>

You may want to take a look at the orgtbl-aggregate package available on 
Melpa.

#+BEGIN: aggregate :table "data" :cols "'Cual 1' count()"
| 'Cual 1' | count() |
|----------+---------|
| NT       |       3 |
| MH       |       2 |
| AP       |       5 |
| SS       |       1 |
| NP       |       3 |
| SB       |       1 |
#+END:

It features the count() function which computes frequency, but also sum, 
mean, filtering and much more.
Documentation here: https://github.com/tbanel/orgaggregate

To gain access to the Melpa repository I have those settings in my 
.emacs file:

(require 'package)
(setq package-enable-at-startup nil)   ; To prevent initialising twice
(add-to-list 'package-archives '("gnu"   . 
"http://elpa.gnu.org/packages/"     ) t)
(add-to-list 'package-archives '("melpa" . 
"http://melpa.milkbox.net/packages/") t)
(package-initialize)

Have fun
Thierry

Re: very long table calc expressions ?

[Uwe Brauer]

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


   > Le 19/06/2018 19:21, Uwe Brauer a écrit :

   > You may want to take a look at the orgtbl-aggregate package available on 
   > Melpa.

   > #+BEGIN: aggregate :table "data" :cols "'Cual 1' count()"
   > | 'Cual 1' | count() |

   > |----------+---------|
   > | NT       |       3 |
   > | MH       |       2 |
   > | AP       |       5 |
   > | SS       |       1 |
   > | NP       |       3 |
   > | SB       |       1 |
   > #+END:

   > It features the count() function which computes frequency, but also sum, 
   > mean, filtering and much more.
   > Documentation here: https://github.com/tbanel/orgaggregate


Thanks very much. I have already installed that package but the example
presented in the documentation were too sophisticated for my purpose. May
I suggest to include this example in the documentation, because your
package is a real time saver!!


I take the opportunity to ask whether your package can also easily
deal with the following problem (which is very important to me and
drives my crazy)

Take the following table
#+TBLNAME: raw-data
| Test |
|------|
|    0 |
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
|      |
|      |
|      |

Important are the last three empty strings. 

I know, thanks to the
https://orgmode.org/worg/org-tutorials/org-lookups.html
how to count the frequencies of the numbers in the column in an interval  using the 
function 

#+TBLNAME: stat-table
#+ATTR_HTML: :border 2 :rules all :frame border
|    | lower bound | upper bound | frequency |
|----+-------------+-------------+-----------|
| NP |           0 |           0 |         1 |
| SS |           0 |         4.9 |         5 |
| AP |           5 |         6.9 |         2 |
| NT |           7 |         8.9 |         2 |
| SB |           9 |          10 |         1 |
#+TBLFM: $4='(length (org-lookup-all '($2 $3) '(remote(raw-data,@2$1..@>$1)) nil 'in-interval));N


#+BEGIN_SRC emacs-lisp
  (defun in-interval (bounds el)
    (and (>= el (car bounds)) (<= el (cadr bounds))))
#+END_SRC

However that function can not deal with empty strings (I could replace
the empty string by some string but then the function does not
distinguish between the string and 0). So I want that the entry NP
represents the counts of the empty strings: there are three in that column.

So can your package deal with this situation?

Thanks again

Uwe Brauer


[-- Attachment #2: smime.p7s --]
[-- Type: application/pkcs7-signature, Size: 5025 bytes --]

Re: very long table calc expressions ?

[Thierry Banel]

Le 20/06/2018 11:09, Uwe Brauer a écrit :
>     > Le 19/06/2018 19:21, Uwe Brauer a écrit :
>
>     > You may want to take a look at the orgtbl-aggregate package available on
>     > Melpa.
>
>
>
> Thanks very much. I have already installed that package but the example
> presented in the documentation were too sophisticated for my purpose. May
> I suggest to include this example in the documentation, because your
> package is a real time saver!!
>
I included such an example (and added your name to the contributors list).

> I take the opportunity to ask whether your package can also easily
> deal with the following problem (which is very important to me and
> drives my crazy)
Not easily. orgaggregate groups rows using equality, whereas you need to 
group rows based on intervals (between 5 and 6.9 for example).

But you can prepare the data table with a new, computed column, telling 
in which interval each value falls:

#+TBLNAME: raw-data
| Test | Interval |
|------+----------|
|    0 | SS       |
|    1 | SS       |
|    2 | SS       |
|    3 | SS       |
|    4 | SS       |
|    5 | AP       |
|    6 | AP       |
|    7 | NT       |
|    8 | NT       |
|    9 | SB       |
|      | NP       |
|      | NP       |
|      | NP       |
#+TBLFM: $2= "nan"="$1" ? string("NP") : 0<=$1 && $1<=4.9 ? string("SS") 
: 5<=$1 && $1<=6.9 ? string("AP") : 7<=$1 && $1<=8.9 ? string("NT") : 
9<=$1 && $1<=10  ? string("SB") : string("OTHER");E

And then orgaggregate can handle it:

#+BEGIN: aggregate :table "raw-data" :cols "Interval count()"
| Interval | count() |
|----------+---------|
| SS       |       5 |
| AP       |       2 |
| NT       |       2 |
| SB       |       1 |
| NP       |       3 |
#+END:

>
> #+TBLFM: $4='(length (org-lookup-all '($2 $3) '(remote(raw-data,@2$1..@>$1)) nil 'in-interval));N
>
>
> However that function can not deal with empty strings (I could replace
> the empty string by some string but then the function does not
> distinguish between the string and 0). So I want that the entry NP
> represents the counts of the empty strings: there are three in that column.
>
> So can your package deal with this situation?
>
> Thanks again
>
> Uwe Brauer
>
I don't have a solution out of my hat. But I guess the problem you face 
may originate from the remote() function. It filters out empty values. 
However you can change this behavior using the ;E modifier.

| [0, 1, 2, 3, 4, 5, 6, 7, 8, 9] |
#+TBLFM: $1=remote(raw-data,@2$1..@>$1)

| [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, nan, nan, nan] |
#+TBLFM: $1=remote(raw-data,@2$1..@>$1);E

Regards
Thierry

Re: very long table calc expressions ?

[Uwe Brauer]

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


   > Le 20/06/2018 11:09, Uwe Brauer a écrit :
   > I included such an example (and added your name to the contributors list).

Thanks! I don't feel that I am a contributor, but thanks very much.


   > Not easily. orgaggregate groups rows using equality, whereas you need to 
   > group rows based on intervals (between 5 and 6.9 for example).

   > But you can prepare the data table with a new, computed column, telling 
   > in which interval each value falls:

   > #+TBLNAME: raw-data
   > | Test | Interval |

   > |------+----------|
   > |    0 | SS       |
   > |    1 | SS       |
   > |    2 | SS       |
   > |    3 | SS       |
   > |    4 | SS       |
   > |    5 | AP       |
   > |    6 | AP       |
   > |    7 | NT       |
   > |    8 | NT       |
   > |    9 | SB       |
   > |      | NP       |
   > |      | NP       |
   > |      | NP       |

   > #+TBLFM: $2= "nan"="$1" ? string("NP") : 0<=$1 && $1<=4.9 ? string("SS") 
   > : 5<=$1 && $1<=6.9 ? string("AP") : 7<=$1 && $1<=8.9 ? string("NT") : 

   > 9<=$1 && $1<=10  ? string("SB") : string("OTHER");E

   > And then orgaggregate can handle it:

   > #+BEGIN: aggregate :table "raw-data" :cols "Interval count()"
   > | Interval | count() |

   > |----------+---------|
   > | SS       |       5 |
   > | AP       |       2 |
   > | NT       |       2 |
   > | SB       |       1 |
   > | NP       |       3 |
   > #+END:


This is great. I have to play a around with it a bit but it looks
*precisely* what I was looking for since quite a while.

I have to admitd that the complicated structure

#+TBLFM: $2= "nan"="$1" ? string("NP") : 0<=$1 && $1<=4.9 ? string("SS")  

I would have never found myself.

Thanks very much. Again I have the feeling that this is a real world
example worth to be mentioned in the documentation (such example make
your package so much more attractive because I presume most users feel
like me, they are overwhelmed by its potential but on able to really use
it). But maybe I am a bit biased :-D

I have not looked into your second part of your answer since the first
seems so useful.

Regards

Uwe 

[-- Attachment #2: smime.p7s --]
[-- Type: application/pkcs7-signature, Size: 5025 bytes --]

Re: very long table calc expressions ?

[Bernt Hansen]

Uwe Brauer <oub@mat.ucm.es> writes:

> #+TBLNAME: stat-final2
> |    | Frequency |
> |----+-----------|
> | SS |         1 |
> | AP |         5 |
> | NT |         3 |
> | SB |         1 |
> | MH |         2 |
> | NP |         3 |
> #+TBLFM: @>$2='(length (org-lookup-all "NP" '(remote(data,@2$2..@>I$2)) nil))::@>>$2='(length (org-lookup-all "MH" '(remote(data,@2$2..@>I$2)) nil))::@>>>$2='(length (org-lookup-all "SB" '(remote(data,@2$2..@>I$2)) nil))::@>>>>$2='(length (org-lookup-all "NT" '(remote(data,@2$2..@>I$2)) nil))::@>>>>>$2='(length (org-lookup-all "AP" '(remote(data,@2$2..@>I$2)) nil))::@>>>>>>$2='(length (org-lookup-all "SS" '(remote(data,@2$2..@>I$2)) nil))
>
>
> Are there any rules to break this very long expression?

Hi Uwe,

You can edit or view the table formulas with C-c ' in case that helps.
This splits the formulas one per line for editing.

Regards,
Bernt

Re: very long table calc expressions ?

[Uwe Brauer]

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


   > Uwe Brauer <oub@mat.ucm.es> writes:

Hello Bernt,
   > Hi Uwe,

   > You can edit or view the table formulas with C-c ' in case that helps.
   > This splits the formulas one per line for editing.

Thanks for pointing this out to me, but I seem unable to do it. 

When I use C-c ' I obtain that very long formula in a special buffer,
but it is again a long line. Breaking this line and then using C-c C-c
cuts the second line. So this is not what I was looking for, but maybe I
misunderstood something fundamental.

Uwe 

[-- Attachment #2: smime.p7s --]
[-- Type: application/pkcs7-signature, Size: 5025 bytes --]

Re: very long table calc expressions ?

[Bernt Hansen]

Uwe Brauer <oub@mat.ucm.es> writes:

>    > Uwe Brauer <oub@mat.ucm.es> writes:
>
> Hello Bernt,
>    > Hi Uwe,
>
>    > You can edit or view the table formulas with C-c ' in case that helps.
>    > This splits the formulas one per line for editing.
>
> Thanks for pointing this out to me, but I seem unable to do it. 
>
> When I use C-c ' I obtain that very long formula in a special buffer,
> but it is again a long line. Breaking this line and then using C-c C-c
> cuts the second line. So this is not what I was looking for, but maybe I
> misunderstood something fundamental.

When I hit C-c ' in this table

#+TBLNAME: stat-final2
|    | Frequency |
|----+-----------|
| SS |           |
| AP |         5 |
| NT |         3 |
| SB |    #ERROR |
| MH |         2 |
| NP |         3 |
#+TBLFM: @>$2='(length (org-lookup-all "NP" '(remote(data,@2$2..@>I$2)) nil))::@>>$2='(length (org-lookup-all "MH" '(remote(data,@2$2..@>I$2)) nil))::@>>>$2='(length (org-lookup-all "SB" '\(remote(data,@2$2..@>I$2)) nil))::@>>>>$2='(length (org-lookup-all "NT" '(remote(data,@2$2..@>I$2)) nil))::@>>>>>$2='(length (org-lookup-all "AP" '(remote(data,@2$2..@>I$2)) nil))

I get a new window with this in it:

# Field and Range Formulas
@>$2 = '(length (org-lookup-all "NP" '(remote(data,@2$2..@>I$2)) nil))
@>>$2 = '(length (org-lookup-all "MH" '(remote(data,@2$2..@>I$2)) nil))
@>>>$2 = '(length (org-lookup-all "SB" '\(remote(data,@2$2..@>I$2))
nil))
@>>>>$2 = '(length (org-lookup-all "NT" '(remote(data,@2$2..@>I$2))
nil))
@>>>>>$2 = '(length (org-lookup-all "AP" '(remote(data,@2$2..@>I$2))
nil))

and I can edit individual lines and then C-c C-c or C-c ' again to exit
and rebuild the formula line on the table.

It seems to reverse the lines every other time I do it which is strange
but it works for me.

Regards,
Bernt

Re: very long table calc expressions ?

[Uwe Brauer]

[-- Attachment #1.1: Type: text/plain, Size: 781 bytes --]


   > Uwe Brauer <oub@mat.ucm.es> writes:

   > When I hit C-c ' in this table

   > #+TBLNAME: stat-final2
   > |    | Frequency |

   > |----+-----------|
   > | SS |           |
   > | AP |         5 |
   > | NT |         3 |
   > | SB |    #ERROR |
   > | MH |         2 |
   > | NP |         3 |


   > I get a new window with this in it:

   > # Field and Range Formulas
   > @>$2 = '(length (org-lookup-all "NP" '(remote(data,@2$2..@>I$2)) nil))
   > nil))
   > nil))
   > nil))

   > and I can edit individual lines and then C-c C-c or C-c ' again to exit
   > and rebuild the formula line on the table.


Not for me, that is strange. Here is the screenshot I obtain (running
git master emacs from May and org git master form last July, so maybe
this feature is missing?)


[-- Attachment #1.2: edit-special.png --]
[-- Type: image/png, Size: 83372 bytes --]

[-- Attachment #2: smime.p7s --]
[-- Type: application/pkcs7-signature, Size: 5025 bytes --]

Re: very long table calc expressions ?

[Jonathan Leech-Pepin]

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

Hi Uwe,

On Fri, Jun 22, 2018 at 10:16 AM, Uwe Brauer <oub@mat.ucm.es> wrote:

>
>    > Uwe Brauer <oub@mat.ucm.es> writes:
>
>    > When I hit C-c ' in this table
>
>    > #+TBLNAME: stat-final2
>    > |    | Frequency |
>
>    > |----+-----------|
>    > | SS |           |
>    > | AP |         5 |
>    > | NT |         3 |
>    > | SB |    #ERROR |
>    > | MH |         2 |
>    > | NP |         3 |
>
>
>    > I get a new window with this in it:
>
>    > # Field and Range Formulas
>    > @>$2 = '(length (org-lookup-all "NP" '(remote(data,@2$2..@>I$2))
> nil))
>    > nil))
>    > nil))
>    > nil))
>
>    > and I can edit individual lines and then C-c C-c or C-c ' again to
> exit
>    > and rebuild the formula line on the table.
>
>
> Not for me, that is strange. Here is the screenshot I obtain (running
> git master emacs from May and org git master form last July, so maybe
> this feature is missing?)
>
> Pretty sure the spacer between formulas is supposed to be a double colon
(::)
not just a single.  Brent's example has it with double while yours only
shows
it with a single one.

Regards,
Jon

[-- Attachment #2: Type: text/html, Size: 1855 bytes --]

Re: very long table calc expressions ?

[Uwe Brauer]

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

Hi Jonathan

   > Hi Uwe,
   > On Fri, Jun 22, 2018 at 10:16 AM, Uwe Brauer <oub@mat.ucm.es> wrote:

   > (::)
   > not just a single.  Brent's example has it with double while yours only
   > shows
   > it with a single one.

Right, thanks for clarifying that. My original example had various :: and
indeed there Bernt's advice helped. 

Meanwhile Thierry posted a more sophisticated example containing only
simple :
(which   does not work with ::) and that example cannot be dealt with via 
C-c '.

@Thierry: that is correct?

Uwe 

[-- Attachment #2: smime.p7s --]
[-- Type: application/pkcs7-signature, Size: 5025 bytes --]

Re: very long table calc expressions ?

[Nick Dokos]

Uwe Brauer <oub@mat.ucm.es> writes:

> Hi Jonathan
>
>    > Hi Uwe,
>    > On Fri, Jun 22, 2018 at 10:16 AM, Uwe Brauer <oub@mat.ucm.es> wrote:
>
>    > (::)
>    > not just a single.  Brent's example has it with double while yours only
>    > shows
>    > it with a single one.
>
> Right, thanks for clarifying that. My original example had various :: and
> indeed there Bernt's advice helped. 
>
> Meanwhile Thierry posted a more sophisticated example containing only
> simple :
> (which   does not work with ::) and that example cannot be dealt with via 
> C-c '.
>
> @Thierry: that is correct?
>

The double colons separate different formulas. The single colons in Thierry's
formula are  part of the syntax of a conditional expression, i.e. they are part
of a *single* (long) formula. So yes, that cannot be dealt with via C-c '.

-- 
Nick

"There are only two hard problems in computer science: cache
invalidation, naming things, and off-by-one errors." -Martin Fowler

Re: very long table calc expressions ?

[Thierry Banel]

On 22/06/2018 18:48, Nick Dokos wrote:
> Uwe Brauer <oub@mat.ucm.es> writes:
>
>> Hi Jonathan
>>
>>    > Hi Uwe,
>>    > On Fri, Jun 22, 2018 at 10:16 AM, Uwe Brauer <oub@mat.ucm.es> wrote:
>>
>>    > (::)
>>    > not just a single.  Brent's example has it with double while yours only
>>    > shows
>>    > it with a single one.
>>
>> Right, thanks for clarifying that. My original example had various :: and
>> indeed there Bernt's advice helped. 
>>
>> Meanwhile Thierry posted a more sophisticated example containing only
>> simple :
>> (which   does not work with ::) and that example cannot be dealt with via 
>> C-c '.
>>
>> @Thierry: that is correct?
>>
> The double colons separate different formulas. The single colons in Thierry's
> formula are  part of the syntax of a conditional expression, i.e. they are part
> of a *single* (long) formula. So yes, that cannot be dealt with via C-c '.
>
A utility lisp function can enhance readability:

#+begin_src elisp
  (defun test-to-interval (x)
    (let ((n (string-to-number x)))
      (cond
       ((equal x "")              "NP")
       ((and (<= 0 n) (<= n 4.9)) "SS")
       ((and (<= 5 n) (<= n 6.9)) "AP")
       ((and (<= 7 n) (<= n 8.9)) "NT")
       ((and (<= 9 n) (<= n 10 )) "SB")
       (t                         "other"))))
#+end_src

#+TBLNAME: raw-data
| Test | Interval |
|------+----------|
|    0 | SS       |
|    1 | SS       |
|    2 | SS       |
|    3 | SS       |
|    4 | SS       |
|    5 | AP       |
|    6 | AP       |
|    7 | NT       |
|    8 | NT       |
|    9 | SB       |
|      | NP       |
|      | NP       |
|      | NP       |
#+TBLFM: $2='(test-to-interval $1)

Re: very long table calc expressions ?

[Uwe Brauer]

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


   > On 22/06/2018 18:48, Nick Dokos wrote:
   > A utility lisp function can enhance readability:

   > #+begin_src elisp
   >   (defun test-to-interval (x)
   >     (let ((n (string-to-number x)))
   >       (cond
   >        ((equal x "")              "NP")
   >        ((and (<= 0 n) (<= n 4.9)) "SS")
   >        ((and (<= 5 n) (<= n 6.9)) "AP")
   >        ((and (<= 7 n) (<= n 8.9)) "NT")
   >        ((and (<= 9 n) (<= n 10 )) "SB")
   >        (t                         "other"))))
   > #+end_src

This is extremely helpful! Thanks a lot. I hope France will make it to
the final of the World Cup (more I cannot wish you :-D )

@Nick, I think this example should make it into the documentation, for
example in  https://orgmode.org/worg/org-tutorials/org-lookups.html.

What do you think?

Uwe 

[-- Attachment #2: smime.p7s --]
[-- Type: application/pkcs7-signature, Size: 4393 bytes --]