org-tutorials/org-lookups.org

   1 #+TITLE:      Org tutorial on table lookup functions
   2 #+AUTHOR:     Jarmo Hurri
   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)
   8 #+LANGUAGE:   en
   9 #+PRIORITIES: A C B
  10 #+CATEGORY:   org-tutorial
  11
  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.
  15
  16 * Introduction
  17
  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.
  23
  24 * Associative array with unique keys
  25
  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
  28   corresponding value.
  29
  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.
  34 :  #+TBLNAME: rates
  35 : | currency        | abbreviation | euros |
  36 : |-----------------+--------------+-------|
  37 : | euro            | eur          |     1 |
  38 : | Norwegian krone | nok          |  0.14 |
  39 : | Swedish krona   | sek          |  0.12 |
  40 : | US dollar       | usd          |  0.77 |
  41
  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:
  46   #+BEGIN_SRC elisp
  47     (org-lookup-first VAL S-LIST R-LIST &optional PREDICATE)
  48   #+END_SRC
  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
  59   for.
  60
  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
  71
  72 * Multiple matches with preferred ordering
  73
  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.
  78
  79 :  #+TBLNAME: grade-boundaries
  80 : | lower bound | grade |
  81 : |-------------+-------|
  82 : |           0 | F     |
  83 : |          10 | D     |
  84 : |          20 | C     |
  85 : |          30 | B     |
  86 : |          40 | A     |
  87
  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=:
  92   #+BEGIN_SRC elisp
  93     (org-lookup-last VAL S-LIST R-LIST &optional PREDICATE)
  94   #+END_SRC
  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.
 103
 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.
 114
 115 : | student | marks | grade |
 116 : |---------+-------+-------|
 117 : | X       |    30 | B     |
 118 : | Y       |    29 | C     |
 119 : | Z       |     5 | F     |
 120 : | W       |    55 | A     |
 121 :  #+TBLFM: $3='(org-lookup-last $2 '(remote(grade-boundaries,@2$1..@>$1)) '(remote(grade-boundaries,@2$2..@>$2)) '>=);L
 122
 123 * Counting matching cells
 124
 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
 128   Elisp functions.
 129
 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:
 133   #+BEGIN_SRC elisp
 134     (org-lookup-all VAL S-LIST R-LIST &optional PREDICATE)
 135   #+END_SRC
 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
 144
 145 : | group | round 1 | round 2 |
 146 : |-------+---------+---------|
 147 : | A     |         |     2.4 |
 148 : | B     |     4.7 |      11 |
 149 : | C     |         |         |
 150 : | D     |       5 |         |
 151 : | E     |         |     7.2 |
 152 : | F     |     3.2 |     4.3 |
 153 : | G     |         |     4.4 |
 154 : | H     |         |       8 |
 155 : |-------+---------+---------|
 156 : | total | missing |       7 |
 157 :  #+TBLFM: @>$3='(length(org-lookup-all "" '(@2$2..@-1$3) nil));E
 158
 159 * Ranking results
 160
 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.
 165
 166 : | group | marks | rank |
 167 : |-------+-------+------|
 168 : | A     |    22 |    2 |
 169 : | B     |    22 |    2 |
 170 : | C     |    14 |    4 |
 171 : | D     |    28 |    1 |
 172 : | E     |     9 |    5 |
 173 :  #+TBLFM: $3='(+ 1 (length (org-lookup-all $2 '(@2$2..@>$2) nil '<)));N
 174
 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
 182   from a table.
 183
 184   Consider the following table with different results from different
 185   groups A-I.
 186 :  #+TBLNAME: raw-data
 187 : | group | result |
 188 : |-------+--------|
 189 : | A     |    2.3 |
 190 : | B     |    4.2 |
 191 : | C     |    1.1 |
 192 : | D     |    3.6 |
 193 : | E     |    4.5 |
 194 : | F     |    2.4 |
 195 : | G     |    1.0 |
 196 : | H     |    2.3 |
 197 : | I     |    2.8 |
 198
 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.
 206
 207 :  #+BEGIN_SRC emacs-lisp
 208 :    (defun in-interval (bounds el)
 209 :      (and (>= el (car bounds)) (<= el (cadr bounds))))
 210 :  #+END_SRC
 211
 212 :  #+RESULTS:
 213 :  : in-interval
 214
 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
 219   bounds.
 220
 221 : | lower bound | upper bound | frequency |
 222 : |-------------+-------------+-----------|
 223 : |           1 |         1.9 |         2 |
 224 : |           2 |         2.9 |         4 |
 225 : |           3 |         3.9 |         1 |
 226 : |           4 |         4.9 |         2 |
 227 :  #+TBLFM: $3='(length (org-lookup-all '($1 $2) '(remote(raw-data,@2$2..@>$2)) nil 'in-interval));N
 228 * Conclusion
 229
 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.