Org Mode spreadsheet SUMIF

Org Mode spreadsheet SUMIF

[Jeff Kowalczyk]

(Apologies if this is a FAQ, a gmane.org search of this list did not return any
matches)

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.

I'll probably use a remote table reference for the summary formulas.

Thanks,
Jeff

Re: Org Mode spreadsheet SUMIF

[William Henney]

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.

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

-- 

  Dr William Henney, Centro de Radioastronomía y Astrofísica,
  Universidad Nacional Autónoma de México, Campus Morelia

Re: Org Mode spreadsheet SUMIF

[Dan Davison]

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