5

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?

Improve this question
4
  • 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. Commented Apr 27, 2015 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. Commented Apr 27, 2015 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? Commented Apr 27, 2015 at 13:55
  • Edited, the remote should be (and is in practise) the third argument. Commented Apr 27, 2015 at 14:45

3 Answers 3

Reset to default
4

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?

Improve this answer
2

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 |
Improve this answer
1
  • 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. Commented Apr 28, 2015 at 6:57
2

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.
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.