Determine min/max values in a table

Determine min/max values in a table

[Karl Voit]

Hi!

How can I determine minimum and/or maximum value of a table?

Here is my example:

#+NAME: myvalues
| Values |
|--------|
|      4 |
|      2 |
|      3 |
|      7 |
|      5 |
|      6 |

| Min    | Max    | Average | First | Last |
|--------+--------+---------+-------+------|
| #ERROR | #ERROR |     4.5 |     4 |    6 |
#+TBLFM: @2$1='(min (remote(myvalues,@2$1..@>$1)))::@2$2='(max (remote(myvalues,@2$1..@>$1)))::@2$3=vmean(remote(myvalues,@2$1..@>$1))::@2$4=remote(myvalues,@2$1)::@2$5=remote(myvalues,@>$1)

My goal is to get min==2 in the first column and max==7 in the
second.

-- 
get mail|git|SVN|photos|postings|SMS|phonecalls|RSS|CSV|XML into Org-mode:
       > get Memacs from https://github.com/novoid/Memacs <
Personal Information Management > http://Karl-Voit.at/tags/pim/
Emacs-related > http://Karl-Voit.at/tags/emacs/

Finding calc/elisp methods (was: Determine min/max values in a table)

[Karl Voit]

Hi,

* Karl Voit <devnull@Karl-Voit.at> wrote:
>
> How can I determine minimum and/or maximum value of a table?
>
> Here is my example:
>
> #+NAME: myvalues
>| Values |
>|--------|
>|      4 |
>|      2 |
>|      3 |
>|      7 |
>|      5 |
>|      6 |
>
>| Min    | Max    | Average | First | Last |
>|--------+--------+---------+-------+------|
>| #ERROR | #ERROR |     4.5 |     4 |    6 |
> #+TBLFM: @2$1='(min (remote(myvalues,@2$1..@>$1)))::@2$2='(max (remote(myvalues,@2$1..@>$1)))::@2$3=vmean(remote(myvalues,@2$1..@>$1))::@2$4=remote(myvalues,@2$1)::@2$5=remote(myvalues,@>$1)
>
> My goal is to get min==2 in the first column and max==7 in the
> second.

Leslie helped here by mentioning vmin/vmax which is that obvious
that I do feel embarrassed now ;-)

#+TBLFM: @2$1=vmin(remote(myvalues,@2$1..@>$1))::@2$2=vmax(remote(myvalues,@2$1..@>$1))::@2$3=vmean(remote(myvalues,@2$1..@>$1))::@2$4=remote(myvalues,@2$1)::@2$5=remote(myvalues,@>$1)

However, let's make this thread a valuable one by adding a question:
how does somebody find such things? Is there a source of information
where I could find calc methods by "apropos" method together with
elisp methods?

"M-h a minimum" returns only calc-find-minimum which is hardly of
any help in this case. "apropos-documentation" returns more results
but still lacks "vmin".

What is your method of choice to find answers to those kind of
questions? At least my internet search engine of choice did not help
me in the first place.

-- 
get mail|git|SVN|photos|postings|SMS|phonecalls|RSS|CSV|XML into Org-mode:
       > get Memacs from https://github.com/novoid/Memacs <
Personal Information Management > http://Karl-Voit.at/tags/pim/
Emacs-related > http://Karl-Voit.at/tags/emacs/

Re: Finding calc/elisp methods

[Nick Dokos]

Karl Voit <devnull@Karl-Voit.at> writes:

> ...
> Leslie helped here by mentioning vmin/vmax which is that obvious
> that I do feel embarrassed now ;-)
>
> #+TBLFM: @2$1=vmin(remote(myvalues,@2$1..@>$1))::@2$2=vmax(remote(myvalues,@2$1..@>$1))::@2$3=vmean(remote(myvalues,@2$1..@>$1))::@2$4=remote(myvalues,@2$1)::@2$5=remote(myvalues,@>$1)
>
> However, let's make this thread a valuable one by adding a question:
> how does somebody find such things? Is there a source of information
> where I could find calc methods by "apropos" method together with
> elisp methods?
>
> "M-h a minimum" returns only calc-find-minimum which is hardly of
> any help in this case. "apropos-documentation" returns more results
> but still lacks "vmin".
>
> What is your method of choice to find answers to those kind of
> questions? At least my internet search engine of choice did not help
> me in the first place.

