# Can columns of an org table be grouped to conveniently work with data?

I have an `org` table

``````|        |        | P1 | P2 | P3 | P4 | Q1 | Q2 |  E1 |  E2 |   F |
|--------+--------+----+----+----+----+----+----+-----+-----+-----|
|        | Totals | 20 | 40 | 35 | 20 | 50 | 45 | 100 | 100 | 200 |
|--------+--------+----+----+----+----+----+----+-----+-----+-----|
| John   |        | 18 | 18 | 15 |  0 | 45 | 22 |  45 |  97 | 112 |
| Paul   |        |  4 | 12 |  4 |  8 | 10 | 14 |  31 |  58 | 158 |
| George |        | 15 | 34 | 32 | 16 | 46 | 12 |  94 |  46 | 141 |
| Ringo  |        | 20 | 17 | 25 |  1 | 49 | 16 |  61 |  52 | 129 |
``````

I would like to fill in the column `Totals` with a weighted sum of the other columns. The weighted sum should have a formula

``````Totals = w*(P1+P2+P3+P4)+x*(Q1+Q2)+y*(E1+E2)+z*(F)
``````

Where `w`, `x`, `y`, and `z` are defined constants.

This specific formula is easy to write down. My problem is that I'd like the formula to respond when I add columns to the table. So my formula should really be something like

``````Totals = w*(sum of cols marked "P")+x*(sum of cols marked "Q")
+y*(sum of cols marked "E")+z*(sum of cols marked "F")
``````

Thus when my table is changed to

``````|        |        | P1 | P2 | P3 | P4 | P5 | Q1 | Q2 |  E1 |  E2 |  E3 |   F |
|--------+--------+----+----+----+----+----+----+----+-----+-----+-----+-----|
|        | Totals | 20 | 40 | 35 | 20 | 40 | 50 | 45 | 100 | 100 | 100 | 200 |
|--------+--------+----+----+----+----+----+----+----+-----+-----+-----+-----|
| John   |        | 18 | 18 | 15 |  0 | 40 | 45 | 22 |  45 |  97 |  65 | 112 |
| Paul   |        |  4 | 12 |  4 |  8 | 33 | 10 | 14 |  31 |  58 |  45 | 158 |
| George |        | 15 | 34 | 32 | 16 | 24 | 46 | 12 |  94 |  46 |  78 | 141 |
| Ringo  |        | 20 | 17 | 25 |  1 | 38 | 49 | 16 |  61 |  52 |  65 | 129 |
``````

My formula should become

``````Totals = w*(P1+P2+P3+P4+P5)+x*(Q1+Q2)+y*(E1+E2+E3)+z*(F)
``````

Is there a way to do this?

I have an answer using elisp formulas:

``````#+begin_src emacs-lisp :results none
(defun jj/factor (code)
(let ((prefix (string-to-char code)))
(cond ((eq prefix ?P) 0.1)
((eq prefix ?Q) 0.2)
((eq prefix ?E) 0.3)
((eq prefix ?F) 0.4)
(t 1.0))))

(defun jj/factorlist (codelist)
(mapcar 'jj/factor codelist))

(defun jj/dotproduct (a b)
(apply '+ (mapcar* '* (jj/factorlist a) (mapcar 'string-to-number b))))
#+end_src

|        |        | P1 | P2 | P3 | P4 | Q1 | Q2 | E1  | E2  | F   |
|--------+--------+----+----+----+----+----+----+-----+-----+-----|
|        | Totals | 20 | 40 | 35 | 20 | 50 | 45 | 100 | 100 | 200 |
|--------+--------+----+----+----+----+----+----+-----+-----+-----|
| John   |        | 18 | 18 | 15 | 0  | 45 | 22 | 45  | 97  | 112 |
| Paul   |        | 4  | 12 | 4  | 8  | 10 | 14 | 31  | 58  | 158 |
| George |        | 15 | 34 | 32 | 16 | 46 | 12 | 94  | 46  | 141 |
| Ringo  |        | 20 | 17 | 25 | 1  | 49 | 16 | 61  | 52  | 129 |
#+TBLFM: @3\$2..@>\$2='(jj/dotproduct (list @1\$3..@1\$>) (list \$3..\$>))
``````

You have to evaluate the source block once (by hitting `C-c C-c` over it).

The `jj/factor` function assigns a numeric factor given the a string.

The second function, `jj/factorlist` gets a list of factors from a list of strings.

The last function, `jj/dotproduct`, will sum all products as you intended.

• I have a question about making some modifications to your elisp code. It's too long to post as a comment, so I've posted it as a question: emacs.stackexchange.com/questions/13893/… Thanks again for your contributions--I'm a newcomer to plain-text but I'm really enjoying it! Jul 11, 2015 at 0:50

You can group columns for output formatting only, but you can't then refer to them like you can with row groups (`@I..II` etc).

What you can do though is give columns names and refer to them:

``````| \$ |    |    |    | x=2   |
| ! | p1 |    | pn |       |
|   | P1 | P2 | P3 | Total |
|---+----+----+----+-------|
|   | 1  |  2 | 3  | 12    |
#+TBLFM: @4\$5=vsum(\$p1..\$pn)*\$x
``````

If you want to add a `p4` column by putting the point in `Total` and pressing `M-S-right` you will have to manually move the `pn` label from the `p3` to the `p4` column, so it is not quite as convenient as the `@I` syntax for rows.

Also, you can store your constants in the table using a row marked `\$`.

• This is a cool idea. Thanks for looking at it! Jul 9, 2015 at 20:43