# orgmode - summary table as different group from input table

I need to summary a table according to one column and sum the specific rows of another column.

e.g. The summary table is generated from the input table. input table column 1 grouped to IN and OUT, column 2 is the sum of IN or OUT columns.

``````#+NAME: summary
| Item  | Value |
|-------+-------|
| IN    | \$5.0  |
| OUT   | \$7.0  |
| Total | \$13.0 |

#+NAME: input
| Item | Value |
|------+-------|
| IN   | \$1.5  |
| OUT  | \$2.5  |
| IN   | \$3.5  |
| OUT  | \$4.5  |
|------+-------|

#+BEGIN_SRC elisp :results output :var input=input
(let (item (in 0)(out 0))
(dolist (line input)
(setq item (car line))
(setq val (string-to-number (substring (cadr line) 1) 10))
(if (string= item "OUT")
(progn
(setq out (+ out val))))
(if (string= item "IN")
(progn
(setq in (+ in val)))))
(print in)
(print out)
(print (+ in out))
)
#+END_SRC

#+RESULTS:
:
: 5.0
:
: 7.0
:
: 12.0
``````

Currently, I use elisp to calculate it but I know formula maybe better but I don't know how to do it!

I don't know if two tables are necessary in your case. Some clean up to the data table or the "input" table is needed beforehand, by removing the `\$` from the values which should be trivial by any editor.

I have two solutions for this, but I prefer the first one:

Solution 1

``````| Item  | USD   | Sum In | Sum Out |
|-------+-------+--------+---------|
| IN    |   1.5 |        |         |
| OUT   |   2.5 |        |         |
| IN    |   3.5 |        |         |
| OUT   |   4.5 |        |         |
|-------+-------+--------+---------|
| Total |  12.0 |    5.0 |     7.0 |
#+TBLFM: @2\$2..@-1\$2='(if (string= "\$2" "[]") 0 \$2);N
#+TBLFM: @>\$3='(apply '+ (org-lookup-all "IN" '(@2\$1..@-1\$1) '(@2..@-1\$2) 'string=));L
#+TBLFM: @>\$4='(apply '+ (org-lookup-all "OUT" '(@2\$1..@-1\$1) '(@2..@-1\$2) 'string=));L
#+TBLFM: @>\$2=\$3+\$4;f1
``````

P.S.

• To apply the formulas, hit `C-c C-c` on each line starting with `TBLFM:`.
• The first `TBLFM` line is to convert any values with no data or with string to zeros before doing our calculations, otherwise error will be thrown.

• Have a look at this Worg link for more detail about using the lookup functions.

Solution 2

``````| Item  | Value | Sum In | Sum Out |
|-------+-------+--------+---------|
| IN    |   1.5 |    1.5 |       0 |
| OUT   |   2.5 |      0 |     2.5 |
| IN    |   3.5 |    3.5 |       0 |
| OUT   |   4.5 |      0 |     4.5 |
|-------+-------+--------+---------|
| Total |  12.0 |    5.0 |     7.0 |
#+TBLFM: @2\$3..@-1\$3='(if (string= "\$1" "IN") \$2 0);L::@>\$3=vsum (@2..@-1);f1
#+TBLFM: @2\$4..@-1\$4='(if (string= "\$1" "OUT") \$2 0);L::@>\$4=vsum (@2..@-1);f1
#+TBLFM: @6\$2=\$3+\$4;f1
``````

P.S.

