Insert calc vector directly into spreadsheet cells?

Insert calc vector directly into spreadsheet cells?

[Steven Adrian]

I would like to insert a calc vector directly into a range of spreadsheet
cells from a function. As a specific example, consider the calc index
function, which returns a calc vector of integers from 1 to n. Once calc has
started within an emacs session, the index function can be accessed from
within the scratch buffer as:

(calcFunc-index 10)

If the above function is evaluated, the echo area will display the calc
vector returned by the function:

(vec 1 2 3 4 5 6 7 8 9 10)

I would like to put these values into a range of cells with a table formula
like:

#+TBLFM: @1$1..@1$10=index(10)

But the formula above just puts the whole vector in each cell. Can anyone
tell me how to put the vector values in individual cells?

Re: Insert calc vector directly into spreadsheet cells?

[Bastien]

Hi Steven,

Steven Adrian <sadrian@acumeniacal.com> writes:

> #+TBLFM: @1$1..@1$10=index(10)

Table formulas apply to individual fields, not to a range of fields.

> But the formula above just puts the whole vector in each cell. Can anyone
> tell me how to put the vector values in individual cells?

AFAIU you will need several TBLFM lines for this.

HTH,

-- 
 Bastien

Re: Insert calc vector directly into spreadsheet cells?

[Michael Brand]

Hi Steven

> Steven Adrian <sadrian@acumeniacal.com> writes:
>> #+TBLFM: @1$1..@1$10=index(10)
>>
>> But the formula above just puts the whole vector in each cell. Can anyone
>> tell me how to put the vector values in individual cells?

The vector elements can be accessed with Calc subscr() and Org "field
coordinates":
http://orgmode.org/manual/References.html

| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
#+TBLFM: @1$1..@1$10 = subscr(index(10), $#)

In a TBLFM I would not use Calc vector functions like index() but a
calculation of $#, here simply f(x) = x:

| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
#+TBLFM: @1$1..@1$10 = $#

Or is there an interesting Calc vector function that is not easy to
mimic?

Michael

Re: Insert calc vector directly into spreadsheet cells?

[Steven Adrian]

Michael,

This is great. I wish I had seen this earlier. I did not know about the subscr function, but I had basically gotten to the same point by writing an equivalent custom calc function.

I understand that using a  calc function in a table formula is inefficient, since it is executed for every table cell.  But for small tables, I think the combination of table formulas and calc functions is extremely powerful. Here are two examples of mine:

1. I wrote a custom calc function called tailsInARow that uses the calc random function to simulate a coin toss and return the number of tails in a row. I used this function to fill a 20x20 table of values. I then used the calc histogram function in a table formula to read the table values, histogram the number of tails in a row, then write the histogram to a new table.

2. I calculated a rotation matrix as a succession of individual rotations about x, y, and z axes. I then wrote the resulting matrix to a table. I then wrote a table function to read the matrix, take the inverse, and write the result to a new table.

I think calc is well suited for small problems like this for which I want to have a record of what I did later. But I am always looking for better approaches, if you have any to suggest.

Steven

On May 21, 2014, at 3:29 AM, Michael Brand <michael.ch.brand@gmail.com> wrote:

> Hi Steven
> 
>> Steven Adrian <sadrian@acumeniacal.com> writes:
>>> #+TBLFM: @1$1..@1$10=index(10)
>>> 
>>> But the formula above just puts the whole vector in each cell. Can anyone
>>> tell me how to put the vector values in individual cells?
> 
> The vector elements can be accessed with Calc subscr() and Org "field
> coordinates":
> http://orgmode.org/manual/References.html
> 
> | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
> #+TBLFM: @1$1..@1$10 = subscr(index(10), $#)
> 
> In a TBLFM I would not use Calc vector functions like index() but a
> calculation of $#, here simply f(x) = x:
> 
> | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
> #+TBLFM: @1$1..@1$10 = $#
> 
> Or is there an interesting Calc vector function that is not easy to
> mimic?
> 
> Michael
> 

Re: Insert calc vector directly into spreadsheet cells?

[Michael Brand]

Hi Steven

On Mon, May 26, 2014 at 9:39 PM, Steven Adrian <sadrian@acumeniacal.com> wrote:
> This is great. I wish I had seen this earlier. I did not know about
> the subscr function, but I had basically gotten to the same point by
> writing an equivalent custom calc function.

