Re: Spreadsheet FR

[Dan Davison <davison@stats.ox.ac.uk>]

Hi Russell,

Carsten Dominik <carsten.dominik@gmail.com> 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