* questions about table mode and spreadsheets
@ 2010-09-06 13:38 Inquisitive Scientist
2010-09-06 22:44 ` Christian Moe
0 siblings, 1 reply; 5+ messages in thread
From: Inquisitive Scientist @ 2010-09-06 13:38 UTC (permalink / raw)
To: emacs-orgmode
[-- Attachment #1.1: Type: text/plain, Size: 968 bytes --]
Dear Experts,
I am becoming very fond of the table/spreadsheet in org-mode and trying to
use it more extensively. I have tried digging through the docs but am still
having trouble with some things:
1. Is there a way to dynamically add a new row to a table using a table
formula? For example, a variety of org-mode things will produce a table as
output and allow me to specify some formulas. I'd like to add a formula that
says something like "create a row at the bottom that sums column 3" and have
that be a new row that gets added. Any thoughts?
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 |
|------+---+---+---|
Thanks,
-I.S.
[-- Attachment #1.2: Type: text/html, Size: 1059 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
^ permalink raw reply [flat|nested] 5+ messages in thread
* Re: questions about table mode and spreadsheets
2010-09-06 13:38 questions about table mode and spreadsheets Inquisitive Scientist
@ 2010-09-06 22:44 ` Christian Moe
2010-09-07 0:56 ` Juan
0 siblings, 1 reply; 5+ messages in thread
From: Christian Moe @ 2010-09-06 22:44 UTC (permalink / raw)
To: Inquisitive Scientist; +Cc: emacs-orgmode
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 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...
Cheers,
Christian
>
> Thanks,
> -I.S.
>
>
>
> _______________________________________________
> 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
--
Christian Moe
E-mail: mail@christianmoe.com
Website: http://christianmoe.com
^ permalink raw reply [flat|nested] 5+ messages in thread
* Re: questions about table mode and spreadsheets
2010-09-06 22:44 ` Christian Moe
@ 2010-09-07 0:56 ` Juan
2010-09-07 9:20 ` Christian Moe
0 siblings, 1 reply; 5+ messages in thread
From: Juan @ 2010-09-07 0:56 UTC (permalink / raw)
To: Christian Moe; +Cc: emacs-orgmode, Inquisitive Scientist
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.
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 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...
>
^ permalink raw reply [flat|nested] 5+ messages in thread
* Re: questions about table mode and spreadsheets
2010-09-07 0:56 ` Juan
@ 2010-09-07 9:20 ` Christian Moe
2010-09-15 12:08 ` Inquisitive Scientist
0 siblings, 1 reply; 5+ messages in thread
From: Christian Moe @ 2010-09-07 9:20 UTC (permalink / raw)
To: Juan; +Cc: emacs-orgmode, Inquisitive Scientist
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
>
^ permalink raw reply [flat|nested] 5+ messages in thread
* Re: questions about table mode and spreadsheets
2010-09-07 9:20 ` Christian Moe
@ 2010-09-15 12:08 ` Inquisitive Scientist
0 siblings, 0 replies; 5+ messages in thread
From: Inquisitive Scientist @ 2010-09-15 12:08 UTC (permalink / raw)
To: mail; +Cc: emacs-orgmode, Juan
[-- 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
^ permalink raw reply [flat|nested] 5+ messages in thread
end of thread, other threads:[~2010-09-15 12:08 UTC | newest]
Thread overview: 5+ messages (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
2010-09-06 13:38 questions about table mode and spreadsheets Inquisitive Scientist
2010-09-06 22:44 ` Christian Moe
2010-09-07 0:56 ` Juan
2010-09-07 9:20 ` Christian Moe
2010-09-15 12:08 ` Inquisitive Scientist
Code repositories for project(s) associated with this public inbox
https://git.savannah.gnu.org/cgit/emacs/org-mode.git
This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox;
as well as URLs for read-only IMAP folder(s) and NNTP newsgroup(s).