From: Jarmo Hurri <jarmo.hurri@syk.fi>
To: emacs-orgmode@gnu.org
Subject: [PATCH] Lookup functions, take two
Date: Sun, 23 Sep 2012 18:26:19 +0300 [thread overview]
Message-ID: <87y5k0g3qc.fsf@syk.fi> (raw)
[-- 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
next reply other threads:[~2012-09-23 15:26 UTC|newest]
Thread overview: 15+ messages / expand[flat|nested] mbox.gz Atom feed top
2012-09-23 15:26 Jarmo Hurri [this message]
2012-09-24 14:01 ` [PATCH] Lookup functions, take two 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
Avoid top-posting and favor interleaved quoting:
https://en.wikipedia.org/wiki/Posting_style#Interleaved_style
List information: https://www.orgmode.org/
* Reply using the --to, --cc, and --in-reply-to
switches of git-send-email(1):
git send-email \
--in-reply-to=87y5k0g3qc.fsf@syk.fi \
--to=jarmo.hurri@syk.fi \
--cc=emacs-orgmode@gnu.org \
/path/to/YOUR_REPLY
https://kernel.org/pub/software/scm/git/docs/git-send-email.html
* If your mail client supports setting the In-Reply-To header
via mailto: links, try the mailto: link
Be sure your reply has a Subject: header at the top and a blank line
before the message body.
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).