• `@-1`: means the row before the last
• `;f1`: is just for output formatting, meaning 1 digit after the decimal place but you can change that to any thing.
• `;L` is used to interpolate lisp output to numbers after removing the quotations known of lisp
• `@>` means last row in the table, alternatively you can use `@6` but not extensible as the first notation, try adding more INs and OUTs, the table formula will still perform accordingly.
• ``(lisp code);L` is your friend to do lisp evaluation in table formulas.
• `TBLFM:` lines could be made one liner by separating them with `::`, I made it in three lines for better readability of code.
• Don't forget to hit `C-c C-c` while your point at the `TBLFM:` statement. Three times in this case, each for each line to apply the respective formulas and fill the values in the table.
• The third and fourth columns are calculated and are empty in the beginning. Only the first and second columns are assumed to be entered.
• Read Chapter 3 about Tables in the Org-manual for more detail.
• Thanks, it works as expected. what's @2..@-1\$2 mean? is it the same as @2\$2..@-1\$2? Aug 12 '17 at 2:37
• Yes, it is exactly the same `@<row>\$<column>`, but org-mode is smart enough to understand what you were trying to say and allows such shortcuts in writing references. Aug 12 '17 at 7:05

Another version base on doctorate's answer but use remote table:

``````#+NAME: A
| Item  | Value |
|-------+-------|
| IN    |   5.0 |
| OUT   |   7.0 |
| Total |  12.0 |
#+TBLFM: @2\$2='(apply '+ (org-lookup-all "IN" '(remote(B, @2\$1..@>\$1)) '(remote(B, @2\$2..@>\$2)) 'string=));L::@3\$2='(apply '+ (org-lookup-all "OUT" '(remote(B, @2\$1..@>\$1)) '(remote(B, @2\$2..@>\$2)) 'string=));L::@4\$2=vsum(@2\$2..@-1\$2);f1

#+NAME: B
| Item | Value |
|------+-------+
| IN   |   1.5 |
| OUT  |   2.5 |
| IN   |   3.5 |
| OUT  |   4.5 |
``````

Table name changed to A and B to save some characters in formula!

Org mode with R is great, I modified the followings to sum up `Value` for each unique `Item` by `tapply()` and `sum()` function, thank for lucky1928's suggestion. `addmargins()` function adds total "Sum" of each sum.

``````#+NAME: input
| Item | Value |
|------+-------|
| IN   | 1.5   |
| OUT  | 2.5   |
| IN   | 3.5   |
| OUT  | 4.5   |
| etc  |  10   |
| etc  |  20   |
|------+-------|

#+BEGIN_SRC R :session *R* :results output :exports both :var dd=input :colnames yes
#+END_SRC

#+RESULTS:
: etc  IN OUT Sum
:  30   5   7  42
``````

For HTML table export, just add `:results output value`. We also transpose the result vector by `t()` function, for printing the table horizontally.

``````#+BEGIN_SRC R :session *R* :results output value :exports both :var dd=input :colnames yes
#+END_SRC

#+RESULTS:
| etc | IN | OUT | Sum |
|-----+----+-----+-----|
|  30 |  5 |   7 |  42 |
``````
• Amazing R solution. if I want to group all unique values from value and create a summary table, how to do it with R? I mean do not hardcode the key "IN" and "OUT" but extract it by R code! Aug 14 '17 at 14:47
• @lucky1928 R code `aggregate(dd\$Value, by=list(Category=dd\$Item), FUN=sum)` Aug 14 '17 at 16:34

As an alternative, you may try the `orgtbl-aggregate` package available on Melpa.

Input table

`````` #+NAME: input
| Item | Value |
|------+-------|
| IN   |   1.5 |
| OUT  |   2.5 |
| IN   |   3.5 |
| OUT  |   4.5 |
|------+-------|
``````

An aggregation using the `sum` function gathering `Value` with similar `Item`

`````` #+name: sums
#+BEGIN: aggregate :table "input" :cols "Item sum(Value)"
| Item | sum(Value) |
|------+------------|
| IN   |         5. |
| OUT  |         7. |
#+END:
``````

An aggregation using the `sum` function gathering all `Value`

`````` #+BEGIN: aggregate :table "input" :cols "sum(Value)"
| sum(Value) |
|------------|
|        12. |
#+END:
``````
• I'm trying to get orgtbl-aggregate to work but keep getting the error "Invalid function: ((symbol-function(quote math-read-preprocess-string)) (function identity)). Any idea what's wrong? May 3 '18 at 7:55