* Re: Org Mode spreadsheet SUMIF
2010-03-08 15:59 ` William Henney
@ 2010-03-09 18:00 ` Dan Davison
0 siblings, 0 replies; 3+ messages in thread
From: Dan Davison @ 2010-03-09 18:00 UTC (permalink / raw)
To: William Henney; +Cc: Jeff Kowalczyk, emacs-orgmode
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
^ permalink raw reply [flat|nested] 3+ messages in thread