Re: Problem with table sums

From: Dan Davison <davison@stats.ox.ac.uk>
To: Carsten Dominik <carsten.dominik@gmail.com>
Cc: "Sébastien Vauban" <wxhgmqzgwmuf@spammotel.com>, emacs-orgmode@gnu.org
Subject: Re: Problem with table sums
Date: Wed, 24 Feb 2010 10:40:53 -0500	[thread overview]
Message-ID: <87ocje63qy.fsf@stats.ox.ac.uk> (raw)
In-Reply-To: <13C985E1-535E-42D0-9004-12E0618DEADA@gmail.com> (Carsten Dominik's message of "Wed, 24 Feb 2010 10:40:01 +0100")

Carsten Dominik <carsten.dominik@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 | 40000.00        |
>>   | ^ |                   | pj  | Prestations     |
>>   |   | Frais annexes     |     | 1280.00         |
>>   | ^ |                   |     | FraisAnnexes    |
>>   |   | Gestion du projet |     | 3200.00         |
>>   | ^ |                   |     | GestionDuProjet |
>>   |   | Licence           |     | 8000.00         |
>>   | ^ |                   |     | Licence         |
>>   |---+-------------------+-----+-----------------|
>>   |   | Total             |     | 40000.00        |
>>   | ^ |                   |     | Total           |
>>   #+TBLFM: @2$4=@2$3*400.00;%.2f::@4$4=@2$4*0.08*0.40;%.
>> 2f::@6$4=@2$4*0.08;%.2f::@8$4=@2$4*0.20;%.2f::@10$4=vsum(@-I..@-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 '+ '(@-I..@-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 (@-I..@-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 @-I..@-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