Field coordinates and moving averages

Field coordinates and moving averages

[Benjamin Beckwith]

I'm trying to create a table of periodic data and calculate a moving
average in an adjacent column.  I was trying to use field coordinates,
but they do not work how I would like them to work.

Here is what I have for a 5-day moving average:

| Daily Data | Moving Average |
|------------+----------------|
|         10 |                |
|         11 |                |
|         12 |                |
|         13 |                |
|         14 |                |
|         14 |                |
|         16 |                |
#+TBLFM: $2=if(@# >=7, vmean(@-5$1..@0$1),string(""));

I expeced the if(@# >=7..) to protect the sub-clauses from executing
unless the predicate is true.  However, that is not the case.  When I
try to execute this formula, I receive the error: "Row descriptor -5
used in line ### leads outside table".

I can see how this is true if the ranges are checked for every entry.
Is there a better way for me to write this column forumla?  Is this a
limitation in the field coordinates?

Thanks,
Ben

Re: Field coordinates and moving averages

[Nick Dokos]

Benjamin Beckwith <bnbeckwith@gmail.com> wrote:

> 
> I'm trying to create a table of periodic data and calculate a moving
> average in an adjacent column.  I was trying to use field coordinates,
> but they do not work how I would like them to work.
> 
> Here is what I have for a 5-day moving average:
> 
> | Daily Data | Moving Average |
> |------------+----------------|
> |         10 |                |
> |         11 |                |
> |         12 |                |
> |         13 |                |
> |         14 |                |
> |         14 |                |
> |         16 |                |
> #+TBLFM: $2=if(@# >=7, vmean(@-5$1..@0$1),string(""));
> 
> I expeced the if(@# >=7..) to protect the sub-clauses from executing
> unless the predicate is true.  However, that is not the case.  When I
> try to execute this formula, I receive the error: "Row descriptor -5
> used in line ### leads outside table".
> 
> I can see how this is true if the ranges are checked for every entry.
> Is there a better way for me to write this column forumla?  Is this a
> limitation in the field coordinates?
> 

Try a range on the LHS:

#+TBLFM: @7$2..@>$2=vmean(@-5$1..@0$1)

FWIW, I avoid calc conditionals because I don't really understand how
they work. E.g. you are assuming that it evals the condition and iff
true, it proceeds to eval one or the other of the clauses, but the lisp
error shows that that's not how it works. I prefer to deal with such
problems by writing a lisp function instead.

Nick

Re: Field coordinates and moving averages

[Michael Brand]

Hi Ben

On Tue, Nov 15, 2011 at 16:21, Benjamin Beckwith <bnbeckwith@gmail.com> wrote:
> #+TBLFM: $2=if(@# >=7, vmean(@-5$1..@0$1),string(""));

The above does not work because the range expression "@-5$1..@0$1" is
an Org construct and has to be evaluated for every affected row
already in Org, before it is given to Calc. It has therefore no other
choice than failing together with "$2 =" (out of range), the
expression "@# >= 7" is evaluated only in Calc later. This is one of
the reasons for which I added "field coordinates in formulas" (@# and
$#) in 2010-03.

There are at least two solutions with a Calc formula for this use
case:


1) range formula

The clean solution, already given by Nick:

On Tue, Nov 15, 2011 at 16:52, Nick Dokos <nicholas.dokos@hp.com> wrote:
> Try a range on the LHS:
> #+TBLFM: @7$2..@>$2=vmean(@-5$1..@0$1)


2) Calc subvec with field coordinates in formulas

This was the only solution before Carsten introduced range formulas in
2011-03 and might be interesting for understanding purposes:

| Daily Data | Moving Average |
|------------+----------------|
|         10 |                |
|         11 |                |
|         12 |                |
|         13 |                |
|         14 |                |
|         14 |         12.333 |
|         16 |         13.333 |
#+TBLFM: $2 = if(@# >= 7, vmean(subvec(@I$1..@>$1, @# - 6, @#)) +.0,
string("")); f-3

See also "Dynamic variation of ranges" in "field coordinates in
formulas (@# and $#)" here:
http://orgmode.org/worg/org-hacks.html
reachable about one or two pages down with the current section
numbering here:
http://orgmode.org/worg/org-hacks.html#sec-1-3-5
For such a two-dimensional moving range, Calc subsrc/subvec is still
the simpler solution to get the triangle than several range formulas
would be.

If you change the input field 16 to 20 you see that "+.0" is required
in the formula to get 14.000 instead of 14 for the result. See "Which
float format shows the fraction part also when the latter is zero?"
here:
http://orgmode.org/worg/org-faq.html#table-float-fraction

Michael