emacs-orgmode@gnu.org archives
 help / color / mirror / code / Atom feed
* [PATCH] Lookup functions for tables
@ 2012-09-19 16:48 Jarmo Hurri
  2012-09-19 16:53 ` Jarmo Hurri
                   ` (2 more replies)
  0 siblings, 3 replies; 14+ messages in thread
From: Jarmo Hurri @ 2012-09-19 16:48 UTC (permalink / raw)
  To: emacs-orgmode


* 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

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

* Re: [PATCH] Lookup functions for tables
  2012-09-19 16:48 [PATCH] Lookup functions for tables Jarmo Hurri
@ 2012-09-19 16:53 ` Jarmo Hurri
  2012-09-19 17:29   ` Bastien
                     ` (2 more replies)
  2012-09-19 19:13 ` Achim Gratz
  2012-09-20 15:48 ` Michael Brand
  2 siblings, 3 replies; 14+ messages in thread
From: Jarmo Hurri @ 2012-09-19 16:53 UTC (permalink / raw)
  To: emacs-orgmode


Greetings.

The patch posted here is the first patch I have ever made with git, so I
hope it is ok.

I tested the new functions locally, as can be seen from the examples. I
also ran make without problems. I am, however, unable to install org
from the git version (for some unknown reason), so I was unable to test
the final, committed version.

Happy to contribute if possible.

--
Jarmo

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

* Re: [PATCH] Lookup functions for tables
  2012-09-19 16:53 ` Jarmo Hurri
@ 2012-09-19 17:29   ` Bastien
  2012-09-20  8:45     ` Jarmo Hurri
  2012-09-19 17:29   ` Bastien
  2012-09-19 19:24   ` Achim Gratz
  2 siblings, 1 reply; 14+ messages in thread
From: Bastien @ 2012-09-19 17:29 UTC (permalink / raw)
  To: Jarmo Hurri; +Cc: emacs-orgmode

Hi Jarmo,

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

> The patch posted here is the first patch I have ever made with git, so I
> hope it is ok.

thanks for contributing to Org!  The patch is longer than 20 lines, so
we would need you to sign the FSF papers to be able to accept it.

The form is here:

  http://orgmode.org/w/?p=org-mode.git;a=blob_plain;f=request-assign-future.txt;hb=HEAD

It takes a while for this to be processed -- so we will have time to
polish your contribution if needed :)

Thanks!

-- 
 Bastien

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

* Re: [PATCH] Lookup functions for tables
  2012-09-19 16:53 ` Jarmo Hurri
  2012-09-19 17:29   ` Bastien
@ 2012-09-19 17:29   ` Bastien
  2012-09-19 19:24   ` Achim Gratz
  2 siblings, 0 replies; 14+ messages in thread
From: Bastien @ 2012-09-19 17:29 UTC (permalink / raw)
  To: Jarmo Hurri; +Cc: emacs-orgmode

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

> I am, however, unable to install org
> from the git version (for some unknown reason), so I was unable to test
> the final, committed version.

Please let us know what's wrong here, we will try to help.

-- 
 Bastien

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

* Re: [PATCH] Lookup functions for tables
  2012-09-19 16:48 [PATCH] Lookup functions for tables Jarmo Hurri
  2012-09-19 16:53 ` Jarmo Hurri
@ 2012-09-19 19:13 ` Achim Gratz
  2012-09-20  9:46   ` Jarmo Hurri
  2012-09-20 15:48 ` Michael Brand
  2 siblings, 1 reply; 14+ messages in thread
From: Achim Gratz @ 2012-09-19 19:13 UTC (permalink / raw)
  To: emacs-orgmode

Jarmo Hurri writes:
> * lisp/org-table.el: added functions org-lookup-first and
> org-lookup-last
> * doc/org.texi: documented the use of lookup functions
>
> TINYCHANGE

Well, it doesn't become a TINYCHANGE just because you put that stamp on
it.  This patch clearly is over 20 non-trivial lines, so you will need
to assign copyright to the FSF.


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

Waldorf MIDI Implementation & additional documentation:
http://Synth.Stromeko.net/Downloads.html#WaldorfDocs

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

