From mboxrd@z Thu Jan 1 00:00:00 1970 From: Jarmo Hurri Subject: [PATCH] Lookup functions for tables Date: Wed, 19 Sep 2012 19:48:25 +0300 Message-ID: <87392e557q.fsf@syk.fi> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Return-path: Received: from eggs.gnu.org ([208.118.235.92]:47755) by lists.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1TENSC-00059d-T4 for emacs-orgmode@gnu.org; Wed, 19 Sep 2012 12:48:54 -0400 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1TENS8-0001ad-9C for emacs-orgmode@gnu.org; Wed, 19 Sep 2012 12:48:48 -0400 Received: from plane.gmane.org ([80.91.229.3]:57629) by eggs.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1TENS7-0001aI-UC for emacs-orgmode@gnu.org; Wed, 19 Sep 2012 12:48:44 -0400 Received: from list by plane.gmane.org with local (Exim 4.69) (envelope-from ) id 1TENS9-0002xx-Kq for emacs-orgmode@gnu.org; Wed, 19 Sep 2012 18:48:45 +0200 Received: from cs181237063.pp.htv.fi ([82.181.237.63]) by main.gmane.org with esmtp (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Wed, 19 Sep 2012 18:48:45 +0200 Received: from jarmo.hurri by cs181237063.pp.htv.fi with local (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Wed, 19 Sep 2012 18:48:45 +0200 List-Id: "General discussions about Org-mode." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org Sender: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org To: emacs-orgmode@gnu.org * 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