* org-table formulas with missing values
@ 2011-02-26 18:32 Thorsten
2011-02-27 0:01 ` Thorsten
0 siblings, 1 reply; 8+ messages in thread
From: Thorsten @ 2011-02-26 18:32 UTC (permalink / raw)
To: emacs-orgmode
Hello,
how do I best enter missing values in an org-tabel (just empty cells or NaN?)
and how do I use that missing values in formulas?
Thanks
Thorsten
^ permalink raw reply [flat|nested] 8+ messages in thread
* Re: org-table formulas with missing values
2011-02-26 18:32 org-table formulas with missing values Thorsten
@ 2011-02-27 0:01 ` Thorsten
2011-02-27 11:45 ` Bastien
0 siblings, 1 reply; 8+ messages in thread
From: Thorsten @ 2011-02-27 0:01 UTC (permalink / raw)
To: emacs-orgmode
Thorsten <gruenderteam.berlin@googlemail.com> writes:
> Hello,
> how do I best enter missing values in an org-tabel (just empty cells or NaN?)
> and how do I use that missing values in formulas?
Ok, trial and error suggests that missing values in numeric columns can
be represented as 0 in formulas, in string columns as "". Is there
something like NaN in calc/org-table?
^ permalink raw reply [flat|nested] 8+ messages in thread
* Re: Re: org-table formulas with missing values
2011-02-27 0:01 ` Thorsten
@ 2011-02-27 11:45 ` Bastien
2011-03-05 10:55 ` Thorsten
0 siblings, 1 reply; 8+ messages in thread
From: Bastien @ 2011-02-27 11:45 UTC (permalink / raw)
To: Thorsten; +Cc: emacs-orgmode
Hi Thorsten,
Thorsten <gruenderteam.berlin@googlemail.com> writes:
> Ok, trial and error suggests that missing values in numeric columns can
> be represented as 0 in formulas, in string columns as "". Is there
> something like NaN in calc/org-table?
I don't know what is NaN. Can you give an example of what you're trying
to achieve?
--
Bastien
^ permalink raw reply [flat|nested] 8+ messages in thread
* Re: org-table formulas with missing values
2011-02-27 11:45 ` Bastien
@ 2011-03-05 10:55 ` Thorsten
2011-03-05 11:01 ` Rainer M Krug
0 siblings, 1 reply; 8+ messages in thread
From: Thorsten @ 2011-03-05 10:55 UTC (permalink / raw)
To: emacs-orgmode
Bastien <bzg@altern.org> writes:
> Hi Thorsten,
>
> Thorsten <gruenderteam.berlin@googlemail.com> writes:
>
>> Ok, trial and error suggests that missing values in numeric columns can
>> be represented as 0 in formulas, in string columns as "". Is there
>> something like NaN in calc/org-table?
>
> I don't know what is NaN. Can you give an example of what you're trying
> to achieve?
NaN stands for NotaNumber and is usually used in statistic programs to
denote missing numerical values (that deserve special treatment, since
including them in the calculations as 0 values would not be wise due to
introduced bias or confusion with true measurements of a value=0).
Fot example:
| day | tel- calls | change (%) |
|-----+------------+---------------------|
| 1 | 7 | 0 |
| 2 | 2 | -71 |
| 3 | 3 | 50 |
| 4 | | 0 |
| 5 | 2 | round((2/0) 100, 0) |
| 6 | 0 | 0 |
| 7 | 3 | round((3/0) 100, 0) |
#+TBLFM: $3=if(@$-1=0,0,round((((@$-1-@-1$-1) / @-1$-1) * 100),
0)::@2$3=@2$2-@2$2
Here one measurement (day 4) was missed, one day had 0 tel-calls (day
6).
The change (%) is :
((calls day x) - (calls day x-1)) / (calls day x-1) * 100.
The column-formula treats measurment and zero value the same, although
it would be better to replace the missing value with the average (but
how to adress it in the formula if not with 0 ?), and it treats the zero
measurement like a missing value here. Even if there would be no zero
value here, the missing value would cause a 'divide by zero' error.
I could write 'nan' instead of leaving a blank, and use the string in
the formula, but this causes errors.
But calc does know the concept of nan: "The variables `inf', `uinf', and
`nan' stand for infinite or indeterminate values. It's best not to use
them as regular variables, since Calc uses special algebraic rules when
it manipulates them. Calc displays a warning message if you store a
value into any of these special variables."
I'm not sure how to use that nan variable in tables and formulas - it
seems not to be recognized.
Thorsten
^ permalink raw reply [flat|nested] 8+ messages in thread
* Re: Re: org-table formulas with missing values
2011-03-05 10:55 ` Thorsten
@ 2011-03-05 11:01 ` Rainer M Krug
2011-03-06 22:44 ` Thorsten
0 siblings, 1 reply; 8+ messages in thread
From: Rainer M Krug @ 2011-03-05 11:01 UTC (permalink / raw)
To: Thorsten; +Cc: emacs-orgmode
On Sat, Mar 5, 2011 at 11:55 AM, Thorsten
<gruenderteam.berlin@googlemail.com> wrote:
> Bastien <bzg@altern.org> writes:
>
>> Hi Thorsten,
>>
>> Thorsten <gruenderteam.berlin@googlemail.com> writes:
>>
>>> Ok, trial and error suggests that missing values in numeric columns can
>>> be represented as 0 in formulas, in string columns as "". Is there
>>> something like NaN in calc/org-table?
>>
>> I don't know what is NaN. Can you give an example of what you're trying
>> to achieve?
>
> NaN stands for NotaNumber and is usually used in statistic programs to
> denote missing numerical values
NaN ia, as you state, NotANumber, but it is NOT a missing value, for
which the abbreviation is NA (Not Vavailable). An example for NaN
would be 1/0 - the value is there, but it is not a number. And,
consequently, NaN and NA can be treated separately. In general, if you
enter values, you use NA, as you usually do not enter values which are
not a number...
> (that deserve special treatment, since
> including them in the calculations as 0 values would not be wise due to
> introduced bias or confusion with true measurements of a value=0).
True.
>
> Fot example:
>
> | day | tel- calls | change (%) |
> |-----+------------+---------------------|
> | 1 | 7 | 0 |
> | 2 | 2 | -71 |
> | 3 | 3 | 50 |
> | 4 | | 0 |
> | 5 | 2 | round((2/0) 100, 0) |
> | 6 | 0 | 0 |
> | 7 | 3 | round((3/0) 100, 0) |
>
> #+TBLFM: $3=if(@$-1=0,0,round((((@$-1-@-1$-1) / @-1$-1) * 100),
> 0)::@2$3=@2$2-@2$2
>
>
> Here one measurement (day 4) was missed, one day had 0 tel-calls (day
> 6).
> The change (%) is :
> ((calls day x) - (calls day x-1)) / (calls day x-1) * 100.
>
> The column-formula treats measurment and zero value the same, although
> it would be better to replace the missing value with the average (but
> how to adress it in the formula if not with 0 ?), and it treats the zero
> measurement like a missing value here. Even if there would be no zero
> value here, the missing value would cause a 'divide by zero' error.
>
> I could write 'nan' instead of leaving a blank, and use the string in
> the formula, but this causes errors.
>
> But calc does know the concept of nan: "The variables `inf', `uinf', and
> `nan' stand for infinite or indeterminate values. It's best not to use
> them as regular variables, since Calc uses special algebraic rules when
> it manipulates them. Calc displays a warning message if you store a
> value into any of these special variables."
>
> I'm not sure how to use that nan variable in tables and formulas - it
> seems not to be recognized.
>
> Thorsten
>
>
>
--
NEW GERMAN FAX NUMBER!!!
Rainer M. Krug, PhD (Conservation Ecology, SUN), MSc (Conservation
Biology, UCT), Dipl. Phys. (Germany)
Centre of Excellence for Invasion Biology
Natural Sciences Building
Office Suite 2039
Stellenbosch University
Main Campus, Merriman Avenue
Stellenbosch
South Africa
Cell: +27 - (0)83 9479 042
Fax: +27 - (0)86 516 2782
Fax: +49 - (0)321 2125 2244
email: Rainer@krugs.de
Skype: RMkrug
Google: R.M.Krug@gmail.com
^ permalink raw reply [flat|nested] 8+ messages in thread
* Re: Re: org-table formulas with missing values
2011-03-05 11:01 ` Rainer M Krug
@ 2011-03-06 22:44 ` Thorsten
2011-03-06 23:03 ` suvayu ali
0 siblings, 1 reply; 8+ messages in thread
From: Thorsten @ 2011-03-06 22:44 UTC (permalink / raw)
To: emacs-orgmode
Rainer M Krug <r.m.krug@gmail.com> writes:
> On Sat, Mar 5, 2011 at 11:55 AM, Thorsten
> <gruenderteam.berlin@googlemail.com> wrote:
>> Bastien <bzg@altern.org> writes:
>>
>>> Hi Thorsten,
>>>
>>> Thorsten <gruenderteam.berlin@googlemail.com> writes:
>>>
>>>> Ok, trial and error suggests that missing values in numeric columns can
>>>> be represented as 0 in formulas, in string columns as "". Is there
>>>> something like NaN in calc/org-table?
>>>
>>> I don't know what is NaN. Can you give an example of what you're trying
>>> to achieve?
>>
>> NaN stands for NotaNumber and is usually used in statistic programs to
>> denote missing numerical values
>
> NaN ia, as you state, NotANumber, but it is NOT a missing value, for
> which the abbreviation is NA (Not Vavailable). An example for NaN
> would be 1/0 - the value is there, but it is not a number. And,
> consequently, NaN and NA can be treated separately. In general, if you
> enter values, you use NA, as you usually do not enter values which are
> not a number...
It seems there is no special variable 'na' in calc, and anyway, 'nan' is
not recognized either, but calc-info agrees with you.
nil does the job - but somehow interferes with the else-part of the
formula, that is not calculated anymore:
| day | tel- calls | change (%) |
|-----+------------+-------------------------------------------------|
| 1 | 7 | 0 |
| 2 | 2 | 2 = nil ? nil : round(((2 - 7) / 7) 100, 0) |
| 3 | 3 | 3 = nil ? nil : round(((3 - 2) / 2) 100, 0) |
| 4 | nil | nil |
| 5 | 2 | 2 = nil ? nil : round(((2 - nil) / nil) 100, 0) |
| 6 | 0 | 0 = nil ? nil : round(((0 - 2) / 2) 100, 0) |
| 7 | 3 | 3 = nil ? nil : round(((3 - 0) / 0) 100, 0) |
#+TBLFM: $3=if(@$-1=nil,nil,round((((@$-1-@-1$-1) / @-1$-1) * 100), 0)::@2$3=@2$2-@2$2
But even if nil would work, my basic question remains: if I have for
example a dynamic report were one code-block in the org file retrieves
new data frequently and stores them in a table, which has some formula
applied to it, and 0 values as well as missing values are possible, how
can I address the empty cells in the formula without confusion with
regards to the cells with 0 values?
Thorsten
^ permalink raw reply [flat|nested] 8+ messages in thread
* Re: Re: org-table formulas with missing values
2011-03-06 22:44 ` Thorsten
@ 2011-03-06 23:03 ` suvayu ali
2011-03-08 19:18 ` Thorsten
0 siblings, 1 reply; 8+ messages in thread
From: suvayu ali @ 2011-03-06 23:03 UTC (permalink / raw)
To: Thorsten; +Cc: emacs-orgmode
Hi Thorsten,
Disclaimer: I haven't tried any of what I am proposing below. Its just
a thought that came to me while reading your post.
On Sun, Mar 6, 2011 at 2:44 PM, Thorsten
<gruenderteam.berlin@googlemail.com> wrote:
> But even if nil would work, my basic question remains: if I have for
> example a dynamic report were one code-block in the org file retrieves
> new data frequently and stores them in a table, which has some formula
> applied to it, and 0 values as well as missing values are possible, how
> can I address the empty cells in the formula without confusion with
> regards to the cells with 0 values?
>
Since recently calc code block support was added to babel, why not use
that to process your table instead of table formulae? Wouldn't you
expect it would be easier to deal with NaN and its likes directly with
calc source compared to formulae?
> Thorsten
>
--
Suvayu
Open source is the future. It sets us free.
^ permalink raw reply [flat|nested] 8+ messages in thread
* Re: Re: org-table formulas with missing values
2011-03-06 23:03 ` suvayu ali
@ 2011-03-08 19:18 ` Thorsten
0 siblings, 0 replies; 8+ messages in thread
From: Thorsten @ 2011-03-08 19:18 UTC (permalink / raw)
To: emacs-orgmode
suvayu ali <fatkasuvayu+linux@gmail.com> writes:
> Since recently calc code block support was added to babel, why not use
> that to process your table instead of table formulae? Wouldn't you
> expect it would be easier to deal with NaN and its likes directly with
> calc source compared to formulae?
I'm going to try that. I just did my first little trial using org-tables
as a spread-sheet and immediatly hit the problem of how to deal with
empty cells in column formulars. Therefore I thought that must be a very
common issue with some easy well-known solution.
____________________
Thorsten
^ permalink raw reply [flat|nested] 8+ messages in thread
end of thread, other threads:[~2011-03-08 19:19 UTC | newest]
Thread overview: 8+ messages (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
2011-02-26 18:32 org-table formulas with missing values Thorsten
2011-02-27 0:01 ` Thorsten
2011-02-27 11:45 ` Bastien
2011-03-05 10:55 ` Thorsten
2011-03-05 11:01 ` Rainer M Krug
2011-03-06 22:44 ` Thorsten
2011-03-06 23:03 ` suvayu ali
2011-03-08 19:18 ` Thorsten
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).