Re: [Orgmode] Re: Can't import a remote reference to a whole column in orgtbl

[Nick Dokos <nicholas.dokos@hp.com>]

[Aaaargh: premature communication - apologies to all and let me try again]

Michael Brand <michael.brand@alumni.ethz.ch> wrote:

> Carsten Dominik wrote:
> > this is neat, but still kind of hard to do, because you have to put
> > all these formulas there by hand.  I am skipping this for the manual
> > - maybe you'd like to put this into org-hacks, or into the FAQ on
> > Worg?
> 
> Ok, I have put it into Worg org-hacks.org:
> http://orgmode.org/worg/org-hacks.php
> in the section `Field coordinates in formulas', currently with this numbering
> http://orgmode.org/worg/org-hacks.php#sec-17.2
> 
> And only now I have seen and answered this thread:
> `feature request: transpose a table'
> started here
> http://thread.gmane.org/gmane.emacs.orgmode/17453
> and continued here
> http://thread.gmane.org/gmane.emacs.orgmode/23809
> 

Since this is  a reply to an old thread, let me set some context: there was a flurry
of activity about transposing a table about 1.5 years ago - in addition to the two
threads above, there was

	http://thread.gmane.org/gmane.emacs.orgmode/22610

	http://thread.gmane.org/gmane.emacs.orgmode/22930

The latter contains a patch by Michael Brand that  introduced "field coordinates"
that got incorporated into org. That allowed Michael to do a table transposition
that he also added to org-hacks (but the section number has changed - it is at

          http://orgmode.org/worg/org-hacks.html#sec-1-3-5

currently).

There were various other solutions too using lisp (by Tom Dye and Juan
Pechiar: iiuc, both of these were based on library-of-babel code), that might
be more efficient than Michael's solution (Carsten warns explicitly about
the inefficiency somewhere).

But Michael's solution is clever: the idea is to create an empty table of the
right dimensions, delete any separator lines manually and then apply a sequence
of (identical) formulas, one for each column in the destination table, that
populates the column from the corresponding row of the source table, then add
separator lines back manually. To simplify the discussion, here's an example
without separators:

#+TBLNAME: FOO
| year | 2004 | 2005 | 2006 | 2007 | 2008 | 2009 |
| min  |  401 |  501 |  601 |  701 |  801 |  901 |
| avg  |  402 |  502 |  602 |  702 |  802 |  902 |
| max  |  403 |  503 |  603 |  703 |  803 |  903 |

So create a 7x4 table: M-x org-table-create RET 4x7 RET [fn:1]
delete the separator, and apply the formulas: 

| year | min | avg | max |
| 2004 | 401 | 402 | 403 |
| 2005 | 501 | 502 | 503 |
| 2006 | 601 | 602 | 603 |
| 2007 | 701 | 702 | 703 |
| 2008 | 801 | 802 | 803 |
| 2009 | 901 | 902 | 903 |
#+TBLFM: $1 = remote(FOO, @$#$@#) :: $2 = remote(FOO, @$#$@#) :: $3 = remote(FOO, @$#$@#) :: $4 = remote(FOO, @$#$@#)

And voilà - transposition.

As Carsten notes however, this is kind of hard to do and at the time there was
no way to condense the multiple formulas into one; but ranges on the LHS have now
been added to org and can do just that:

| year | min | avg | max |
| 2004 | 401 | 402 | 403 |
| 2005 | 501 | 502 | 503 |
| 2006 | 601 | 602 | 603 |
| 2007 | 701 | 702 | 703 |
| 2008 | 801 | 802 | 803 |
| 2009 | 901 | 902 | 903 |
#+TBLFM: @1$1..@>$> = remote(FOO, @$#$@#)

Nick

Footnotes:

[fn:1] Note the order - not sure why org-table-create wants the dimensions in
the "opposite" order.