Generate (populate?) table in org-mode using org-lookup-all

Question

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?

Answer 1

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?

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 |

Answer 3

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.