A long time ago, I struggled with this question and I don't know
of a satisfying answer. For a while, I went directly to the
following info node and looked around:

    (info "(calc) Matrix Functions")

Now I just remember the initial "v" :-)

But there are other calc functions that are useful as well, so this
will certainly not suffice in general. But it's a start.

-- 
Nick

Re: Finding calc/elisp methods

[Adonay Felipe Nogueira]

Hi, you can find such information in the Calc Mode info pages:

[[info:calc]]

Avoid the commands between parenthesis which start with "calc-" because
these are actually Emacs Lisp procedures --- if you would really need to
use such, you would have to write the formula as a Emacs Lisp notation
and as far as I know, we can't mix Emacs Lisp procedures together with
Calc commands in the same formula, like so:

vmax([2, 3, '(max 2 4)])

The example above would cause an error. 'max is a "native" Emacs Lisp
procedure that would return the maximum value from the values given.

Happy hacking! :)

-- 
- [[https://libreplanet.org/wiki/User:Adfeno]]
- Palestrante e consultor sobre /software/ livre (não confundir com
  gratis).
- "WhatsApp"? Ele não é livre, por isso não uso. Iguais a ele prefiro
  GNU Ring, ou Tox. Quer outras formas de contato? Adicione o vCard
  que está no endereço acima aos teus contatos.
- Pretende me enviar arquivos .doc, .ppt, .cdr, ou .mp3? OK, eu
  aceito, mas não repasso. Entrego apenas em formatos favoráveis ao
  /software/ livre. Favor entrar em contato em caso de dúvida.

Re: Determine min/max values in a table

[Thierry Banel]

Le 02/08/2017 14:07, Karl Voit a écrit :
> Hi!
>
> How can I determine minimum and/or maximum value of a table?
>
> Here is my example:
>
> #+NAME: myvalues
> | Values |
> |--------|
> |      4 |
> |      2 |
> |      3 |
> |      7 |
> |      5 |
> |      6 |
>
> | Min    | Max    | Average | First | Last |
> |--------+--------+---------+-------+------|
> | #ERROR | #ERROR |     4.5 |     4 |    6 |
> #+TBLFM: @2$1='(min (remote(myvalues,@2$1..@>$1)))::@2$2='(max (remote(myvalues,@2$1..@>$1)))::@2$3=vmean(remote(myvalues,@2$1..@>$1))::@2$4=remote(myvalues,@2$1)::@2$5=remote(myvalues,@>$1)
>
> My goal is to get min==2 in the first column and max==7 in the
> second.
>

Alternatively you have the orgtbl-aggregate package available on Melpa.

#+BEGIN: aggregate :table "myvalues" :cols "min(Values) max(Values)
mean(Values)"
| min(Values) | max(Values) | mean(Values) |
|-------------+-------------+--------------|
|           2 |           7 |          4.5 |
#+END:

Re: Determine min/max values in a table

[Adam Porter]

Thierry Banel <tbanelwebmin@free.fr> writes:

> Alternatively you have the orgtbl-aggregate package available on Melpa.
>
> #+BEGIN: aggregate :table "myvalues" :cols "min(Values) max(Values)
> mean(Values)"
>
> | min(Values) | max(Values) | mean(Values) |
> |-------------+-------------+--------------|
> |           2 |           7 |          4.5 |

Wow, that's very cool!  Had no idea about that package.

Karl, if that doesn't work for you, you might look at the
org-table-to-lisp function.  Here's an example of a function that uses
it to sum columns in the current region:

#+BEGIN_SRC elisp
(defun org-fitness-sum-table-lines ()
    "Sum each numeric column in table lines touched by the region."
    (interactive)
    (org-with-wide-buffer
     (let* (
            ;; Add empty column because (org-table-get-specials) leaves the empty one out, which throws off the indices
            (header (cons nil (org-table-column-names)))
            (start (save-excursion
                     (goto-line (line-number-at-pos (region-beginning)))
                     (line-beginning-position)))
            (end (save-excursion
                   (goto-line (line-number-at-pos (region-end)))
                   (line-end-position)))
            (lines (buffer-substring-no-properties start end))
            (table (--remove (equal 'hline it)
                             (org-table-to-lisp lines)))
            (indices (cdr  ; Drop index representing first column, which is always empty
                      (butlast  ; Drop index representing last column, which is comments
                       (-find-indices (lambda (col)
                                        (or (string= col "")
                                            (string= col "0")
                                            (string= col "0.0")
                                            (string= col "0.00")
                                            (< 0 (string-to-number col))))
                                      (car table)))))
            (sums (cl-loop for i in indices
                           collect (-reduce '+ (-map 'string-to-number
                                                     (-select-column i table)))))
            (result (-zip (-select-by-indices indices header) sums)))
       (org-fitness-display-values result :prefix "Lines: "))))
#+END_SRC

Re: Finding calc/elisp methods

[Adam Porter]

Karl Voit <devnull@Karl-Voit.at> writes:

> Leslie helped here by mentioning vmin/vmax which is that obvious
> that I do feel embarrassed now ;-)
>
> #+TBLFM: @2$1=vmin(remote(myvalues,@2$1..@>$1))::@2$2=vmax(remote(myvalues,@2$1..@>$1))::@2$3=vmean(remote(myvalues,@2$1..@>$1))::@2$4=remote(myvalues,@2$1)::@2$5=remote(myvalues,@>$1)
>
> However, let's make this thread a valuable one by adding a question:
> how does somebody find such things? Is there a source of information
> where I could find calc methods by "apropos" method together with
> elisp methods?
>
> "M-h a minimum" returns only calc-find-minimum which is hardly of
> any help in this case. "apropos-documentation" returns more results
> but still lacks "vmin".
>
> What is your method of choice to find answers to those kind of
> questions? At least my internet search engine of choice did not help
> me in the first place.

IIRC these kinds of things are in the Org manual and info pages.  Helm's
helm-info-org command is very helpful for finding things in the Org info
manual.

I learned something new today: I didn't realize that Org could use
calc-mode commands in tables.  Wow.  Org never ceases to surprise and
amaze me!

So googling for "org table calc" I got the manual page:
http://orgmode.org/manual/Formula-syntax-for-Calc.html

It wouldn't hurt to add a section to that page explaining how to find
more info about the calc functions...  ;)

Re: Determine min/max values in a table

[Karl Voit]

* Adam Porter <adam@alphapapa.net> wrote:
> Thierry Banel <tbanelwebmin@free.fr> writes:
>
>> Alternatively you have the orgtbl-aggregate package available on Melpa.
>>
>> #+BEGIN: aggregate :table "myvalues" :cols "min(Values) max(Values)
>> mean(Values)"
>>
>> | min(Values) | max(Values) | mean(Values) |
>> |-------------+-------------+--------------|
>> |           2 |           7 |          4.5 |
>
> Wow, that's very cool!  Had no idea about that package.

Although I do have the package installed, I did not think about
min/max/mean of it ;-)

