Greg,

Of course, I’m not surprised by the results of your efforts.  Nice!

I myself don’t prefer the tidyverse, mainly except for ggplot, and instead find myself reaching for sqldf or data.tables where such benefit is needed.

YMMV,

Malcolm

From: Greg Minshall <minshall@umich.edu>
Sent: Monday, February 22, 2021 02:13
To: Cook, Malcolm <MEC@stowers.org>
Cc: John Kitchin <jkitchin@andrew.cmu.edu>; Tim Cross <theophilusx@gmail.com>; org-mode-email <emacs-orgmode@gnu.org>
Subject: Re: state of the art in org-mode tables e.g. join, etc

ATTENTION: This email came from an external source. Do not open attachments or click on links from unknown senders or unexpected emails.

Malcolm,

> Checkout what R sqldf package makes easy:

very nice!

Greg

ps -- (feeling a challenge... :) for base R, dplyr::inner_join, the
following seem to work (i apologize that i don't know how people embed
org-frags in e-mail, or how important that format might be?)
----
#+NAME: original
| Day | Color | Level | Quantity |
|-----------+-------+-------+----------|
| Monday | Red | 30 | 11 |
| Monday | Blue | 25 | 3 |
| Tuesday | Red | 51 | 12 |
| Tuesday | Red | 45 | 15 |
| Tuesday | Blue | 33 | 18 |
| Wednesday | Red | 27 | 23 |
| Wednesday | Blue | 12 | 16 |
| Wednesday | Blue | 15 | 15 |
| Thursday | Red | 39 | 24 |
| Thursday | Red | 41 | 29 |
| Thursday | Red | 49 | 30 |
| Friday | Blue | 7 | 5 |
| Friday | Blue | 6 | 8 |
| Friday | Blue | 11 | 9 |

#+PROPERTY: header-args:R :session *R*
#+begin_src R :results none
library(dplyr)
#+end_src

#+begin_src R :var original=original :colnames yes
as.data.frame(table(Color=original$Color))
#+end_src

#+RESULTS:
| Color | Freq |
|-------+------|
| Blue | 7 |
| Red | 7 |



*** join example

Example from https://github.com/tbanel/orgtbljoin

#+name: nutrition
| type | 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 |


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


#+begin_src R :var recipe=recipe :var nutrition=nutrition :colnames yes
dplyr::inner_join(nutrition, recipe)
#+end_src

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