From 4861f2c3124475e9a48b3ae32b704327b5abea39 Mon Sep 17 00:00:00 2001 From: Michael Brand Date: Sun, 14 Apr 2013 10:03:17 +0200 Subject: [PATCH 2/2] Improve doc of empty fields * doc/org.texi (Formula syntax for Calc): Improve the documentation of empty fields in formulas for spreadsheet. * testing/lisp/test-org-table.el: Rename wrongly named functions. * testing/lisp/test-org-table.el: Use the more obvious "==" instead of "=" for Calc conditions. * testing/lisp/test-org-table.el (test-org-table/empty-field): Improve the examples for vmean with empty fields. --- doc/org.texi | 84 +++++++++++++++--------- testing/lisp/test-org-table.el | 139 +++++++++++++++++++++------------------- 2 files changed, 126 insertions(+), 97 deletions(-) diff --git a/doc/org.texi b/doc/org.texi index c9b1f5b..f5e5c8b 100644 --- a/doc/org.texi +++ b/doc/org.texi @@ -2538,10 +2538,10 @@ $<<<..$>> @r{start in third column, continue to the one but last} @end example @noindent Range references return a vector of values that can be fed -into Calc vector functions. Empty fields in ranges are normally -suppressed, so that the vector contains only the non-empty fields (but -see the @samp{E} mode switch below). If there are no non-empty fields, -@samp{[0]} is returned to avoid syntax errors in formulas. +into Calc vector functions. Empty fields in ranges are normally suppressed, +so that the vector contains only the non-empty fields. For other options +with the mode switches @samp{E}, @samp{N} and examples @pxref{Formula syntax +for Calc}. @subsubheading Field coordinates in formulas @cindex field coordinates @@ -2652,6 +2652,8 @@ format, however, has been changed to @code{(float 8)} to keep tables compact. The default settings can be configured using the variable @code{org-calc-default-modes}. +@noindent List of modes: + @table @asis @item @code{p20} Set the internal Calc calculation precision to 20 digits. @@ -2660,32 +2662,39 @@ Normal, scientific, engineering or fixed format of the result of Calc passed back to Org. Calc formatting is unlimited in precision as long as the Calc calculation precision is greater. @item @code{D}, @code{R} -Angle modes: Degree, radian. +Degree and radian angle modes of Calc. @item @code{F}, @code{S} -Fraction and symbolic modes. +Fraction and symbolic modes of Calc. +@item @code{T}, @code{t} +Duration computations in Calc or Lisp, @pxref{Durations and time values}. @item @code{E} -Keep empty fields in ranges and use nan (not a number) in Calc formulas for -empty fields in range references and for empty field references. Else -suppress empty fields in range references and use 0 for empty field -references, see also the notes for `Range references' in @pxref{References}. +If and how to consider empty fields. Without @samp{E} empty fields in range +references are suppressed so that the Calc vector or Lisp list contains only +the non-empty fields. With @samp{E} the empty fields are kept. For empty +fields in ranges or empty field references the value @samp{nan} (not a +number) is used in Calc formulas and the empty string is used for Lisp +formulas. Add @samp{N} to use 0 instead for both formula types. For the +value of a field the mode @samp{N} has higher precedence than @samp{E}. @item @code{N} -Interpret all fields as numbers, use 0 for non-numbers. @code{N} has higher -precedence than @code{E} (for the value of the field). +Interpret all fields as numbers, use 0 for non-numbers. See the next section +to see how this is essential for computations with Lisp formulas. In Calc +formulas it is used only occasionally because there number strings are +already interpreted as numbers without @samp{N}. @item @code{L} -Literal, for Lisp formulas only. +Literal, for Lisp formulas only. See the next section. @end table @noindent -Unless you use large integer numbers or high-precision-calculation -and -display for floating point numbers you may alternatively provide a -@code{printf} format specifier to reformat the Calc result after it has been +Unless you use large integer numbers or high-precision-calculation and +-display for floating point numbers you may alternatively provide a +@samp{printf} format specifier to reformat the Calc result after it has been passed back to Org instead of letting Calc already do the -formatting@footnote{The @code{printf} reformatting is limited in precision -because the value passed to it is converted into an @code{integer} or -@code{double}. The @code{integer} is limited in size by truncating the -signed value to 32 bits. The @code{double} is limited in precision to 64 -bits overall which leaves approximately 16 significant decimal digits.}. -A few examples: +formatting@footnote{The @samp{printf} reformatting is limited in precision +because the value passed to it is converted into an @samp{integer} or +@samp{double}. The @samp{integer} is limited in size by truncating the +signed value to 32 bits. The @samp{double} is limited in precision to 64 +bits overall which leaves approximately 16 significant decimal digits.}. A +few examples: @example $1+$2 @r{Sum of first and second field} @@ -2696,23 +2705,36 @@ $0;%.1f @r{Reformat current cell to 1 decimal} $c/$1/$cm @r{Hz -> cm conversion, using @file{constants.el}} tan($1);Dp3s1 @r{Compute in degrees, precision 3, display SCI 1} sin($1);Dp3%.1e @r{Same, but use printf specifier for display} -vmean($2..$7) @r{Compute column range mean, suppress empty fields} -vmean($2..$7);EN @r{Same, but treat empty fields as 0} taylor($3,x=7,2) @r{Taylor series of $3, at x=7, second degree} @end example -Calc also contains a complete set of logical operations. For example +Calc also contains a complete set of logical operations, (@pxref{Logical +Operations, , Logical Operations, calc, GNU Emacs Calc Manual}). For example @table @code @item if($1 < 20, teen, string("")) -"teen" if age $1 is less than 20, else empty. -@item if("$1" = "nan" || "$2" = "nan", string(""), $1 + $2); E -Sum of first two columns unless one or both empty. +"teen" if age $1 is less than 20, else the Org table result field is set to +empty with the empty string. +@item if("$1" == "nan" || "$2" == "nan", string(""), $1 + $2); E +Sum of the first two columns. When at least one of the input fields is empty +the Org table result field is set to empty. +@item if(typeof(vmean($1..$7)) == 12, string(""), vmean($1..$7); E +Mean value of a range unless there is any empty field. Every field in the +range that is empty is replaced by @samp{nan} which lets @samp{vmean} result +in @samp{nan}. Then @samp{typeof == 12} detects the @samp{nan} from +@samp{vmean} and the Org table result field is set to empty. Use this when +the sample set is expected to never have missing values. +@item if("$1..$7" == "[]", string(""), vmean($1..$7)) +Mean value of a range with empty fields skipped. Every field in the range +that is empty is skipped. When all fields in the range are empty the mean +value is not defined and the Org table result field is set to empty. Use +this when the sample set can have a variable size. +@item vmean($1..$7); EN +To complete the example before: Mean value of a range with empty fields +counting as samples with value 0. Use this only when incomplete sample sets +should be padded with 0 to the full size. @end table -Note that you can also use two org-specific flags @code{T} and @code{t} for -durations computations @ref{Durations and time values}. - You can add your own Calc functions defined in Emacs Lisp with @code{defmath} and use them in formula syntax for Calc. diff --git a/testing/lisp/test-org-table.el b/testing/lisp/test-org-table.el index 2dd5f38..732ddb3 100644 --- a/testing/lisp/test-org-table.el +++ b/testing/lisp/test-org-table.el @@ -184,10 +184,10 @@ " "Special numbers for Calc formula.") -(ert-deftest test-org-table/references/format-specifier-EL () +(ert-deftest test-org-table/references/mode-string-EL () "Basic: Assign field reference, sum of field references, sum and len of simple range reference (no row) and complex range -reference (with row). Format specifier EL." +reference (with row). Mode string EL." ;; Empty fields are kept during parsing field but lost as list ;; elements within Lisp formula syntactically when used literally ;; and not enclosed with " within fields, see last columns with len. @@ -227,10 +227,10 @@ reference (with row). Format specifier EL." "$5 = '(+ $1..$2); EL :: $6 = '(+ @0$1..@0$2); EL :: " "$7 = '(length '($1..$2)); EL :: $8 = '(length '(@0$1..@0$2)); EL"))) -(ert-deftest test-org-table/references/format-specifier-E () +(ert-deftest test-org-table/references/mode-string-E () "Basic: Assign field reference, sum of field references, sum and len of simple range reference (no row) and complex range -reference (with row). Format specifier E." +reference (with row). Mode string E." (let ((lisp (concat "#+TBLFM: $3 = '(identity $1); E :: $4 = '(+ $1 $2); E :: " @@ -270,10 +270,10 @@ reference (with row). Format specifier E." " 1 calc))) -(ert-deftest test-org-table/references/format-specifier-EN () +(ert-deftest test-org-table/references/mode-string-EN () "Basic: Assign field reference, sum of field references, sum and len of simple range reference (no row) and complex range -reference (with row). Format specifier EN." +reference (with row). Mode string EN." (let ((lisp (concat "#+TBLFM: $3 = '(identity $1); EN :: $4 = '(+ $1 $2); EN :: " "$5 = '(+ $1..$2); EN :: $6 = '(+ @0$1..@0$2); EN :: " @@ -302,10 +302,10 @@ reference (with row). Format specifier EN." " 1 calc))) -(ert-deftest test-org-table/references/format-specifier-L () +(ert-deftest test-org-table/references/mode-string-L () "Basic: Assign field reference, sum of field references, sum and len of simple range reference (no row) and complex range -reference (with row). Format specifier L." +reference (with row). Mode string L." (org-test-table-target-expect references/target-normal ;; All the #ERROR show that for Lisp calculations N has to be used. @@ -320,10 +320,10 @@ reference (with row). Format specifier L." "$5 = '(+ $1..$2); L :: $6 = '(+ @0$1..@0$2); L :: " "$7 = '(length '($1..$2)); L :: $8 = '(length '(@0$1..@0$2)); L"))) -(ert-deftest test-org-table/references/format-specifier-none () +(ert-deftest test-org-table/references/mode-string-none () "Basic: Assign field reference, sum of field references, sum and len of simple range reference (no row) and complex range -reference (with row). No format specifier." +reference (with row). No mode string." (let ((lisp (concat "#+TBLFM: $3 = '(identity $1) :: $4 = '(+ $1 $2) :: " "$5 = '(+ $1..$2) :: $6 = '(+ @0$1..@0$2) :: " @@ -361,10 +361,10 @@ reference (with row). No format specifier." " 1 calc))) -(ert-deftest test-org-table/references/format-specifier-N () +(ert-deftest test-org-table/references/mode-string-N () "Basic: Assign field reference, sum of field references, sum and len of simple range reference (no row) and complex range -reference (with row). Format specifier N." +reference (with row). Mode string N." (let ((lisp (concat "#+TBLFM: $3 = '(identity $1); N :: $4 = '(+ $1 $2); N :: " @@ -421,16 +421,16 @@ reference (with row). Format specifier N." " 1 ;; Compare field reference ($1) with field reference (@1) - "#+TBLFM: @I$<<..@>$> = if(\"$1\" = \"@1\", x, string(\"\")); E" + "#+TBLFM: @I$<<..@>$> = if(\"$1\" == \"@1\", x, string(\"\")); E" ;; Compare field reference ($1) with absolute term (concat "#+TBLFM: " - "$2 = if(\"$1\" = \"(0)\" , x, string(\"\")); E :: " - "$3 = if(\"$1\" = \"(z)\" , x, string(\"\")); E :: " - "$4 = if(\"$1\" = \"nan\" , x, string(\"\")); E :: " - "$5 = if(\"$1\" = \"(nan)\" , x, string(\"\")); E :: " - "$6 = if(\"$1\" = \"(uinf)\", x, string(\"\")); E :: " - "$7 = if(\"$1\" = \"(-inf)\", x, string(\"\")); E :: " - "$8 = if(\"$1\" = \"(inf)\" , x, string(\"\")); E")) + "$2 = if(\"$1\" == \"(0)\" , x, string(\"\")); E :: " + "$3 = if(\"$1\" == \"(z)\" , x, string(\"\")); E :: " + "$4 = if(\"$1\" == \"nan\" , x, string(\"\")); E :: " + "$5 = if(\"$1\" == \"(nan)\" , x, string(\"\")); E :: " + "$6 = if(\"$1\" == \"(uinf)\", x, string(\"\")); E :: " + "$7 = if(\"$1\" == \"(-inf)\", x, string(\"\")); E :: " + "$8 = if(\"$1\" == \"(inf)\" , x, string(\"\")); E")) ;; Check field reference converted from an empty field: Despite this ;; field reference will not end up in a result, Calc evaluates it. @@ -448,42 +448,10 @@ reference (with row). Format specifier N." | | | | nan | nan | " - 1 "#+TBLFM: $2 = if(\"$1\" = \"nan\", string(\"\"), $1 + 1); E")) + 1 "#+TBLFM: $2 = if(\"$1\" == \"nan\", string(\"\"), $1 + 1); E")) (ert-deftest test-org-table/empty-field () "Examples how to deal with empty fields." - ;; Empty fields in simple and complex range reference: Suppress them - ;; ($5 and $6) or keep them and use 0 ($7 and $8) - - (let ((calc (concat - "#+TBLFM: " - "$5 = vmean($1..$4) :: " - "$6 = vmean(@0$1..@0$4) :: " - "$7 = vmean($1..$4); EN :: " - "$8 = vmean(@0$1..@0$4); EN")) - (lisp (concat - "#+TBLFM: " - "$5 = '(/ (+ $1..$4 ) (length '( $1..$4 ))); N :: " - "$6 = '(/ (+ @0$1..@0$4) (length '(@0$1..@0$4))); N :: " - "$7 = '(/ (+ $1..$4 ) (length '( $1..$4 ))); EN :: " - "$8 = '(/ (+ @0$1..@0$4) (length '(@0$1..@0$4))); EN"))) - (org-test-table-target-expect - "\n| | | 5 | 7 | replace | replace | replace | replace |\n" - "\n| | | 5 | 7 | 6 | 6 | 3 | 3 |\n" - 1 calc lisp) - - ;; The mean value of a range with only empty fields is not defined - (let ((target - "\n| | | | | replace | replace | replace | replace |\n")) - (org-test-table-target-expect - target - "\n| | | | | vmean([]) | vmean([]) | 0 | 0 |\n" - 1 calc) - (org-test-table-target-expect - target - "\n| | | | | #ERROR | #ERROR | 0 | 0 |\n" - 1 lisp))) - ;; Test if one field is empty, else do a calculation (org-test-table-target-expect " @@ -498,7 +466,7 @@ reference (with row). Format specifier N." " 1 ;; Calc formula - "#+TBLFM: $2 = if(\"$1\" = \"nan\", string(\"\"), $1 + 1); E" + "#+TBLFM: $2 = if(\"$1\" == \"nan\", string(\"\"), $1 + 1); E" ;; Lisp formula "#+TBLFM: $2 = '(if (eq \"$1\" \"\") \"\" (1+ $1)); L") @@ -518,7 +486,7 @@ reference (with row). Format specifier N." " 1 ;; Calc formula - (concat "#+TBLFM: $3 = if(\"$1\" = \"nan\" || \"$2\" = \"nan\", " + (concat "#+TBLFM: $3 = if(\"$1\" == \"nan\" || \"$2\" == \"nan\", " "string(\"\"), $1 + $2); E") ;; Lisp formula (concat "#+TBLFM: $3 = '(if (or (eq \"$1\" \"\") (eq \"$2\" \"\")) " @@ -540,10 +508,49 @@ reference (with row). Format specifier N." " 1 ;; Calc formula - (concat "#+TBLFM: $2 = if(\"$1\" = \"nan\", " - "if(\"$2\" = \"nan\", string(\"\"), $2 +.0), $1 + 0.5); E f-1") + (concat "#+TBLFM: $2 = if(\"$1\" == \"nan\", " + "if(\"$2\" == \"nan\", string(\"\"), $2 +.0), $1 + 0.5); E f-1") ;; Lisp formula not implemented yet - )) + ) + + ;; Empty fields in simple and complex range reference + (org-test-table-target-expect + " +| | | | | repl | repl | repl | repl | repl | repl | +| | | 5 | 7 | repl | repl | repl | repl | repl | repl | +| 1 | 3 | 5 | 7 | repl | repl | repl | repl | repl | repl | +" + " +| | | | | | | | | 0 | 0 | +| | | 5 | 7 | | | 6 | 6 | 3 | 3 | +| 1 | 3 | 5 | 7 | 4 | 4 | 4 | 4 | 4 | 4 | +" + 1 + ;; Calc formula + (concat + "#+TBLFM: " + "$5 = if(typeof(vmean($1..$4)) == 12, " + "string(\"\"), vmean($1..$4)); E :: " + "$6 = if(typeof(vmean(@0$1..@0$4)) == 12, " + "string(\"\"), vmean(@0$1..@0$4)); E :: " + "$7 = if(\"$1..$4\" == \"[]\", string(\"\"), vmean($1..$4)) :: " + "$8 = if(\"@0$1..@0$4\" == \"[]\", string(\"\"), vmean(@0$1..@0$4)) :: " + "$9 = vmean($1..$4); EN :: " + "$10 = vmean(@0$1..@0$4); EN") + ;; Lisp formula + (concat + "#+TBLFM: " + "$5 = '(let ((l '($1..$4))) (if (member \"\" l) \"\" " + "(/ (apply '+ (mapcar 'string-to-number l)) (length l)))); E :: " + "$6 = '(let ((l '(@0$1..@0$4))) (if (member \"\" l) \"\" " + "(/ (apply '+ (mapcar 'string-to-number l)) (length l)))); E :: " + "$7 = '(let ((l '($1..$4))) " + "(if l (/ (apply '+ l) (length l)) \"\")); N :: " + "$8 = '(let ((l '(@0$1..@0$4))) " + "(if l (/ (apply '+ l) (length l)) \"\")); N :: " + "$9 = '(/ (+ $1..$4) (length '($1..$4))); EN :: " + "$10 = '(/ (+ @0$1..@0$4) (length '(@0$1..@0$4))); EN") +)) (ert-deftest test-org-table/copy-field () "Experiments on how to copy one field into another field." @@ -580,7 +587,7 @@ reference (with row). Format specifier N." | 2012-12 | 2012-12 | | [2012-12-31 Mon] | <2012-12-31 Mon> | " - 1 (concat "#+TBLFM: $2 = if(\"$1\" = \"nan\", " + 1 (concat "#+TBLFM: $2 = if(\"$1\" == \"nan\", " "string(\"\"), string(subvec(\"$1\", 2, vlen(\"$1\")))); E")) ;; Calc formula simple @@ -594,11 +601,11 @@ reference (with row). Format specifier N." | 2012-12 | 2000 | | [2012-12-31 Mon] | <2012-12-31 Mon> | " - 1 "#+TBLFM: $2 = if(\"$1\" = \"nan\", string(\"\"), $1); E"))) + 1 "#+TBLFM: $2 = if(\"$1\" == \"nan\", string(\"\"), $1); E"))) ;; End of table examples and beginning of internal tests. -(ert-deftest test-org-table/org-table-make-reference/format-specifier-EL () +(ert-deftest test-org-table/org-table-make-reference/mode-string-EL () (fset 'f 'org-table-make-reference) ;; For Lisp formula only (should (equal "0" (f "0" t nil 'literal))) @@ -609,7 +616,7 @@ reference (with row). Format specifier N." (should (equal " 1" (f '("" "1") t nil 'literal))) (should (equal " " (f '("" "" ) t nil 'literal)))) -(ert-deftest test-org-table/org-table-make-reference/format-specifier-E () +(ert-deftest test-org-table/org-table-make-reference/mode-string-E () (fset 'f 'org-table-make-reference) ;; For Lisp formula (should (equal "\"0\"" (f "0" t nil t))) @@ -637,7 +644,7 @@ reference (with row). Format specifier N." (should (equal "[-inf,1]" (f '("-inf" "1") t nil nil))) (should (equal "[inf,1]" (f '( "inf" "1") t nil nil)))) -(ert-deftest test-org-table/org-table-make-reference/format-specifier-EN () +(ert-deftest test-org-table/org-table-make-reference/mode-string-EN () (fset 'f 'org-table-make-reference) ;; For Lisp formula (should (equal "0" (f "0" t t t))) @@ -665,7 +672,7 @@ reference (with row). Format specifier N." (should (equal "[0,1]" (f '("-inf" "1") t t nil))) (should (equal "[0,1]" (f '( "inf" "1") t t nil)))) -(ert-deftest test-org-table/org-table-make-reference/format-specifier-L () +(ert-deftest test-org-table/org-table-make-reference/mode-string-L () (fset 'f 'org-table-make-reference) ;; For Lisp formula only (should (equal "0" (f "0" nil nil 'literal))) @@ -676,7 +683,7 @@ reference (with row). Format specifier N." (should (equal "1" (f '("" "1") nil nil 'literal))) (should (equal "" (f '("" "" ) nil nil 'literal)))) -(ert-deftest test-org-table/org-table-make-reference/format-specifier-none () +(ert-deftest test-org-table/org-table-make-reference/mode-string-none () (fset 'f 'org-table-make-reference) ;; For Lisp formula (should (equal "\"0\"" (f "0" nil nil t))) @@ -704,7 +711,7 @@ reference (with row). Format specifier N." (should (equal "[-inf,1]" (f '("-inf" "1") nil nil nil))) (should (equal "[inf,1]" (f '( "inf" "1") nil nil nil)))) -(ert-deftest test-org-table/org-table-make-reference/format-specifier-N () +(ert-deftest test-org-table/org-table-make-reference/mode-string-N () (fset 'f 'org-table-make-reference) ;; For Lisp formula (should (equal "0" (f "0" nil t t))) -- 1.7.4.2