state of the art in org-mode tables e.g. join, etc

[John Kitchin <jkitchin@andrew.cmu.edu>]

[-- Attachment #1: Type: text/plain, Size: 2221 bytes --]

Is there a state of the art in using org-tables as little databases with
joins and stuff?

This package https://github.com/tbanel/orgtbljoin

seems close, but not quite what I had in mind. I don't want to modify
tables in place, or create dynamic tables. I do want to combine tables in
memory though for subsequent calculations.

I was thinking more like given the tables from that page:

#+name: quantities
| type-1   | quty |
|----------+------|
| onion    |   70 |
| tomatoe  |  120 |
| eggplant |  300 |
| tofu     |  100 |

#+name: nutrition
| type-2   | Fiber | Sugar | Protein | Carb |
|----------+-------+-------+---------+------|
| eggplant |   2.5 |   3.2 |     0.8 |  8.6 |
| tomatoe  |   0.6 |   2.1 |     0.8 |  3.4 |
| onion    |   1.3 |   4.4 |     1.3 |  9.0 |
| egg      |     0 |  18.3 |    31.9 | 18.3 |
| rice     |   0.2 |     0 |     1.5 | 16.0 |
| bread    |   0.7 |   0.7 |     3.3 | 16.0 |
| orange   |   3.1 |  11.9 |     1.3 | 17.6 |
| banana   |   2.1 |   9.9 |     0.9 | 18.5 |
| tofu     |   0.7 |   0.5 |     6.6 |  1.4 |
| nut      |   2.6 |   1.3 |     4.9 |  7.2 |
| corn     |   4.7 |   1.8 |     2.8 | 21.3 |

you would be able to do something like this:

#+BEGIN_SRC emacs-lisp
(join "quantities" "type-1" "nutrition" "type-2")
#+END_SRC

#+RESULTS:
| type-1   | quty | Fiber | Sugar | Protein | Carb |
|----------+------+-------+-------+---------+------|
| onion    |   70 |   1.3 |   4.4 |     1.3 |  9.0 |
| tomatoe  |  120 |   0.6 |   2.1 |     0.8 |  3.4 |
| eggplant |  300 |   2.5 |   3.2 |     0.8 |  8.6 |
| tofu     |  100 |   0.7 |   0.5 |     6.6 |  1.4 |

or, to sum the Fiber column:

#+BEGIN_SRC emacs-lisp :var data=(join "quantities" "type-1" "nutrition"
"type-2")
(cl-loop for row in data sum (nth 2 row)))
#+END_SRC

#+RESULTS:
: 5.1000000000000005

I have spent a lot of time with Pandas in Python lately,  and trying to
think through what some analogues with org-tables would be. I would be very
happy not to reinvent the wheel here!


John

-----------------------------------
Professor John Kitchin
Doherty Hall A207F
Department of Chemical Engineering
Carnegie Mellon University
Pittsburgh, PA 15213
412-268-7803
@johnkitchin
http://kitchingroup.cheme.cmu.edu

[-- Attachment #2: Type: text/html, Size: 3117 bytes --]