From mboxrd@z Thu Jan 1 00:00:00 1970 From: Carsten Dominik Subject: Re: new spreadsheet features: field coordinates, multi line TBLFM with comments Date: Sun, 14 Mar 2010 11:06:07 +0100 Message-ID: <24E1A3E6-3F21-432B-801A-56726F002155@gmail.com> References: <4B9BAED2.8000600@alumni.ethz.ch> Mime-Version: 1.0 (Apple Message framework v936) Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes Content-Transfer-Encoding: 7bit Return-path: Received: from mailman by lists.gnu.org with tmda-scanned (Exim 4.43) id 1Nqki6-0007kn-Oo for emacs-orgmode@gnu.org; Sun, 14 Mar 2010 06:06:14 -0400 Received: from [140.186.70.92] (port=56579 helo=eggs.gnu.org) by lists.gnu.org with esmtp (Exim 4.43) id 1Nqki4-0007i5-RA for emacs-orgmode@gnu.org; Sun, 14 Mar 2010 06:06:14 -0400 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.69) (envelope-from ) id 1Nqki2-0005X2-Aq for emacs-orgmode@gnu.org; Sun, 14 Mar 2010 06:06:12 -0400 Received: from mail-ew0-f216.google.com ([209.85.219.216]:41059) by eggs.gnu.org with esmtp (Exim 4.69) (envelope-from ) id 1Nqki2-0005Ww-3N for emacs-orgmode@gnu.org; Sun, 14 Mar 2010 06:06:10 -0400 Received: by ewy8 with SMTP id 8so696663ewy.8 for ; Sun, 14 Mar 2010 03:06:09 -0700 (PDT) In-Reply-To: <4B9BAED2.8000600@alumni.ethz.ch> 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: Michael Brand Cc: Org Mode Hi Michael, On Mar 13, 2010, at 4:27 PM, Michael Brand wrote: > Hi Carsten, hi all > > 1) field coordinates: > As a proposal I implemented the following to cover one of my > spreadsheet use cases: Why not let `@#' and `$#' in Calc formulas > (works also for Lisp formulas) be substituted to the row or column > number of the formula result field?. The traditional Lisp formula > equivalents are org-table-current-dline and org-table-current- > column. I believe it does not break the TBLFM syntax for any other > use. > > My use case is an overview with the average annual relative changes > of a time series. Here is an example with averages for a history of > 1 year up to 4 years: > | year | quote | 1 a | 2 a | 3 a | 4 a | > |------+-------+-------+-------+-------+-------| > | 2005 | 10 | | | | | > | 2006 | 12 | 0.200 | | | | > | 2007 | 14 | 0.167 | 0.183 | | | > | 2008 | 16 | 0.143 | 0.155 | 0.170 | | > | 2009 | 18 | 0.125 | 0.134 | 0.145 | 0.158 | > #+TBLFM: $3 = if(@# + 1 > $#, ($2 / subscr(@-I$2..@+I$2, @# + 1 - > $#)) ^ (1 / ($# - 2)) - 1, string("")); f3 :: $4 = if(@# + 1 > $#, > ($2 / subscr(@-I$2..@+I$2, @# + 1 - $#)) ^ (1 / ($# - 2)) - 1, > string("")); f3 :: $5 = if(@# + 1 > $#, ($2 / subscr(@-I$2..@+I$2, > @# + 1 - $#)) ^ (1 / ($# - 2)) - 1, string("")); f3 :: $6 = if(@# + > 1 > $#, ($2 / subscr(@-I$2..@+I$2, @# + 1 - $#)) ^ (1 / ($# - 2)) - > 1, string("")); f3 > > The patch (the files with `---' were taken from org-version 6.34c): > ====================================================================== > --- a/doc/org.texi > +++ b/doc/org.texi > @@ -2098,6 +2098,21 @@ table in that entry. REF is an absolute > described above for example @code{@@3$3} or @code{$somename}, valid > in the > referenced table. > > +@subsubheading Field coordinates > +@cindex field coordinates > +@cindex coordinates, of field > +@cindex row, of field coordinates > +@cindex column, of field coordinates > + > +For Calc formulas and Lisp formulas @code{@@#} and @code{$#} can be > used to > +get the row or column number of the field where the formula result > goes. > +The traditional Lisp formula equivalents are @code{org-table- > current-dline} > +and @code{org-table-current-column}. Example: > + > +@example > +if(@@# % 2, $#, string("")) @r{column number on odd lines only} > +@end example > + > @node Formula syntax for Calc, Formula syntax for Lisp, References, > The spreadsheet > @subsection Formula syntax for Calc > @cindex formula syntax, Calc > --- a/lisp/org-table.el > +++ b/lisp/org-table.el > @@ -2243,6 +2243,21 @@ not overwrite the stored one." > (setq form (copy-sequence formula) > lispp (and (> (length form) 2)(equal (substring form 0 2) > "'("))) > (if (and lispp literal) (setq lispp 'literal)) > + > + ;; Insert row number of formula result field > + (while (string-match "\\@#" form) > + (setq form > + (replace-match > + (save-match-data > + (format "%d" (org-table-current-dline))) > + t t form))) > + ;; Insert column number of formula result field > + (while (string-match "\\$#" form) > + (setq form > + (replace-match > + (save-match-data > + (format "%d" (org-table-current-column))) > + t t form))) > ;; Check for old vertical references > (setq form (org-table-rewrite-old-row-references form)) > ;; Insert remote references > ====================================================================== That does look reasonable, and I am going to take that patch. > > 2) self-edited multi line TBLFM with comments: > For me it would be great to have the now missing option to hack all > the formulas myself just directly, but rather into a multi line > TBLFM with comments than into the one line #+TBLFM:. For the use > case above this would look like: Before looking further, I would like to find out if you are aware of the existence of the formula editior, invoked the command C-c ' (that is C-c followed by the single quote) while the cursor is in a table. The formula editor does not have comments, but otherwise it is more powerful than what you propose. - Carsten > > [...] > | 2009 | 18 | 0.125 | 0.134 | 0.145 | 0.158 | > #+BEGIN_TBLFM > $3 = if(@# + 1 > $#, ($2 / subscr(@-I$2..@+I$2, @# + 1 - $#)) ^ > (1 / ($# - 2)) - 1, string("")); f3 :: # rel. change for 1 year > $4 = if(@# + 1 > $#, ($2 / subscr(@-I$2..@+I$2, @# + 1 - $#)) ^ > (1 / ($# - 2)) - 1, string("")); f3 :: # average for 2 years > $5 = if(@# + 1 > $#, ($2 / subscr(@-I$2..@+I$2, @# + 1 - $#)) ^ > (1 / ($# - 2)) - 1, string("")); f3 :: # average for 3 years > $6 = if(@# + 1 > $#, ($2 / subscr(@-I$2..@+I$2, @# + 1 - $#)) ^ > (1 / ($# - 2)) - 1, string("")); f3 # average for 4 years > #+END_TBLFM > > I suggest to leave the one line #+TBLFM without the comment > possibility to avoid issues with the very nice write back to #+TBLFM > when editing with e. g. `C-c =' or moving rows and columns. In favor > of a complexity reduction and to avoid confusion I suggest to leave > the #+BEGIN_TBLFM option without write back and to unconditionally > reject a trial to do it. Similar to the nice reject when trying to > edit column 1 with `C-c =' in the following example, where #+TBLFM > has been upset by self-editing it: > | 0 | > #+TBLFM: $1 = 0 :: $1 = 0 > > This way the implementation for the multi line TBLFM with comments > could on evaluation simply strip the comments from all the lines > between #+BEGIN_TBLFM and #+END_TBLFM, join the rest of these lines > into one line and pass it to where the #+TBLFM: content is passed > today. Of course there would still be more to do to make the whole > work. > > Surprise: #+BEGIN_TBLFM already folds by TAB without implementing > anything yet. > > > The TBLFM comments should not be parsed as `#' but rather ` #' in > order to not break > a) the new field coordinates `@#' and `$#' if done as in my proposal > above > b) the Calc input radix > > Example: > | (1, 1) | (1, 2) | 256 | > #+BEGIN_TBLFM > $1 = (@#, $#) :: $2 = (@#, $#) :: # a) numbers of row and column > $3 = 16#100 # b) Calc input radix with hash char > #+END_TBLFM > > I'm not sure if adding a regexp for multi line TBLFM comments to > some face for highlighting would make too much trouble with breaking > other stuff containing ` #' outside multi line TBLFM. > > > How easy would that be to implement? > > Michael - Carsten