Tutorial 16.1 - Emacs orgmode tables

14 Jan 2013

Table creation

Date	Category 1	Category 2	Numeric
<2013-01-14>	A	C	10.0
<2013-01-15>	B	C	15.0
<2013-01-16>	A	A	12.5

There are a number of ways to create the above table in orgmode:

• Create a table with C-c |. You will be prompted for the table dimensions and once you hit return the empty table will be created.

  |   |   |   |   |   |
  |---+---+---+---+---|
  |   |   |   |   |   |
  			  

  And then fill the table in. Use the key to move to the next cell and correct the cell widths.
  

  | Date         | Category 1 | Category 2 | Numeric |   |
  |--------------+------------+------------+---------+---|
  | <2013-01-14> | A          | C          | 10.0    |   |
  | <2013-01-14> | B          | C          | 15.0    |   |
  | <2013-01-15> | A          | A          | 12.5    |   |
  			  

• Copy and paste the table in as plain text, and then convert the text to table.

  Date	Category 1	Category 2	Numeric
  <2013-01-14>	A	C	10.0
  <2013-01-15>	B	C	15.0
  <2013-01-16>	A	A	12.5				
  			  

  Then use the C-c | key-bind to convert the text into a table

  | Date         | Category 1 | Category 2 | Numeric |
  | <2013-01-14> | A          | C          |    10.0 |
  | <2013-01-15> | B          | C          |    15.0 |
  | <2013-01-16> | A          | A          |    12.5 |
  			  

  Finally, make the first row a header row by placing a row line under it. Do so by placing the cursor on the first row and entering C-c -.

  | Date         | Category 1 | Category 2 | Numeric |
  |--------------+------------+------------+---------|
  | <2013-01-14> | A          | C          |    10.0 |
  | <2013-01-15> | B          | C          |    15.0 |
  | <2013-01-16> | A          | A          |    12.5 |  
  			  

• Return the output of an external application.
  
  • Start by issuing the C-u M-! key combination. This will prompt for a command line application and return the standard output to current position of the cursor. ls -l ~/Work

    total 56
    drwxr-xr-x  7 murray murray  4096 Mar  7 16:50 AIMS
    drwxr-xr-x  3 murray murray  4096 Mar 11 08:18 Analyses
    drwxr-xr-x 29 murray murray 12288 Jan 31 06:36 Book
    drwxr-xr-x  2 murray murray  4096 Feb 20 11:04 figure
    -rw-r--r--  1 murray murray 23080 Feb 21 07:04 junk.html
    -rw-r--r--  1 murray murray  1211 Feb 21 07:04 junk.md
    -rw-r--r--  1 murray murray   780 Feb 20 11:31 junk.Rmd  
    				  

  • Remove the first line

    drwxr-xr-x  7 murray murray  4096 Mar  7 16:50 AIMS
    drwxr-xr-x  3 murray murray  4096 Mar 11 08:18 Analyses
    drwxr-xr-x 29 murray murray 12288 Jan 31 06:36 Book
    drwxr-xr-x  2 murray murray  4096 Feb 20 11:04 figure
    -rw-r--r--  1 murray murray 23080 Feb 21 07:04 junk.html
    -rw-r--r--  1 murray murray  1211 Feb 21 07:04 junk.md
    -rw-r--r--  1 murray murray   780 Feb 20 11:31 junk.Rmd  
    				  

  • Use the C-c | to convert the text into a table

    | drwxr-xr-x |  7 | murray | murray |  4096 | Mar |  7 | 16:50 | AIMS      |
    | drwxr-xr-x |  3 | murray | murray |  4096 | Mar | 11 | 08:18 | Analyses  |
    | drwxr-xr-x | 29 | murray | murray | 12288 | Jan | 31 | 06:36 | Book      |
    | drwxr-xr-x |  2 | murray | murray |  4096 | Feb | 20 | 11:04 | figure    |
    | -rw-r--r-- |  1 | murray | murray | 23080 | Feb | 21 | 07:04 | junk.html |
    | -rw-r--r-- |  1 | murray | murray |  1211 | Feb | 21 | 07:04 | junk.md   |
    | -rw-r--r-- |  1 | murray | murray |   780 | Feb | 20 | 11:31 | junk.Rmd  |
    				  

  • Insert a line above the first (using M-S-down)

    |            |    |        |        |       |     |    |       |           |
    | drwxr-xr-x |  7 | murray | murray |  4096 | Mar |  7 | 16:50 | AIMS      |
    | drwxr-xr-x |  3 | murray | murray |  4096 | Mar | 11 | 08:18 | Analyses  |
    | drwxr-xr-x | 29 | murray | murray | 12288 | Jan | 31 | 06:36 | Book      |
    | drwxr-xr-x |  2 | murray | murray |  4096 | Feb | 20 | 11:04 | figure    |
    | -rw-r--r-- |  1 | murray | murray | 23080 | Feb | 21 | 07:04 | junk.html |
    | -rw-r--r-- |  1 | murray | murray |  1211 | Feb | 21 | 07:04 | junk.md   |
    | -rw-r--r-- |  1 | murray | murray |   780 | Feb | 20 | 11:31 | junk.Rmd  |
    				  

  • Finally, add the heading titles and insert a horizontal row C-c -

    | Properties | Subfolders | Owner  | User   |  Size | Mtn | Day |  Time | Name      |
    |------------+------------+--------+--------+-------+-----+-----+-------+-----------|
    | drwxr-xr-x |          7 | murray | murray |  4096 | Mar |   7 | 16:50 | AIMS      |
    | drwxr-xr-x |          3 | murray | murray |  4096 | Mar |  11 | 08:18 | Analyses  |
    | drwxr-xr-x |         29 | murray | murray | 12288 | Jan |  31 | 06:36 | Book      |
    | drwxr-xr-x |          2 | murray | murray |  4096 | Feb |  20 | 11:04 | figure    |
    | -rw-r--r-- |          1 | murray | murray | 23080 | Feb |  21 | 07:04 | junk.html |
    | -rw-r--r-- |          1 | murray | murray |  1211 | Feb |  21 | 07:04 | junk.md   |
    | -rw-r--r-- |          1 | murray | murray |   780 | Feb |  20 | 11:31 | junk.Rmd  |  
    					

