orgmode - summary table as different group from input table

Question

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!

Answer 1

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.

Answer 2

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!

Answer 3

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
addmargins(tapply(dd$Value,dd$Item,sum))
#+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
t(addmargins(tapply(dd$Value,dd$Item,sum)))
#+END_SRC

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

Answer 4

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: