[PATCH] Lookup functions, take two

[PATCH] Lookup functions, take two

[Jarmo Hurri]

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


Greetings.

Modified the patch as requested: the ordering in the documentation is
now "Column formulas" before the new "Lookup functions".

Also made org-table.el (require 'cl) in general, not only compile time,
because there was a warning of it being required at runtime, but there
is still a warning at compile time. Seemed like a reasonable thing to
do, although given the discussion on possible namespace clashes on the
net makes me doubt the approach. It seems GNU does not like CL. I can
implement a new function corresponding to CL's "position" if you find it
necessary.

Have fun.

--

Jarmo


[-- Warning: decoded text below may be mangled, UTF-8 assumed --]
[-- Attachment #2: lookup functions --]
[-- Type: text/x-patch, Size: 7059 bytes --]

From 238551583ecf381d576f42b718e16f1601a43e55 Mon Sep 17 00:00:00 2001
From: Jarmo Hurri <jarmo.hurri@syk.fi>
Date: Sun, 23 Sep 2012 18:16:01 +0300
Subject: [PATCH] Lookup functions for tables

* lisp/org-table.el: added functions org-lookup-first and
org-lookup-last
* doc/org.texi: documented the use of lookup functions
---
 doc/org.texi      |   89 +++++++++++++++++++++++++++++++++++++++++++++++++++-
 lisp/org-table.el |   13 ++++++-
 2 files changed, 98 insertions(+), 4 deletions(-)

diff --git a/doc/org.texi b/doc/org.texi
index e740ca5..2eb8b58 100644
--- a/doc/org.texi
+++ b/doc/org.texi
@@ -378,6 +378,7 @@ The spreadsheet
 * Durations and time values::   How to compute durations and time values
 * 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
 * Editing and debugging formulas::  Fixing formulas
 * Updating the table::          Recomputing all dependent fields
 * Advanced features::           Field and column names, parameters and automatic recalc
@@ -2405,6 +2406,7 @@ formula, moving these references by arrow keys
 * Durations and time values::   How to compute durations and time values
 * 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
 * Editing and debugging formulas::  Fixing formulas
 * Updating the table::          Recomputing all dependent fields
 * Advanced features::           Field and column names, parameters and automatic recalc
@@ -2790,7 +2792,7 @@ 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 Column formulas, Lookup functions, Field and range formulas, The spreadsheet
 @subsection Column formulas
 @cindex column formula
 @cindex formula, for table column
@@ -2829,7 +2831,90 @@ stores it.  With a numeric prefix argument(e.g.@: @kbd{C-5 C-c =}) the command
 will apply it to that many consecutive fields in the current column.
 @end table
 
-@node Editing and debugging formulas, Updating the table, Column formulas, The spreadsheet
+@node Lookup functions, Editing and debugging formulas, Column 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 Editing and debugging formulas, Updating the table, Lookup functions, The spreadsheet
 @subsection Editing and debugging formulas
 @cindex formula editing
 @cindex editing, of table formulas
diff --git a/lisp/org-table.el b/lisp/org-table.el
index 37889af..3db603f 100644
--- a/lisp/org-table.el
+++ b/lisp/org-table.el
@@ -34,8 +34,7 @@
 
 ;;; Code:
 
-(eval-when-compile
-  (require 'cl))
+(require 'cl)
 (require 'org)
 
 (declare-function org-table-clean-before-export "org-exp"
@@ -4826,6 +4825,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, take two

[Bastien]

Hi Jarmo,

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

> Modified the patch as requested: the ordering in the documentation is
> now "Column formulas" before the new "Lookup functions".

I feel my brain is a bit slow today... so sorry in advance if the
question sounds stupid.  What is the advantage of using org-lookup-*
functions instead of a simple reference in the table formulas?

-- 
 Bastien

Re: [PATCH] Lookup functions, take two

[Jarmo Hurri]

Bastien <bzg@altern.org> writes:

> I feel my brain is a bit slow today... so sorry in advance if the
> question sounds stupid.  What is the advantage of using org-lookup-*
> functions instead of a simple reference in the table formulas?

As noted in the documentation, most often you do something like this
when fetching data from another table (in the case of org, using
remote). I would preferred to include cases like this, but the
tblfm-lines were too long for the resulting PDF... Say I have 100
students, and I want to grade them. I find the sum of their marks, and
then have a grading table for the grades. The lookup function maps the
marks to the grades, as shown in documentation in the second example.

Another application would be, say, mapping your postal code to your
municipality. The "user" would input the postal code, and the table
would automatically fetch the corresponding municipality from another
table.

A third example would be a discount rate that depends on the total sum
of your bill. Or a bonus that depends on how productive you were (given
as a table by the suits).

M*crosoft equivalents, supported by libreoffice, are the hlookup and
vlookup functions:

http://help.libreoffice.org/Calc/Spreadsheet_Functions#VLOOKUP

--

Jarmo

Re: [PATCH] Lookup functions, take two

[Bastien]

Hi Jarmo,

thanks for the explanations -- I should I've read your doc patch 
more closely. 

It is a nice spreadsheet utility function but my feeling is that
having it in Org's core does not really fit, as it introduces
functions where we usually have references.  Functions are fine
for users to add, though, that's the whole point of allowing
Elisp in table formulas.

Maybe I'm too conservative on this one.  I will wait for other
users' insight -- especially Carsten, who can better decide what
fits and what does not in this area.

Thanks,

-- 
 Bastien

Re: [PATCH] Lookup functions, take two

[Jarmo Hurri]

> It is a nice spreadsheet utility function but my feeling is that
> having it in Org's core does not really fit, as it introduces
> functions where we usually have references.  Functions are fine for
> users to add, though, that's the whole point of allowing Elisp in
> table formulas.

So if it does not fit the core, where should I add it so that the users
will still find it? I think that in general it is a very good idea to
have powerful tables in org. Right?

Many users in the land of spreadsheet programs have gotten used to these
functions.  You have had at least one explicit request for this feature:

http://lists.gnu.org/archive/html/emacs-orgmode/2010-07/msg01227.html

--

Jarmo

Re: [PATCH] Lookup functions, take two

[Bastien]

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

> So if it does not fit the core, where should I add it so that the users
> will still find it? I think that in general it is a very good idea to
> have powerful tables in org. Right?

Right :)

> Many users in the land of spreadsheet programs have gotten used to these
> functions.  You have had at least one explicit request for this feature:
>
> http://lists.gnu.org/archive/html/emacs-orgmode/2010-07/msg01227.html

Indeed, thanks again for contributing this.

PS: My über-cautious attitude was more because I'm not using such
functionalities.

-- 
 Bastien

Re: [PATCH] Lookup functions, take two

[Dominik, Carsten]

Hi Bastien,

since you copied me, I guess you want my comment?

I think this is very useful functionality and I would vote for putting it into the core.

The documentation should be kept as compact as possible, I think without an example, just describe the functionality. Maybe Jarmo can write a little tutorial for Worg and document it there more extensively?

- Carsten

On 25.9.2012, at 03:49, Bastien wrote:

> Hi Jarmo,
> 
> thanks for the explanations -- I should I've read your doc patch 
> more closely. 
> 
> It is a nice spreadsheet utility function but my feeling is that
> having it in Org's core does not really fit, as it introduces
> functions where we usually have references.  Functions are fine
> for users to add, though, that's the whole point of allowing
> Elisp in table formulas.
> 
> Maybe I'm too conservative on this one.  I will wait for other
> users' insight -- especially Carsten, who can better decide what
> fits and what does not in this area.
> 
> Thanks,
> 
> -- 
> Bastien

Re: [PATCH] Lookup functions, take two

[Bastien]

Hi Carsten,

"Dominik, Carsten" <C.Dominik@uva.nl> writes:

> since you copied me, I guess you want my comment?

yes, sorry for requesting this implicitely.

> I think this is very useful functionality and I would vote for putting
> it into the core.

Okay, thanks.

> The documentation should be kept as compact as possible, I think without an
> example, just describe the functionality. Maybe Jarmo can write a little
> tutorial for Worg and document it there more extensively?

Jarmo, would you be okay reworking the patch with these directions?  
(We have some time before your assignment papers get processed by the 
FSF, so please take your time for this.)

Having a new node in the manual is fine -- the node can later evolve
and document other utility functions.

Also be careful about this typographic convention: the Elisp manuals
use two whitespaces for ending sentences.

A tutorial for Worg would indeed be nice: please send me your public 
key and I will give you write access to Worg.

Thanks!

-- 
 Bastien

Re: [PATCH] Lookup functions, take two

[Jarmo Hurri]

Bastien <bzg@altern.org> writes:

> "Dominik, Carsten" <C.Dominik@uva.nl> writes:
>> The documentation should be kept as compact as possible, I think without an
>> example, just describe the functionality. Maybe Jarmo can write a little
>> tutorial for Worg and document it there more extensively?
>
> Jarmo, would you be okay reworking the patch with these directions?  

Will do that.

> Also be careful about this typographic convention: the Elisp manuals
> use two whitespaces for ending sentences.

I assume that by "Elisp manuals" you mean org.texi? At least it seems to
follow that convention, and thanks for pointing it out, since I did not
know it.

> A tutorial for Worg would indeed be nice: please send me your public 
> key and I will give you write access to Worg.

Coming up.

From what I see in the manual, it seems to be ok to point to a Worg
tutorial for more information on the topic.

--
Jarmo

Re: [PATCH] Lookup functions, take two

[Bastien]

Hi Jarmo,

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

>> Jarmo, would you be okay reworking the patch with these directions?  
>
> Will do that.

Thanks!

(If you don't mind, please use the equivalent code I sent yesterday.)

> I assume that by "Elisp manuals" you mean org.texi? 

I mean the GNU Elisp manuals in general, but yes, org.texi in this case.

> From what I see in the manual, it seems to be ok to point to a Worg
> tutorial for more information on the topic.

Sure!  No hesitation.

All best,

-- 
 Bastien

Re: [PATCH] Lookup functions, take two

[Achim Gratz]

Jarmo Hurri writes:
> Also made org-table.el (require 'cl) in general, not only compile time,
> because there was a warning of it being required at runtime, but there
> is still a warning at compile time. Seemed like a reasonable thing to
> do, although given the discussion on possible namespace clashes on the
> net makes me doubt the approach. It seems GNU does not like CL. I can
> implement a new function corresponding to CL's "position" if you find it
> necessary.

Using cl at runtime is a no-no for GNU Emacs and you are actually only
using cl-seq.  You could use the new cl-lib starting with Emacs 24.3,
but that's a no-no for backwards compatibility.  So we would need a
compatibility definition.  Since these functions were autoloaded in all
emacs versions, a defalias to the correct function depending on version
might be good enough, if not Org would have to re-implement the part you
need.  You could ask on the Emacs devel list if that'd be OK.


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

Factory and User Sound Singles for Waldorf Q+, Q and microQ:
http://Synth.Stromeko.net/Downloads.html#WaldorfSounds

Re: [PATCH] Lookup functions, take two

[Bastien]

Achim Gratz <Stromeko@nexgo.de> writes:

> You could ask on the Emacs devel list if that'd be OK.

I suggest using this code:

(defun org-lookup (val s-list r-list lastp &optional predicate)
  "Look for VAL in S-LIST and return the corresponding element in R-LIST.
If LASTP, ignore all matching VAL in SEARCH-LIST except the last one.
If PREDICATE is not nil, use this instead of `equal' to match VAL."
  (let ((p (or predicate 'equal)) (c 0) r)
    (nth (dolist (i search-list r) (setq c (1+ c))
		 (if (and (funcall p val i) (or lastp (not r)))
		     (setq r (1- c))))
	 return-list)))

(defun org-lookup-first (val s-list r-list &optional predicate)
  "Look for VAL in S-LIST and return the corresponding element in R-LIST.
If PREDICATE is not nil, use this instead of `equal' to match VAL."
  (org-lookup val s-list r-list nil predicate))

(defun org-lookup-last (val s-list r-list &optional predicate)
  "Look for VAL in S-LIST and return the corresponding element in R-LIST.
If PREDICATE is not nil, use this instead of `equal' to match VAL."
  (org-lookup val s-list r-list t predicate))

;; (org-lookup-first 2 '(1 2 3 2) '(A B C D E)) => B
;; (org-lookup-last 2 '(1 2 3 2) '(A B C D E))  => D

No `cl-position' anymore.

Less dense and elegant, of course, but more explicit.  Users will be
able to check the docstring of org-lookup-first/last, which I think is
good for functions that we advertize in the manual.

Jarmo, would you be okay if I commit this?

Then you can commit a <20 lines patch for the documentation :)  
Or you commit the code (19 lines!) and I commit the doc patch,
as you want!

Thanks,

-- 
 Bastien

Re: [PATCH] Lookup functions, take two

[Jarmo Hurri]

Bastien <bzg@altern.org> writes:

> I suggest using this code:
>
> ...
>
> Less dense and elegant, of course, but more explicit.  Users will be
> able to check the docstring of org-lookup-first/last, which I think is
> good for functions that we advertize in the manual.
>
> Jarmo, would you be okay if I commit this?

I have nothing against the idea. However, I get only errors when I try
to apply your functions in my examples. I can track down the source of
the problems later (not today, though).

But how about combining your idea about getting rid of CL's position
with the following idea of building the docstring on the fly in the
macro? That is, you do not need to define multiple functions by hand,
but the docstrings can still be unique.

#+BEGIN_SRC emacs-lisp
(defmacro org-lookup-function (name-str from-end-p)
  `(defun ,(intern (format "org-lookup-%s" name-str)) (val search-list return-list &optional predicate)
    ,(format "Searches for the %s 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." (if from-end-p "last" "first"))
     (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)
#+END_SRC

--

Jarmo

Re: [PATCH] Lookup functions, take two

[Bastien]

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

Hi Jarmo,

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

> I have nothing against the idea. However, I get only errors when I try
> to apply your functions in my examples. I can track down the source of
> the problems later (not today, though).

There was a typo, I attach the correct patch.

> But how about combining your idea about getting rid of CL's position
> with the following idea of building the docstring on the fly in the
> macro? That is, you do not need to define multiple functions by hand,
> but the docstrings can still be unique.

As you prefer.  But C-h f will not point to org-table.el if we use this
macro.  This is acceptable, but we need to mention `org-lookup' as the
"matrix" function in the docstring, so that user C-h f'ing org-lookup
will find it in org-table.el.

Another wish style-wise: the first sentence of the docstring should be
one line long.  See the short parameters names and the docstrings in my
patch to get an idea -- but please feel free to also follow your taste
here, of course.

Thanks!


[-- Warning: decoded text below may be mangled, UTF-8 assumed --]
[-- Attachment #2: org-table.el.patch --]
[-- Type: text/x-patch, Size: 1415 bytes --]

diff --git a/lisp/org-table.el b/lisp/org-table.el
index 37889af..95b8231 100644
--- a/lisp/org-table.el
+++ b/lisp/org-table.el
@@ -4826,6 +4826,29 @@ list of the fields in the rectangle ."
 		      (org-table-get-range (match-string 0 form) tbeg 1))
 		  form)))))))))
 
