From mboxrd@z Thu Jan 1 00:00:00 1970 From: Inquisitive Scientist Subject: Re: questions about table mode and spreadsheets Date: Wed, 15 Sep 2010 08:08:29 -0400 Message-ID: References: <4C856EB3.9060608@christianmoe.com> <20100907005653.GE2414@soloJazz.com> <4C8603FB.9080409@christianmoe.com> Mime-Version: 1.0 Content-Type: multipart/mixed; boundary="===============0017254219==" Return-path: Received: from [140.186.70.92] (port=43393 helo=eggs.gnu.org) by lists.gnu.org with esmtp (Exim 4.43) id 1Ovqn1-0000NU-QS for emacs-orgmode@gnu.org; Wed, 15 Sep 2010 08:08:48 -0400 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.69) (envelope-from ) id 1Ovqms-0000oQ-Ta for emacs-orgmode@gnu.org; Wed, 15 Sep 2010 08:08:39 -0400 Received: from mail-gw0-f41.google.com ([74.125.83.41]:38662) by eggs.gnu.org with esmtp (Exim 4.69) (envelope-from ) id 1Ovqms-0000oH-Q8 for emacs-orgmode@gnu.org; Wed, 15 Sep 2010 08:08:30 -0400 Received: by gwj16 with SMTP id 16so55548gwj.0 for ; Wed, 15 Sep 2010 05:08:30 -0700 (PDT) In-Reply-To: <4C8603FB.9080409@christianmoe.com> List-Id: "General discussions about Org-mode." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Sender: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org Errors-To: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org To: mail@christianmoe.com Cc: emacs-orgmode@gnu.org, Juan --===============0017254219== Content-Type: multipart/alternative; boundary=0016369202c90b269f04904b3002 --0016369202c90b269f04904b3002 Content-Type: text/plain; charset=ISO-8859-1 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 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 >> >> > > --0016369202c90b269f04904b3002 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable 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 =A0| b | c |
|------+----+---+---|
| foo =A0| 1 =A0| 2 | 3 |
| bar =A0| 3 =A0| 2 | 1 |
| bar =A0| 4 =A0| 5 | 6 |
|------+----+---+---|
| =A0 =A0 =A0| 7 =A0| =A0 | =A0 |
#+TBLFM: @5$2=3D'(apply '+ (mapcar* (lambda(x y) (if (string=3D x &= quot;bar") y 0)) '(@I$1..@II$1) '(@I$2..@II$2)));L

=A0* the two arguments at the end are the name and a columns: '(foo ba= r bar) and '(1 3 4)
=A0* the lambda function returns the second argument if first is "bar= ", 0 otherwise.
=A0* mapcar* applies the lambda function to arguments from the 2 lists. =A0* 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
=A0(defun vsumif (string x y)
=A0"Sum values of Y for all X matching STRING."

=A0 =A0(apply '+
=A0 =A0 =A0 =A0 =A0 (mapcar*
=A0 =A0 =A0 =A0 =A0 =A0(lambda(x y)
=A0 =A0 =A0 =A0 =A0 =A0 =A0(if (string=3D x match) y 0))
=A0 =A0 =A0 =A0 =A0 =A0x 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 =A0| 1 | 2 | 3 |
| bar =A0| 3 | 2 | 1 |
| bar =A0| 4 | 5 | 6 |
|------+---+---+---|
| bar =A0| =A0 | =A0 | =A0 |
#+TBLFM: @5$2=3D'(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:

| =A0 | name | foo | bar | bar | bar |
|---+------+-----+-----+-----+-----|
| / | <> =A0 | =A0 < | =A0 =A0 | =A0 > | =A0<> |
| =A0 | a =A0 =A0| =A0 1 | =A0 3 | =A0 4 | =A0 =A0 |
| =A0 | b =A0 =A0| =A0 2 | =A0 2 | =A0 5 | =A0 =A0 |
| =A0 | c =A0 =A0| =A0 3 | =A0 1 | =A0 6 | =A0 =A0 |
#+TBLFM: $6=3D'(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 =3Dvsumif=3D 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 =3Dvsumifstring=3D.

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:

=A0 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 =A0| 1 | 2 | 3 |
| bar =A0| 3 | 2 | 1 |
| bar =A0| 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 | =A0 | a | b | c |
|------+---+---+---+---|
| foo =A0| =A0 | 1 | 2 | 3 |
| bar =A0| =A0 | 3 | 2 | 1 |
| bar =A0| =A0 | 4 | 5 | 6 |
|------+---+---+---+---|
| =A0 =A0 =A0| =A0 | =A0 | =A0 | =A0 |
=A0 #+TBLFM: $2=3D'(if (string=3D $1 "bar") 1 0)::
@5$3=3Dvsum(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@gn= u.org
http://lists.gnu.org/mailman/listinfo/emacs-orgmode




--0016369202c90b269f04904b3002-- --===============0017254219== Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Disposition: inline _______________________________________________ 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 --===============0017254219==--