emacs-orgmode@gnu.org archives
 help / color / mirror / code / Atom feed
* [PATCH] Lookup functions, take two
@ 2012-09-23 15:26 Jarmo Hurri
  2012-09-24 14:01 ` Bastien
  2012-09-25 19:39 ` Achim Gratz
  0 siblings, 2 replies; 15+ messages in thread
From: Jarmo Hurri @ 2012-09-23 15:26 UTC (permalink / raw)
  To: emacs-orgmode

[-- 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


^ permalink raw reply related	[flat|nested] 15+ messages in thread

end of thread, other threads:[~2012-09-26 17:48 UTC | newest]

Thread overview: 15+ messages (download: mbox.gz follow: Atom feed
-- links below jump to the message on this page --
2012-09-23 15:26 [PATCH] Lookup functions, take two Jarmo Hurri
2012-09-24 14:01 ` Bastien
2012-09-24 17:33   ` Jarmo Hurri
2012-09-25  1:49     ` Bastien
2012-09-25  4:13       ` Jarmo Hurri
2012-09-25 10:04         ` Bastien
2012-09-25  9:15       ` Dominik, Carsten
2012-09-25 10:02         ` Bastien
2012-09-26 13:00           ` Jarmo Hurri
2012-09-26 13:14             ` Bastien
2012-09-25 19:39 ` Achim Gratz
2012-09-25 22:43   ` Bastien
2012-09-26 13:45     ` Jarmo Hurri
2012-09-26 14:26       ` Bastien
2012-09-26 17:47         ` Jarmo Hurri

Code repositories for project(s) associated with this public inbox

	https://git.savannah.gnu.org/cgit/emacs/org-mode.git

This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox;
as well as URLs for read-only IMAP folder(s) and NNTP newsgroup(s).