From mboxrd@z Thu Jan 1 00:00:00 1970 From: Dan Davison Subject: Re: Org Mode spreadsheet SUMIF Date: Tue, 09 Mar 2010 13:00:19 -0500 Message-ID: <87eijtgyto.fsf@stats.ox.ac.uk> References: <41c818191003080759p2d823e5cqdf353941f1751363@mail.gmail.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Return-path: Received: from mailman by lists.gnu.org with tmda-scanned (Exim 4.43) id 1Np3jG-0001vn-MV for emacs-orgmode@gnu.org; Tue, 09 Mar 2010 13:00:26 -0500 Received: from [140.186.70.92] (port=56374 helo=eggs.gnu.org) by lists.gnu.org with esmtp (Exim 4.43) id 1Np3jF-0001uM-NW for emacs-orgmode@gnu.org; Tue, 09 Mar 2010 13:00:26 -0500 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.69) (envelope-from ) id 1Np3jE-0004rK-9S for emacs-orgmode@gnu.org; Tue, 09 Mar 2010 13:00:25 -0500 Received: from markov.stats.ox.ac.uk ([163.1.210.1]:43149) by eggs.gnu.org with esmtp (Exim 4.69) (envelope-from ) id 1Np3jD-0004qt-P3 for emacs-orgmode@gnu.org; Tue, 09 Mar 2010 13:00:24 -0500 In-Reply-To: <41c818191003080759p2d823e5cqdf353941f1751363@mail.gmail.com> (William Henney's message of "Mon, 8 Mar 2010 09:59:03 -0600") List-Id: "General discussions about Org-mode." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Sender: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org Errors-To: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org To: William Henney Cc: Jeff Kowalczyk , emacs-orgmode@gnu.org William Henney writes: > Hi Jeff > > On Mon, Mar 8, 2010 at 6:54 AM, Jeff Kowalczyk 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