sum up variables from different org-mode tables

sum up variables from different org-mode tables

[Martin Gross]

Dear helpers,

I have a big file where I enter incomings and outgoings of book
exchanges with different institutions:

* Institution A
  |   | In      |  # |   € | Out     |  # |   € |
  |---+---------+----+-----+---------+----+-----|
  |   | Title P |  1 |  45 | Title A |  1 |  15 |
  |   |         |    |     | Title B |  2 |  28 |
  |---+---------+----+-----+---------+----+-----|
  | # |         |  1 |  45 |         |  3 |  43 |
  | ^ |         | TE | TEE |         | TA | TAE |
  #+TBLFM: $TE=vsum(@I..@II)::$TEE=vsum(@I..@II)::$TA=vsum(@I..@II)::$TAE=vsum(@I..@II)

* Institution B
  |   | In | #  |  €   | Out | #   | €   |
  ...
  | # |    |  1 |  24  |     |  2  | 31  |
  | ^ |    | TE | TEE  |     | TA  | TAE |

Now I would like to get some statistics that consider all institutions
together.  For example summing up the "variable" TAE from all the
tables in the file to get the very total cost of outgoings.

Is something like that possible?

Thank you very much in advance!

Martin

Re: sum up variables from different org-mode tables

[Michael Brand]

Hi Martin

On Fri, Nov 16, 2012 at 12:33 PM, Martin Gross <m-gross@gmx.net> wrote:
> Now I would like to get some statistics that consider all institutions
> together.  For example summing up the "variable" TAE from all the
> tables in the file to get the very total cost of outgoings.

Below are two approaches to achieve this. For future development I
would like to point out two things:

1) Assignment of empty field: I think that, generally and not only for
   your example, some existing or future format specifier should take
   care to not fill the destination cell with 0 when the source cell
   is empty. The format specifiers E and L how they work now do not
   help in this case. Sometimes I use a workaround like
   $3 = if($1 && $2, $1 + $2, string(""))
   which works only for non-zero numbers.

