3

Let's say we have a table with a column like this:

|---|
| S |
| K |
| K |
| K |
| R |
| S |
| K |
| R |
| R |
| K |

Can org mode calculate and report some kind of table of occurrences or percentages of the items in this column (K 5 times, R three times, S twice)?

A search on Emacs.stackexchange for keywords like "occurrence", "statistics", or "histogram" doesn't seem to lead to any related questions.

2
  • 1
    I would use orgaggregate for that.
    – crocefisso
    May 28, 2023 at 0:21
  • @crocefisso That's a great solution too, please post it as an answer.
    – pglpm
    May 28, 2023 at 10:29

2 Answers 2

3

[A less manual way to do this is described in @Crocefisso's answer. If you are not interested in how tables are passed to source code blocks, but want to get on with the job at hand, you might find that answer more to your taste. And even after you've read this answer, if you are interested in manipulating tables, you should probably install the orgagreggate package and play with it. I did and it looks very good.]


Here's one way - we pass the table as an argument to an elisp src block that does the calculations and outputs a new table:

#+NAME: raw
|---|
| S |
| K |
| K |
| K |
| R |
| S |
| K |
| R |
| R |
| K |

#+name: calculate-freq
#+begin_src elisp :var l=raw[,0] :results table
    (defun item-counts (l)
      (let (result)
        (while l
          (setq cur (list (car l) 0))
          (while (equal (car l) (car cur))
            ;; increment the count
            (setcdr cur (list (1+ (cadr cur))))
            (setq l (cdr l)))
          (setq result (append result (list cur))))
        result))

  (append (list '("Item" "Count") 'hline)
          (item-counts (sort l #'string<)))
#+end_src

#+name: freq
#+RESULTS: calculate-freq
| Item | Count |
|------+-------|
| K    |     5 |
| R    |     3 |
| S    |     2 |

Press C-c C-c on the src block to evaluate the freq table. Change the :results setting to drawer if you want to see the lisp structure that is produced (more-or-less: there are no quotation marks on the strings). That's how tables are represented in Lisp.

I'm sure item-counts can be written more elegantly: this is fairly brute-force. Note that it assumes that the list is sorted to begin with.


EDIT in response to comments: I edited the above answer to answer the OP's question in one of the comments (how to pass a different column to the code block). That simplifies the code as well, so the current version is preferable. Feel free to skim the rest of this lightly: it elaborates on the basic idea. The documentation reference for accessing table rows, columns or elements (plus a lot more) is Environment of a Code Block.

[Feel free to stop here if you want. The rest of the answer is elaboration]

As you surmised, the table is passed to the code block through the :var l=raw, so that inside the code block, you can do things with and to the table through the variable l. The problem is how the table is represented in the code block language (Lisp in this instance, or R or python or whatever you prefer). The best way to find that out is to write a code block that just evaluates the variable and produces the results in the native format (as a list in Lisp e.g) - that's what :results drawer does below:

#+name:raw
|   |    |
|---+----|
| a | b  |
| c | d` |
| e | f  |

#+begin_src elisp :var l=raw :results drawer
l
#+end_src

#+RESULTS:
:results:
((a b) (c d`) (e f))
:end:

As you can see the table is represented in Lisp as a list of lists, specifically as a column list consisting of row lists.

You can easily select a column:

#+name:raw
|   |    |
|---+----|
| a | b  |
| c | d` |
| e | f  |

#+begin_src elisp :var l=raw[,0]  :results drawer
l
#+end_src

#+RESULTS:
:results:
(a c e)
:end:

This selects all the rows (because the row number is absent) and column 0 (Lisp starts numbering at 0).

Rows are not quite that simple because you have to account for the header row (row 0) and the separator line (row 1):

#+name:raw
|   |    |
|---+----|
| a | b  |
| c | d` |
| e | f  |

#+begin_src elisp :var l=raw[2,]  :results drawer
l
#+end_src

+RESULTS:
:results:
(a b)
:end:

I should have written the original answer by choosing a column - that would have simplified the program a bit. Notice the difference between this:

#+name:raw
|---|
| S |
| K |
| K |
| K |
| R |
| S |
| K |
| R |
| R |
| K |

#+begin_src elisp :var l=raw  :results drawer
l
#+end_src

#+RESULTS:
:results:
((S) (K) (K) (K) (R) (S) (K) (R) (R) (K))
:end:

and this:

#+name:raw
|---|
| S |
| K |
| K |
| K |
| R |
| S |
| K |
| R |
| R |
| K |

#+begin_src elisp :var l=raw[,0]  :results drawer
l
#+end_src


#+RESULTS:
:results:
(S K K K R S K R R K)
:end:

By choosing a column, I get a simple list (the elements of a column, whereas getting the while table gives me a list of rows, with each row consisting of, in this case, a single element.

The (mapcar #'car l) in the original answer turned the list of lists into a simple list, but I could have done that by choosing a column which would obviate the need for the mapcar. I edited the original answer to do exactly that: it's simpler and it also answers the question in your second comment.

4
  • This is brilliant, thank you! Could you comment on how the elisp code grabs the data column? it looks like its in the "l=raw" bit. I could use R code in a similar way then.
    – pglpm
    May 27, 2023 at 17:26
  • Can I also ask: would this be simple to adapt if the column is, say, the 3rd of a table, rather than being the table's only column?
    – pglpm
    May 27, 2023 at 17:28
  • 1
    Modified the answer so that only a column is passed to the code block (and you can change that easily, which answers the second question. I also added a (longish) explanation of how the code block grabs (parts of) the table. Hope it helps!
    – NickD
    May 27, 2023 at 19:58
  • Absolutely brilliant, thank you for your time and help. You have actually answered three or four more questions I was wondering about regarding column and row handling and interaction with code snippets.
    – pglpm
    May 27, 2023 at 21:51
5

There is a well-documented package dedicated to aggregating values from tables: orgaggregate.

It creates new tables by computing sums, averages, and so on, out of material from a given table.

You can install it easily with use-package:

(use-package orgtbl-aggregate
  :after (org))

or from MELPA perhaps using package-install, or from Github directly: it's a single-file package, so all you have to do is clone the repo somewhere locally, add the repo directory to load-path and (require 'orgtbl-aggregate) in your init file (using (with-eval-after-load 'org (require 'orgtbl-aggregate)) to make sure it's loaded after Org mode is loaded - that's basically what the use-package invocation above does).

The basic example that the OP asked for can be done trivially with the following (which is almost verbatim off the README file):


* count items in table

#+name:raw
| items |
|-------|
| S     |
| K     |
| K     |
| K     |
| R     |
| S     |
| K     |
| R     |
| R     |
| K     |


#+BEGIN: aggregate :table "raw" :cols "items count()"
#+END:

The #+BEGIN:/#+END: construct is called a dynamic block and is used in other places in Org mode (e.g. for clock tables and column view). Pressing C-c C-c on the dynamic block (which can be anywhere in the file) evaluates it, producing this:

...
#+BEGIN: aggregate :table "raw" :cols "items count()"
| items | count() |
|-------+---------|
| S     |       2 |
| K     |       5 |
| R     |       3 |
#+END:

Sorting the items column can be done by modifying the header of the dynamic block slightly and recalculating with C-c C-c:

...
#+BEGIN: aggregate :table "raw" :cols "items;^a count()"
| items | count() |
|-------+---------|
| K     |       5 |
| R     |       3 |
| S     |       2 |
#+END:

See the Sorting section in the README for more details.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.