From: Dan Davison <davison@stats.ox.ac.uk>
To: William Henney <whenney@gmail.com>
Cc: Jeff Kowalczyk <jtk@yahoo.com>, emacs-orgmode@gnu.org
Subject: Re: Org Mode spreadsheet SUMIF
Date: Tue, 09 Mar 2010 13:00:19 -0500 [thread overview]
Message-ID: <87eijtgyto.fsf@stats.ox.ac.uk> (raw)
In-Reply-To: <41c818191003080759p2d823e5cqdf353941f1751363@mail.gmail.com> (William Henney's message of "Mon, 8 Mar 2010 09:59:03 -0600")
William Henney <whenney@gmail.com> writes:
> Hi Jeff
>
> On Mon, Mar 8, 2010 at 6:54 AM, Jeff Kowalczyk <jtk@yahoo.com> wrote:
>> What is the Org Mode spreadsheet formula idiom for a SUMIF function?
>>
>> The objective is to add up the numeric values for rows matching a tag column:
>>
>> | 2010-03-01 | 12.2 | foo |
>> | 2010-03-02 | 11.5 | foo |
>> | 2010-03-02 | 12.6 | bar |
>> | 2010-03-03 | 10.2 | foo |
>>
>> Need a total for foo: 33.9, and bar: 12.6, etc.
Hi Jeff,
If there is a programming language that you're somewhat comfortable
with, then these sorts of slightly more complicated table tasks are one
of the reasons that org-babel[1] was written.
Personally I would reach for R first for a task like this:
#+tblname: table-data
| 2010-03-01 | 12.2 | foo |
| 2010-03-02 | 11.5 | foo |
| 2010-03-02 | 12.6 | bar |
| 2010-03-03 | 10.2 | foo |
#+begin_src R :var x=table-data :colnames yes
t(sapply(split(x[[2]], x[[3]]), sum))
#+end_src
#+results:
| bar | foo |
|------+------|
| 12.6 | 33.9 |
This was the most concise solution I came up with, plus you get the tag
names. In case you're comfortable with R, python or elisp, as a starting
point I've pasted blocks below that demonstrate the data structure that
your table is turned into in each of those languages.
Dan
[1] http://orgmode.org/worg/org-contrib/babel/index.php
*** R
#+begin_src R :var x=table-data :results output
str(x)
#+end_src
#+results:
: 'data.frame': 4 obs. of 3 variables:
: $ V1: chr "2010-03-01" "2010-03-02" "2010-03-02" "2010-03-03"
: $ V2: num 12.2 11.5 12.6 10.2
: $ V3: chr "foo" "foo" "bar" "foo"
*** python
#+begin_src python :var x=table-data :results output
print x
#+end_src
#+results:
: [['2010-03-01', 12.199999999999999, 'foo'], ['2010-03-02', 11.5, 'foo'], ['2010-03-02', 12.6, 'bar'], ['2010-03-03', 10.199999999999999, 'foo']]
*** elisp
#+begin_src emacs-lisp :var x=table-data :results pp
x
#+end_src
#+results:
: (("2010-03-01" 12.2 "foo")
: ("2010-03-02" 11.5 "foo")
: ("2010-03-02" 12.6 "bar")
: ("2010-03-03" 10.2 "foo"))
*** python solution
#+begin_src python :var x=table-data
[sum([row[1] if row[2] == tag else 0 for row in x]) for tag in ["bar","foo"]]
#+end_src
#+results:
| 12.6 | 33.9 |
>
> This is perhaps not the most elegant solution, but it does work.
>
> | date | values | tag | foo values | bar values |
> |------------+--------+-----+------------+------------|
> | 2010-03-01 | 12.2 | foo | 12.2 | 0 |
> | 2010-03-02 | 11.5 | foo | 11.5 | 0 |
> | 2010-03-02 | 12.6 | bar | 0 | 12.6 |
> | 2010-03-03 | 10.2 | foo | 10.2 | 0 |
> |------------+--------+-----+------------+------------|
> | | | | 33.9 | 12.6 |
> #+TBLFM: $4='(if (string-equal "$3" "foo") $2 0);L::$5='(if
> (string-equal "$3" "bar") $2
> 0);L::@6$4=vsum(@I..@II)::@6$5=vsum(@I..@II)
>
> The idea is to make an extra column containing only the values with a
> certain tag, and then sum that. It has the advantage that there is a
> natural place in the table to put each conditional sum.
>
> You could probably avoid the need for the extra columns if you recoded
> the summation using an elisp formula instead of a calc formula. As far
> as I know, you can't do string comparisons inside a calc formula.
>
> Cheers
>
> Will
prev parent reply other threads:[~2010-03-09 18:00 UTC|newest]
Thread overview: 3+ messages / expand[flat|nested] mbox.gz Atom feed top
2010-03-08 12:54 Org Mode spreadsheet SUMIF Jeff Kowalczyk
2010-03-08 15:59 ` William Henney
2010-03-09 18:00 ` Dan Davison [this message]
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
Avoid top-posting and favor interleaved quoting:
https://en.wikipedia.org/wiki/Posting_style#Interleaved_style
List information: https://www.orgmode.org/
* Reply using the --to, --cc, and --in-reply-to
switches of git-send-email(1):
git send-email \
--in-reply-to=87eijtgyto.fsf@stats.ox.ac.uk \
--to=davison@stats.ox.ac.uk \
--cc=emacs-orgmode@gnu.org \
--cc=jtk@yahoo.com \
--cc=whenney@gmail.com \
/path/to/YOUR_REPLY
https://kernel.org/pub/software/scm/git/docs/git-send-email.html
* If your mail client supports setting the In-Reply-To header
via mailto: links, try the mailto: link
Be sure your reply has a Subject: header at the top and a blank line
before the message body.
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).