Columns and rows

Starting with the following table:

| Date         | Category 1 | Category 2 | Numeric |   |
|--------------+------------+------------+---------+---|
| <2013-01-14> | A          | C          | 10.0    |   |
| <2013-01-14> | B          | C          | 15.0    |   |
| <2013-01-15> | A          | A          | 12.5    |   |  
		  

Moving columns and rows

A column can be shifted left and right using the M-left and M-right key combinations respectively.

| Date         | Numeric | Category 1 | Category 2 |   |
|--------------+---------+------------+------------+---|
| <2013-01-14> |    10.0 | A          | C          |   |
| <2013-01-14> |    15.0 | B          | C          |   |
| <2013-01-15> |    12.5 | A          | A          |   |  
		  

Similarly, A row can be shifted up and down using the M-up and M-down key combinations respectively.

Adding/deleting columns and rows

A column can be added (to the right) or deleted using the M-S-left and M-S-right key combinations respectively.

Sorting

The column currently containing the cursor can be sorted using the C-c ^ key combination. You will be prompted to indicate whether the sort should occur by:

• [a]lphabetically - decending
• [A]lphabetically - acending
• [n]umerically - lowest to highest
• [N]umerically - highest to lowest
• [t]ime - oldest to youngest
• [T]ime - youngest to oldest

Cell references

Cell references are typically of the formula

			@row$column
		  

Row and column references can be toggled on and off with the C-c } key combination.

   1| Date         | Numeric | Category 1 | Category 2 |   |
I*1 |$1------------+$2-------+$3----------+$4----------+$5-|
   2| <2013-01-14> |    15.0 | B          | C          |   |
   3| <2013-01-14> |    10.0 | A          | C          |   |
   4| <2013-01-15> |    12.5 | A          | A          |   |
		  

To get the reference options for the current cell, type C-c ? Row references can be :