> Karl, if that doesn't work for you, you might look at the
> org-table-to-lisp function.  Here's an example of a function that uses
> it to sum columns in the current region:

Thanks for the code I copied to my knowledge base.

For now, vmin/vmax did the trick and I also refreshed my knowledge
of orgtbl-aggregate.

-- 
get mail|git|SVN|photos|postings|SMS|phonecalls|RSS|CSV|XML into Org-mode:
       > get Memacs from https://github.com/novoid/Memacs <
Personal Information Management > http://Karl-Voit.at/tags/pim/
Emacs-related > http://Karl-Voit.at/tags/emacs/

Re: Finding calc/elisp methods

[Eric S Fraga]

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

On Wednesday,  2 Aug 2017 at 14:56, Karl Voit wrote:

[...]

> However, let's make this thread a valuable one by adding a question:
> how does somebody find such things? Is there a source of information
> where I could find calc methods by "apropos" method together with
> elisp methods?

The calc info manual has a full list of functions in the "Function
Index" section.  E.g.

  C-h i m calc RET m Func TAB RET

will take you the right place (hopefully).  You can then search using
's'.

HTH,
eric


-- 
: Eric S Fraga via Emacs 26.0.50, Org release_9.0.9-551-g92e8c8

[-- Attachment #2: signature.asc --]
[-- Type: application/pgp-signature, Size: 194 bytes --]