Added a new tutorial on table lookup functions.
[worg.git] / org-tutorials / org-lookups.org
1 #+OPTIONS:    H:3 num:nil toc:t \n:nil @:t ::t |:t ^:t -:t f:t *:t TeX:t LaTeX:t skip:nil d:(HIDE) tags:not-in-toc
2 #+STARTUP:    align fold nodlcheck hidestars oddeven lognotestate
3 #+SEQ_TODO:   TODO(t) INPROGRESS(i) WAITING(w@) | DONE(d) CANCELED(c@)
4 #+TAGS:       Write(w) Update(u) Fix(f) Check(c) 
5 #+TITLE:      Org tutorial on table lookup functions
6 #+AUTHOR:     Jarmo Hurri
7 #+EMAIL:      jarmo.hurri AT syk DOT fi
8 #+LANGUAGE:   en
9 #+PRIORITIES: A C B
10 #+CATEGORY:   org-tutorial
11 * Introduction
12
13   Org provides three different functions for performing searches and
14   data dependent calculations in tables. These functions can, among
15   other things, be used to implement associative arrays, count
16   matching cells, rank results, or group data. The following examples
17   will hopefully help you in getting started with these functions.
18
19 * Associative array with unique keys
20
21   The most straightforward use of lookups is to treat part of an org
22   table as an associative array: a key can be used to look up a
23   corresponding value. 
24
25   Say you are taking a trip to Scandinavia, and you want to keep track
26   of how much money you have spent on the trip. You decide to convert
27   all sums to euros. Before your trip you write down the following
28   table of approximate currency rates.
29   #+TBLNAME: rates
30   | currency        | abbreviation | euros |
31   |-----------------+--------------+-------|
32   | euro            | eur          |     1 |
33   | Norwegian krone | nok          |  0.14 |
34   | Swedish krona   | sek          |  0.12 |
35   | US dollar       | usd          |  0.77 |
36
37   In what follows we will use the function =org-lookup-first= and the
38   previous table =rates= to automatically convert the sums in
39   different currencies to euros. The signature of function
40   =org-lookup-first= looks as follows:
41   #+BEGIN_SRC elisp
42     (org-lookup-first VAL S-LIST R-LIST &optional PREDICATE)  
43   #+END_SRC
44   Assuming that =PREDICATE= is =nil=, in which case the default
45   predicate =equal= is used, this function does a search for the first
46   instance of =VAL= in =S-LIST= and returns the a value from the
47   corresponding position in =R-LIST=. In the table below, each sum is
48   assigned a currency abbreviation; a lookup is done in table =rates=
49   above in the second column for the corresponding abbreviation, and
50   then the corresponding rate is returned from the third column. For
51   each row only the first four columns need to filled; columns 5 and 6
52   are calculated automatically. Notice that an error results if the
53   key is not found: in the last row, an empty key is being searched
54   for.
55
56   |  date | expense          |  sum | currency |   rate |  euros |
57   |-------+------------------+------+----------+--------+--------|
58   |  1.3. | flights          |  324 | eur      |      1 |    324 |
59   |  4.6. | books and maps   |  243 | usd      |   0.77 | 187.11 |
60   | 30.7. | rental car       | 8300 | sek      |   0.12 |   996. |
61   |  2.7. | hotel            | 1150 | sek      |   0.12 |   138. |
62   |  2.7. | lunch            |  190 | sek      |   0.12 |   22.8 |
63   |  3.7. | fishing licenses | 1400 | nok      |   0.14 |   196. |
64   |  3.7. | gasoline         |  340 |          | #ERROR | #ERROR |
65   #+TBLFM: $5='(org-lookup-first $4 '(remote(rates,@2$2..@>$2)) '(remote(rates,@2$3..@>$3)))::$6=$5*$3
66
67 * Multiple matches with preferred ordering
68
69   A common task for teachers is the assignment of exam grades from
70   total marks. The starting point for such grading is a table with
71   grade boundaries. Below is one such table, with the rows in
72   increasing order of the lower bound required for a particular grade.
73
74   #+TBLNAME: grade-boundaries
75   | lower bound | grade |
76   |-------------+-------|
77   |           0 | F     |
78   |          10 | D     |
79   |          20 | C     |
80   |          30 | B     |
81   |          40 | A     |
82
83   We will use the function =org-lookup-last= and the previous table
84   =grade-boundaries= to assign grades to students based on their
85   marks. The signature of function =org-lookup-last= is exactly like
86   the signature of =org-lookup-first=:
87   #+BEGIN_SRC elisp
88     (org-lookup-last VAL S-LIST R-LIST &optional PREDICATE)  
89   #+END_SRC
90   However, this function does a search for the /last/ match in
91   =S-LIST= and returns the a value from the corresponding position in
92   =R-LIST=. Here the idea of the lookup used in assigning the grade is
93   as follows. Say a student's exam result is 33 marks.  We look for
94   the /last/ row in the table for which the students marks are greater
95   than or equal to the lower bound; in this case it is the row with
96   lower bound 30. The students grade is the corresponding element from
97   the second column, in this case a B.
98
99   Thus, given the number of marks =VAL= of a student, we find the last
100   row of the first column of table =grade-boundaries= for which the
101   lower bound =S= fulfils ~(>= VAL S)~. Thus we will use ~>=~ as
102   =PREDICATE= to perform the matching. Note that =VAL= and =S= are
103   assigned as arguments to the predicate in the order they are in the
104   signature of =org-lookup-last=, where =VAL= precedes =S-LIST=. The
105   following table does the conversion from total marks to the final
106   grade.  Notice the literal interpolation =L= of table values into
107   the Elisp formula, which is needed because some values are numbers
108   and some are symbols.
109
110   | student | marks | grade |
111   |---------+-------+-------|
112   | X       |    30 | B     |
113   | Y       |    29 | C     |
114   | Z       |     5 | F     |
115   | W       |    55 | A     |
116   #+TBLFM: $3='(org-lookup-last $2 '(remote(grade-boundaries,@2$1..@>$1)) '(remote(grade-boundaries,@2$2..@>$2)) '>=);L
117
118 * Counting matching cells
119
120   The function =org-lookup-all= can not be used by itself in a table
121   equation, because it returns a list of values. However, powerful
122   lookup tasks can be performed by combining the function with other
123   Elisp functions.
124
125   As a simple example consider counting the number of missing values
126   in a table. The signature of function =org-lookup-all= is exactly
127   like the signatures of the other two lookup functions:
128   #+BEGIN_SRC elisp
129     (org-lookup-all VAL S-LIST R-LIST &optional PREDICATE)  
130   #+END_SRC
131   However, this function does a search for the /all/ matches in
132   =S-LIST= and returns the all corresponding values from the
133   corresponding positions in =R-LIST=. As is the case with
134   =org-lookup-first= and =org-lookup-last=, if =R-LIST= is =nil=, then
135   the corresponding matching values of =S-LIST= are returned
136   directly. Notice the use of the =E= flag to retain empty fields in
137   the range. Also notice that in this case we are doing the lookup in
138   a true two-dimensional range, which is thus also possible
139
140   | group | round 1 | round 2 |
141   |-------+---------+---------|
142   | A     |         |     2.4 |
143   | B     |     4.7 |      11 |
144   | C     |         |         |
145   | D     |       5 |         |
146   | E     |         |     7.2 |
147   | F     |     3.2 |     4.3 |
148   | G     |         |     4.4 |
149   | H     |         |       8 |
150   |-------+---------+---------|
151   | total | missing |       7 |
152   #+TBLFM: @>$3='(length(org-lookup-all "" '(@2$2..@-1$3) nil));E
153 * Ranking results
154
155   Another example application of =org-lookup-all= is an automatic
156   ranking of results. In the table below, a larger total number of
157   marks is better. Notice that the Elisp expression also
158   automatically takes care of ties.
159
160   | group | marks | rank |
161   |-------+-------+------|
162   | A     |    22 |    2 |
163   | B     |    22 |    2 |
164   | C     |    14 |    4 |
165   | D     |    28 |    1 |
166   | E     |     9 |    5 |
167   #+TBLFM: $3='(+ 1 (length (org-lookup-all $2 '(@2$2..@>$2) nil '<)));N
168 * Frequency counts from raw data
169   A common situation in the analysis of data is the classification
170   (grouping) of raw data values for, e.g., visualisation. Often this
171   is done by counting the frequencies of observations within certain
172   bounds. The function =org-lookup-all=, combined with other Elisp
173   functions, can be used to perform this task. This example also shows
174   how to construct more complicated lookup rules using multiple values
175   from a table.
176
177   Consider the following table with different results from different
178   groups A-I.
179   #+TBLNAME: raw-data
180   | group | result |
181   |-------+--------|
182   | A     |    2.3 |
183   | B     |    4.2 |
184   | C     |    1.1 |
185   | D     |    3.6 |
186   | E     |    4.5 |
187   | F     |    2.4 |
188   | G     |    1.0 |
189   | H     |    2.3 |
190   | I     |    2.8 |
191
192   We will classify the results into different, mutually exclusive
193   classes. For example, the observations that will belong to the first
194   class are in the interval =[1, 1.9]= (endpoints included). In order
195   to perform this classification, we define the following two-place
196   predicate function =in-interval=. Notice that the first parameter of
197   this function is a pair whose first element is the lower bound and
198   second member the upper bound of the interval.
199
200   #+BEGIN_SRC emacs-lisp
201     (defun in-interval (bounds el)
202       (and (>= el (car bounds)) (<= el (cadr bounds))))
203   #+END_SRC
204
205   #+RESULTS:
206   : in-interval
207
208   Using this predicate function, we can construct a table with class
209   boundaries and corresponding frequencies. Note that the first
210   argument to the function =org-lookup-all=, which is then passed over
211   as the first argument to the predicate =in-interval=, is the pair of
212   bounds.
213
214   | lower bound | upper bound | frequency |
215   |-------------+-------------+-----------|
216   |           1 |         1.9 |         2 |
217   |           2 |         2.9 |         4 |
218   |           3 |         3.9 |         1 |
219   |           4 |         4.9 |         2 |
220   #+TBLFM: $3='(length (org-lookup-all '($1 $2) '(remote(raw-data,@2$2..@>$2)) nil 'in-interval));N
221 * Conclusion
222
223   The org lookup functions can be used for a large number of different
224   data-dependent calculations. For example, the following spreadsheet
225   operations familiar to libreoffice or Excel users can be implemented
226   using them: =HLOOKUP=, =VLOOKUP=, =COUNTIF=, =SUMIF= and
227   =FREQUENCY=. If you have other interesting examples of the use of
228   these functions, feel free to send them to the [[https://lists.gnu.org/mailman/listinfo/emacs-orgmode][org mailing list]] and
229   we will be happy to add them on this page.