In an otherwise empty *.org file I have a table like

#+TBLNAME: tbl1
|Name|Value|
|A   |    1|
|B   |    2|
|A   |    3|

and I want to somehow create a table that should look something like this:

#+TBLNAME: tbl2
|Name|Frequency|Sum|
|A   |2        |4  |
|B   |1        |2  |

In my real case tbl1 is 150 rows and changing, so it is only feasible if tbl2 is autogenerated somehow.

#+TBLFM: @2$2..@>$2='(length (org-lookup-all $1 '(remote(tbl1,@2$1..@>$1)) '(remote(tbl,@2$2..@>$2))))

Produces the desired content for column 2 in tbl2, but

#+TBLFM: @2$3..@>$3='(apply '+ (org-lookup-all $1 '(remote(tbl,@2$1..@>$1)) '(remote(tbl1,@2$2..@>$2))))

produces "#ERROR" with the following content in Substitution history:

Substitution history of formula
Orig:   '(apply '+ (org-lookup-all $1 '(remote(tbl,@2$1..@>$1)) '(remote(tbl,@2$2..@>$2))))
$xyz->  '(apply '+ (org-lookup-all $1 '(remote(tbl,@2$1..@>$1)) '(remote(tbl,@2$2..@>$2))))
@r$c->  '(apply '+ (org-lookup-all $1 '(#("A" 0 1 (fontified t face org-table)) #("B" 0 1 (fontified t face org-table)) #("A" 0 1 (fontified t face org-table))) '(#("1" 0 1 (fontified t face org-table)) #("2" 0 1 (fontified t face org-table)) #("3" 0 1 (fontified t face org-table)))))
$1->    '(apply '+ (org-lookup-all "B" '(#("A" 0 1 (fontified t face org-table)) #("B" 0 1 (fontified t face org-table)) #("A" 0 1 (fontified t face org-table))) '(#("1" 0 1 (fontified t face org-table)) #("2" 0 1 (fontified t face org-table)) #("3" 0 1 (fontified t face org-table)))))
Result: #ERROR
Format: NONE
Final:  #ERROR

When appending ";N" to the second TBLFM, both rows in tbl2 "Sum" is 6 (that is, the sum of all entries).

What is wrong with the second TBLFM?

  • I think this is a little bit stretching the limits of what table formulas can do, besides, this would be really easy to code in almost any general-purpose programming language, or anything that specifically deals with databases. If you are ok with using some SQL database for this, this would be really a matter of a one-line query to do it. – wvxvw Apr 27 '15 at 12:12
  • I will solve this in common lisp if elisp/orgmode is not working. But the worg explicity states that vlookup, sumif etc can be implemented this way (orgmode.org/worg/org-tutorials/org-lookups.html). Since org-lookup-all behaves as expected when counting frequency, I really expect there to be a way to sum the returned list. Somehow. – otyn Apr 27 '15 at 12:23
  • Are you sure that the parenthesis are the way you intended? I.e. isn't '(remote(tbl1,@2$2..@>$2)) the third argument of org-lookup-all rather than the ending of the second argument? – wvxvw Apr 27 '15 at 13:55
  • Edited, the remote should be (and is in practise) the third argument. – otyn Apr 27 '15 at 14:45

Here's in case you were interested, a way to do that using SQLite (I wrote this mostly as an exercise, since I didn't need to import Org tables into SQLite before), but it seems to work, so, why not.

* Populate source.db using SQLite
  #+header: :results silent
  #+header: :dir ~/Projects/org/
  #+header: :db source.db
  #+begin_src sqlite
    create table example(name varchar(10), value int);
    insert into example values('A', 1);
    insert into example values('B', 2);
    insert into example values('A', 3);
  #+end_src

* Populate source.db from Org table
  :PROPERTIES:
  :TABLE_EXPORT_FILE: ~/Projects/org/exported.csv
  :TABLE_EXPORT_FORMAT: orgtbl-to-csv
  :END:
  #+TBLNAME: tbl1
  | Name | Value |
  |------+-------|
  | A    |     1 |
  | B    |     2 |
  | A    |     3 | 

  This is only needed to remove the first row of the table.  For
  some reason the SQLite import option, where it creates column
  names from the first row didn't work.
  #+begin_src sh
    tail -n +2 ~/Projects/org/exported.csv > ~/Projects/org/exported.tmp
    mv ~/Projects/org/exported.tmp ~/Projects/org/exported.csv
  #+end_src

  #+header: :results silent
  #+header: :dir ~/Projects/org/
  #+header: :db source.db
  #+begin_src sqlite
    create table if not exists org_export(name varchar(10), value int);
    .mode csv
    .import /home/wvxvw/Projects/org/exported.csv org_export
  #+end_src

* Retrieve from source.db
  #+header: :list :colnames yes
  #+header: :dir ~/Projects/org/
  #+header: :db source.db
  #+begin_src sqlite
    select `name`, 
           count(`name`) as frequency,
           sum(`value`) as sum
     from example group by `name`;
  #+end_src

  #+RESULTS:
  | name | frequency | sum |
  |------+-----------+-----|
  | A    |         2 |   4 |
  | B    |         1 |   2 |

  #+header: :list :colnames yes
  #+header: :dir ~/Projects/org/
  #+header: :db source.db
  #+begin_src sqlite
    select `name`, 
           count(`name`) as frequency,
           sum(`value`) as sum
     from org_export group by `name`;
  #+end_src

  #+RESULTS:
  | name | frequency | sum |
  |------+-----------+-----|
  | A    |         2 |   4 |
  | B    |         1 |   2 |
  • I'd upvote this answer if I could, as it seems solve my problem by going around it. But I am still hoping for an answer that adresses the org-lookup-all function itself. – otyn Apr 28 '15 at 6:57

You may want to have a look at the orgtbl-aggregate package on Melpa or here: https://github.com/tbanel/orgaggregate

#+BEGIN: aggregate :table "tbl1" :cols "Name count() vsum((Value)"
| Name | count() | vsum((Value) |
|------+---------+--------------|
| A    |       2 |            4 |
| B    |       1 |            2 |
#+END:
  • Aggregation is made on the NAME column
  • count() stands for Frequency
  • vsum(Value) column contains the desired sums.
up vote 1 down vote accepted

The problem is that the return is a group of strings (Re: Help with org-lookup-all) so they must me mapcar'ed like so:

#+TBLFM: @2$3..@>$3='(apply '+ (mapcar (function string-to-number) (org-lookup-all $1 '(remote(tbl,@2$1..@>$1)) '(remote(tbl1,@2$2..@>$2)))))

This produces the desired table.

Is there a better way to go about it?

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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