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