[PATCH] Lookup functions for tables

[PATCH] Lookup functions for tables

[Jarmo Hurri]

* lisp/org-table.el: added functions org-lookup-first and
org-lookup-last
* doc/org.texi: documented the use of lookup functions

TINYCHANGE
---
 doc/org.texi      |   88 +++++++++++++++++++++++++++++++++++++++++++++++++++-
 lisp/org-table.el |   10 ++++++
 2 files changed, 96 insertions(+), 2 deletions(-)

diff --git a/doc/org.texi b/doc/org.texi
index e183969..fc7d9dd 100644
--- a/doc/org.texi
+++ b/doc/org.texi
@@ -2399,6 +2399,7 @@ formula, moving these references by arrow keys
 * Formula syntax for Lisp::     Writing formulas in Emacs Lisp
 * Durations and time values::   How to compute durations and time values
 * Field and range formulas::    Formula for specific (ranges of) fields
+* Lookup functions::            Lookup functions for searching tables
 * Column formulas::             Formulas valid for an entire column
 * Editing and debugging formulas::  Fixing formulas
 * Updating the table::          Recomputing all dependent fields
@@ -2729,7 +2730,7 @@ example above).
 Negative duration values can be manipulated as well, and integers will be
 considered as seconds in addition and subtraction.
 
-@node Field and range formulas, Column formulas, Durations and time values, The spreadsheet
+@node Field and range formulas, Lookup functions, Durations and time values, The spreadsheet
 @subsection Field and range formulas
 @cindex field formula
 @cindex range formula
@@ -2785,7 +2786,90 @@ can also be used to assign a formula to some but not all fields in a row.
 Named field, see @ref{Advanced features}.
 @end table
 
-@node Column formulas, Editing and debugging formulas, Field and range formulas, The spreadsheet
+@node Lookup functions, Column formulas, Field and range formulas, The spreadsheet
+@subsection Lookup functions
+@cindex lookup functions in tables
+@cindex table lookup functions
+
+Org has two predefined Emacs Lisp functions for lookups in tables.
+@table @code
+@item (org-lookup-first val search-list return-list &optional predicate)
+@findex org-lookup-first
+Searches for the first element @code{el} in list @code{search-list} for which
+@lisp
+(predicate val el)
+@end lisp
+is @code{t}; returns a value from the corresponding
+position in list @code{return-list}. The default @code{predicate} is
+@code{equal}.
+@item (org-lookup-last val search-list return-list &optional predicate)
+@findex org-lookup-last
+Similar as @code{org-lookup-first} above, but searches for the @i{last} element for which the predicate is
+@code{t}.
+@end table
+
+The examples below illustrate searches inside a single table. In real-world
+applications, the searched data is often in a different table and is accessed
+by remote references.
+
+The first example contains the searched data in the first and the second
+column. The lookup is performed in column 5, where the year corresponding to
+the percentage in column 4 is searched. Notice that an error is returned if
+the lookup is unsuccessful.
+@example
+@group
+  | year | percentage |   | percentage | year   |
+  |------+------------+---+------------+--------|
+  | 2009 |       12.2 |   |       14.3 | 2010   |
+  | 2010 |       14.3 |   |       19.4 | 2012   |
+  | 2011 |       14.3 |   |       11.5 | #ERROR |
+  | 2012 |       19.4 |   |            | #ERROR |
+  #+TBLFM: $5='(org-lookup-first $4 '(@@2$2..@@>$2) '(@@2$1..@@>$1));N
+@end group
+@end example
+
+The second example illustrates standard use of lookups for teachers. The
+first two columns contain a grading table. The fourth and the fifth column
+contain student names and their marks, and the last column contains the
+results of doing a lookup for the appropriate grade. Notice the use of
+@code{org-lookup-last}, the predicate @code{>=} and the use of the @code{L}
+flag for literal interpolation of table values.
+@example
+@group
+  | lower bound | grade |   | student | marks | grade |
+  |-------------+-------+---+---------+-------+-------|
+  |           0 | D     |   | X       |    33 | A     |
+  |          10 | C     |   | Y       |     5 | D     |
+  |          20 | B     |   | Z       |    10 | C     |
+  |          30 | A     |   | W       |    22 | B     |
+  #+TBLFM: $6='(org-lookup-last $5 '(@@2$1..@@>$1) '(@@2$2..@@>$2) '>=);L
+@end group
+@end example
+
+In the previous examples the searched ranges were one-dimensional (single
+columns). Because two-dimensional ranges are converted to one-dimensional
+vectors in Lisp expressions, it is also possible to search true
+two-dimensional ranges. The example below does a lookup in the two first
+columns for values whose distance from @code{search key} is at most 1.
+@example
+@group
+#+BEGIN_SRC emacs-lisp
+(defun my-p (val1 val2)
+  (<= (abs (- val1 val2)) 1))
+#+END_SRC
+
+#+RESULTS:
+: my-p
+
+| group 1 | group 2 |   | search key | result |
+|---------+---------+---+------------+--------|
+|      22 |      12 |   |         -9 |     -8 |
+|      -8 |      11 |   |         23 |     22 |
+#+TBLFM: $5='(org-lookup-first $4 '(@@2$1..@@>$2) '(@@2$1..@@>$2) 'my-p);N
+@end group
+@end example
+
+@node Column formulas, Editing and debugging formulas, Lookup functions, The spreadsheet
 @subsection Column formulas
 @cindex column formula
 @cindex formula, for table column
