Re: questions about table mode and spreadsheets

From: Inquisitive Scientist <inquisitive.scientist@gmail.com>
To: mail@christianmoe.com
Cc: emacs-orgmode@gnu.org, Juan <Pechiar@computer.org>
Subject: Re: questions about table mode and spreadsheets
Date: Wed, 15 Sep 2010 08:08:29 -0400	[thread overview]
Message-ID: <AANLkTimMLkVjsrRLOf_B=+jowC07WcK2J510L+yzZtVb@mail.gmail.com> (raw)
In-Reply-To: <4C8603FB.9080409@christianmoe.com>

[-- Attachment #1.1: Type: text/plain, Size: 4159 bytes --]

Thanks for the helpful replies to question 2 but what about question 1? Any
thoughts on how I can get org-mode to dynamically add an extra row?

Thanks,
-Emin

On Tue, Sep 7, 2010 at 5:20 AM, Christian Moe <mail@christianmoe.com> wrote:

> On 9/7/10 2:56 AM, Juan wrote:
>
>> A very complex way of not adding the extra column:
>>
>> | name | a  | b | c |
>> |------+----+---+---|
>> | foo  | 1  | 2 | 3 |
>> | bar  | 3  | 2 | 1 |
>> | bar  | 4  | 5 | 6 |
>> |------+----+---+---|
>> |      | 7  |   |   |
>> #+TBLFM: @5$2='(apply '+ (mapcar* (lambda(x y) (if (string= x "bar") y 0))
>> '(@I$1..@II$1) '(@I$2..@II$2)));L
>>
>>  * the two arguments at the end are the name and a columns: '(foo bar bar)
>> and '(1 3 4)
>>  * the lambda function returns the second argument if first is "bar", 0
>> otherwise.
>>  * mapcar* applies the lambda function to arguments from the 2 lists.
>>  * apply '+ adds the resulting list
>>
>> Regards,
>> .j.
>>
>
> Neat! This is what I wanted to achieve. Good thing I gave up, though,
> I see it would have kept me up all night.
>
> (And yes, I meant "add a new /column/", not row.)
>
> If one wants to do this often (e.g., in the other two columns), one
> could tuck away some of the complexity into one's .emacs, and at the
> same time get away from hard-coding the match string, like so:
>
> #+begin_src elisp
>  (defun vsumif (string x y)
>  "Sum values of Y for all X matching STRING."
>
>    (apply '+
>           (mapcar*
>            (lambda(x y)
>              (if (string= x match) y 0))
>            x y)))
> #+end_src
>
> Now, one can e.g. put the string one is matching for in the table. Try
> updating the spreadsheet below, then changing `foo' in the bottom row
> (@5$1) to `bar' and updating again.
>
>
> | name | a | b | c |
> |------+---+---+---|
> | foo  | 1 | 2 | 3 |
> | bar  | 3 | 2 | 1 |
> | bar  | 4 | 5 | 6 |
> |------+---+---+---|
> | bar  |   |   |   |
> #+TBLFM: @5$2='(vsumif '@5$1 '(@I$1..@II$1) '(@I$2..@II$2));L
>
> It's still a lengthy formula and not the easiest to write. If you'd
> like to add up all foos or bars for columns a, b and c, you may be
> better off swapping rows and columns so you can use column formulas:
>
> |   | name | foo | bar | bar | bar |
> |---+------+-----+-----+-----+-----|
> | / | <>   |   < |     |   > |  <> |
> |   | a    |   1 |   3 |   4 |     |
> |   | b    |   2 |   2 |   5 |     |
> |   | c    |   3 |   1 |   6 |     |
> #+TBLFM: $6='(vsumif '@1$6 '(@1$3..@1$5) '($3..$5));L
>
> Again, replace `foo' in @1$6 with `bar' to get totals for bar.
>
> I have added vertical lines to the table.
>
> One could presumable write =vsumif= in a more general form that would
> not be hard-coded to test only for matching strings. The function
> defined above might be better named =vsumifstring=.
>
> Cheers,
> CM
>
>
>> On Tue, Sep 07, 2010 at 12:44:03AM +0200, Christian Moe wrote:
>>
>>> On 9/6/10 3:38 PM, Inquisitive Scientist wrote:
>>>
>>>    2. How do I compute the sum of a column only if a corresponding row
>>>> matches some condition? For example, how do I compute the sum of
>>>> numbers in column a for which the name in column "name" is "bar"? For
>>>> example, I should get 7 for the sum in column a in the table below:
>>>>
>>>> | name | a | b | c |
>>>> |------+---+---+---|
>>>> | foo  | 1 | 2 | 3 |
>>>> | bar  | 3 | 2 | 1 |
>>>> | bar  | 4 | 5 | 6 |
>>>> |------+---+---+---|
>>>>
>>>
>>> Here's one way: Add a new row
>>>
>>
> sorry: meant "add a new column"
>
>  after the first, as below. Then run C-c
>>> C-c on the formula line:
>>>
>>> | name |   | a | b | c |
>>> |------+---+---+---+---|
>>> | foo  |   | 1 | 2 | 3 |
>>> | bar  |   | 3 | 2 | 1 |
>>> | bar  |   | 4 | 5 | 6 |
>>> |------+---+---+---+---|
>>> |      |   |   |   |   |
>>>   #+TBLFM: $2='(if (string= $1 "bar") 1 0)::
>>> @5$3=vsum(vmask(@I$2..@II$2,@I..@II))
>>>
>>> It does exactly what you asked, but I don't think it will scale well...
>>>
>>>
>> _______________________________________________
>> 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
>>
>>
>
>

[-- Attachment #1.2: Type: text/html, Size: 6044 bytes --]

[-- Attachment #2: 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