* sum up variables from different org-mode tables @ 2012-11-16 11:33 Martin Gross 2012-11-16 15:16 ` Michael Brand 0 siblings, 1 reply; 9+ messages in thread From: Martin Gross @ 2012-11-16 11:33 UTC (permalink / raw) To: emacs-orgmode 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 ^ permalink raw reply [flat|nested] 9+ messages in thread
* Re: sum up variables from different org-mode tables 2012-11-16 11:33 sum up variables from different org-mode tables Martin Gross @ 2012-11-16 15:16 ` Michael Brand 2012-11-19 11:54 ` Martin Gross 2014-01-04 15:29 ` Michael Brand 0 siblings, 2 replies; 9+ messages in thread From: Michael Brand @ 2012-11-16 15:16 UTC (permalink / raw) To: Martin Gross; +Cc: Org Mode 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 ^ permalink raw reply [flat|nested] 9+ messages in thread
* Re: sum up variables from different org-mode tables 2012-11-16 15:16 ` Michael Brand @ 2012-11-19 11:54 ` Martin Gross 2012-11-19 13:50 ` Michael Brand 2012-11-19 21:21 ` Eric Schulte 2014-01-04 15:29 ` Michael Brand 1 sibling, 2 replies; 9+ messages in thread From: Martin Gross @ 2012-11-19 11:54 UTC (permalink / raw) To: Michael Brand; +Cc: Org Mode 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 ^ permalink raw reply [flat|nested] 9+ messages in thread
* Re: sum up variables from different org-mode tables 2012-11-19 11:54 ` Martin Gross @ 2012-11-19 13:50 ` Michael Brand 2012-11-21 17:01 ` Martin Gross 2012-11-19 21:21 ` Eric Schulte 1 sibling, 1 reply; 9+ messages in thread From: Michael Brand @ 2012-11-19 13:50 UTC (permalink / raw) To: Martin Gross; +Cc: Org Mode 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 ^ permalink raw reply [flat|nested] 9+ messages in thread
* Re: sum up variables from different org-mode tables 2012-11-19 13:50 ` Michael Brand @ 2012-11-21 17:01 ` Martin Gross 2012-11-21 18:46 ` Suvayu Ali 0 siblings, 1 reply; 9+ messages in thread From: Martin Gross @ 2012-11-21 17:01 UTC (permalink / raw) To: Michael Brand; +Cc: Org Mode 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 ^ permalink raw reply [flat|nested] 9+ messages in thread
* Re: sum up variables from different org-mode tables 2012-11-21 17:01 ` Martin Gross @ 2012-11-21 18:46 ` Suvayu Ali 0 siblings, 0 replies; 9+ messages in thread From: Suvayu Ali @ 2012-11-21 18:46 UTC (permalink / raw) To: emacs-orgmode 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. ^ permalink raw reply [flat|nested] 9+ messages in thread
* Re: sum up variables from different org-mode tables 2012-11-19 11:54 ` Martin Gross 2012-11-19 13:50 ` Michael Brand @ 2012-11-19 21:21 ` Eric Schulte 2012-11-22 10:03 ` Martin Gross 1 sibling, 1 reply; 9+ messages in thread From: Eric Schulte @ 2012-11-19 21:21 UTC (permalink / raw) To: Martin Gross; +Cc: Michael Brand, Org Mode [-- 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 ^ permalink raw reply [flat|nested] 9+ messages in thread
* Re: sum up variables from different org-mode tables 2012-11-19 21:21 ` Eric Schulte @ 2012-11-22 10:03 ` Martin Gross 0 siblings, 0 replies; 9+ messages in thread From: Martin Gross @ 2012-11-22 10:03 UTC (permalink / raw) To: Eric Schulte; +Cc: Michael Brand, Org Mode 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 ^ permalink raw reply [flat|nested] 9+ messages in thread
* Re: sum up variables from different org-mode tables 2012-11-16 15:16 ` Michael Brand 2012-11-19 11:54 ` Martin Gross @ 2014-01-04 15:29 ` Michael Brand 1 sibling, 0 replies; 9+ messages in thread From: Michael Brand @ 2014-01-04 15:29 UTC (permalink / raw) To: Org Mode [-- 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) ^ permalink raw reply related [flat|nested] 9+ messages in thread
end of thread, other threads:[~2014-01-04 15:29 UTC | newest] Thread overview: 9+ messages (download: mbox.gz / follow: Atom feed) -- links below jump to the message on this page -- 2012-11-16 11:33 sum up variables from different org-mode tables Martin Gross 2012-11-16 15:16 ` Michael Brand 2012-11-19 11:54 ` Martin Gross 2012-11-19 13:50 ` Michael Brand 2012-11-21 17:01 ` Martin Gross 2012-11-21 18:46 ` Suvayu Ali 2012-11-19 21:21 ` Eric Schulte 2012-11-22 10:03 ` Martin Gross 2014-01-04 15:29 ` Michael Brand
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).