Re: [O] how to group together fields

[Michael Brand]

Hi Stefan

On Mon, Feb 3, 2014 at 4:25 PM, Stefan Huchler <address@hidden> wrote:
> I get with the 1. COLUMNS line something like that:
>
> Tea Shop 1     |  5.0   | ...
> Tea Shop 2     |  7.0   | ...
>
> I want another output that looks like that:
>
> Blend 1        |  6.0   | ...
> Blend 2        |  3.0   | ...

My suggestion for these several GROUP BY as in SQL on the same data is
to use Org babel with R or TBLFM.

I am not a user of the R programming language and therefore can not
provide a working example but it would be one of the simpler
solutions. Put all data into one Org table just like "#+NAME: data"
below and for each result table use an Org babel source block with R
that reads from this table.

Some R example of GROUP BY as in SQL:
http://stats.stackexchange.com/questions/8225/how-to-summarize-data-by-group-in-r

With TBLFM the only thing that comes to my mind is org-lookup-all. The
disadvantage of this solution is that you have to enumerate all groups
manually in the result tables:

#+NAME: data
| Purchase | Product | Shop | Rating |
|----------+---------+------+--------|
| a        | p1      | s1   |      1 |
| b        | p1      | s2   |      4 |
| c        | p2      | s1   |      2 |
| d        | p3      | s2   |      8 |

Product rating and ranking by average purchase:

| Product | Rating | Ranking |
|---------+--------+---------|
| p1      |    2.5 |       2 |
| p2      |    2.0 |       3 |
| p3      |    8.0 |       1 |
#+TBLFM: $2 = '(let ((all (org-lookup-all '$1 '(remote(data,
@I$2..@>$2)) '(remote(data, @I$4..@>$4))))) (/ (apply '+ all) (length
all) 1.0)); L :: $3 = '(+ 1 (length (org-lookup-all $2 '(@I$2..@>$2)
nil '<))); N

Shop rating and ranking by average purchase:

| Shop | Rating | Ranking |
|------+--------+---------|
| s1   |    1.5 |       2 |
| s2   |    6.0 |       1 |
#+TBLFM: $2 = '(let ((all (org-lookup-all '$1 '(remote(data,
@I$3..@>$3)) '(remote(data, @I$4..@>$4))))) (/ (apply '+ all) (length
all) 1.0)); L :: $3 = '(+ 1 (length (org-lookup-all $2 '(@I$2..@>$2)
nil '<))); N

I added the above TBLFM solution as an ERT that can also be found via
Worg: On the page with the Worg tutorials in the spreadsheet section
follow the link to the "Emacs Regression Test (ERT) for Org tables"
with example use cases for TBLFM:
http://orgmode.org/worg/org-tutorials/index.html#Spreadsheet
In the ERT it is test-org-table/org-lookup-all.

Michael