* Org Mode spreadsheet SUMIF
@ 2010-03-08 12:54 Jeff Kowalczyk
2010-03-08 15:59 ` William Henney
0 siblings, 1 reply; 3+ messages in thread
From: Jeff Kowalczyk @ 2010-03-08 12:54 UTC (permalink / raw)
To: emacs-orgmode
(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
^ permalink raw reply [flat|nested] 3+ messages in thread
* Re: Org Mode spreadsheet SUMIF
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
0 siblings, 1 reply; 3+ messages in thread
From: William Henney @ 2010-03-08 15:59 UTC (permalink / raw)
To: Jeff Kowalczyk; +Cc: emacs-orgmode
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
^ permalink raw reply [flat|nested] 3+ messages in thread
* 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
end of thread, other threads:[~2010-03-09 18:00 UTC | newest]
Thread overview: 3+ messages (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
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
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).