From mboxrd@z Thu Jan 1 00:00:00 1970 From: Thierry Banel Subject: Re: aggregate: percent, table headers, float format Date: Thu, 28 Jun 2018 19:36:54 +0200 Message-ID: <32245322-e7b3-b3fb-bcca-107aa620197e@free.fr> References: <874lhn9inv.fsf@mat.ucm.es> Mime-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Return-path: Received: from eggs.gnu.org ([2001:4830:134:3::10]:35069) by lists.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1fYaqr-00033T-FC for emacs-orgmode@gnu.org; Thu, 28 Jun 2018 13:37:02 -0400 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1fYaqn-0005OU-HV for emacs-orgmode@gnu.org; Thu, 28 Jun 2018 13:37:01 -0400 Received: from smtp5-g21.free.fr ([212.27.42.5]:48286) by eggs.gnu.org with esmtps (TLS1.0:DHE_RSA_AES_256_CBC_SHA1:32) (Exim 4.71) (envelope-from ) id 1fYaqn-0005LL-8I for emacs-orgmode@gnu.org; Thu, 28 Jun 2018 13:36:57 -0400 Received: from [IPv6:2a01:e35:2e21:def0:a9a7:a8c2:7f80:a4cb] (unknown [IPv6:2a01:e35:2e21:def0:a9a7:a8c2:7f80:a4cb]) by smtp5-g21.free.fr (Postfix) with ESMTP id 012C75FFA1 for ; Thu, 28 Jun 2018 19:36:54 +0200 (CEST) In-Reply-To: <874lhn9inv.fsf@mat.ucm.es> Content-Language: en-US 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" To: emacs-orgmode@gnu.org On 28/06/2018 14:53, Uwe Brauer wrote: > Hi > > Org-aggregate is great and very helpful. > > However I have some problems/questions which I think a maybe interestin= g > for the list. > > Take the following code > > > #+begin_src emacs-lisp > (setq org-calc-default-modes '(calc-internal-prec 12 calc-float-format > (fix 3) ;;; the default here was (float 8) > calc-angle-mode deg calc-prefer-frac nil calc-symbo= lic-mode nil calc-date-format > (YYYY "-" MM "-" DD " " Www > (" " hh ":" mm)) > calc-display-working-message t)) > > #+end_src > > Which sets the float format to (fix 3) is taken into account by the > org-table but ignored by org aggregate as the example below shows. Iwill look into that. In the meantime you can use a format specification ;f3which means "fix 3 decimal positions". #+BEGIN: aggregate :table "raw-data" :cols "CalJunio count()=C2=A0 (count()/19)*100;f3" | CalJunio | count() | (count()/19)*100;f3 | |----------+---------+---------------------| | AP=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 7 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 36.842 | | NT=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 1 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0 5.263 | | SS=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 5 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 26.316 | | NP=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 6 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 31.579 | #+END: > > #+TBLNAME: raw-data > | Number | CalJunio | > |--------+----------| > | 1 | AP | > | 2 | NT | > | 3 | SS | > | 4 | SS | > | 5 | SS | > | 6 | AP | > | 7 | SS | > | 8 | NP | > | 9 | AP | > | 10 | NP | > | 11 | NP | > | 12 | AP | > | 13 | NP | > | 14 | AP | > | 15 | NP | > | 16 | AP | > | 17 | SS | > | 18 | NP | > | 19 | AP | > #+TBLFM: $1=3D@#-1 > > > I want that org aggregate counts the entries in the second column of th= e > tale raw-data, which it does (thanks to Thierry), but I also would like > to have, automatically, to calculate its percentage. And that it seems = I > can only do manually as the below example shows. > > #+BEGIN: aggregate :table "raw-data" :cols "CalJunio count() (count()/= 19)*100" > | CalJunio | count() | (count()/19)*100 | > |----------+---------+------------------| > | AP | 7 | 36.8421052632 | > | NT | 1 | 5.26315789474 | > | SS | 5 | 26.3157894737 | > | NP | 6 | 31.5789473684 | > #+END: In this example the hardcoded 19 value is annoying. It is the total count() of rows in raw-data table. It should be computed automatically by some other mean. One way is to use another named aggregation: #+name: total-count #+BEGIN: aggregate :table "raw-data" :cols "count()" | count() | |---------| |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 19 | #+END: Then the cell containing 19 can be accessed using a "remote" formula: remote(total-count,@2$1) #+BEGIN: aggregate :table "raw-data" :cols "CalJunio count()" | CalJunio | count() |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | |----------+---------+--------| | AP=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 7 | 36.842 | | NT=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 1 |=C2=A0 5.263 | | SS=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 5 | 26.316 | | NP=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 6 | 31.579 | #+TBLFM: $3=3D$2*100/remote(total-count,@2$1);f3 #+END: Here a regular spreadsheet expression was used, instead of an aggregate formula. Orgaggregate takes care of the table formulas between re-computing. So you can refresh the aggregated table as often as needed. > > Is there a more elegant solution and how can I change the float format? > > By the way is there a way to customize the header of the above table, > something like > > > #+BEGIN: aggregate :table "raw-data" :cols "CalJunio count() (count()/= 19)*100" > | CalJunio | count | percent | > |----------+-------+---------------| > | AP | 7 | 36.8421052632 | > | NT | 1 | 5.26315789474 | > | SS | 5 | 26.3157894737 | > | NP | 6 | 31.5789473684 | > #+END: > The header can be set with this cell formula: @1$3=3Dpercent #+BEGIN: aggregate :table "raw-data" :cols "CalJunio count()" | CalJunio | count() | percent | |----------+---------+---------| | AP=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 7 |=C2=A0 36.842 | | NT=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 1 |=C2=A0=C2=A0 5.263 | | SS=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 5 |=C2=A0 26.316 | | NP=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 6 |=C2=A0 31.579 | #+TBLFM: $3=3D$2*100/remote(total-count,@2$1);f3::@1$3=3Dpercent #+END: Again, the #+TBLFM specification survives refreshes (performed with C-c C-c on the #+BEGIN: line). Everything else is recomputed. > thanks > > Uwe Brauer=20 > > > Regards Thierry