2) Indirection of remote table name: Although there is a very nice
   formula editor C-c ' (org-table-edit-formulas) which helps a lot in
   this case, in both variants editing of the total formula scales
   badly with the number of tables. A nice solution for variant 2
   would be if
   @2$3..@2$7 = remote(A, @>>$$#) :: @3$3..@3$7 = remote(B, @>>$$#)
   could be simplified to
   @I$3..@II$7 = remote($8, @>>$$#)

Your example with the two approaches:

* Institution A
  #+TBLNAME: A
  |   | In      |  # |   € | Out     |  # |   € |
  |---+---------+----+-----+---------+----+-----|
  |   | Title P |  1 |  45 | Title A |  1 |  15 |
  |   |         |    |     | Title B |  2 |  28 |
  |---+---------+----+-----+---------+----+-----|
  | # |         |  1 |  45 |         |  3 |  43 |
  | ^ |         | TE | TEE |         | TA | TAE |
  #+TBLFM: $TE=vsum(@I..@II)::$TEE=vsum(@I..@II)::$TA=vsum(@I..@II)::$TAE=vsum(@I..@II)

* Institution B
  #+TBLNAME: B
  |   | In      |  # |   € | Out     |  # |   € |
  |---+---------+----+-----+---------+----+-----|
  |   | Title Q |  1 |  24 | Title C |  2 |  31 |
  |---+---------+----+-----+---------+----+-----|
  | # |         |  1 |  24 |         |  2 |  31 |
  | ^ |         | TE | TEE |         | TA | TAE |
  #+TBLFM: $TE=vsum(@I..@II)::$TEE=vsum(@I..@II)::$TA=vsum(@I..@II)::$TAE=vsum(@I..@II)

* total variant 1
  |   | In | #  | €   | Out | #  | €   |
  |---+----+----+-----+-----+----+-----|
  | # |    | 2  | 69  |     | 5  | 74  |
  | ^ |    | TE | TEE |     | TA | TAE |
  #+TBLFM: $TE = remote(A, $TE) + remote(B, $TE) :: $TEE = remote(A,
$TEE) + remote(B, $TEE) :: $TA = remote(A, $TA) + remote(B, $TA) ::
$TAE = remote(A, $TAE) + remote(B, $TAE)

* total variant 2
  |   | In |  # |   € | Out |  # |   € | institution |
  |---+----+----+-----+-----+----+-----+-------------|
  |   |    |  1 |  45 |   0 |  3 |  43 | A           |
  |   |    |  1 |  24 |   0 |  2 |  31 | B           |
  |---+----+----+-----+-----+----+-----+-------------|
  | # |    |  2 |  69 |     |  5 |  74 |             |
  | ^ |    | TE | TEE |     | TA | TAE |             |
  #+TBLFM: @2$3..@2$7 = remote(A, @>>$$#) :: @3$3..@3$7 = remote(B,
@>>$$#) :: $TE=vsum(@I..@II) :: $TEE=vsum(@I..@II) ::
$TA=vsum(@I..@II) :: $TAE=vsum(@I..@II)

“$$#”: the first “$” is for “column” and “$#” is for the number of the
current column to copy a row column by column, just like the
equivalent “@@#” mentioned in “3.5.1 References" in the manual.

Michael

Re: sum up variables from different org-mode tables

[Martin Gross]

Dear Michael,

thank you very much, this is what I was looking for.  I myself
supposed the answer should be related to "remote references", but was
not sure how to use them.

Just one point is not yet optimum to me: I do not have just 2 tables,
but hundreds.  It would be hard and not very clean to add them all one
by one.  Do you know if it there is a better way to reference remote
tables?  For example telling org-mode to search data from all tables
but "this" one?

Best regards,

Martin

Re: sum up variables from different org-mode tables

[Michael Brand]

Hi Martin

On Mon, Nov 19, 2012 at 12:54 PM, Martin Gross <m-gross@gmx.net> wrote:
> Just one point is not yet optimum to me: I do not have just 2 tables,
> but hundreds.  It would be hard and not very clean to add them all one
> by one.  Do you know if it there is a better way to reference remote
> tables?  For example telling org-mode to search data from all tables
> but "this" one?

I don’t know a way to tell Org “all other tables in this file”.

And this:

On Fri, Nov 16, 2012 at 4:16 PM, Michael Brand
<michael.ch.brand@gmail.com> wrote:
>    A nice solution for variant 2 would be if
>    @2$3..@2$7 = remote(A, @>>$$#) :: @3$3..@3$7 = remote(B, @>>$$#)
>    could be simplified to
>    @I$3..@II$7 = remote($8, @>>$$#)

is only a partial solution for you because - although moved from the
formula to the result table - the table enumeration is still
necessary.

But if it’s just summing up disjoint sets hierarchically you might
consider column view with “7.5.1.2 Column attributes” from the manual
to show the sums for each heading, like:

#+COLUMNS: %20ITEM %6In_num{+;%6d} %6In_EUR{+;%6.2f} %6Out_num{+;%6d}
%6Out_EUR{+;%6.2f}
* total
** Institution A
*** In
**** Title P
     :PROPERTIES:
     :In_num:   1
     :In_EUR:   45
     :END:
*** Out
**** Title A
     :PROPERTIES:
     :Out_num:   1
     :Out_EUR:   15
     :END:
**** Title B
     :PROPERTIES:
     :Out_num:   2
     :Out_EUR:   28
     :END:
** Institution B
*** In
**** Title Q
     :PROPERTIES:
     :In_num:   1
     :In_EUR:   24
     :END:
*** Out
**** Title C
     :PROPERTIES:
     :Out_num:   2
     :Out_EUR:   31
     :END:

Unfortunately formatting takes place only for calculations and not for
the raw source values which for me is at least a missing feature.

Michael

Re: sum up variables from different org-mode tables

[Martin Gross]

Dear Michael

> if it’s just summing up disjoint sets hierarchically you might ...

Thank you very much again for your efforts.  Unfortunately this would
not solve my problem neither.  On the one hand my tables are on
different entry levels (*, **, ***, etc.) and on the other I am afraid
I need a more automatic actualization of the mathematic operations.

Regards,

Martin

Re: sum up variables from different org-mode tables

[Suvayu Ali]

On Wed, Nov 21, 2012 at 06:01:20PM +0100, Martin Gross wrote:
> Dear Michael
> 
> > if it’s just summing up disjoint sets hierarchically you might ...
> 
> Thank you very much again for your efforts.  Unfortunately this would
> not solve my problem neither.  On the one hand my tables are on
> different entry levels (*, **, ***, etc.) and on the other I am afraid
> I need a more automatic actualization of the mathematic operations.
> 

If I may suggest, from your description of the scale I think you need a
proper programing language with real data structures.  Tables are after
all a convenience (they are human readable).  I would suggest you try
Python or R.  You could input your data as org tables, process them with
source blocks, and finally output the accumulated results as tables
again.

Babel should be able to do something like this.  If you do work out a
solution, it would be great if you share it on the list (maybe a Worg
article?).

GL

-- 
Suvayu

Open source is the future. It sets us free.

Re: sum up variables from different org-mode tables

[Eric Schulte]

[-- Attachment #1: Type: text/plain, Size: 626 bytes --]

Martin Gross <m-gross@gmx.net> writes:

> Dear Michael,
>
> thank you very much, this is what I was looking for.  I myself
> supposed the answer should be related to "remote references", but was
> not sure how to use them.
>
> Just one point is not yet optimum to me: I do not have just 2 tables,
> but hundreds.  It would be hard and not very clean to add them all one
> by one.  Do you know if it there is a better way to reference remote
> tables?

Not a pure Org-mode solution, but something like the attached could be
used to sum up a particular column from every table in a file.  Not
flexible but possibly sufficient.


[-- Warning: decoded text below may be mangled, UTF-8 assumed --]
[-- Attachment #2: multiple-tables.org --]
[-- Type: text/x-org, Size: 343 bytes --]

#+Title: Example 

* one table
| 1 |  2 |
| 2 |  4 |
| 3 |  6 |
| 4 |  8 |
| 5 | 10 |

* another table
| 20 | 20 |

* and one more
| 0 | 1 |
| 0 | 1 |

* here we sum them up
#+begin_src sh :results scalar :var file=(buffer-file-name) :var col=1
  cat $file|grep "^|"|sed 's/|//g'|awk '{ sum += $1} END{ print sum }'
#+end_src

#+RESULTS:
: 35

[-- Attachment #3: Type: text/plain, Size: 298 bytes --]


> For example telling org-mode to search data from all tables but "this"
> one?
>

With a little more work and one more sed command this could stop
processing the file at a particular line or keyword.

Hope this helps,

>
> Best regards,
>
> Martin
>

-- 
Eric Schulte
http://cs.unm.edu/~eschulte

Re: sum up variables from different org-mode tables

[Martin Gross]

Dear Eric,

I was sure something like that could be done.  Thank for the example.
Being a layman (not a programmer) I will need a time study it.

Sincerely,

Martin

Re: sum up variables from different org-mode tables

[Michael Brand]

[-- Attachment #1: Type: text/plain, Size: 1062 bytes --]

Hi all

On Fri, Nov 16, 2012 at 4:16 PM, Michael Brand
<michael.ch.brand@gmail.com> wrote:
> [...]
>    A nice solution for variant 2 would be if
>    @2$3..@2$7 = remote(A, @>>$$#) :: @3$3..@3$7 = remote(B, @>>$$#)
>    could be simplified to
>    @2$3..@3$7 = remote($8, @>>$$#)
> [...]

Because I need the above indirection of remote references I
implemented it a few days ago, see the attached patches. Comments are
welcome, otherwise I will push the changes to the Org repo in a few
days.

This is the use case that is checked in the Emacs Regression Test
test-org-table/remote-reference-indirect in
testing/lisp/test-org-table.el:

#+NAME: 2012
| amount |
|--------|
|      1 |
|      2 |
|--------|
|      3 |
#+TBLFM: @>$1 = vsum(@I..@II)

#+NAME: 2013
| amount |
|--------|
|      4 |
|      8 |
|--------|
|     12 |
#+TBLFM: @>$1 = vsum(@I..@II)

#+NAME: summary
|  year | amount |
|-------+--------|
|  2012 |      3 |
|  2013 |     12 |
|-------+--------|
| total |     15 |
#+TBLFM: @<<$2..@>>$2 = remote($<, @>$1) :: @>$2 = vsum(@I..@II)

Michael

[-- Attachment #2: 0001-TBLFM-remote-ref-Add-ERT-for-summary-table.patch.txt --]
[-- Type: text/plain, Size: 2979 bytes --]

From 21152c2045345135fd18749e016367aa0388ae4b Mon Sep 17 00:00:00 2001
From: Michael Brand <michael.ch.brand@gmail.com>
Date: Sat, 4 Jan 2014 16:21:28 +0100
Subject: [PATCH 1/2] TBLFM remote ref: Add ERT for summary table

* testing/lisp/test-org-table.el
(test-org-table/remote-reference-indirect): Add a use case of
summarizing two tables with a sum into one table for the total; as a
preparation for remote reference indirection.
---
 testing/lisp/test-org-table.el | 80 ++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 80 insertions(+)

diff --git a/testing/lisp/test-org-table.el b/testing/lisp/test-org-table.el
index edb51c4..a4f8caa 100644
--- a/testing/lisp/test-org-table.el
+++ b/testing/lisp/test-org-table.el
@@ -793,6 +793,86 @@ See also `test-org-table/copy-field'."
 	     ;; Do a calculation: Use Calc (or Lisp ) formula
 	     "$2 = 2 * remote(table, @1$2)")))
 
+(ert-deftest test-org-table/remote-reference-indirect ()
+  "Access to remote reference with indirection of name or ID."
+  (let ((source-tables "
+#+NAME: 2012
+| amount |
+|--------|
+|      1 |
+|      2 |
+|--------|
+|      3 |
+#+TBLFM: @>$1 = vsum(@I..@II)
+
+#+NAME: 2013
+| amount |
+|--------|
+|      4 |
+|      8 |
+|--------|
+|     12 |
+#+TBLFM: @>$1 = vsum(@I..@II)
+"))
+
+    ;; Read several remote references from same column
+    (org-test-table-target-expect
+     (concat source-tables "
+#+NAME: summary
+|  year | amount  |
+|-------+---------|
+|  2012 | replace |
+|  2013 | replace |
+|-------+---------|
+| total | replace |
+")
+     (concat source-tables "
+#+NAME: summary
+|  year | amount |
+|-------+--------|
+|  2012 |      3 |
+|  2013 |     12 |
+|-------+--------|
+| total |     15 |
+")
+     1
+     ;; Calc formula
+     (concat "#+TBLFM: "
+	     "@2$2 = remote(2012, @>$1) :: "
+	     "@3$2 = remote(2013, @>$1) :: "
+	     "@>$2 = vsum(@I..@II)")
+     ;; Lisp formula
+     (concat "#+TBLFM: "
+	     "@2$2 = '(identity remote(2012, @>$1)); N :: "
+	     "@3$2 = '(identity remote(2013, @>$1)); N :: "
+	     "@>$2 = '(+ @I..@II); N"))
+
+    ;; Read several remote references from same row
+    (org-test-table-target-expect
+     (concat source-tables "
+#+NAME: summary
+| year   |    2012 |    2013 | total   |
+|--------+---------+---------+---------|
+| amount | replace | replace | replace |
+")
+     (concat source-tables "
+#+NAME: summary
+| year   | 2012 | 2013 | total |
+|--------+------+------+-------|
+| amount |    3 |   12 |    15 |
+")
+     1
+     ;; Calc formula
+     (concat "#+TBLFM: "
+	     "@2$2 = remote(2012, @>$1) :: "
+	     "@2$3 = remote(2013, @>$1) :: "
+	     "@2$> = vsum($<<..$>>)")
+     ;; Lisp formula
+     (concat "#+TBLFM: "
+	     "@2$2 = '(identity remote(2012, @>$1)); N :: "
+	     "@2$3 = '(identity remote(2013, @>$1)); N :: "
+	     "@2$> = '(+ $<<..$>>); N"))))
+
 (ert-deftest test-org-table/org-at-TBLFM-p ()
   (org-test-with-temp-text-in-file
       "
-- 
1.7.12.4 (Apple Git-37)


[-- Attachment #3: 0002-TBLFM-remote-ref-Add-indirection-of-name-or-ID.patch.txt --]
[-- Type: text/plain, Size: 4651 bytes --]

From 38ad5df8ab16713f3e32d1c2106f2b1cf1e6ea42 Mon Sep 17 00:00:00 2001
From: Michael Brand <michael.ch.brand@gmail.com>
Date: Sat, 4 Jan 2014 16:25:42 +0100
Subject: [PATCH 2/2] TBLFM remote ref: Add indirection of name or ID

* doc/org.texi (References): Add description for indirection of
NAME-OR-ID.

* lisp/org-table.el (org-table-eval-formula): Make use of
`org-table-remote-reference-indirection'.
(org-table-remote-reference-indirection): New function.

* testing/lisp/test-org-table.el
(test-org-table/remote-reference-indirect): Change to use remote
reference indirection.
---
 doc/org.texi                   |  6 ++++++
 lisp/org-table.el              | 25 +++++++++++++++++++++++++
 testing/lisp/test-org-table.el | 18 ++++--------------
 3 files changed, 35 insertions(+), 14 deletions(-)

diff --git a/doc/org.texi b/doc/org.texi
index bf5dafd..f710971 100644
--- a/doc/org.texi
+++ b/doc/org.texi
@@ -2630,6 +2630,12 @@ table in that entry.  REF is an absolute field or range reference as
 described above for example @code{@@3$3} or @code{$somename}, valid in the
 referenced table.
 
+Indirection of NAME-OR-ID: When NAME-OR-ID has the format @code{@@ROW$COLUMN}
+it will be substituted with the name or ID found in this field of the current
+table.  For example @code{remote($1, @@>$2)} => @code{remote(year_2013,
+@@>$1)}.  The format @code{B3} is not supported because it can not be
+distinguished from a plain table name or ID.
+
 @node Formula syntax for Calc
 @subsection Formula syntax for Calc
 @cindex formula syntax, Calc
diff --git a/lisp/org-table.el b/lisp/org-table.el
index 36478f8..8c823d2 100644
--- a/lisp/org-table.el
+++ b/lisp/org-table.el
@@ -2657,6 +2657,7 @@ not overwrite the stored one."
 	;; Check for old vertical references
 	(setq form (org-table-rewrite-old-row-references form))
 	;; Insert remote references
+	(setq form (org-table-remote-reference-indirection form))
 	(while (string-match "\\<remote([ \t]*\\([-_a-zA-Z0-9]+\\)[ \t]*,[ \t]*\\([^\n)]+\\))" form)
 	  (setq form
 		(replace-match
@@ -5010,6 +5011,30 @@ list of the fields in the rectangle."
 		      (org-table-get-range (match-string 0 form) tbeg 1))
 		  form)))))))))
 
+(defun org-table-remote-reference-indirection (form)
+  "Return formula with table remote references substituted by indirection.
+For example \"remote($1, @>$2)\" => \"remote(year_2013, @>$1)\".
+This indirection works only with the format @ROW$COLUMN.  The
+format \"B3\" is not supported because it can not be
+distinguished from a plain table name or ID."
+  (while (string-match (concat
+			;; Same as in `org-table-eval-formula'.
+			"\\<remote([ \t]*\\("
+			;; Allow "$1", "@<", "$-1", "@<<$1" etc.
+			"[@$][^,]+"
+			;; Same as in `org-table-eval-formula'.
+			"\\)[ \t]*,[ \t]*\\([^\n)]+\\))")
+		       form)
+    ;; Substitute the remote reference with the value found in the
+    ;; field.
+    (setq form
+	  (replace-match
+	   (save-match-data
+	     (org-table-get-range (org-table-formula-handle-first/last-rc
+				   (match-string 1 form))))
+	   t t form 1)))
+  form)
+
 (defmacro org-define-lookup-function (mode)
   (let ((mode-str (symbol-name mode))
 	(first-p (equal mode 'first))
diff --git a/testing/lisp/test-org-table.el b/testing/lisp/test-org-table.el
index a4f8caa..dd13fbf 100644
--- a/testing/lisp/test-org-table.el
+++ b/testing/lisp/test-org-table.el
@@ -837,14 +837,9 @@ See also `test-org-table/copy-field'."
 ")
      1
      ;; Calc formula
-     (concat "#+TBLFM: "
-	     "@2$2 = remote(2012, @>$1) :: "
-	     "@3$2 = remote(2013, @>$1) :: "
-	     "@>$2 = vsum(@I..@II)")
+     "#+TBLFM: @<<$2..@>>$2 = remote($<, @>$1) :: @>$2 = vsum(@I..@II)"
      ;; Lisp formula
-     (concat "#+TBLFM: "
-	     "@2$2 = '(identity remote(2012, @>$1)); N :: "
-	     "@3$2 = '(identity remote(2013, @>$1)); N :: "
+     (concat "#+TBLFM: @<<$2..@>>$2 = '(identity remote($<, @>$1)); N :: "
 	     "@>$2 = '(+ @I..@II); N"))
 
     ;; Read several remote references from same row
@@ -863,14 +858,9 @@ See also `test-org-table/copy-field'."
 ")
      1
      ;; Calc formula
-     (concat "#+TBLFM: "
-	     "@2$2 = remote(2012, @>$1) :: "
-	     "@2$3 = remote(2013, @>$1) :: "
-	     "@2$> = vsum($<<..$>>)")
+     "#+TBLFM: @2$<<..@2$>> = remote(@<, @>$1) :: @2$> = vsum($<<..$>>)"
      ;; Lisp formula
-     (concat "#+TBLFM: "
-	     "@2$2 = '(identity remote(2012, @>$1)); N :: "
-	     "@2$3 = '(identity remote(2013, @>$1)); N :: "
+     (concat "#+TBLFM: @2$<<..@2$>> = '(identity remote(@<, @>$1)); N :: "
 	     "@2$> = '(+ $<<..$>>); N"))))
 
 (ert-deftest test-org-table/org-at-TBLFM-p ()
-- 
1.7.12.4 (Apple Git-37)