* Re: [PATCH] Lookup functions for tables
  2012-09-19 16:53 ` Jarmo Hurri
  2012-09-19 17:29   ` Bastien
  2012-09-19 17:29   ` Bastien
@ 2012-09-19 19:24   ` Achim Gratz
  2012-09-20 10:30     ` Jarmo Hurri
  2 siblings, 1 reply; 14+ messages in thread
From: Achim Gratz @ 2012-09-19 19:24 UTC (permalink / raw)
  To: emacs-orgmode

Jarmo Hurri writes:
> I tested the new functions locally, as can be seen from the examples. I
> also ran make without problems. I am, however, unable to install org
> from the git version (for some unknown reason), so I was unable to test
> the final, committed version.

I can't decode that last part… you installed Org from git with make,
then made your changes (also in git) and now Org does or doesn't do what
when you try to install?


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

Wavetables for the Waldorf Blofeld:
http://Synth.Stromeko.net/Downloads.html#BlofeldUserWavetables

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

* Re: [PATCH] Lookup functions for tables
  2012-09-19 17:29   ` Bastien
@ 2012-09-20  8:45     ` Jarmo Hurri
  2012-09-21 16:03       ` Bastien
  0 siblings, 1 reply; 14+ messages in thread
From: Jarmo Hurri @ 2012-09-20  8:45 UTC (permalink / raw)
  To: emacs-orgmode

Bastien <bzg@altern.org> writes:

> The patch is longer than 20 lines, so we would need you to sign the
> FSF papers to be able to accept it.

Request submitted.

--
Jarmo

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

* Re: [PATCH] Lookup functions for tables
  2012-09-19 19:13 ` Achim Gratz
@ 2012-09-20  9:46   ` Jarmo Hurri
  0 siblings, 0 replies; 14+ messages in thread
From: Jarmo Hurri @ 2012-09-20  9:46 UTC (permalink / raw)
  To: emacs-orgmode

Achim Gratz <Stromeko@nexgo.de> writes:

> Well, it doesn't become a TINYCHANGE just because you put that stamp
> on it.  This patch clearly is over 20 non-trivial lines, so you will
> need to assign copyright to the FSF.

The program code (one macro and two calls) is less than 20 lines, and I
was hoping documentation would not count. Now I know there is no
hope. :-)

--
Jarmo

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

* Re: [PATCH] Lookup functions for tables
  2012-09-19 19:24   ` Achim Gratz
@ 2012-09-20 10:30     ` Jarmo Hurri
  2012-09-20 19:06       ` Achim Gratz
  0 siblings, 1 reply; 14+ messages in thread
From: Jarmo Hurri @ 2012-09-20 10:30 UTC (permalink / raw)
  To: emacs-orgmode


>> I tested the new functions locally, as can be seen from the
>> examples. I also ran make without problems. I am, however, unable to
>> install org from the git version (for some unknown reason), so I was
>> unable to test the final, committed version.
>
> I can't decode that last part… you installed Org from git with make,
> then made your changes (also in git) and now Org does or doesn't do
> what when you try to install?

Not being able to decode is understandable, because it wasn't very
clear.

Yesterday my org installation from git did not work, but today, when I
took a fresh look at the situation, I was able to notice a path problem
which I solved. I now tested the committed version with my changes, and
it works.

A couple of points about the process:

1. Already yesterday I ran make to make sure that org still compiles
   ok. It did, although the used cl function 'position' does give two
   warnings.

2. When I tried to run "make test", it failed because I don't have ERT
   installed. I am running Emacs 23.3.1, which does not include ERT, and
   I could not find a package providing ert.el in the repositories of
   Fedora 16. Because I was running out of time, I decided not to start
   installing ERT manually.

   Today I installed ERT by hand, but "make test" still does not work,
   because the test script runs "emacs -Q" and thus does not load my
   init file (where the directory of ERT is added to path). I don't want
   to make a manually installed ERT system-wide, so I had to skip the
   tests.

--

Jarmo

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

* Re: [PATCH] Lookup functions for tables
  2012-09-19 16:48 [PATCH] Lookup functions for tables Jarmo Hurri
  2012-09-19 16:53 ` Jarmo Hurri
  2012-09-19 19:13 ` Achim Gratz
@ 2012-09-20 15:48 ` Michael Brand
  2012-09-21  5:44   ` Jarmo Hurri
  2 siblings, 1 reply; 14+ messages in thread
From: Michael Brand @ 2012-09-20 15:48 UTC (permalink / raw)
  To: Jarmo Hurri; +Cc: emacs-orgmode

Hi Jarmo

On Wed, Sep 19, 2012 at 6:48 PM, Jarmo Hurri <jarmo.hurri@syk.fi> wrote:
> --- 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).

In my opinion “Field and range formulas” and “Column formulas” should
stay together, so I suggest

 * 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

for the menu and the corresponding move for the texinfo subsection.

Michael

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

* Re: [PATCH] Lookup functions for tables
  2012-09-20 10:30     ` Jarmo Hurri
