From mboxrd@z Thu Jan 1 00:00:00 1970 From: Dan Davison Subject: Re: Spreadsheet FR Date: Fri, 02 Apr 2010 10:44:23 -0400 Message-ID: <878w95sym0.fsf@stats.ox.ac.uk> References: <20100401214648.GL7262@thinkpad.adamsinfoserv.com> <49A58658-88AF-4A88-93E9-1CDB710B08A2@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 1Nxi6v-0001Wc-5I for emacs-orgmode@gnu.org; Fri, 02 Apr 2010 10:44:37 -0400 Received: from [140.186.70.92] (port=42162 helo=eggs.gnu.org) by lists.gnu.org with esmtp (Exim 4.43) id 1Nxi6s-0001T8-Hi for emacs-orgmode@gnu.org; Fri, 02 Apr 2010 10:44:35 -0400 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.69) (envelope-from ) id 1Nxi6q-0002QR-FO for emacs-orgmode@gnu.org; Fri, 02 Apr 2010 10:44:34 -0400 Received: from markov.stats.ox.ac.uk ([163.1.210.1]:54490) by eggs.gnu.org with esmtp (Exim 4.69) (envelope-from ) id 1Nxi6q-0002QG-7j for emacs-orgmode@gnu.org; Fri, 02 Apr 2010 10:44:32 -0400 In-Reply-To: <49A58658-88AF-4A88-93E9-1CDB710B08A2@gmail.com> (Carsten Dominik's message of "Fri, 2 Apr 2010 09:49:46 +0200") 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: Carsten Dominik Cc: Russell Adams , emacs-orgmode Hi Russell, Carsten Dominik writes: > On Apr 1, 2010, at 11:46 PM, Russell Adams wrote: > >> Fellow Org'ers, >> >> I adore the text spreadsheet, however there's one feature Excel >> provides which I don't have in org. >> >> I often use Excel for "lists", where I can sort or narrow the data by >> specific criteria from a larger list. >> >> Would it be feasible to "narrow" a table by criteria on a specific >> field in between separators? Ie: only display those cells in field A >> if they are > 2, or if field B matches "Pick Me!". How about keeping a master table containing all the information, and then generating narrowed views as separate tables? The babel way to do this would be to have a block function "filter-table" (provided below) and then call it where needed: #+TBLNAME: lime-table | *Lime* | *Cost* | |--------+--------| | Y | 1 | | Y | 2 | | Y | 2 | | N | 3 | | N | 4 | | Y | 5 | | Total | 17 | #+TBLFM: @8$2=vsum(@-I..@-II) #+call: filter-table(table=lime-table, field=0, value="Y") #+results: filter-table(table=lime-table, field=0, value="Y") | *Lime* | *Cost* | |--------+--------| | Y | 1 | | Y | 2 | | Y | 2 | | Y | 5 | As you can see I got rid of a few horizontal separator lines, and we don't currently have totals. I'll suggest fixes for that below, but my main point is that although it may make sense to extend org-mode, it's already easy to do this in org-babel. The separator lines are details, we can fix that by tinkering (if there's interest we could continue this thread to do so). As for the totals, we could go two routes: 1. We could add a final total row and use a table formula to compute the total 2. We could add the total row and compute the totals in a block function. As far as I know there's no way to add a final row using a table formula -- is that right? But we could automate (1) by wrapping the call to filter-table in a second block function that is specific to this problem: #+call: filter-with-total-line(table=lime-table, field=0, value="Y") #+results: filter-with-total-line(table=lime-table, field=0, value="Y") | *Lime* | *Cost* | |--------+--------| | Y | 1 | | Y | 2 | | Y | 2 | | Y | 5 | | Total | 10 | #+TBLFM: @6$2=vsum(@-I..) OK, so I hard wired a 6 into the TBLFM line. Perhaps someone can tell me how to refer to the last line of the table. Also, it might be nice if babel could apply the table formula automaticaly after generating the table. Here are the block functions. Once someone has written them, nobody else needs to know anything about the implementation. They can be placed in the same file (or in a different file, and added to your library of babel using org-babel-lob-ingest). I've already added filter-table to LoB on Worg. #+function: filter-table(table, field, value) #+begin_src emacs-lisp (defun org-lob-filter-table (table field value) (if (and (> (length table) 1) (eq (second table) 'hline)) (append (list (first table) 'hline) (org-lob-filter-table (cddr table) field value)) (delq nil (mapcar (lambda (row) (cond ((eq row 'hline) 'hline) ((equal (nth field row) value) row))) table)))) (org-lob-filter-table table field value) #+end_src #+function: filter-with-total-line(table, field, value) #+begin_src emacs-lisp (append (org-lob-filter-table table field value) '(("Total" ""))) #+end_src For those who are still reading, 1. These are written in elisp so that they're usable by anyone, although they would be one-liners in R. 2. The second one uses a function defined in the first one. This may be OK, but a perhaps preferable solution would be #+function: append-total-line(table) #+begin_src emacs-lisp (append table '(("Total" ""))) #+end_src #+call: append-total-line(table=filter-table(table=lime-table, field=0, value="Y")) Dan > > This one might be possible - but dangerous for losing data. > >> >> A nice feature would be updating the totals at the bottom with only >> the visible data. > > I don't think Excel works like this, does it? > >> >> Just like the outline folding the goal would be to hide entries that >> don't match, they should still remain. >> >> So for example: >> >> |--------+--------| >> | *Lime* | *Cost* | >> |--------+--------| >> | Y | 1 | >> | Y | 2 | >> | Y | 2 | >> | N | 3 | >> | N | 4 | >> | Y | 5 | >> |--------+--------| >> | Total | 17 | >> |--------+--------| >> #+TBLFM: @8$2=vsum(@-I..@-II) >> >> I can already sort by Lime or Cost, but if I filtered where Lime = >> "Y", I would have: >> >> |--------+--------| >> | *Lime* | *Cost* | >> |--------+--------| >> | Y | 1 | >> | Y | 2 | >> | Y | 2 |... >> | Y | 5 | >> |--------+--------| >> | Total | 10 | >> |--------+--------| >> #+TBLFM: @8$2=vsum(@-I..@-II) >> >> No loss of the lines, on reload or changing the view they would come >> back, but note that the formula at the end didn't consider >> them. Ideally the cell references wouldn't change (the example above >> would break if recalc was hit). >> >> I'm not sure whether this would be cumulative or whether the criteria >> specification would need to allow for multiple logical conditions. >> >> Would anyone else consider this a useful feature? I'm not sure how >> difficult the implementation would be. >> >> Thanks. >> >> >> _______________________________________________ >> Emacs-orgmode mailing list >> Please use `Reply All' to send replies to the list. >> Emacs-orgmode@gnu.org >> http://lists.gnu.org/mailman/listinfo/emacs-orgmode > > - Carsten > > > > > > _______________________________________________ > Emacs-orgmode mailing list > Please use `Reply All' to send replies to the list. > Emacs-orgmode@gnu.org > http://lists.gnu.org/mailman/listinfo/emacs-orgmode