Re: Problem with table sums

From: Carsten Dominik <carsten.dominik@gmail.com>
To: Dan Davison <davison@stats.ox.ac.uk>
Cc: "Sébastien Vauban" <wxhgmqzgwmuf@spammotel.com>, emacs-orgmode@gnu.org
Subject: Re: Problem with table sums
Date: Mon, 1 Mar 2010 08:49:46 +0100	[thread overview]
Message-ID: <89173E6D-8B7A-49CE-B5AB-2AFD5F9CC618@gmail.com> (raw)
In-Reply-To: <87ocj8513i.fsf@stats.ox.ac.uk>

[-- Attachment #1: Type: text/plain, Size: 467 bytes --]

On Mar 1, 2010, at 1:37 AM, Dan Davison wrote:

> 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)

Well, yes.  But the pure Org-solution is not predictable.  If you turn  
on formula debugging,  you see that Org computes this:

[-- Attachment #2: pastedGraphic.tiff --]
[-- Type: image/tiff, Size: 19010 bytes --]

[-- Attachment #3: Type: text/plain, Size: 5407 bytes --]

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.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

- Carsten

[-- Attachment #4: Type: text/plain, Size: 201 bytes --]

_______________________________________________
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