William Henney when...@gmail.com writes:
Hi Jeff
On Mon, Mar 8, 2010 at 6:54 AM, Jeff Kowalczyk j...@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.199, 'foo'], ['2010-03-02', 11.5, 'foo'],
['2010-03-02', 12.6, 'bar'], ['2010-03-03', 10.199, '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
___
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
