Re: [O] [PATCH] Lookup functions, take two
Bastien 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
Re: [O] [PATCH] Lookup functions, take two
Hi Jarmo, Jarmo Hurri 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! 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 -- Bastien
Re: [O] [PATCH] Lookup functions, take two
Bastien 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: [O] [PATCH] Lookup functions, take two
Hi Jarmo, Jarmo Hurri 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: [O] [PATCH] Lookup functions, take two
Bastien writes: > "Dominik, Carsten" 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: [O] [PATCH] Lookup functions, take two
Achim Gratz 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: [O] [PATCH] Lookup functions, take two
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: [O] [PATCH] Lookup functions, take two
Jarmo Hurri 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: [O] [PATCH] Lookup functions, take two
Hi Carsten, "Dominik, Carsten" 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: [O] [PATCH] Lookup functions, take two
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: [O] [PATCH] Lookup functions, take two
> 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: [O] [PATCH] Lookup functions, take two
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: [O] [PATCH] Lookup functions, take two
Bastien 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: [O] [PATCH] Lookup functions, take two
Hi Jarmo, Jarmo Hurri 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
[O] [PATCH] Lookup functions, take two
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
>From 238551583ecf381d576f42b718e16f1601a43e55 Mon Sep 17 00:00:00 2001
From: Jarmo Hurri
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
+@c