@ 2012-09-20 19:06       ` Achim Gratz
  0 siblings, 0 replies; 14+ messages in thread
From: Achim Gratz @ 2012-09-20 19:06 UTC (permalink / raw)
  To: emacs-orgmode

Jarmo Hurri writes:
> 2. When I tried to run "make test", it failed because I don't have ERT
>    installed. I am running Emacs 23.3.1, which does not include ERT, and
>    I could not find a package providing ert.el in the repositories of
>    Fedora 16. Because I was running out of time, I decided not to start
>    installing ERT manually.

Add this to local.mk:

BTEST_POST = -L /path/to/ert

I keep this installed in testing/ert for historical reasons.  Get the
latest version for emacs 23 from:

https://github.com/ohler/ert


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

SD adaptation for Waldorf microQ V2.22R2:
http://Synth.Stromeko.net/Downloads.html#WaldorfSDada

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

* Re: [PATCH] Lookup functions for tables
  2012-09-20 15:48 ` Michael Brand
@ 2012-09-21  5:44   ` Jarmo Hurri
  2012-09-21  6:43     ` Michael Brand
  0 siblings, 1 reply; 14+ messages in thread
From: Jarmo Hurri @ 2012-09-21  5:44 UTC (permalink / raw)
  To: emacs-orgmode


>> --- 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).
>
> In my opinion “Field and range formulas” and “Column formulas” should
> stay together, so I suggest
>
>  * 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
>
> for the menu and the corresponding move for the texinfo subsection.

Ok. I guess this also means changing the (next,prev,up) parts of
@node-commands. Do you want me to make the change and submit a new
patch?

--
Jarmo

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

* Re: [PATCH] Lookup functions for tables
  2012-09-21  5:44   ` Jarmo Hurri
@ 2012-09-21  6:43     ` Michael Brand
  0 siblings, 0 replies; 14+ messages in thread
From: Michael Brand @ 2012-09-21  6:43 UTC (permalink / raw)
  To: Jarmo Hurri; +Cc: emacs-orgmode

Hi Jarmo

On Fri, Sep 21, 2012 at 7:44 AM, Jarmo Hurri <jarmo.hurri@syk.fi> wrote:
> Ok. I guess this also means changing the (next,prev,up) parts of
> @node-commands.

Yes.

> Do you want me to make the change and submit a new patch?

Yes please, I suggest to wait a bit if there will be more comments.

Michael

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

* Re: [PATCH] Lookup functions for tables
  2012-09-20  8:45     ` Jarmo Hurri
@ 2012-09-21 16:03       ` Bastien
  0 siblings, 0 replies; 14+ messages in thread
From: Bastien @ 2012-09-21 16:03 UTC (permalink / raw)
  To: Jarmo Hurri; +Cc: emacs-orgmode

Hi Jarmo

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

> Bastien <bzg@altern.org> writes:
>
>> The patch is longer than 20 lines, so we would need you to sign the
>> FSF papers to be able to accept it.
>
> Request submitted.

Thanks -- I added your name in the "pending" section of this Worg page
(which will get updated soon) :

http://orgmode.org/worg/org-contribute.html#sec-6-2

-- 
 Bastien

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

end of thread, other threads:[~2012-09-21 16:03 UTC | newest]

Thread overview: 14+ messages (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
2012-09-19 16:48 [PATCH] Lookup functions for tables Jarmo Hurri
2012-09-19 16:53 ` Jarmo Hurri
2012-09-19 17:29   ` Bastien
2012-09-20  8:45     ` Jarmo Hurri
2012-09-21 16:03       ` Bastien
2012-09-19 17:29   ` Bastien
2012-09-19 19:24   ` Achim Gratz
2012-09-20 10:30     ` Jarmo Hurri
2012-09-20 19:06       ` Achim Gratz
2012-09-19 19:13 ` Achim Gratz
2012-09-20  9:46   ` Jarmo Hurri
2012-09-20 15:48 ` Michael Brand
2012-09-21  5:44   ` Jarmo Hurri
2012-09-21  6:43     ` Michael Brand

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).