2

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!

3

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? – lucky1928 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. – doctorate Aug 12 '17 at 7:05
3

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!

2

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 |
  • 1
    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! – lucky1928 Aug 14 '17 at 14:47
  • 1
    @lucky1928 R code aggregate(dd$Value, by=list(Category=dd$Item), FUN=sum) – beetlej Aug 14 '17 at 16:34
2

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? – Brian Fitzpatrick May 3 '18 at 7:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.