From mboxrd@z Thu Jan 1 00:00:00 1970 From: Michael Brand Subject: Re: how to group together fields Date: Sat, 22 Feb 2014 21:20:29 +0100 Message-ID: References: <87fvo5tqvo.fsf@mars.lan> <87fvo4bcjw.fsf@gmail.com> <87r47nshuh.fsf@mars.lan> <87bnyrayda.fsf@gmail.com> <87ioszrr6l.fsf@mars.lan> <87wqhf9fmy.fsf@gmail.com> <87d2j7rno9.fsf@mars.lan> <87txcgy3ya.fsf@bzg.ath.cx> <8738k09q13.fsf@mars.lan> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Return-path: Received: from eggs.gnu.org ([2001:4830:134:3::10]:38171) by lists.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1WHJ3n-0006QM-TL for emacs-orgmode@gnu.org; Sat, 22 Feb 2014 15:20:33 -0500 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1WHJ3m-0006LJ-JG for emacs-orgmode@gnu.org; Sat, 22 Feb 2014 15:20:31 -0500 Received: from mail-qa0-x229.google.com ([2607:f8b0:400d:c00::229]:48773) by eggs.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1WHJ3m-0006LC-Db for emacs-orgmode@gnu.org; Sat, 22 Feb 2014 15:20:30 -0500 Received: by mail-qa0-f41.google.com with SMTP id w8so4925394qac.0 for ; Sat, 22 Feb 2014 12:20:29 -0800 (PST) In-Reply-To: <8738k09q13.fsf@mars.lan> List-Id: "General discussions about Org-mode." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org Sender: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org To: Stefan Huchler Cc: Org Mode Hi Stefan On Mon, Feb 3, 2014 at 4:25 PM, Stefan Huchler 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