empty/nil in table cells

empty/nil in table cells

[Mario Frasca]

Hi people,

I've been experimenting with associating the empty cell with the value 
nil, both ways, and it simplifies a lot writing functions. also, I've 
removed the error generation when reading from outside the table 
(instead of giving up with a user-error, I now get a nil).

it works for me, and I don't see counterindications.  is it worthwhile, 
offering you the patch?

MF

Re: empty/nil in table cells

[Nicolas Goaziou]

Hello,

Mario Frasca <mario@anche.no> writes:

> I've been experimenting with associating the empty cell with the value
> nil, 

Where?

> both ways,

What do you mean?

> and it simplifies a lot writing functions. 

How so?

> also, I've removed the error generation when reading from outside the
> table (instead of giving up with a user-error, I now get a nil).

Would you mind explaining?

> it works for me, and I don't see counterindications.  is it
> worthwhile, offering you the patch?

Thank you. It may be so, but I may be missing some context.

Regards,
-- 
Nicolas Goaziou

Re: empty/nil in table cells

[Mario Frasca]

[-- Attachment #1: Type: text/plain, Size: 710 bytes --]

I think we really need a bug-tracking mechanism, you know?

see attachment

On 09/07/2020 16:59, Nicolas Goaziou wrote:
> Hello,
>
> Mario Frasca <mario@anche.no> writes:
>
>> I've been experimenting with associating the empty cell with the value
>> nil,
> Where?
>
>> both ways,
> What do you mean?
>
>> and it simplifies a lot writing functions.
> How so?
>
>> also, I've removed the error generation when reading from outside the
>> table (instead of giving up with a user-error, I now get a nil).
> Would you mind explaining?
>
>> it works for me, and I don't see counterindications.  is it
>> worthwhile, offering you the patch?
> Thank you. It may be so, but I may be missing some context.
>
> Regards,

[-- Attachment #2: bug: Row descriptor <...> leads outside table.eml --]
[-- Type: message/rfc822, Size: 11069 bytes --]

[-- Attachment #2.1.1: Type: text/plain, Size: 3742 bytes --]

sorry for the messed up setting of the table, trying again, and adding a 
web-paste

http://ix.io/2qMC

|  date | measure | running avg |
|-------+---------+-------------|
| 01-27 |     604 |             |
| 01-28 |     314 |             |
| 01-29 |     636 |             |
| 01-30 |     305 |             |
| 01-31 |     760 |             |
| 02-01 |     531 |             |
| 02-02 |     331 |      497.29 |
| 02-03 |      77 |      422.00 |
| 02-04 |     621 |      465.86 |
| 02-05 |     406 |      433.00 |
| 02-06 |     621 |      478.14 |
| 02-07 |     975 |      508.86 |
| 02-08 |     252 |      469.00 |
| 02-09 |     794 |      535.14 |
| 02-10 |      36 |      529.29 |
#+TBLFM: $3='(unless (> @# 7) "skipped")
#+TBLFM: $3='(when (> @# 7) (/ (apply #'+ '(@-6$2..$2)) 7.0));N%0.2f
#+TBLFM: $3='(/ (apply #'+ '(@-6$2..$2)) 7.0);N%0.2f::@2$3='(string)::@3$3='(string)::@4$3='(string)::@5$3='(string)::@6$3='(string)::@7$3='(string)

On 03/07/2020 09:53, Mario Frasca wrote:
> hi again,
>
> I haven't found how to solve this, short of removing the evaluation of 
> `user-error' from the org-table.el code, or stuffing my TBLFM line 
> with field formulas.
>
> a very simple use case: three columns: the date, a daily measurement, 
> and a running 4-days average.
>
> |  date | measure | running avg ||-------+---------+-------------|| 
> 01-27 |     604 | skipped || 01-28 |     314 | skipped     || 01-29 
> |     636 | skipped || 01-30 |     305 | skipped     || 01-31 |     
> 760 | skipped || 02-01 |     531 | skipped     || 02-02 |     331 | 
> nil || 02-03 |      77 | nil         || 02-04 |     621 | nil || 02-05 
> |     406 | nil         || 02-06 |     621 | nil || 02-07 |     975 | 
> nil         || 02-08 |     252 | nil || 02-09 |     794 | nil         
> || 02-10 |      36 | nil |#+TBLFM: $3='(unless (> @# 7) "skipped") 
> #+TBLFM: $3='(when (> @# 7) (/ (apply #'+ '(@-6$2..$2)) 7.0));N%0.2f 
> #+TBLFM: $3='(/ (apply #'+ '(@-6$2..$2)) 
> 7.0);N%0.2f::@2$3='(string)::@3$3='(string)::@4$3='(string)::@5$3='(string)::@6$3='(string)::@7$3='(string)
>
> the first TBLFM shows you which cells I'm skipping, that is, where I'm 
> not applying the @-6 reference.  the status of the table results from 
> evaluation of that TBLFM line.
>
> the second TBLFM line skips all @-6 references, but still fails with 
> the message
>
> user-error: Row descriptor -6 leads outside table
>
> the third TBLFM line "solves" the problem, in one of the ugliestests 
> ways.
>
> how would you people approach this?
>
>
> On 29/06/2020 12:03, Mario Frasca wrote:
>> Hi,
>>
>> I need some help understanding how to use org-mode/org-table for 
>> references leading outside my table.
>>
>> I have a series of daily figures, and I am computing the series of 
>> running sums.  column one is the daily data, column two is the 
>> running sum of the preceding 14 values from column 1.
>>
>> http://ix.io/2qu7
>>
>> problem is the @-13$1 reference in the function for the second 
>> column: it hits a software-generated "user-error: Row descriptor -13 
>> leads outside table".
>>
>> this happens even if the formula says (if (> @# 13) (apply '+ 
>> '(@-13$1..$1)) 0), that is, even if the formula is not evaluated on 
>> the cells where the reference does indeed lead out of the table.
>>
>> this happens around line 2809 of org-table.el, inside 
>> org-table--row-type
>>
>> I've tried to catch the user-error with a `condition-case', but even 
>> there, the error seems to happen before evaluation.
>>
>> I've replaced the `(user-error)' function with a `nil', and it works 
>> for me, but that's a very rough measure I'm afraid.
>>

[-- Attachment #2.1.2: Type: text/html, Size: 4949 bytes --]

Re: empty/nil in table cells

[Michael Brand]

Hi Mario

For such cases I use ~subseq~ to take the running window out of the
complete range ~@I$2..@II$2~:

|  date | measure | running avg |
|-------+---------+-------------|
| 01-27 |     604 |             |
| 01-28 |     314 |             |
| 01-29 |     636 |             |
| 01-30 |     305 |             |
| 01-31 |     760 |             |
| 02-01 |     531 |             |
| 02-02 |     331 |      497.29 |
| 02-03 |      77 |      422.00 |
| 02-04 |     621 |      465.86 |
| 02-05 |     406 |      433.00 |
| 02-06 |     621 |      478.14 |
| 02-07 |     975 |      508.86 |
| 02-08 |     252 |      469.00 |
| 02-09 |     794 |      535.14 |
| 02-10 |      36 |      529.29 |
#+TBLFM: $3 = '(let ((len 7)) (if (> @# len) (format "%.2f" (/ (apply
#'+ (subseq '(@I$2..@II$2) (- @# 1 len) (- @# 1))) (float len))) ""));
N

Michael

Re: empty/nil in table cells

[Nicolas Goaziou]

Hello,

Mario Frasca <mario@anche.no> writes:

> I think we really need a bug-tracking mechanism, you know?

Hmmmm...

I had read about the message you've sent again. However, even if we
don't have a bug-tracking mechanism, you can still group messages
related to the same issue in the same thread. It would be more helpful
than starting a new thread with a, IMO, rather cryptic message.

Unfortunately, your message above only answers some of my questions.
"Where?", "What do you mean?" and "How so?" still apply, AFAICT. IOW,
I suggest to be more explicit if you want to have comments about your
suggestion.

Regards,
-- 
Nicolas Goaziou

Re: empty/nil in table cells

[Mario Frasca]

[-- Attachment #1: Type: text/plain, Size: 1078 bytes --]

On 09/07/2020 16:59, Nicolas Goaziou wrote:
>> and it simplifies a lot writing functions.
> How so?

thank you Michael, for answering Nicolas' question, in a different and 
much structured way than I did in my original message.

the formula you provided —I reduced it slightly, hard coding a constant— 
looks like this:

(if (> @# 4) (format "%.2f" (/ (apply '+ (subseq '(@I$2..@II$2) (- @# 1 
4) (- @# 1))) 4.0)) ""));N

my impression is this programs in the spreadsheet something that should 
be part of the org-table software.

if we let references leading outside the table return 'nil instead of 
raising an error, and represent 'nil by the empty string, then this is 
enough:

(when (> @# 4) (/ (+ @-3$2..$2) 4.0));L%0.2f

my suggestion is to associate 'nil with empty cells "both ways", in the 
sense that a formula returning 'nil causes an empty org-table cell (see 
above), and that the empty cell, through the L filter evaluates to nil 
and not to the empty string.  this for symmetry, and for uniformity with 
reference leading outside the table.

regards,

MF


[-- Attachment #2: Type: text/html, Size: 1711 bytes --]