diff --git a/lisp/org-table.el b/lisp/org-table.el
index 37889af..174fe59 100644
--- a/lisp/org-table.el
+++ b/lisp/org-table.el
@@ -4826,6 +4826,16 @@ list of the fields in the rectangle ."
 		      (org-table-get-range (match-string 0 form) tbeg 1))
 		  form)))))))))
 
+(defmacro org-lookup-function (name-str from-end-p)
+  `(defun ,(intern (format "org-lookup-%s" name-str)) (val search-list return-list &optional predicate)
+     "Searches for the element el in list search-list for which
+(predicate val el) is t; returns a value from the corresponding
+position in list return-list. The default predicate is equal."
+     (let ((p (if (eq predicate nil) 'equal predicate)))
+      (nth (position val search-list :test p :from-end ,from-end-p) return-list))))
+(org-lookup-function "first" nil)
+(org-lookup-function "last" t)
+
 (provide 'org-table)
 
 ;;; org-table.el ends here
-- 
1.7.7.6

Re: [PATCH] Lookup functions for tables

[Jarmo Hurri]

Greetings.

The patch posted here is the first patch I have ever made with git, so I
hope it is ok.

I tested the new functions locally, as can be seen from the examples. I
also ran make without problems. I am, however, unable to install org
from the git version (for some unknown reason), so I was unable to test
the final, committed version.

Happy to contribute if possible.

--
Jarmo

Re: [PATCH] Lookup functions for tables

[Bastien]

Hi Jarmo,

Jarmo Hurri <jarmo.hurri@syk.fi> writes:

> The patch posted here is the first patch I have ever made with git, so I
> hope it is ok.

thanks for contributing to Org!  The patch is longer than 20 lines, so
we would need you to sign the FSF papers to be able to accept it.

The form is here:

  http://orgmode.org/w/?p=org-mode.git;a=blob_plain;f=request-assign-future.txt;hb=HEAD

It takes a while for this to be processed -- so we will have time to
polish your contribution if needed :)

Thanks!

-- 
 Bastien

Re: [PATCH] Lookup functions for tables

[Bastien]

Jarmo Hurri <jarmo.hurri@syk.fi> writes:

> I am, however, unable to install org
> from the git version (for some unknown reason), so I was unable to test
> the final, committed version.

Please let us know what's wrong here, we will try to help.

-- 
 Bastien

Re: [PATCH] Lookup functions for tables

[Achim Gratz]

Jarmo Hurri writes:
> * lisp/org-table.el: added functions org-lookup-first and
> org-lookup-last
> * doc/org.texi: documented the use of lookup functions
>
> TINYCHANGE

Well, it doesn't become a TINYCHANGE just because you put that stamp on
it.  This patch clearly is over 20 non-trivial lines, so you will need
to assign copyright to the FSF.


Regards,
Achim.
-- 
+<[Q+ Matrix-12 WAVE#46+305 Neuron microQkb Andromeda XTk Blofeld]>+

Waldorf MIDI Implementation & additional documentation:
http://Synth.Stromeko.net/Downloads.html#WaldorfDocs

Re: [PATCH] Lookup functions for tables

[Achim Gratz]

Jarmo Hurri writes:
> I tested the new functions locally, as can be seen from the examples. I
> also ran make without problems. I am, however, unable to install org
> from the git version (for some unknown reason), so I was unable to test
> the final, committed version.

I can't decode that last part… you installed Org from git with make,
then made your changes (also in git) and now Org does or doesn't do what
when you try to install?


Regards,
Achim.
-- 
+<[Q+ Matrix-12 WAVE#46+305 Neuron microQkb Andromeda XTk Blofeld]>+

Wavetables for the Waldorf Blofeld:
http://Synth.Stromeko.net/Downloads.html#BlofeldUserWavetables

Re: [PATCH] Lookup functions for tables

[Jarmo Hurri]

Bastien <bzg@altern.org> writes:

> The patch is longer than 20 lines, so we would need you to sign the
> FSF papers to be able to accept it.

Request submitted.

--
Jarmo

Re: [PATCH] Lookup functions for tables

[Jarmo Hurri]

Achim Gratz <Stromeko@nexgo.de> writes:

> Well, it doesn't become a TINYCHANGE just because you put that stamp
> on it.  This patch clearly is over 20 non-trivial lines, so you will
> need to assign copyright to the FSF.

The program code (one macro and two calls) is less than 20 lines, and I
was hoping documentation would not count. Now I know there is no
hope. :-)

--
Jarmo

Re: [PATCH] Lookup functions for tables

[Jarmo Hurri]

>> I tested the new functions locally, as can be seen from the
>> examples. I also ran make without problems. I am, however, unable to
>> install org from the git version (for some unknown reason), so I was
>> unable to test the final, committed version.
>
> I can't decode that last part… you installed Org from git with make,
> then made your changes (also in git) and now Org does or doesn't do
> what when you try to install?

Not being able to decode is understandable, because it wasn't very
clear.

Yesterday my org installation from git did not work, but today, when I
took a fresh look at the situation, I was able to notice a path problem
which I solved. I now tested the committed version with my changes, and
it works.

A couple of points about the process:

1. Already yesterday I ran make to make sure that org still compiles
   ok. It did, although the used cl function 'position' does give two
   warnings.

2. When I tried to run "make test", it failed because I don't have ERT
   installed. I am running Emacs 23.3.1, which does not include ERT, and
   I could not find a package providing ert.el in the repositories of
   Fedora 16. Because I was running out of time, I decided not to start
   installing ERT manually.

   Today I installed ERT by hand, but "make test" still does not work,
   because the test script runs "emacs -Q" and thus does not load my
   init file (where the directory of ERT is added to path). I don't want
   to make a manually installed ERT system-wide, so I had to skip the
   tests.

--

Jarmo

Re: [PATCH] Lookup functions for tables

[Michael Brand]

Hi Jarmo

On Wed, Sep 19, 2012 at 6:48 PM, Jarmo Hurri <jarmo.hurri@syk.fi> wrote:
> --- a/doc/org.texi
> +++ b/doc/org.texi
> @@ -2399,6 +2399,7 @@ formula, moving these references by arrow keys
>  * Formula syntax for Lisp::     Writing formulas in Emacs Lisp
>  * Durations and time values::   How to compute durations and time values
>  * Field and range formulas::    Formula for specific (ranges of) fields
> +* Lookup functions::            Lookup functions for searching tables
>  * Column formulas::             Formulas valid for an entire column
>  * Editing and debugging formulas::  Fixing formulas
>  * Updating the table::          Recomputing all dependent fields
> @@ -2729,7 +2730,7 @@ example above).

In my opinion “Field and range formulas” and “Column formulas” should
stay together, so I suggest

 * Field and range formulas::    Formula for specific (ranges of) fields
 * Column formulas::             Formulas valid for an entire column
+* Lookup functions::            Lookup functions for searching tables

for the menu and the corresponding move for the texinfo subsection.

Michael

Re: [PATCH] Lookup functions for tables

[Achim Gratz]

Jarmo Hurri writes:
> 2. When I tried to run "make test", it failed because I don't have ERT
>    installed. I am running Emacs 23.3.1, which does not include ERT, and
>    I could not find a package providing ert.el in the repositories of
>    Fedora 16. Because I was running out of time, I decided not to start
>    installing ERT manually.

Add this to local.mk:

BTEST_POST = -L /path/to/ert

I keep this installed in testing/ert for historical reasons.  Get the
latest version for emacs 23 from:

https://github.com/ohler/ert


Regards,
Achim.
-- 
+<[Q+ Matrix-12 WAVE#46+305 Neuron microQkb Andromeda XTk Blofeld]>+

SD adaptation for Waldorf microQ V2.22R2:
http://Synth.Stromeko.net/Downloads.html#WaldorfSDada

Re: [PATCH] Lookup functions for tables

[Jarmo Hurri]

>> --- a/doc/org.texi
>> +++ b/doc/org.texi
>> @@ -2399,6 +2399,7 @@ formula, moving these references by arrow keys
>>  * Formula syntax for Lisp::     Writing formulas in Emacs Lisp
>>  * Durations and time values::   How to compute durations and time values
>>  * Field and range formulas::    Formula for specific (ranges of) fields
>> +* Lookup functions::            Lookup functions for searching tables
>>  * Column formulas::             Formulas valid for an entire column
>>  * Editing and debugging formulas::  Fixing formulas
>>  * Updating the table::          Recomputing all dependent fields
>> @@ -2729,7 +2730,7 @@ example above).
>
> In my opinion “Field and range formulas” and “Column formulas” should
> stay together, so I suggest
>
>  * Field and range formulas::    Formula for specific (ranges of) fields
>  * Column formulas::             Formulas valid for an entire column
> +* Lookup functions::            Lookup functions for searching tables
>
> for the menu and the corresponding move for the texinfo subsection.

Ok. I guess this also means changing the (next,prev,up) parts of
@node-commands. Do you want me to make the change and submit a new
patch?

--
Jarmo

Re: [PATCH] Lookup functions for tables

[Michael Brand]

Hi Jarmo

On Fri, Sep 21, 2012 at 7:44 AM, Jarmo Hurri <jarmo.hurri@syk.fi> wrote:
> Ok. I guess this also means changing the (next,prev,up) parts of
> @node-commands.

Yes.

> Do you want me to make the change and submit a new patch?

Yes please, I suggest to wait a bit if there will be more comments.

Michael

Re: [PATCH] Lookup functions for tables

[Bastien]

Hi Jarmo

Jarmo Hurri <jarmo.hurri@syk.fi> writes:

> Bastien <bzg@altern.org> writes:
>
>> The patch is longer than 20 lines, so we would need you to sign the
>> FSF papers to be able to accept it.
>
> Request submitted.

Thanks -- I added your name in the "pending" section of this Worg page
(which will get updated soon) :

http://orgmode.org/worg/org-contribute.html#sec-6-2

-- 
 Bastien