From: Michael Brand Date: Fri, 28 Dec 2012 14:03:10 +0000 (+0100) Subject: Use nan for empty fields in Calc formulas X-Git-Tag: release_8.0-pre~625 X-Git-Url: https://repo.or.cz/w/org-mode.git/commitdiff_plain/a77442b37ba403fa131e877c7c8dc5167406061a Use nan for empty fields in Calc formulas * doc/org.texi (Formula syntax for Calc): Add explanation and example for empty field. * lisp/org-table.el (org-table-eval-formula): Use `keep-empty' in more places. (org-table-make-reference): Use nan (not a number) for empty fields in Calc formulas. * testing/lisp/test-org-table.el: Adapt expected for several ert-deftest. (test-org-table/empty-field): New examples dealing with empty fields. (test-org-table/copy-field): New ert-deftest with examples dealing with empty fields. This makes it possible to write spreadsheet Calc formulas that check for empty fields: To sum the first two columns unless one or both empty: $3 = if("$1" = "nan" || "$2" = "nan", string(""), $1 + $2); E --- diff --git a/doc/org.texi b/doc/org.texi index 08c5408f8..709fe4dce 100644 --- a/doc/org.texi +++ b/doc/org.texi @@ -2616,7 +2616,9 @@ n3 s3 e2 f4 @r{Normal, scientific, engineering, or fixed} @r{long as the Calc calculation precision is greater.} D R @r{angle modes: degrees, radians} F S @r{fraction and symbolic modes} -E @r{keep empty fields in ranges; else suppress empty fields in} +E @r{keep empty fields in ranges and use nan (not a number)} + @r{in Calc formulas for empty fields in range references and} + @r{for empty field references; else suppress empty fields in} @r{range references and use 0 for empty field references, see} @r{also the notes for `Range references' in @pxref{References}} N @r{interpret all fields as numbers, use 0 for non-numbers;} @@ -2653,7 +2655,10 @@ taylor($3,x=7,2) @r{Taylor series of $3, at x=7, second degree} Calc also contains a complete set of logical operations. For example @example -if($1<20,teen,string("")) @r{"teen" if age $1 less than 20, else empty} +if($1 < 20, teen, string("")) + @r{"teen" if age $1 is less than 20, else empty} +if("$1" = "nan" || "$2" = "nan", string(""), $1 + $2); E + @r{sum of first two columns unless one or both empty} @end example Note that you can also use two org-specific flags @code{T} and @code{t} for diff --git a/lisp/org-table.el b/lisp/org-table.el index 1f540b496..0e7b94414 100644 --- a/lisp/org-table.el +++ b/lisp/org-table.el @@ -2637,7 +2637,8 @@ not overwrite the stored one." (match-string 0 form))) (setq form (replace-match (save-match-data - (org-table-make-reference x nil numbers lispp)) + (org-table-make-reference + x keep-empty numbers lispp)) t t form))) (if lispp @@ -2664,7 +2665,8 @@ not overwrite the stored one." (setq ev (if (and duration (string-match "^[0-9]+:[0-9]+\\(?::[0-9]+\\)?$" form)) form - (calc-eval (cons form org-tbl-calc-modes) (if numbers 'num))) + (calc-eval (cons form org-tbl-calc-modes) + (when (and (not keep-empty) numbers) 'num))) ev (if duration (org-table-time-seconds-to-string (if (string-match "^[0-9]+:[0-9]+\\(?::[0-9]+\\)?$" ev) (string-to-number (org-table-time-string-to-seconds ev)) @@ -2851,15 +2853,27 @@ and TABLE is a vector with line types." "Convert list ELEMENTS to something appropriate to insert into formula. KEEP-EMPTY indicated to keep empty fields, default is to skip them. NUMBERS indicates that everything should be converted to numbers. -LISPP means to return something appropriate for a Lisp list." - (if (stringp elements) ; just a single val +LISPP non-nil means to return something appropriate for a Lisp +list, 'literal is for the format specifier L." + ;; Calc nan (not a number) is used for the conversion of the empty + ;; field to a reference for several reasons: (i) It is accepted in a + ;; Calc formula (e. g. "" or "()" would result in a Calc error). + ;; (ii) In a single field (not in range) it can be distinguished + ;; from "(nan)" which is the reference made from a single field + ;; containing "nan". + (if (stringp elements) + ;; field reference (if lispp (if (eq lispp 'literal) elements (prin1-to-string (if numbers (string-to-number elements) elements))) - (if (equal elements "") (setq elements "0")) - (if numbers (setq elements (number-to-string (string-to-number elements)))) - (concat "(" elements ")")) + (if (string-match "\\S-" elements) + (progn + (when numbers (setq elements (number-to-string + (string-to-number elements)))) + (concat "(" elements ")")) + (if (or (not keep-empty) numbers) "(0)" "nan"))) + ;; range reference (unless keep-empty (setq elements (delq nil @@ -2879,7 +2893,7 @@ LISPP means to return something appropriate for a Lisp list." (if numbers (number-to-string (string-to-number x)) x) - (if (or (not keep-empty) numbers) "0" ""))) + (if (or (not keep-empty) numbers) "0" "nan"))) elements ",") "]")))) diff --git a/testing/lisp/test-org-table.el b/testing/lisp/test-org-table.el index 6133005ee..4c09239c5 100644 --- a/testing/lisp/test-org-table.el +++ b/testing/lisp/test-org-table.el @@ -254,10 +254,10 @@ reference (with row). Format specifier E." (org-test-table-target-expect references/target-normal " -| 0 | 1 | 0 | 1 | 1 | 1 | 2 | 2 | -| z | 1 | z | z + 1 | z + 1 | z + 1 | 2 | 2 | -| | 1 | 0 | 1 | #ERROR | #ERROR | #ERROR | #ERROR | -| | | 0 | 0 | #ERROR | #ERROR | #ERROR | #ERROR | +| 0 | 1 | 0 | 1 | 1 | 1 | 2 | 2 | +| z | 1 | z | z + 1 | z + 1 | z + 1 | 2 | 2 | +| | 1 | nan | nan | nan | nan | 2 | 2 | +| | | nan | nan | nan | nan | 2 | 2 | " 1 calc) (org-test-table-target-expect @@ -398,24 +398,26 @@ reference (with row). Format specifier N." "Basic: Compare field references in Calc." (org-test-table-target-expect " -| | 0 | z | nan | uinf | -inf | inf | -|------+------+------+------+------+------+------| -| 0 | repl | repl | repl | repl | repl | repl | -| z | repl | repl | repl | repl | repl | repl | -| nan | repl | repl | repl | repl | repl | repl | -| uinf | repl | repl | repl | repl | repl | repl | -| -inf | repl | repl | repl | repl | repl | repl | -| inf | repl | repl | repl | repl | repl | repl | +| | 0 | z | | nan | uinf | -inf | inf | +|------+------+------+------+------+------+------+------| +| 0 | repl | repl | repl | repl | repl | repl | repl | +| z | repl | repl | repl | repl | repl | repl | repl | +| | repl | repl | repl | repl | repl | repl | repl | +| nan | repl | repl | repl | repl | repl | repl | repl | +| uinf | repl | repl | repl | repl | repl | repl | repl | +| -inf | repl | repl | repl | repl | repl | repl | repl | +| inf | repl | repl | repl | repl | repl | repl | repl | " " -| | 0 | z | nan | uinf | -inf | inf | -|------+---+---+-----+------+------+-----| -| 0 | x | | | | | | -| z | | x | | | | | -| nan | | | x | | | | -| uinf | | | | x | | | -| -inf | | | | | x | | -| inf | | | | | | x | +| | 0 | z | | nan | uinf | -inf | inf | +|------+---+---+---+-----+------+------+-----| +| 0 | x | | | | | | | +| z | | x | | | | | | +| | | | x | | | | | +| nan | | | | x | | | | +| uinf | | | | | x | | | +| -inf | | | | | | x | | +| inf | | | | | | | x | " 1 ;; Compare field reference ($1) with field reference (@1) @@ -424,10 +426,11 @@ reference (with row). Format specifier N." (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\" = \"(uinf)\", x, string(\"\")); E :: " - "$6 = if(\"$1\" = \"(-inf)\", x, string(\"\")); E :: " - "$7 = if(\"$1\" = \"(inf)\" , 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. @@ -436,11 +439,13 @@ reference (with row). Format specifier N." " | 0 | replace | | z | replace | +| | replace | | nan | replace | " " | 0 | 1 | | z | z + 1 | +| | | | nan | nan | " 1 "#+TBLFM: $2 = if(\"$1\" = \"nan\", string(\"\"), $1 + 1); E")) @@ -463,7 +468,119 @@ reference (with row). Format specifier N." "$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"))) + "$8 = '(/ (+ @0$1..@0$4) (length '(@0$1..@0$4))); EN")) + + ;; Test if one field is empty, else do a calculation + (org-test-table-target-expect + " +| -1 | replace | +| 0 | replace | +| | replace | +" + " +| -1 | 0 | +| 0 | 1 | +| | | +" + 1 + ;; Calc formula + "#+TBLFM: $2 = if(\"$1\" = \"nan\", string(\"\"), $1 + 1); E" + ;; Lisp formula + "#+TBLFM: $2 = '(if (eq \"$1\" \"\") \"\" (1+ $1)); L") + + ;; Test if several fields are empty, else do a calculation + (org-test-table-target-expect + " +| 1 | 2 | replace | +| 4 | | replace | +| | 8 | replace | +| | | replace | +" + " +| 1 | 2 | 3 | +| 4 | | | +| | 8 | | +| | | | +" + 1 + ;; Calc formula + (concat "#+TBLFM: $3 = if(\"$1\" = \"nan\" || \"$2\" = \"nan\", " + "string(\"\"), $1 + $2); E") + ;; Lisp formula + (concat "#+TBLFM: $3 = '(if (or (eq \"$1\" \"\") (eq \"$2\" \"\")) " + "\"\" (+ $1 $2)); L")) + + ;; $2: Use $1 + 0.5 if $1 available, else only reformat $2 if $2 available + (org-test-table-target-expect + " +| 1.5 | 0 | +| 3.5 | | +| | 5 | +| | | +" + " +| 1.5 | 2.0 | +| 3.5 | 4.0 | +| | 5.0 | +| | | +" + 1 + ;; Calc formula + (concat "#+TBLFM: $2 = if(\"$1\" = \"nan\", " + "if(\"$2\" = \"nan\", string(\"\"), $2 +.0), $1 + 0.5); E f-1") + ;; Lisp formula not implemented yet + )) + +(ert-deftest test-org-table/copy-field () + "Experiments on how to copy one field into another field." + (let ((target + " +| 0 | replace | +| a b | replace | +| c d | replace | +| | replace | +| 2012-12 | replace | +| [2012-12-31 Mon] | replace | +")) + ;; Lisp formula to copy literally + (org-test-table-target-expect + target + " +| 0 | 0 | +| a b | a b | +| c d | c d | +| | | +| 2012-12 | 2012-12 | +| [2012-12-31 Mon] | [2012-12-31 Mon] | +" + 1 "#+TBLFM: $2 = '(identity $1)") + + ;; Calc formula to copy quite literally + (org-test-table-target-expect + target + " +| 0 | 0 | +| a b | a b | +| c d | c d | +| | | +| 2012-12 | 2012-12 | +| [2012-12-31 Mon] | <2012-12-31 Mon> | +" + 1 (concat "#+TBLFM: $2 = if(\"$1\" = \"nan\", " + "string(\"\"), string(subvec(\"$1\", 2, vlen(\"$1\")))); E")) + + ;; Calc formula simple + (org-test-table-target-expect + target + " +| 0 | 0 | +| a b | a b | +| c d | c d | +| | | +| 2012-12 | 2000 | +| [2012-12-31 Mon] | <2012-12-31 Mon> | +" + 1 "#+TBLFM: $2 = if(\"$1\" = \"nan\", string(\"\"), $1); E"))) ;; End of table examples and beginning of internal tests. @@ -491,11 +608,11 @@ reference (with row). Format specifier N." ;; For Calc formula (should (equal "(0)" (f "0" t nil nil))) (should (equal "(z)" (f "z" t nil nil))) - (should (equal "(0)" (f "" t nil nil))) + (should (equal "nan" (f "" t nil nil))) (should (equal "[0,1]" (f '("0" "1") t nil nil))) (should (equal "[z,1]" (f '("z" "1") t nil nil))) - (should (equal "[,1]" (f '("" "1") t nil nil))) - (should (equal "[,]" (f '("" "" ) t nil nil))) + (should (equal "[nan,1]" (f '("" "1") t nil nil))) + (should (equal "[nan,nan]" (f '("" "" ) t nil nil))) ;; For Calc formula, special numbers (should (equal "(nan)" (f "nan" t nil nil))) (should (equal "(uinf)" (f "uinf" t nil nil)))