* new spreadsheet features: field coordinates, multi line TBLFM with comments
@ 2010-03-13 15:27 Michael Brand
2010-03-14 10:06 ` Carsten Dominik
0 siblings, 1 reply; 4+ messages in thread
From: Michael Brand @ 2010-03-13 15:27 UTC (permalink / raw)
To: Carsten Dominik; +Cc: Org Mode
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
======================================================================
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:
[...]
| 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.
<side_note on comments>
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.
</side_note>
How easy would that be to implement?
Michael
^ permalink raw reply [flat|nested] 4+ messages in thread
* Re: new spreadsheet features: field coordinates, multi line TBLFM with comments
2010-03-13 15:27 new spreadsheet features: field coordinates, multi line TBLFM with comments Michael Brand
@ 2010-03-14 10:06 ` Carsten Dominik
2010-03-14 11:51 ` Michael Brand
0 siblings, 1 reply; 4+ messages in thread
From: Carsten Dominik @ 2010-03-14 10:06 UTC (permalink / raw)
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.
>
> <side_note on comments>
> 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.
> </side_note>
>
> How easy would that be to implement?
>
> Michael
- Carsten
^ permalink raw reply [flat|nested] 4+ messages in thread
* Re: new spreadsheet features: field coordinates, multi line TBLFM with comments
2010-03-14 10:06 ` Carsten Dominik
@ 2010-03-14 11:51 ` Michael Brand
2010-03-14 12:13 ` Carsten Dominik
0 siblings, 1 reply; 4+ messages in thread
From: Michael Brand @ 2010-03-14 11:51 UTC (permalink / raw)
To: Carsten Dominik; +Cc: Org Mode
Carsten Dominik wrote:
>> 1) field coordinates:
>> [...]
>> +@subsubheading Field coordinates
>> [...]
>
> That does look reasonable, and I am going to take that patch.
Thank you. Maybe this change of the above would make it clearer:
+@subsubheading Field coordinates in formulas
>> 2) self-edited multi line TBLFM with comments:
>
> 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.
Much more powerful, really great and well designed! Most of all the
highlighting of fields and ranges. I prefer that much over my proposal and
abandon comments inside. I knew the manual heading but since I stuck on the
word `debugging' and _underestimated_ the word `Editing', at least when
already editing in a org-mode buffer, I thought there is no need to read as
long as my formulas work... Not only for this reason but mostly because of
the great value I think it is more than worth mentioning it in the
spreadsheet summary in the manual somehow like this:
======================================================================
--- org.texi.orig.texi Sat Mar 13 15:41:20 2010
+++ org.texi Sun Mar 14 12:39:07 2010
@@ -1931,7 +1931,9 @@ derive fields from other fields. While
implementation is not identical to other spreadsheets. For example,
Org knows the concept of a @emph{column formula} that will be
applied to all non-header fields in a column without having to copy the
-formula to each relevant field.
+formula to each relevant field. There is a special formula editor with
+features for highlighting fields in the table corresponding to the
+references at the point in the formula, moving these references by
+arrow keys and a formula debugger.
@menu
* References:: How to refer to another field or range
=====================================================================
^ permalink raw reply [flat|nested] 4+ messages in thread
* Re: new spreadsheet features: field coordinates, multi line TBLFM with comments
2010-03-14 11:51 ` Michael Brand
@ 2010-03-14 12:13 ` Carsten Dominik
0 siblings, 0 replies; 4+ messages in thread
From: Carsten Dominik @ 2010-03-14 12:13 UTC (permalink / raw)
To: Michael Brand; +Cc: Org Mode
On Mar 14, 2010, at 12:51 PM, Michael Brand wrote:
> Carsten Dominik wrote:
>>> 1) field coordinates:
>>> [...]
>>> +@subsubheading Field coordinates
>>> [...]
>> That does look reasonable, and I am going to take that patch.
>
> Thank you. Maybe this change of the above would make it clearer:
> +@subsubheading Field coordinates in formulas
Done.
>
>
>>> 2) self-edited multi line TBLFM with comments:
>> 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.
>
> Much more powerful, really great and well designed! Most of all the
> highlighting of fields and ranges.
Thanks.
> I prefer that much over my proposal and abandon comments inside. I
> knew the manual heading but since I stuck on the word `debugging'
> and _underestimated_ the word `Editing', at least when already
> editing in a org-mode buffer, I thought there is no need to read as
> long as my formulas work... Not only for this reason but mostly
> because of the great value I think it is more than worth mentioning
> it in the spreadsheet summary in the manual somehow like this:
>
> ======================================================================
> --- org.texi.orig.texi Sat Mar 13 15:41:20 2010
> +++ org.texi Sun Mar 14 12:39:07 2010
> @@ -1931,7 +1931,9 @@ derive fields from other fields. While
> implementation is not identical to other spreadsheets. For example,
> Org knows the concept of a @emph{column formula} that will be
> applied to all non-header fields in a column without having to copy
> the
> -formula to each relevant field.
> +formula to each relevant field. There is a special formula editor
> with
> +features for highlighting fields in the table corresponding to the
> +references at the point in the formula, moving these references by
> +arrow keys and a formula debugger.
>
> @menu
> * References:: How to refer to another field or range
> =====================================================================
Applied as well, thank you!
- Carsten
^ permalink raw reply [flat|nested] 4+ messages in thread
end of thread, other threads:[~2010-03-14 12:14 UTC | newest]
Thread overview: 4+ messages (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
2010-03-13 15:27 new spreadsheet features: field coordinates, multi line TBLFM with comments Michael Brand
2010-03-14 10:06 ` Carsten Dominik
2010-03-14 11:51 ` Michael Brand
2010-03-14 12:13 ` Carsten Dominik
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).