+(defun org-lookup (val s-list r-list lastp &optional predicate)
+  "Look for VAL in S-LIST and return the corresponding element in R-LIST.
+If LASTP, ignore all matching VAL in S-LIST except the last one.
+If PREDICATE is not nil, use this instead of `equal' to match VAL."
+  (let ((p (or predicate 'equal)) (c 0) r)
+    (nth (dolist (i s-list r) (setq c (1+ c))
+		 (if (and (funcall p val i) (or lastp (not r)))
+		     (setq r (1- c))))
+	 return-list)))
+
+(defun org-lookup-first (val s-list r-list &optional predicate)
+  "Look for VAL in S-LIST and return the corresponding element in R-LIST.
+If PREDICATE is not nil, use this instead of `equal' to match VAL."
+  (org-lookup val s-list r-list nil predicate))
+
+(defun org-lookup-last (val s-list r-list &optional predicate)
+  "Look for VAL in S-LIST and return the corresponding element in R-LIST.
+If PREDICATE is not nil, use this instead of `equal' to match VAL."
+  (org-lookup val s-list r-list t predicate))
+
+;; (org-lookup-first 2 '(1 2 3 2) '(A B C D E)) => B
+;; (org-lookup-last 2 '(1 2 3 2) '(A B C D E))  => D
+
 (provide 'org-table)
 
 ;;; org-table.el ends here

[-- Attachment #3: Type: text/plain, Size: 14 bytes --]


-- 
 Bastien

Re: [PATCH] Lookup functions, take two

[Jarmo Hurri]

Bastien <bzg@altern.org> writes:

> There was a typo, I attach the correct patch.

Thanks!

[A lot of good info snipped.]

While you're waiting for my assignment papers, I will look into this and
try to make everybody happy.

Have fun.

--

Jarmo