1 #+TITLE: Org tutorial on table lookup functions
3 #+EMAIL: jarmo.hurri AT syk DOT fi
4 #+OPTIONS: H:3 num:nil toc:t \n:nil ::t |:t ^:t -:t f:t *:t tex:t d:(HIDE) tags:not-in-toc
5 #+STARTUP: align fold nodlcheck hidestars oddeven lognotestate
6 #+SEQ_TODO: TODO(t) INPROGRESS(i) WAITING(w@) | DONE(d) CANCELED(c@)
7 #+TAGS: Write(w) Update(u) Fix(f) Check(c)
10 #+CATEGORY: org-tutorial
12 # This file is released by its authors and contributors under the GNU
13 # Free Documentation license v1.3 or later, code examples are released
14 # under the GNU General Public License v3 or later.
18 Org provides three different functions for performing searches and
19 data dependent calculations in tables. These functions can, among
20 other things, be used to implement associative arrays, count
21 matching cells, rank results, or group data. The following examples
22 will hopefully help you in getting started with these functions.
24 * Associative array with unique keys
26 The most straightforward use of lookups is to treat part of an org
27 table as an associative array: a key can be used to look up a
30 Say you are taking a trip to Scandinavia, and you want to keep track
31 of how much money you have spent on the trip. You decide to convert
32 all sums to euros. Before your trip you write down the following
33 table of approximate currency rates.
35 : | currency | abbreviation | euros |
36 : |-----------------+--------------+-------|
38 : | Norwegian krone | nok | 0.14 |
39 : | Swedish krona | sek | 0.12 |
40 : | US dollar | usd | 0.77 |
42 In what follows we will use the function =org-lookup-first= and the
43 previous table =rates= to automatically convert the sums in
44 different currencies to euros. The signature of function
45 =org-lookup-first= looks as follows:
47 (org-lookup-first VAL S-LIST R-LIST &optional PREDICATE)
49 Assuming that =PREDICATE= is =nil=, in which case the default
50 predicate =equal= is used, this function does a search for the first
51 instance of =VAL= in =S-LIST= and returns the a value from the
52 corresponding position in =R-LIST=. In the table below, each sum is
53 assigned a currency abbreviation; a lookup is done in table =rates=
54 above in the second column for the corresponding abbreviation, and
55 then the corresponding rate is returned from the third column. For
56 each row only the first four columns need to filled; columns 5 and 6
57 are calculated automatically. Notice that an error results if the
58 key is not found: in the last row, an empty key is being searched
61 : | date | expense | sum | currency | rate | euros |
62 : |-------+------------------+------+----------+--------+--------|
63 : | 1.3. | flights | 324 | eur | 1 | 324 |
64 : | 4.6. | books and maps | 243 | usd | 0.77 | 187.11 |
65 : | 30.7. | rental car | 8300 | sek | 0.12 | 996. |
66 : | 2.7. | hotel | 1150 | sek | 0.12 | 138. |
67 : | 2.7. | lunch | 190 | sek | 0.12 | 22.8 |
68 : | 3.7. | fishing licenses | 1400 | nok | 0.14 | 196. |
69 : | 3.7. | gasoline | 340 | | #ERROR | #ERROR |
70 : #+TBLFM: $5='(org-lookup-first $4 '(remote(rates,@2$2..@>$2)) '(remote(rates,@2$3..@>$3)))::$6=$5*$3
72 * Multiple matches with preferred ordering
74 A common task for teachers is the assignment of exam grades from
75 total marks. The starting point for such grading is a table with
76 grade boundaries. Below is one such table, with the rows in
77 increasing order of the lower bound required for a particular grade.
79 : #+TBLNAME: grade-boundaries
80 : | lower bound | grade |
81 : |-------------+-------|
88 We will use the function =org-lookup-last= and the previous table
89 =grade-boundaries= to assign grades to students based on their
90 marks. The signature of function =org-lookup-last= is exactly like
91 the signature of =org-lookup-first=:
93 (org-lookup-last VAL S-LIST R-LIST &optional PREDICATE)
95 However, this function does a search for the /last/ match in
96 =S-LIST= and returns the a value from the corresponding position in
97 =R-LIST=. Here the idea of the lookup used in assigning the grade is
98 as follows. Say a student's exam result is 33 marks. We look for
99 the /last/ row in the table for which the student's marks are greater
100 than or equal to the lower bound; in this case it is the row with
101 lower bound 30. The student's grade is the corresponding element from
102 the second column, in this case a B.
104 Thus, given the number of marks =VAL= of a student, we find the last
105 row of the first column of table =grade-boundaries= for which the
106 lower bound =S= fulfils ~(>= VAL S)~. Thus we will use ~>=~ as
107 =PREDICATE= to perform the matching. Note that =VAL= and =S= are
108 assigned as arguments to the predicate in the order they are in the
109 signature of =org-lookup-last=, where =VAL= precedes =S-LIST=. The
110 following table does the conversion from total marks to the final
111 grade. Notice the literal interpolation =L= of table values into
112 the Elisp formula, which is needed because some values are numbers
113 and some are symbols.
115 : | student | marks | grade |
116 : |---------+-------+-------|
121 : #+TBLFM: $3='(org-lookup-last $2 '(remote(grade-boundaries,@2$1..@>$1)) '(remote(grade-boundaries,@2$2..@>$2)) '>=);L
123 * Counting matching cells
125 The function =org-lookup-all= can not be used by itself in a table
126 equation, because it returns a list of values. However, powerful
127 lookup tasks can be performed by combining the function with other
130 As a simple example consider counting the number of missing values
131 in a table. The signature of function =org-lookup-all= is exactly
132 like the signatures of the other two lookup functions:
134 (org-lookup-all VAL S-LIST R-LIST &optional PREDICATE)
136 However, this function does a search for the /all/ matches in
137 =S-LIST= and returns the all corresponding values from the
138 corresponding positions in =R-LIST=. As is the case with
139 =org-lookup-first= and =org-lookup-last=, if =R-LIST= is =nil=, then
140 the corresponding matching values of =S-LIST= are returned
141 directly. Notice the use of the =E= flag to retain empty fields in
142 the range. Also notice that in this case we are doing the lookup in
143 a true two-dimensional range, which is thus also possible
145 : | group | round 1 | round 2 |
146 : |-------+---------+---------|
155 : |-------+---------+---------|
156 : | total | missing | 7 |
157 : #+TBLFM: @>$3='(length(org-lookup-all "" '(@2$2..@-1$3) nil));E
161 Another example application of =org-lookup-all= is an automatic
162 ranking of results. In the table below, a larger total number of
163 marks is better. Notice that the Elisp expression also
164 automatically takes care of ties.
166 : | group | marks | rank |
167 : |-------+-------+------|
173 : #+TBLFM: $3='(+ 1 (length (org-lookup-all $2 '(@2$2..@>$2) nil '<)));N
175 * Frequency counts from raw data
176 A common situation in the analysis of data is the classification
177 (grouping) of raw data values for, e.g., visualisation. Often this
178 is done by counting the frequencies of observations within certain
179 bounds. The function =org-lookup-all=, combined with other Elisp
180 functions, can be used to perform this task. This example also shows
181 how to construct more complicated lookup rules using multiple values
184 Consider the following table with different results from different
186 : #+TBLNAME: raw-data
199 We will classify the results into different, mutually exclusive
200 classes. For example, the observations that will belong to the first
201 class are in the interval =[1, 1.9]= (endpoints included). In order
202 to perform this classification, we define the following two-place
203 predicate function =in-interval=. Notice that the first parameter of
204 this function is a pair whose first element is the lower bound and
205 second member the upper bound of the interval.
207 : #+BEGIN_SRC emacs-lisp
208 : (defun in-interval (bounds el)
209 : (and (>= el (car bounds)) (<= el (cadr bounds))))
215 Using this predicate function, we can construct a table with class
216 boundaries and corresponding frequencies. Note that the first
217 argument to the function =org-lookup-all=, which is then passed over
218 as the first argument to the predicate =in-interval=, is the pair of
221 : | lower bound | upper bound | frequency |
222 : |-------------+-------------+-----------|
227 : #+TBLFM: $3='(length (org-lookup-all '($1 $2) '(remote(raw-data,@2$2..@>$2)) nil 'in-interval));N
230 The org lookup functions can be used for a large number of different
231 data-dependent calculations. For example, the following spreadsheet
232 operations familiar to libreoffice or Excel users can be implemented
233 using them: =HLOOKUP=, =VLOOKUP=, =COUNTIF=, =SUMIF= and
234 =FREQUENCY=. If you have other interesting examples of the use of
235 these functions, feel free to send them to the [[https://lists.gnu.org/mailman/listinfo/emacs-orgmode][org mailing list]] and
236 we will be happy to add them on this page.