* Preserving the geometry of a table range
@ 2012-08-26 6:54 Jarmo Hurri
2012-08-26 7:10 ` Michael Brand
2012-08-27 12:33 ` Michael Brand
0 siblings, 2 replies; 6+ messages in thread
From: Jarmo Hurri @ 2012-08-26 6:54 UTC (permalink / raw)
To: emacs-orgmode
Greetings.
If you pass a table range to an elisp function in a tblfm - say, using
remote - the values in the range seem to be passed to the function as a
single list. Thus the geometry of the region is lost. Is it possible to
return a list of lists, e.g., a list of rows instead? Is this supported
by some notation in a tblfm formula? If not, could we have something
like this?
I ran into this problem while trying to make a contribution by
implementing functions corresponding to vlookup and hlookup in
traditional spreadsheets. I have only used relatively basic properties
of org, so there might be a simple solution that I just don't know.
--
Jarmo
^ permalink raw reply [flat|nested] 6+ messages in thread
* Re: Preserving the geometry of a table range
2012-08-26 6:54 Preserving the geometry of a table range Jarmo Hurri
@ 2012-08-26 7:10 ` Michael Brand
2012-08-26 8:45 ` Jarmo Hurri
2012-08-27 12:33 ` Michael Brand
1 sibling, 1 reply; 6+ messages in thread
From: Michael Brand @ 2012-08-26 7:10 UTC (permalink / raw)
To: Jarmo Hurri; +Cc: emacs-orgmode
Hi Jarmo
could you make a small example with the table content before and after
the yet to be defined formula has been applied, maybe with a
pseudo-formula?
Michael
^ permalink raw reply [flat|nested] 6+ messages in thread
* Re: Preserving the geometry of a table range
2012-08-26 7:10 ` Michael Brand
@ 2012-08-26 8:45 ` Jarmo Hurri
2012-08-26 18:58 ` Michael Brand
0 siblings, 1 reply; 6+ messages in thread
From: Jarmo Hurri @ 2012-08-26 8:45 UTC (permalink / raw)
To: emacs-orgmode
Greetings Michael.
I can try. Here is an example of the desired low-level functionality
with a simple function. The lookup functions would be built on this.
Does this help?
* an example of low level functionality
Here is an example of an elisp function working on a range as a list
of rows. This one first finds the product of the elements on each
row, and then sums up these products.
#+BEGIN_SRC emacs-lisp
(defun sum-row-prods (rows)
(apply '+ (mapcar (lambda (row) (apply '* row)) rows)))
#+END_SRC
#+RESULTS:
: sum-row-prods
Then here we have a table in which the element at @4$1 is given by
applying the function sum-row-prods to a specific list of
lists. This works already in org mode, since in the formula for @4$1
the list of lists is given as a constant. The formula for @4$2
illustrates the desired functionality, that is, being able to apply
the function sum-row-prods to range @2$1..@3$2. The result should be
#+BEGIN_SRC emacs-lisp
(sum-row-prods '((2 3) (4 5)))
#+END_SRC
#+RESULTS:
: 26
but now it naturally gives an error, since the argument to
sum-row-prods is a list, not a list of lists (rows).
| a | b |
|----+--------|
| 2 | 3 |
| 4 | 5 |
| 10 | #ERROR |
#+tblfm: @4$1='(sum-row-prods '((-4 1) (-2 -7)));N::@4$2='(sum-row-prods '(@2$1..@3$2));N
I don't know what the new syntax for referring to a range as a list
of rows should look like.
--
Jarmo
^ permalink raw reply [flat|nested] 6+ messages in thread
* Re: Preserving the geometry of a table range
2012-08-26 8:45 ` Jarmo Hurri
@ 2012-08-26 18:58 ` Michael Brand
0 siblings, 0 replies; 6+ messages in thread
From: Michael Brand @ 2012-08-26 18:58 UTC (permalink / raw)
To: Jarmo Hurri; +Cc: emacs-orgmode
Hi Jarmo
On Sun, Aug 26, 2012 at 10:45 AM, Jarmo Hurri <jarmo.hurri@syk.fi> wrote:
> Does this help?
Yes.
> but now it naturally gives an error, since the argument to
> sum-row-prods is a list, not a list of lists (rows).
AFAIK there is no way to get the list of lists.
But if you didn’t already you may consider to separate the calculation
of rows and columns into two steps which I think should be possible in
most cases and use something like this:
| a | b | prod |
|---+---+------|
| 2 | 3 | 6 |
| 4 | 5 | 20 |
| | | 26 |
#+TBLFM: $3 = '(* $1..$2); N :: @>$3 = '(+ @2..@3); N
or with Calc formulas:
#+TBLFM: $3 = vprod($1..$2) :: @>$3 = vsum(@2..@3)
Michael
^ permalink raw reply [flat|nested] 6+ messages in thread
* Re: Preserving the geometry of a table range
2012-08-26 6:54 Preserving the geometry of a table range Jarmo Hurri
2012-08-26 7:10 ` Michael Brand
@ 2012-08-27 12:33 ` Michael Brand
2012-09-02 14:53 ` Small contrib: lookup (was "Preserving the geometry of a table range") Jarmo Hurri
1 sibling, 1 reply; 6+ messages in thread
From: Michael Brand @ 2012-08-27 12:33 UTC (permalink / raw)
To: Jarmo Hurri; +Cc: emacs-orgmode
Hi Jarmo
On Sun, Aug 26, 2012 at 8:54 AM, Jarmo Hurri <jarmo.hurri@syk.fi> wrote:
> I ran into this problem while trying to make a contribution by
> implementing functions corresponding to vlookup and hlookup in
> traditional spreadsheets. I have only used relatively basic properties
> of org, so there might be a simple solution that I just don't know.
Only now I see that this second part with the actual problem of your
original post is still unanswered. Does this help?:
| key (ordered) | val | | val (ordered) | key |
|---------------+-----+---+---------------+-----|
| 8 | foo | | bar | 42 |
| 42 | bar | | foo | 8 |
#+TBLFM: $5 = '(nth (position $4 '(@<<$2..@>$2) :test 'equal) '(@<<$1..@>$1))
Michael
^ permalink raw reply [flat|nested] 6+ messages in thread
* Small contrib: lookup (was "Preserving the geometry of a table range")
2012-08-27 12:33 ` Michael Brand
@ 2012-09-02 14:53 ` Jarmo Hurri
0 siblings, 0 replies; 6+ messages in thread
From: Jarmo Hurri @ 2012-09-02 14:53 UTC (permalink / raw)
To: emacs-orgmode
Michael Brand <michael.ch.brand@gmail.com> writes:
> Does this help?
Yes indeed! It gave me both a better idea of what the call should look
like and the way to implement it. The only problem with this approach -
using a separate search and return list - when compared with the lookup
functions of traditional spreadsheets, is that you need to specify the
range twice. But then again in this form the range can be even from
different tables (although that is not very likely to happen).
How do you guys find the definitions and the examples at the end of this
message? I decided to write a macro and use it to define two functions
rather than having one function with an optional argument. Made me learn
how to use elisp macros, and also makes it very explicit for the user
whether he/she is searching for the first or the last match.
The last example demonstrates that you can do lookups in general range
geometries of type (n x m).
Could we have a form of remote which takes just one argument and then
refers to the whole table? (See the last example.)
Can we also have multidimensional tables? :-)
* macro for defining two lookup functions org-lookup-first and org-lookup-last
#+BEGIN_SRC emacs-lisp
(defmacro org-lookup-function (name-str from-end-p)
`(defun ,(intern (format "org-lookup-%s" name-str)) (val search-list return-list &optional predicate)
(let ((p (if (eq predicate nil) 'equal predicate)))
(nth (position val search-list :test p :from-end ,from-end-p) return-list))))
(org-lookup-function "first" nil)
(org-lookup-function "last" t)
#+END_SRC
#+RESULTS:
: org-lookup-last
* example 1: exact matches with default predicate equal
data
#+TBLNAME: growth-rates
| year | percentage |
|------+------------|
| 2009 | 12.2 |
| 2010 | 14.3 |
| 2011 | 14.3 |
| 2012 | 19.4 |
lookups
| percentage | year |
|------------+------|
| 14.3 | 2010 |
| 14.3 | 2011 |
#+TBLFM: @3$1=@2$1::@2$2='(org-lookup-first @2$1 '(remote(growth-rates,@2$2..@>$2)) '(remote(growth-rates,@2$1..@>$1)));N::@3$2='(org-lookup-last @3$1 '(remote(growth-rates,@2$2..@>$2)) '(remote(growth-rates,@2$1..@>$1)));N
* example 2: grades with optional predicate
data
#+TBLNAME: grade-boundaries
| lower bound | grade |
|-------------+-------|
| 0 | 4 |
| 2 | 4.25 |
| 4 | 4.5 |
| 6 | 4.75 |
lookups
| student | marks | grade |
|---------+-------+-------|
| A | 3 | 4.25 |
| B | 4 | 4.5 |
#+TBLFM: $3='(org-lookup-last $2 '(remote(grade-boundaries,@2$1..@>$1)) '(remote(grade-boundaries,@2$2..@>$2)) '>=);N
* example 3: searching a true two-dimensional range
data table 1
#+TBLNAME: scores
| 3 | 13 |
| 4 | 11 |
data table 2
#+TBLNAME: groups
| X | Y |
| Z | W |
lookup
| score | group |
|-------+-------|
| 13 | Y |
| 4 | Z |
#+TBLFM: $2='(org-lookup-first $1 '(remote(scores,@<$<..@>$>)) '(remote(groups,@<$<..@>$>)));L
^ permalink raw reply [flat|nested] 6+ messages in thread
end of thread, other threads:[~2012-09-02 14:53 UTC | newest]
Thread overview: 6+ messages (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
2012-08-26 6:54 Preserving the geometry of a table range Jarmo Hurri
2012-08-26 7:10 ` Michael Brand
2012-08-26 8:45 ` Jarmo Hurri
2012-08-26 18:58 ` Michael Brand
2012-08-27 12:33 ` Michael Brand
2012-09-02 14:53 ` Small contrib: lookup (was "Preserving the geometry of a table range") Jarmo Hurri
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).