Intro
This is the second post in the serie of how I use Emacs Org mode (from now on org) and it will cover usage of org tables. Some of the content assumes basic knowledge about org. If you are new to org I can recommend this two videos; hrs and kitchin.
Org tables are like spreadsheets with super powers. Tables can be used to keep information organized, as input to source blocks (more about that in the first post, "Emacs Org mode source blocks") or used in calculations.
My main usage of tables is to calculate work time. I sum up the time I should work and the time I have been working and calc the diff.
Versions
… of Emacs and Org mode.
#+BEGIN_SRC emacs-lisp
(org-version)
#+END_SRC
#+RESULTS:
: 9.3.7
#+BEGIN_SRC emacs-lisp
(emacs-version)
#+END_SRC
#+RESULTS:
: GNU Emacs 26.3 (build 1, x86_64-pc-linux-gnu, GTK+ Version 3.24.20)
: of 2020-05-19Emacs config
Without config Emacs don't know how to execute source blocks. This following snippet
provides Emacs with the ability to understand a set of languages/applications. The set
of languages/applications need to be available in the underlying OS. To get a complete
list of languages/applications available in org. A complete list can be found here:
repo. Support for source blocks are available in files prefixed with ob-.
(setq org-confirm-babel-evaluate nil)
(org-babel-do-load-languages
'org-babel-load-languages
'((emacs-lisp . t)
(shell . t)
(scheme . t)
(python . t)
(awk . t)
(clojure . t)))Table with a name
This example names the table via the #+NAME: command. This named table is then used as
input to a source block. Here we use awk to sum the population in Europe. More about
this can be found in the first post, "Emacs Org mode source blocks".
#+NAME: countries
| Country name | Area | Population | Region |
|--------------+------+------------+---------------|
| USSR | 8649 | 275 | Asia |
| Canada | 3852 | 25 | North America |
| China | 3705 | 1032 | Asia |
| USA | 3615 | 237 | North America |
| Brazil | 3286 | 134 | South America |
| India | 1267 | 746 | Asia |
| Mexico | 762 | 78 | North America |
| France | 211 | 55 | Europe |
| Japan | 144 | 120 | Asia |
| Germany | 96 | 61 | Europe |
| England | 94 | 56 | Europe |
#+BEGIN_SRC awk :stdin countries
/Europe/ { sum+= $3 }
END { print "Sum of the population in Europe is", sum}
#+END_SRC
#+RESULTS:
: Sum of the population in Europe is 172Data is taken from the book The AWK Programming Language.
Calculate sums
Just like the previous example this one will do calculations based on data in a table.
But this on will use table formulas (TBLFM) to do the calculations. This time the
table wont be passed to a programming language outside of Emacs. In the "before" we have
some items with amount and cost data. We would like to know the amount * cost for each
row and the sum of all "Total" rows.
By putting the pointer on the TBLFM formula and use the keyboard sequence C-c C-c the
calculations will be executed.
Org docs on table formulas and advanced features.
Before =TBLFM= is evaluated
| Description | Amounts | Cost | Total |
|-------------+---------+------+-------|
| Item 1 | 5 | 10 | |
| Item 2 | 1 | 55 | |
| Item 3 | 20 | 10 | |
| Item 4 | 1000 | 1 | |
|-------------+---------+------+-------|
| Sum: | | | |
#+TBLFM: $4=$2*$3::@>$4=vsum(@<<$4..@>>$4)
After =TBLFM= is evaluated
| Description | Amounts | Cost | Total |
|-------------+---------+------+-------|
| Item 1 | 5 | 10 | 50 |
| Item 2 | 1 | 55 | 55 |
| Item 3 | 20 | 10 | 200 |
| Item 4 | 1000 | 1 | 1000 |
|-------------+---------+------+-------|
| Sum: | | | 1305 |
#+TBLFM: $4=$2*$3::@>$4=vsum(@<<$4..@>>$4)Calculate with time
This is more or less the same as in previous example but this time we have a U in the
TBLFM and that tells org to calculate time. Org have many different ways of manage
time, more about that here.
Before =TBLFM= is evaluated
| Task | Time |
|-------+-------|
| Taks1 | 8:00 |
| Task2 | 0:30 |
| Task3 | 2:00 |
|-------+-------|
| Total | |
#+TBLFM: @>$2=vsum(@<<$2..@>>$2);U
After =TBLFM= is evaluated
| Task | Time |
|-------+-------|
| Taks1 | 8:00 |
| Task2 | 0:30 |
| Task3 | 2:00 |
|-------+-------|
| Total | 10:30 |
#+TBLFM: @>$2=vsum(@<<$2..@>>$2);UCalculate work time
This is an example on how I use tables to make sure that I work my hours.
At my current work we have 40 hours a week freely distributed, no 8-17. As I am a person
that like routines I often try to do 8-17 but I am not to strict about it. Working some
hours in the night or on weekends is normal. I have a setup where I schedule 8 hours of
work every weekday (5*8=40). And to keep track that I really do that I use a table
together with some Emacs LiSP code and a TBLFM formula. If I am sick or on vacation I
put "0:00" in "Worked". This tells the LiSP code to not add "8:00" hours in the "Should
work" spot. And if I work hours on a weekend that day should not add extra "8:00" to
"Should work".
#+BEGIN_SRC emacs-lisp :results none
(defun jh/hours-a-day (date time)
(if (string-equal time "0:00")
""
(if (or (string-match " Mon" date)
(string-match " Tue" date)
(string-match " Wed" date)
(string-match " Thu" date)
(string-match " Fri" date))
"8:00" "")))
#+END_SRC
The example contains multiple TBLFM, eval them one by one from the top to the bottom.
| Day | Worked | Should work |
|------------------+--------+-------------|
| [2020-06-20 Sat] | 2:00 | | # Day off, it's a weekend
| [2020-06-19 Fri] | 8:00 | 8:00 |
| [2020-06-18 Thu] | 8:00 | 8:00 |
| [2020-06-17 Wed] | 7:30 | 8:00 |
| [2020-06-16 Tue] | 7:30 | 8:00 |
| [2020-06-15 Mon] | 0:00 | | # Sick or vacation, as it's a weekday
|------------------+--------+-------------|
| Total | 33:00 | 32:00 |
| Diff | | 01:00 |
#+TBLFM: @<<$3..@>>>$3='(jh/hours-a-day $1 $2)'
#+TBLFM: @>>$2=vsum(@<<$2..@>>>$2);U
#+TBLFM: @>>$3=vsum(@<<$3..@>>>$3);U
#+TBLFM: @>$3=(@>>$2-@>>$3);ULogbook and clocktable
This example is not about tables but headlines, drawers and clocktable will be part of a bigger example later.
So, org have headlines. A headline is a row that starts with one or more *. The number
of * declares the depth of the sub headline. In this example Work is headline,
[2020-06-10 Wed] Deployed server and [2020-06-11 Thu] Updated deps are sub
headlines. Here is headline docs.
Drawers are values associated with a headline. In this case we use LOGBOOK drawer and
each LOGBOOK have one or more CLOCK entries. Each CLOCK entry is a datetime range.
Clocktable is a report on CLOCK entries. It asks for a scope and finds clocking
information from it. In this case the scope is the current buffer. To eval a
clocktable, put the pointer on #+BEGIN: row and use C-c C-c. Here is the doc on
clocktable.
#+BEGIN: clocktable :scope file :maxlevel 2
#+CAPTION: Clock summary at [2020-06-21 Sun 21:03]
| Headline | Time | |
|--------------------------------------+---------+------|
| *Total time* | *12:00* | |
|--------------------------------------+---------+------|
| Work | 12:00 | |
| \_ [2020-06-11 Thu] Updated deps | | 4:00 |
| \_ [2020-06-10 Wed] Deployed server | | 8:00 |
#+END:
* Work
** [2020-06-11 Thu] Updated deps
:LOGBOOK:
CLOCK: [2020-06-11 Thu 13:00]--[2020-06-11 Thu 17:00] => 4:00
:END:
** [2020-06-10 Wed] Deployed server
:LOGBOOK:
CLOCK: [2020-06-10 Wed 08:00]--[2020-06-10 Wed 12:00] => 4:00
CLOCK: [2020-06-10 Wed 13:00]--[2020-06-10 Wed 17:00] => 4:00
:END:HINT: Total time format may differ. Look here: how to change Total time format.
Composed example
This example combines many of the features we have gone through. The main idea here is a headline with sub headlines. Each sub headline contains a date and a optional text together with clocking information. This file scope is used by clocktable to generate a report. After the report is generated a formula is executed on the table. This formula is pretty extensive. But the main things is to calculate the "Worked" and "Should work" columns and subtract "Should work" with "Worked" to show how many hours I have left to work.
This example depends on the jh/hours-a-day LiSP snippet.
The example below states that I have one more hour to work before I have completed my 40 hours.
* Timetable
#+BEGIN: clocktable :scope file :compact t :formula "$3='(jh/hours-a-day $1 $2)'::@3$3=vsum(@4$3..@>$3);U::@1$2=string(\"Worked|\")::@1$3=string(\"Should work\")::@2$2=vsum(@4$2..@>$2)-vsum(@4$3..@>$3);U::@2$1=string(\"Hour pool\")"
#+CAPTION: Clock summary at [2020-06-21 Sun 21:53]
| Headline | Worked | Should work |
|--------------------------------------------+--------+-------------|
| Hour pool | -01:00 | |
|--------------------------------------------+--------+-------------|
| Work | 39:00 | 40:00 |
| \_ [2020-06-13 Sat] Fixed server problems | 3:00 | |
| \_ [2020-06-12 Fri] | 4:00 | 8:00 |
| \_ [2020-06-11 Thu] Updated deps | 8:00 | 8:00 |
| \_ [2020-06-10 Wed] Deployed server | 8:00 | 8:00 |
| \_ [2020-06-09 Tue] Meetings | 8:00 | 8:00 |
| \_ [2020-06-08 Mon] | 8:00 | 8:00 |
#+TBLFM: $3='(jh/hours-a-day $1 $2)'::@3$3=vsum(@4$3..@>$3);U::@1$2=string("Worked|")::@1$3=string("Should work")::@2$2=vsum(@4$2..@>$2)-vsum(@4$3..@>$3);U::@2$1=string("Hour pool")
#+END:
* Work
** [2020-06-13 Sat] Fixed server problems
:LOGBOOK:
CLOCK: [2020-06-13 Sat 08:00]--[2020-06-13 Sat 11:00] => 3:00
:END:
** [2020-06-12 Fri]
:LOGBOOK:
CLOCK: [2020-06-12 Fri 13:00]--[2020-06-12 Fri 17:00] => 4:00
:END:
** [2020-06-11 Thu] Updated deps
:LOGBOOK:
CLOCK: [2020-06-11 Thu 08:00]--[2020-06-11 Thu 12:00] => 4:00
CLOCK: [2020-06-11 Thu 13:00]--[2020-06-11 Thu 17:00] => 4:00
:END:
** [2020-06-10 Wed] Deployed server
:LOGBOOK:
CLOCK: [2020-06-10 Wed 08:00]--[2020-06-10 Wed 12:00] => 4:00
CLOCK: [2020-06-10 Wed 13:00]--[2020-06-10 Wed 17:00] => 4:00
:END:
** [2020-06-09 Tue] Meetings
:LOGBOOK:
CLOCK: [2020-06-09 Tue 08:00]--[2020-06-09 Tue 12:00] => 4:00
CLOCK: [2020-06-09 Tue 13:00]--[2020-06-09 Tue 17:00] => 4:00
:END:
** [2020-06-08 Mon]
:LOGBOOK:
CLOCK: [2020-06-08 Mon 08:00]--[2020-06-08 Mon 12:00] => 4:00
CLOCK: [2020-06-08 Mon 13:00]--[2020-06-08 Mon 17:00] => 4:00
:END:Nice things I didn't cover
Sub headlines with logbook entries can be created really fast from custom templates. Take a look at capture templates.
Clocking commands can be used on sub headlines to start and stop timing. More info here.