[Top][All Lists]
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: [Orgmode] Org Mode spreadsheet SUMIF
From: |
Dan Davison |
Subject: |
Re: [Orgmode] Org Mode spreadsheet SUMIF |
Date: |
Tue, 09 Mar 2010 13:00:19 -0500 |
User-agent: |
Gnus/5.13 (Gnus v5.13) Emacs/23.1 (gnu/linux) |
William Henney <address@hidden> writes:
> Hi Jeff
>
> On Mon, Mar 8, 2010 at 6:54 AM, Jeff Kowalczyk <address@hidden> 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(@address@hidden)::@6$5=vsum(@address@hidden)
>
> 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