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: Sun, 28 Feb 2010 19:37:05 -0500	[thread overview]
Message-ID: <87ocj8513i.fsf@stats.ox.ac.uk> (raw)
In-Reply-To: <63628A50-3900-4D82-B9F1-B87096391D21@gmail.com> (Carsten Dominik's message of "Thu, 25 Feb 2010 12:11:36 +0100")

Carsten Dominik <carsten.dominik@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.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
>
> - 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