• absolute (e.g. @1 is row 1)
• relative to the current position (e.g. @+1 is one row down or @-2 is two rows above)
• immutable - relative to the first and last row (e.g. @< is the first row, @> is the last row and @>>> is the third last row)
• relative to the hlines (e.g. @+I is the first row after the first hline, @-III is the first row before the third hline and @II+2 is the second row after the second hline.

Column references can be :

• absolute (e.g. $1 is column 1)
• relative to the current position (e.g. $+1 is one column to the right or $-2 is two columns to the left)
• immutable - relative to the first and last column (e.g. $< is the first column, $> is the last column and $>>> is the third last column)

.

Example	Description
@3$2	Row 3, column 2. Same as C3
@3	Row 2, current column.
$2	Current row, column 2. Same as C&
@-2$-3	Two rows up and three columns left of the current cell
@>$<<	The last row, second last column
@+II$2	The first row after the second hline, second column

Range references

A rectangular range of cells are referenced by separating two cell references by ...

Spreadsheet Formulas

Simple formulas

A formula can be added a number of ways:

• by starting a cells entry with := and then add the formula (e.g. $2*2 to multiply the value in the second column by 2).

  | Date         | Numeric | Category 1 | Category 2 |   |
  |--------------+---------+------------+------------+---|
  | <2013-01-14> |    15.0 | B          | C          |:=$2*2 |
  | <2013-01-14> |    10.0 | A          | C          |   |
  | <2013-01-15> |    12.5 | A          | A          |   |  
  			  

  Note, this formula does not specify any rows and therefore is will substitute in the corresponding row.
  Then type C-c C-c.The formula will be replaced by the result of the formula, and the formula itself will be appended to the bottom of the table.

  | Date         | Numeric | Category 1 | Category 2 |     |
  |--------------+---------+------------+------------+-----|
  | <2013-01-14> |    15.0 | B          | C          | 30. |
  | <2013-01-14> |    10.0 | A          | C          |     |
  | <2013-01-15> |    12.5 | A          | A          |     |
  #+TBLFM: @2$5=$2*2
  			  

• by directly adding the formula to a #+TBLFM: specification (see above result)
To paste a column formula in a cell C-c *
To paste a formula into each row of a column C-u C-c *

Math functions

Formulas can include standard math functions:

Function	Description
exp(c)	exponential
log(c)	natural logarithm
log10(c)	log base 10
sqrt(c)	square-root
vmin(v)	minimum of vector (non blanks)
vmax(v)	maximum of vector (non blanks)
vmean(v)	mean of vector (non blanks)
vmedian(v)	median of vector (non blanks)
vsdev(v)	standard deviation of vector (non blanks)
vvar(v)	variance of vector (non blanks)
vcov(v1, v2)	covariance of two vector (non blanks)
vcor(v1, v2)	correlation of two vector (non blanks)

Summary (vector) formulas

Orgmode tables can leverage some of the summarizing functions of calc. For example, to add a total row..

• Use M-S-down followed by M-down on the last row to add a row. Then add a horizontal line with C-c -.

  | Date         | Numeric | Category 1 | Category 2 |     |
  |--------------+---------+------------+------------+-----|
  | <2013-01-14> |    15.0 | B          | C          | 30. |
  | <2013-01-14> |    10.0 | A          | C          |     |
  | <2013-01-15> |    12.5 | A          | A          |     |
  |--------------+---------+------------+------------+-----|
  |              |         |            |            |     |
  #+TBLFM: @2$5=@2$2*2
  			  

• In cell @5$2 (last numeric cell) enter :=vsum(@2..@4) and then C-c C-c

| Date         |        Numeric | Category 1 | Category 2 |     |
|--------------+----------------+------------+------------+-----|
| <2013-01-14> |           15.0 | B          | C          | 30. |
| <2013-01-14> |           10.0 | A          | C          |     |
| <2013-01-15> |           12.5 | A          | A          |     |
|--------------+----------------+------------+------------+-----|
|              | :=vsum(@2..@4) |            |            |     |
#+TBLFM: @2$5=@2$2*2
			

The function vsum() stands for sum of vector, thus yielding

  
| Date         | Numeric | Category 1 | Category 2 |     |
|--------------+---------+------------+------------+-----|
| <2013-01-14> |    15.0 | B          | C          | 30. |
| <2013-01-14> |    10.0 | A          | C          |     |
| <2013-01-15> |    12.5 | A          | A          |     |
|--------------+---------+------------+------------+-----|
|              |    37.5 |            |            |     |
#+TBLFM: @2$5=@2$2*2::@5$2=vsum(@2..@4)
			

Function	Description
vsum(v)	sum of vector
vprod(v)	product of vector
vcount(v)	length of vector (non blanks)
vlen(v)	length of vector (non blanks)
vmin(v)	minimum of vector (non blanks)
vmax(v)	maximum of vector (non blanks)
vmean(v)	mean of vector (non blanks)
vmedian(v)	median of vector (non blanks)
vsdev(v)	standard deviation of vector (non blanks)
vvar(v)	variance of vector (non blanks)
vcov(v1, v2)	covariance of two vector (non blanks)
vcor(v1, v2)	correlation of two vector (non blanks)

Optional modes

A formula can be appended by a semi-colon and one or more optional mode strings that is passed on to the calculation engine (calc) to alter the format of the result. The mode strings are described in the following table:

Mode option	Example	Description
p	;p10	set calculation precision to 10 decimal places
n,s,e,f	;s3	normal, scientific, engineering or fixed formatting. Example indicates scientific formatting with 3-1=2 decimal places.
D,R	;D	degrees or radians
F,S	;F	fraction (ratio) and symbolic (standard) format.
N	;N	interpret all fields as numbers, making non-numerics 0 values
E	;E	keep empty cells in the range
L	;L	treat the values as literal

Alternatively, printf style formatting can be used...