Re: [Orgmode] Problem with table sums
On Mar 1, 2010, at 1:37 AM, Dan Davison wrote: Carsten Dominik carsten.domi...@gmail.com writes: :-) I should have known. These days, babel is the answer to most questions ... :-) Well, that wasn't the conclusion I meant to be drawn -- I thought the pure org solution was best here! (at least for simple summation) Well, yes. But the pure Org-solution is not predictable. If you turn on formula debugging, you see that Org computes this: inline: pastedGraphic.tiff If the name of a field would read as 222GestionDuProjet (which is not a valid name, yes), the effect of the N switch you added would convert this field into not 0, but 222. Furthermore, if the function had not been vsum, but vprod, the result would always be 0 because the names are turned into zeros. Using org-babel (or, if you like, emacs-lisp formulas) can try to throw out these names in the correct way. Really, what should happen is that Org should ignore ^ and _ lines when interpreting the formulas - but that is unfortunately neither implemented, not easy to do (I think...) - Carsten Dan - Carste On Feb 24, 2010, at 4:40 PM, Dan Davison wrote: Carsten Dominik carsten.domi...@gmail.com writes: On Feb 23, 2010, at 11:08 PM, Sébastien Vauban wrote: [...] Though, I need to re-use some of the intermediate computations for another summary table. Therefore, I add names to some cells: #+TBLNAME: etape1 | | Étape 1 | p.j | EUR HTVA| |---+---+-+-| | | Prestations | 100 | 4.00| | ^ | | pj | Prestations | | | Frais annexes | | 1280.00 | | ^ | | | FraisAnnexes| | | Gestion du projet | | 3200.00 | | ^ | | | GestionDuProjet | | | Licence | | 8000.00 | | ^ | | | Licence | |---+---+-+-| | | Total | | 4.00| | ^ | | | Total | #+TBLFM: @2...@2$3*400.00;%.2f::@4...@2$4*0.08*0.40;%. 2f::@6...@2$4*0.08;%.2f::@8...@2$4*0.20;%.2f::@10$4=vsum(@-...@- II); %.2f Now, the total is wrong: it's the value of the first cell... Like if the `^' prefix was simply dropped... and total limited to the first real figure. Any reason for this phenomenon? Or workaround (other than describing every cell to be summed)? Well, the reason is that the parser probably stops at the first text when summing, it tries to add Prestations. I am afraid there is not good work-around for this. Hi Seb, Well, I was going to suggest using org-babel. After playing around for a while, I ended up reading the org manual on table formulas and coming back to a pure org solution. My simplest solution is almost straight out of the manual (which makes me worry that I've missed the point of the question?): #+TBLFM:@10$4='(apply '+ '(@-...@-ii));N But seeing as I've got them, I may as well post my org-babel solutions. Here's the first set of org-babel solutions, which are just like the first solution, but use blocks to do the computation: #+TBLFM:@10$4='(sbe my-sum-LANG (n (@-...@-ii)));N where LANG is whatever language you want to compute the sum in: #+function: my-sum-elisp(n) #+begin_src emacs-lisp (apply '+ n) #+end_src #+function: my-sum-R(n) #+begin_src R sum(n) #+end_src #+function: my-sum-python(n) #+begin_src python return sum(n) #+end_src The second set of org-babel solutions use org-babel to do the table indexing. This was before I realised that I could use the @-...@-ii and ;N syntax in conjunction with the org-babel sbe macro. So these ones have to deal with separating the numeric entries from the character strings. #+TBLFM:@10$4='(sbe my-tab-sum-LANG);%.2f #+function: my-tab-sum-elisp #+begin_src emacs-lisp :var tab=etape1[2:9,3] (apply '+ (remq nil (mapcar (lambda (row) (if (numberp (car row)) (car row))) tab))) #+end_src #+function: my-tab-sum-R #+begin_src R :var tab=etape1[2:9,3] sum(as.numeric(tab[[1]]), na.rm=TRUE) #+end_src #+function: my-tab-sum-python #+begin_src python :var tab=etape1[2:9,3] flatten = lambda(lizt): sum(lizt, []) return sum(filter(lambda x: isinstance(x, float), flatten(tab))) #+end_src Dan - Carsten Best regards, Seb -- Sébastien Vauban ___ Emacs-orgmode mailing list Please use `Reply All' to send replies to the list. Emacs-orgmode@gnu.org http://lists.gnu.org/mailman/listinfo/emacs-orgmode - Carsten ___ Emacs-orgmode mailing list Please use `Reply All' to send replies to the list. Emacs-orgmode@gnu.org http://lists.gnu.org/mailman/listinfo/emacs-orgmode - Carsten ___ Emacs-orgmode mailing list Please use `Reply All' to send replies to the list. Emacs-orgmode@gnu.org
Re: [Orgmode] Problem with table sums
Carsten Dominik carsten.domi...@gmail.com writes: :-) I should have known. These days, babel is the answer to most questions ... :-) Well, that wasn't the conclusion I meant to be drawn -- I thought the pure org solution was best here! (at least for simple summation) Dan - Carste On Feb 24, 2010, at 4:40 PM, Dan Davison wrote: Carsten Dominik carsten.domi...@gmail.com writes: On Feb 23, 2010, at 11:08 PM, Sébastien Vauban wrote: [...] Though, I need to re-use some of the intermediate computations for another summary table. Therefore, I add names to some cells: #+TBLNAME: etape1 | | Étape 1 | p.j | EUR HTVA| |---+---+-+-| | | Prestations | 100 | 4.00| | ^ | | pj | Prestations | | | Frais annexes | | 1280.00 | | ^ | | | FraisAnnexes| | | Gestion du projet | | 3200.00 | | ^ | | | GestionDuProjet | | | Licence | | 8000.00 | | ^ | | | Licence | |---+---+-+-| | | Total | | 4.00| | ^ | | | Total | #+TBLFM: @2...@2$3*400.00;%.2f::@4...@2$4*0.08*0.40;%. 2f::@6...@2$4*0.08;%.2f::@8...@2$4*0.20;%.2f::@10$4=vsum(@-...@-ii); %.2f Now, the total is wrong: it's the value of the first cell... Like if the `^' prefix was simply dropped... and total limited to the first real figure. Any reason for this phenomenon? Or workaround (other than describing every cell to be summed)? Well, the reason is that the parser probably stops at the first text when summing, it tries to add Prestations. I am afraid there is not good work-around for this. Hi Seb, Well, I was going to suggest using org-babel. After playing around for a while, I ended up reading the org manual on table formulas and coming back to a pure org solution. My simplest solution is almost straight out of the manual (which makes me worry that I've missed the point of the question?): #+TBLFM:@10$4='(apply '+ '(@-...@-ii));N But seeing as I've got them, I may as well post my org-babel solutions. Here's the first set of org-babel solutions, which are just like the first solution, but use blocks to do the computation: #+TBLFM:@10$4='(sbe my-sum-LANG (n (@-...@-ii)));N where LANG is whatever language you want to compute the sum in: #+function: my-sum-elisp(n) #+begin_src emacs-lisp (apply '+ n) #+end_src #+function: my-sum-R(n) #+begin_src R sum(n) #+end_src #+function: my-sum-python(n) #+begin_src python return sum(n) #+end_src The second set of org-babel solutions use org-babel to do the table indexing. This was before I realised that I could use the @-...@-ii and ;N syntax in conjunction with the org-babel sbe macro. So these ones have to deal with separating the numeric entries from the character strings. #+TBLFM:@10$4='(sbe my-tab-sum-LANG);%.2f #+function: my-tab-sum-elisp #+begin_src emacs-lisp :var tab=etape1[2:9,3] (apply '+ (remq nil (mapcar (lambda (row) (if (numberp (car row)) (car row))) tab))) #+end_src #+function: my-tab-sum-R #+begin_src R :var tab=etape1[2:9,3] sum(as.numeric(tab[[1]]), na.rm=TRUE) #+end_src #+function: my-tab-sum-python #+begin_src python :var tab=etape1[2:9,3] flatten = lambda(lizt): sum(lizt, []) return sum(filter(lambda x: isinstance(x, float), flatten(tab))) #+end_src Dan - Carsten Best regards, Seb -- Sébastien Vauban ___ Emacs-orgmode mailing list Please use `Reply All' to send replies to the list. Emacs-orgmode@gnu.org http://lists.gnu.org/mailman/listinfo/emacs-orgmode - Carsten ___ Emacs-orgmode mailing list Please use `Reply All' to send replies to the list. Emacs-orgmode@gnu.org http://lists.gnu.org/mailman/listinfo/emacs-orgmode - Carsten ___ Emacs-orgmode mailing list Please use `Reply All' to send replies to the list. Emacs-orgmode@gnu.org http://lists.gnu.org/mailman/listinfo/emacs-orgmode ___ Emacs-orgmode mailing list Please use `Reply All' to send replies to the list. Emacs-orgmode@gnu.org http://lists.gnu.org/mailman/listinfo/emacs-orgmode
Re: [Orgmode] Problem with table sums
:-) I should have known. These days, babel is the answer to most questions ... :-) - Carste On Feb 24, 2010, at 4:40 PM, Dan Davison wrote: Carsten Dominik carsten.domi...@gmail.com writes: On Feb 23, 2010, at 11:08 PM, Sébastien Vauban wrote: [...] Though, I need to re-use some of the intermediate computations for another summary table. Therefore, I add names to some cells: #+TBLNAME: etape1 | | Étape 1 | p.j | EUR HTVA| |---+---+-+-| | | Prestations | 100 | 4.00| | ^ | | pj | Prestations | | | Frais annexes | | 1280.00 | | ^ | | | FraisAnnexes| | | Gestion du projet | | 3200.00 | | ^ | | | GestionDuProjet | | | Licence | | 8000.00 | | ^ | | | Licence | |---+---+-+-| | | Total | | 4.00| | ^ | | | Total | #+TBLFM: @2...@2$3*400.00;%.2f::@4...@2$4*0.08*0.40;%. 2f::@6...@2$4*0.08;%.2f::@8...@2$4*0.20;%.2f::@10$4=vsum(@-...@-ii); %.2f Now, the total is wrong: it's the value of the first cell... Like if the `^' prefix was simply dropped... and total limited to the first real figure. Any reason for this phenomenon? Or workaround (other than describing every cell to be summed)? Well, the reason is that the parser probably stops at the first text when summing, it tries to add Prestations. I am afraid there is not good work-around for this. Hi Seb, Well, I was going to suggest using org-babel. After playing around for a while, I ended up reading the org manual on table formulas and coming back to a pure org solution. My simplest solution is almost straight out of the manual (which makes me worry that I've missed the point of the question?): #+TBLFM:@10$4='(apply '+ '(@-...@-ii));N But seeing as I've got them, I may as well post my org-babel solutions. Here's the first set of org-babel solutions, which are just like the first solution, but use blocks to do the computation: #+TBLFM:@10$4='(sbe my-sum-LANG (n (@-...@-ii)));N where LANG is whatever language you want to compute the sum in: #+function: my-sum-elisp(n) #+begin_src emacs-lisp (apply '+ n) #+end_src #+function: my-sum-R(n) #+begin_src R sum(n) #+end_src #+function: my-sum-python(n) #+begin_src python return sum(n) #+end_src The second set of org-babel solutions use org-babel to do the table indexing. This was before I realised that I could use the @-...@-ii and ;N syntax in conjunction with the org-babel sbe macro. So these ones have to deal with separating the numeric entries from the character strings. #+TBLFM:@10$4='(sbe my-tab-sum-LANG);%.2f #+function: my-tab-sum-elisp #+begin_src emacs-lisp :var tab=etape1[2:9,3] (apply '+ (remq nil (mapcar (lambda (row) (if (numberp (car row)) (car row))) tab))) #+end_src #+function: my-tab-sum-R #+begin_src R :var tab=etape1[2:9,3] sum(as.numeric(tab[[1]]), na.rm=TRUE) #+end_src #+function: my-tab-sum-python #+begin_src python :var tab=etape1[2:9,3] flatten = lambda(lizt): sum(lizt, []) return sum(filter(lambda x: isinstance(x, float), flatten(tab))) #+end_src Dan - Carsten Best regards, Seb -- Sébastien Vauban ___ Emacs-orgmode mailing list Please use `Reply All' to send replies to the list. Emacs-orgmode@gnu.org http://lists.gnu.org/mailman/listinfo/emacs-orgmode - Carsten ___ Emacs-orgmode mailing list Please use `Reply All' to send replies to the list. Emacs-orgmode@gnu.org http://lists.gnu.org/mailman/listinfo/emacs-orgmode - Carsten ___ Emacs-orgmode mailing list Please use `Reply All' to send replies to the list. Emacs-orgmode@gnu.org http://lists.gnu.org/mailman/listinfo/emacs-orgmode
