org-tutorials/org-lookups.org

   1 #+OPTIONS:    H:3 num:nil toc:t \n:nil ::t |:t ^:t -:t f:t *:t tex:t 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 student's marks are greater
  95   than or equal to the lower bound; in this case it is the row with
  96   lower bound 30. The student's 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
 154 * Ranking results
 155
 156   Another example application of =org-lookup-all= is an automatic
 157   ranking of results. In the table below, a larger total number of
 158   marks is better. Notice that the Elisp expression also
 159   automatically takes care of ties.
 160
 161 : | group | marks | rank |
 162 : |-------+-------+------|
 163 : | A     |    22 |    2 |
 164 : | B     |    22 |    2 |
 165 : | C     |    14 |    4 |
 166 : | D     |    28 |    1 |
 167 : | E     |     9 |    5 |
 168 :  #+TBLFM: $3='(+ 1 (length (org-lookup-all $2 '(@2$2..@>$2) nil '<)));N
 169
 170 * Frequency counts from raw data
 171   A common situation in the analysis of data is the classification
 172   (grouping) of raw data values for, e.g., visualisation. Often this
 173   is done by counting the frequencies of observations within certain
 174   bounds. The function =org-lookup-all=, combined with other Elisp
 175   functions, can be used to perform this task. This example also shows
 176   how to construct more complicated lookup rules using multiple values
 177   from a table.
 178
 179   Consider the following table with different results from different
 180   groups A-I.
 181 :  #+TBLNAME: raw-data
 182 : | group | result |
 183 : |-------+--------|
 184 : | A     |    2.3 |
 185 : | B     |    4.2 |
 186 : | C     |    1.1 |
 187 : | D     |    3.6 |
 188 : | E     |    4.5 |
 189 : | F     |    2.4 |
 190 : | G     |    1.0 |
 191 : | H     |    2.3 |
 192 : | I     |    2.8 |
 193
 194   We will classify the results into different, mutually exclusive
 195   classes. For example, the observations that will belong to the first
 196   class are in the interval =[1, 1.9]= (endpoints included). In order
 197   to perform this classification, we define the following two-place
 198   predicate function =in-interval=. Notice that the first parameter of
 199   this function is a pair whose first element is the lower bound and
 200   second member the upper bound of the interval.
 201
 202 :  #+BEGIN_SRC emacs-lisp
 203 :    (defun in-interval (bounds el)
 204 :      (and (>= el (car bounds)) (<= el (cadr bounds))))
 205 :  #+END_SRC
 206
 207 :  #+RESULTS:
 208 :  : in-interval
 209
 210   Using this predicate function, we can construct a table with class
 211   boundaries and corresponding frequencies. Note that the first
 212   argument to the function =org-lookup-all=, which is then passed over
 213   as the first argument to the predicate =in-interval=, is the pair of
 214   bounds.
 215
 216 : | lower bound | upper bound | frequency |
 217 : |-------------+-------------+-----------|
 218 : |           1 |         1.9 |         2 |
 219 : |           2 |         2.9 |         4 |
 220 : |           3 |         3.9 |         1 |
 221 : |           4 |         4.9 |         2 |
 222 :  #+TBLFM: $3='(length (org-lookup-all '($1 $2) '(remote(raw-data,@2$2..@>$2)) nil 'in-interval));N
 223 * Conclusion
 224
 225   The org lookup functions can be used for a large number of different
 226   data-dependent calculations. For example, the following spreadsheet
 227   operations familiar to libreoffice or Excel users can be implemented
 228   using them: =HLOOKUP=, =VLOOKUP=, =COUNTIF=, =SUMIF= and
 229   =FREQUENCY=. If you have other interesting examples of the use of
 230   these functions, feel free to send them to the [[https://lists.gnu.org/mailman/listinfo/emacs-orgmode][org mailing list]] and
 231   we will be happy to add them on this page.