With the Calc function defmath? How does it look like?

> 1. I wrote a custom calc function called tailsInARow that uses the
> calc random function to simulate a coin toss and return the number
> of tails in a row. I used this function to fill a 20x20 table of
> values. I then used the calc histogram function in a table formula
> to read the table values, histogram the number of tails in a row,
> then write the histogram to a new table.

Didn't know that Calc has a histogram function. The histogram values
would fit perfectly to the ASCII plot within an Org table:
http://orgmode.org/worg/org-contrib/orgtbl-ascii-plot.html

> 2. I calculated a rotation matrix as a succession of individual
> rotations about x, y, and z axes. I then wrote the resulting matrix
> to a table. I then wrote a table function to read the matrix, take
> the inverse, and write the result to a new table.

Interesting. How does the "function to read the matrix" look like?

Michael

Re: Insert calc vector directly into spreadsheet cells?

[Steven Adrian]

Michael,

Here is the custom calc function that is similar to subscr. It will work for both vectors and matrices:

(defmath getElem (indices m)
  (let (i j)
    (if (vectorp indices)
      (progn
        (setq i (cadr indices))
        (setq j (nth 2 indices))
        (nth j (nth i m))
      )
      (progn
        (setq i indices)
        (nth i m)
      )
    )
  )
)

Here is the table function to get matrix data from a table, take the inverse, and write the result to a new table:

#+TBLFM: @1$1..@3$3=getElem([@#,$#],inv(arrange(remote(matrixTable,@1$2..@3$4),3))

Thanks for the tip about orgtbl-ascii-plot. I have been working on a set of notes about using calc and org mode that I am migrating over to read more like a tutorial, and I am now thinking about changing the histogram example to use orgtbl-ascii-plot instead of just writing the bin values in a table.

Steven


On May 26, 2014, at 4:43 PM, Michael Brand <michael.ch.brand@gmail.com> wrote:

> Hi Steven
> 
> On Mon, May 26, 2014 at 9:39 PM, Steven Adrian <sadrian@acumeniacal.com> wrote:
>> This is great. I wish I had seen this earlier. I did not know about
>> the subscr function, but I had basically gotten to the same point by
>> writing an equivalent custom calc function.
> 
> With the Calc function defmath? How does it look like?
> 
>> 1. I wrote a custom calc function called tailsInARow that uses the
>> calc random function to simulate a coin toss and return the number
>> of tails in a row. I used this function to fill a 20x20 table of
>> values. I then used the calc histogram function in a table formula
>> to read the table values, histogram the number of tails in a row,
>> then write the histogram to a new table.
> 
> Didn't know that Calc has a histogram function. The histogram values
> would fit perfectly to the ASCII plot within an Org table:
> http://orgmode.org/worg/org-contrib/orgtbl-ascii-plot.html
> 
>> 2. I calculated a rotation matrix as a succession of individual
>> rotations about x, y, and z axes. I then wrote the resulting matrix
>> to a table. I then wrote a table function to read the matrix, take
>> the inverse, and write the result to a new table.
> 
> Interesting. How does the "function to read the matrix" look like?
> 
> Michael
> 

Re: Insert calc vector directly into spreadsheet cells?

[Michael Brand]

Hi Steven

On Tue, May 27, 2014 at 11:12 AM, Steven Adrian <sadrian@acumeniacal.com> wrote:
> Here is the custom calc function that is similar to subscr. It will
> work for both vectors and matrices:
>
> (defmath getElem (indices m)
>   (let (i j)
>     (if (vectorp indices)
>       (progn
>         (setq i (cadr indices))
>         (setq j (nth 2 indices))
>         (nth j (nth i m))
>       )
>       (progn
>         (setq i indices)
>         (nth i m)
>       )
>     )
>   )
> )
>
> Here is the table function to get matrix data from a table, take the
> inverse, and write the result to a new table:
>
> #+TBLFM: @1$1..@3$3=getElem([@#,$#],inv(arrange(remote(matrixTable,@1$2..@3$4),3))

Very nice. I didn't know the Calc function arrange([a, b, c, d], 2) to
get [[a, b], [c, d]]. I always thought that when a TBLFM range spans
more than one row and column like in @1$2..@2$3 it should learn to
result in [[a, b], [c, d]] instead of the current [a, b, c, d]. Thanks
for showing how to deal with this.

Michael