Category Archives: org

Getting data from Org Mode tables

Org Mode is an amazingly powerful package for Emacs. I’ve been learning a lot about how to use its support for plain-text tables and spreadsheet calculations.

Using table data in Emacs Lisp with the :var argument

For example, I wanted to be able to define my abbreviations in an Org Mode table in my config. I remembered coming across this technique a few weeks ago, but I couldn’t find the webpage with the code. It turned out to be simple to write from scratch. Here’s the plain text I added to my config.

#+NAME: abbrev
| Base  | Expansion                             |
| bc    | because                               |
| wo    | without                               |
| wi    | with                                  |
| ex    | For example,                          |
| qm    | [email protected]                   |
| qe    |         |
| qw    |                 |
| qb    |            |
| qc    | |

#+begin_src emacs-lisp :exports code :var data=abbrev
(mapc (lambda (x) (define-global-abbrev (car x) (cadr x))) data)

The :var data=abbrev argument to the Emacs Lisp source block is where all the magic happens. Here, it takes the data from the table named “abbrev” (which I set using #+NAME: before the table) and makes it available to the code. Emacs evaluates that data when the code is tangled (or exported) to my configuration. The code that’s in my Sacha.el looks like this:

(let ((data (quote (("bc" "because")
                    ("wo" "without")
                    ("wi" "with")
                    ("ex" "For example,")
                    ("email" "[email protected]")
                    ("dote" "")
                    ("web" "")
                    ("blog" "")
                    ("ec" "")))))
  (mapc (lambda (x) (define-global-abbrev (car x) (cadr x))) data) )

Looking up data with org-lookup-first, org-lookup-last, and org-lookup-all

You can do more complex things with Org tables, too. Inspired by Eric Boyd’s talk on his Epic Quest of Awesome (which he based on Steve Kamb‘s), I started putting together my own. I made a list of little achievements, guessed at the years, and assigned arbitrary experience points.

The achievements table had rows like this:

Approximate date Category XP Description ID
2014 Life 50 Became a Canadian citizen – link L_CAN
2014 Programming 20 Used NodeJS and AngularJS for a client project – link P_NOD
2014 Programming 5 Pulled information out of Evernote

I wanted to summarize the points by year: points gained, total points, level (according to a lookup table based on D&D experience points), and description. The lookup table was structured like this:

#+TBLNAME: levels
| Total XP | Level | Adjective             |
|        0 |     1 | trained-initiate      |
|     1000 |     2 | experienced           |
|     2250 |     3 | savvy                 |
|     3750 |     4 | veteran               |
|     5500 |     5 | unusually experienced |

Now for the summary table. I created rows for different years, and then I used Org Mode to fill in the rest. (Org Mode! Wow.)

| Year | Points gained | Cumulative points | Level | Adjective        |
| 1997 |             0 |                 0 |     1 | trained-initiate |
| 1998 |            10 |                10 |     1 | trained-initiate |
| 1999 |            50 |                60 |     1 | trained-initiate |
| 2000 |            50 |               110 |     1 | trained-initiate |
| 2001 |           100 |               210 |     1 | trained-initiate |
| 2002 |            60 |               270 |     1 | trained-initiate |
| 2003 |           245 |               515 |     1 | trained-initiate |
| 2004 |           115 |               630 |     1 | trained-initiate |
| 2005 |           140 |               770 |     1 | trained-initiate |
| 2006 |            60 |               830 |     1 | trained-initiate |
| 2007 |           270 |              1100 |     2 | experienced      |
| 2008 |           290 |              1390 |     2 | experienced      |
| 2009 |           205 |              1595 |     2 | experienced      |
| 2010 |           215 |              1810 |     2 | experienced      |
| 2011 |           115 |              1925 |     2 | experienced      |
| 2012 |           355 |              2280 |     3 | savvy            |
| 2013 |           290 |              2570 |     3 | savvy            |
| 2014 |           350 |              2920 |     3 | savvy            |
| 2015 |            45 |              2965 |     3 | savvy            |
#+TBLFM: $2='(calc-eval (format "vsum(%s)" (vconcat (org-lookup-all $1 '(remote(accomplishments,@2$1..@>$1)) '(remote(accomplishments,@2$3..@>$3))))))::$3=vsum(@2$2..@+0$2)::$4='(org-lookup-last $3 '(remote(levels,@2$1..@>$1)) '(remote(levels,@2$2..@>$2)) '>=);N::$5='(org-lookup-last $3 '(remote(levels,@2$1..@>$1)) '(remote(levels,@2$3..@>$3)) '>=);L

The TBLFM (table formula) line is very long, so let me break it down.

Points gained:

 (format "vsum(%s)"

This uses org-lookup-all to look up the value of the first column ($1) in the accomplishments table, from the second row to the last row @2..@>, looking in the first column ($1). It returns the values from the third column of the matching rows ($3). This is then passed through calc’s vsum function to calculate the sum.

Cumulative points: vsum(@2$2..@+0$2) is the sum of the second column $2 from the second row @2 to the current row @+0.

Level: This uses org-lookup-last to find the last value where the operator function returns true. In this case, testing the level from column $3 against each of the values in the levels table’s column $1 while the given level is greater than or equal to the value from levels. When it finds the last matching row, it returns the $2 second column from it. ;N means treat everything as a number.

org-lookup-first is like org-lookup-last, but it returns the first matching row.

Adjective: This one works like Level does, but it returns the value from column $3 instead. I found that it converted the return values to 0 if I used ;N, so I used ;L instead.

Passing data to R or other external programs

Of course, you’re not limited to things that Emacs can do. I wanted to summarize the data in graphs, so here’s what I did.

#+RESULTS: category_analysis

#+name: category_analysis
#+begin_src R :var data=accomplishments :exports both :results graphics :file quest_category.png :height 300
categories <- ddply(data, c("Category"), summarize, Points=sum(XP))
cat_sorted <- transform(categories, Category=reorder(Category, Points))
plot <- ggplot(data = cat_sorted, aes(x = Category, y = Points))
plot <- plot + geom_bar(stat="identity")
plot <- plot + geom_text(aes(label = Points, x = Category, y = Points + 10, hjust=0))
plot <- plot + scale_y_continuous(expand=c(0,70))
plot <- plot + coord_flip()

I like including source code in published pages for fellow geeks, but having the results come first gives people more context for the source block. So I named the source block using the #+name: directive and defined a #+RESULTS: directive before it. The source block used the :var argument to bring the data in from the accomplishments table. With R blocks, the data becomes available as a data frame that you can then do interesting things with. I used the :file argument to save the output to quest_category.png.

Those are a few ways that you can get data out of Org Mode tables and into Emacs Lisp, other Org Mode tables, or external programs. As I learn more about Org Mode, I find myself using it for more of the things that I used to use Microsoft Excel for – tracking, analyzing, and even graphing. I found it a little difficult to piece together what I needed to do from the manuals and examples on the Web, so I hope this explanation will help you (and that it’ll help me when I forget the syntax, as I’m sure I will). If you come up with something really neat that uses Org Mode tables, tell me what you’ve figured out!

Thinking about how to make better use of Yasnippet in my Emacs workflow

One of the awesome things that Karl Voit demonstrated in this Emacs Chat was how he used YASnippet and Org Mode to quickly create projects with several related tasks, such as when organizing a group to attend an event. He selected the snippet and filled in different fields like the artist name and the event date, and Emacs generated all these sub-tasks and e-mail templates with the information already filled in.

I’ve used YASnippet once or twice, but mostly I’ve been using org-capture and org-capture-templates instead. YASnippet looks like it might be more flexible because you can fill in fields in a non-linear order and you can re-evaluate Emacs Lisp expressions as you type.

Lots of people do cool things with YASnippet. For example, it’s popular for programming because it lets people quickly expand short sequences into longer syntax. Check out this Emacs Rocks episode on YASnippet to get a sense of what it can do. (Note: YASnippet has changed its naming convention slightly, so things like yas/text have been replaced with yas-text.) People have used it for e-mail templates and to fill in metadata for blog posts.

I’d like to use YASnippet more. Where can I integrate it into my workflow? Probably wherever checklists and templates make sense. I’ve been thinking about checklists and templates as a way to improve how I do things.

Checklists are good for making sure that you complete tasks more consistently, not missing any important steps. You can work faster when there’s a guide, since you don’t have to keep thinking of the next step each time. The simple act of checking things off can encourage you to put in more effort, since the list shows you your progress. It also makes it easier to remember to follow up.

Templates help you improve the structure of your work. You can make sure you cover all the important parts. If you use similar structures for many things, then people get used to finding information in the same logical places. This doesn’t mean that you’re stuck with cookie-cutter formats. You can still adapt the format to your needs.

I’m particularly interested in using checklists and templates to improve in three areas:

  • Programming: I’d like to write with less friction and use best practices like testing
  • Helping the Emacs community: Checklists can help me make sure I do all the steps to prepare for and make the most of Emacs Hangouts and Emacs Chats. They might also lower the intimidation factor so that I end up scheduling these more often.
  • Writing: I think checklists and templates will help me invest more time into developing thoughts, relationships, and structure.


As mentioned, YASnippet’s popular for programming. You can take advantage of existing collections of snippets for different programming modes (ex: AndreaCrotti’s collection), or you can define your own.

I’d like to get better at developing single-page applications using AngularJS, Twitter Bootstrap / Zurb Foundation, and NodeJS. YASnippet might let me quickly put together short applications and test suites. If I get my workflow smooth enough, I might even be able to do an app-a-week (or app-a-day) sprint for deliberate practice. There are often lots of fiddly little syntax or keyword things that I look up while writing code. While practising typing those things in again and again will help me memorize them, there’s also some value in automating that part with snippets so that I can focus on the core skills of designing and implementing small web applications.

YASnippet might also be able to help me use Org Mode to keep track of ideas for features or small web applications throughout the implementation process. I wonder if I should implement this using lots of subtasks or using TODO states with logs. TODO states might be easier to filter or visualize with the kanban package for Org Mode. Maybe I’ll try both approaches. In any case, checklists will help me remember to think about designs and tests before implementing the code, and maybe I can keep track of deployment notes, lessons learned, and follow-up tasks.

Emacs Community

I find checklists to be really helpful when setting up live videocasts. I’m usually too frazzled to think of all the steps I need to do at the last minute. Paper checklists are good because I can refer to them while keeping my screen ready to be recorded. Still, an Org Mode-based checklist (possibly with dynamic date fields and e-mail templates provided by YASnippet) might go a long way towards standardizing the before- and after-event process, and that might in turn reduce the friction enough for me to do more of them. Both Emacs Chats and Emacs Hangouts seem to be popular, so it would be good to get more of these on the go.

The process would be something like:

  1. Reach out to the person who’s going to be featured on the Emacs Chat, or at least one other person who’s willing to be there for the Emacs Hangout (so that I don’t end up talking to myself for the first ten minutes, which is Awkward)
  2. Figure out what will be discussed (for Emacs Chats)
  3. Set up a time, considering timezones
  4. Set up the Google+ event page
  5. Update the Google Calendar
  6. Post a notice on Twitter and on my blog (I’ve been forgetting to do this step)
  7. On the day of the event
    1. Do the last-minute push (I’ve been forgetting to do this as well)
    2. Create the Google Hangout on Air
    3. Set it up for Q&A
    4. Invite the other person in for Emacs Chats, or post the URL for Emacs Hangouts
    5. Host the video chat
    6. Remind people where the recording can be found
    7. Update the Google+ page with the link to the next thing
  8. Extract the MP3 from the video, change the properties, and upload it to
  9. Post a blog post with the embedded video, podcast audio, and quick notes
  10. Transcribe the video or pay for transcription
  11. Edit the transcript
  12. Update the post with the transcript
  13. Update the Google+ event page with the link to the transcript, post to social networks (I’ve been forgetting this)
  14. Update, too (yet another thing I’ve been forgetting)

I think it would be totally awesome to get to the point where I can call an Emacs Lisp function that would Do The Right Thing at that point, like posting to Twitter or using something like org-trello to make a Trello card and assign it to the person who does my transcriptions.


I’m getting the hang of using outlines to write (and I should post a video about this soon), but it might be even cooler if I can get the hang of writing with more structure. For example, Michael Hyatt posted this blog post checklist that he had been using with Evernote. I like it because:

  • The template asks you to be explicit about the post’s objective and subject.
  • It encourages you to add more illustrations, links, and stories.
  • It reminds you to take steps that you might otherwise skip, and you might spend several days revising the post.

I might not use it for every post, but it’s good to flesh out some ideas further, especially the ones where I think I’m onto something particularly interesting.

It would be even cooler if I could take advantage of YASnippet’s dynamic Emacs Lisp evaluation to remind me of relevant links from my blog post outline given the category. I remember playing around with the Remembrance Agent, which monitored a few hundred words around your cursor and brought up files that had similar words. Matching on category isn’t going to be anywhere as sophisticated, but it still might be a good way to refresh my memory. Even if I had a quick Emacs Lisp interactive function that read whatever category property I’d set (chosen from the org-refile-able targets) and displayed the section from my blog post index in another window, I think that would be a pretty neat start.

I tend to draft posts within my sharing outline (which I sporadically publish at When I’m done, I delete the subtree, sometimes replacing it with a link to the post to help me follow up on it in the future. This means losing that metadata, though. It might be interesting to keep the metadata so that I can review the goals and backstory of a blog post.

YASnippet can also help me keep track of the TODOs related to a post as well. For example, I might want to come up with sketches, tweet links, or follow up on ideas. If I use a YASnippet to plan my blog post in the first place, then I can create a TODO (possibly with a link back to the blog post) that I could leave in place or refile to the appropriate location in my regular Org Mode files.

I don’t think YASnippet dynamic fields persist after the file is saved and reloaded, though. How would that work if I need to change things? Maybe I can use multiple-cursors to mark all the matching text in the subtree, or do other clever things with it…

Next steps

Okay. It looks like setting up YASnippet for Emacs checklists would probably give me the quickest win. Programming is also pretty straightforward. Writing might be interesting too, if I can get the hang of working with that kind of structure. Let’s see how that goes. Once I figure out what those snippets will be like, I’ll post them on Github somewhere. =)

Is YASnippet part of your workflow? Have any thoughts, suggestions, or neat stories?

Org Mode publishing workflow for Sketched Books collection

I want to publish things in chunks that are bigger and more logical than blog posts, so I’ve been experimenting with my ZIP/PDF/EPUB/MOBI workflow.

Org Mode, Calibre, and Vagrant are terrific tools. Org Mode lets me write easy-to-modify source that I can export to different formats, like HTML and LaTeX (with the Beamer package), which lets me use PdfLatex to convert to PDF. Calibre converts HTML to EPUB and MOBI. Since tools can be difficult to set up on Windows, I use Vagrant to set up a virtual machine running Linux and I share my working directory with it.

multiple-cursors was so useful when I was wrangling the directory listing into the right format for Org. I’m glad I learned how to use it!

Here’s a Makefile I put together that simplifies the process for me:

all: index.html sketched-books.epub ebook.pdf

	rm -f *.dvi *.log *.nav *.out *.tex *.snm *.toc

distclean: clean
	rm -f Sketched\ index.html *.epub *.pdf *.mobi

Sketched\ *.png index.html
	(cd ..; zip sketched-books/ sketched-books/* -i *.css -i *.png -i *.html)

	emacs --batch -l build.el -f org-html-export-to-html --kill
	cp index.html index.tmp
	sed -e "s/org-ul/org-ul small-block-grid-3/" -e 's/div id="content"/div id="content" class="columns"/' -e 's/class="status"/class="status columns"/' index.tmp > index.html
	rm -f index.html~ index.tmp

	emacs --batch -l build.el -f org-html-export-to-html --kill

	montage *Sketched*.png -geometry -30-30 -thumbnail x400 -tile 6x5 cover.png

sketched-books.epub: ebook.html
	ebook-convert ebook.html sketched-books.epub --cover cover.png --authors "Sacha Chua" --language "English" ebook.html
	ebook-convert ebook.html --cover cover.png --authors "Sacha Chua" --language "English"

	emacs --batch -l build.el -f org-beamer-export-to-latex --kill

ebook.pdf: ebook.tex
	pdflatex ebook.tex
	cp ebook.pdf sketched-books.pdf
	rm ebook.pdf

And here’s a very simple build.el:

(require 'package)
(require 'ox-beamer)
(setq org-html-validation-link nil)
(setq org-export-with-section-numbers nil)
(setq backup-directory-alist '(("." . nil)))

This assumes I’ve already set up the environment by installing the latest Org from MELPA.

You can check out the and I use, too.

I’m not quite sure about the MOBI output yet. I have to test it on a Kindle, or in the app on my tablet. Most of the things display fine on my computer, though. Hooray!

Neat, huh? I want to get into the habit of making and also making it easy for me to update these things. You can check out the results at .

Someday I might even figure out how to use the Gumroad API to publish updated resources automatically. Wouldn’t that be neat? In the meantime, I’ll just have to replace them myself.

I like giving people the ability to choose which files to download. If I get annoyed with replacing multiple files, though, I might change this to one large ZIP that has the images, PDF, EPUB, and MOBI.

View the source on Github

Emacs Chat: Karl Voit

Org Mode, Memacs, lazyblorg, .emacs, Yasnippet, tags . , ,

Check out Karl’s notes for more details. (Or at least, you can check them out when his server is up again!)

Thanks, Karl!

Got an interesting Emacs workflow? Please share. =) Happy to bring on more people for Emacs Chats. Also, check out the upcoming Emacs Hangout on Dec 17 (8 PM Toronto)!

Check out TRANSCRIPT here!

Improving how I organize notes with Org Mode

Let me think about how I organize my Org Mode files, and how I might improve that. =)

Separate files

You can put different things in different files, of course. I use a few large Org files instead of lots of small ones because I prefer searching within files rather than searching within directories. Separate files make sense when I want to define org-custom-agenda-commands that summarize a subset of my tasks. No sense in going through all my files if I only want the cooking-related ones.

What would help me make better use of lots of files? I can practise on my book notes, which I’ve split up into one file per book. It’s easy enough to open files based on their titles (which I put in my filenames). But I don’t have that overall sense of it yet. Maybe #+INDEX: entries, if I can get them to generate multiple hyperlinks and I have a shortcut to quickly grep across multiple files (maybe with a few lines of context)? Maybe a manual outline, an index like the one I’ve been building for my blog posts? I can work with that as a starter, I think.

Okay. So, coming at it from several directions here:

  • A manual map based on an outline with lots of links, with some links between topics as well – similar to my blog outline or to my evil plans document
  • Quick way to grep? helm-do-grep works, but my long filenames are hard to read.
  • Links between notes and to blog posts
  • TODOs, agenda views


Within each file, outlines work really well. You can create any number of headings by using *, and you can use TAB to collapse or expand headings. You can promote or demote subtrees, move them around, or even sort them.

I generally have a few high-level headings, like this:

* Projects
** One heading per current project
*** TODO Project task
* Reference
Information I need to keep track of
* Other notes
* Tasks
** TODO Lots of miscellaneous tasks go here
** TODO Lots of miscellaneous tasks go here
** TODO Lots of miscellaneous tasks go here

Every so often, I do some clean-up on my Org files, refiling or archiving headings as needed. This makes it easier to review my current list of projects. I keep this list separate from the grab-bag of miscellaneous tasks and notes that might not yet be related to particular projects.

I use org-refile with the C-u argument (so, C-u C-c C-w) to quickly jump to headings by typing in part of them. To make it easy to jump to the main headings in any of my agenda files, I set my org-refile-targets like this:

(setq org-refile-targets '((org-agenda-files . (:maxlevel . 6))))

How can I get better at organizing things with outlines? My writing workflow is a natural place to practise. I’ve accumulated lots of small ideas in my writing file, so if I work on fleshing those out even when I don’t have a lot of energy–breaking things down into points, and organizing several notes into larger chunks–that should help me become more used to outlines.


In addition to organizing notes in outlines, you can also use tags. Tags go on the ends of headings, like this:

** Heading title     :tag:another-tag:

You can filter headings by tags using M-x org-match-sparse-tree (C-c \) or M-x org-tags-view (C-c a m).

Tags are interesting as a way to search for or filter out combinations. I used tags a lot more before, when I was using them for GTD contexts. I don’t use them as much now, although I’ve started tagging recipes by main ingredient and cooking method. (Hmm, maybe I should try visualizing things as a table…) I also use tags to post entries under WordPress blog categories.

How can I get better at using tags? I can look for things that don’t lend themselves well to outlines, but have several dimensions that I may want to browse or search by. That’s probably going to be recipe management for now. If I figure out a neat way to add tags to my datetree journal notes and then visualize them, that might be cool too.


Org Mode links allow me to refer not only to web pages, files, headings, and text searches, but to things like documentation or even executable code. When I find myself jumping between places a lot, I tend to build links so that I don’t have to remember what to jump to. My evil plans Org Mode file uses links to create and visualize structure, so that’s pretty cool, too. But there’s still a lot more that I could probably do with this.

How can I use links more effectively? I can link to more types of things, such as Lisp code. I can go back over my book notes and fill in the citation graph out of curiosity. Come to think of it, I could do that with my writing as well. My writing ideas rarely fit in neat outlines. I often feel like I’m combining multiple threads, and links could help me see those connections.

In addition to explicit links, I can also define “radio targets” that turn any instance of that text into a hyperlink back to that location. Only seems to work within a single file, though, and I’ve never actually used this feature for something yet.


You can set various properties for your Org Mode subtrees and then display those properties in columns or filter your subtrees by those properties. I’ve used Effort to keep track of effort estimates and I have some agenda commands that use that. I also use a custom Quantified property to make it easier to clock into tasks using my Quantified Awesome system.

I could track energy level as either tags or properties. Properties allow for easier sorting, I think. Can I define a custom sort order, or do I have to stick with numeric codes? Yeah, I can sort by a custom function, so I can come up with my own thing. Okay. That suggests a way I can learn to use properties more effectively.

There are even more ways to organize Org Mode notes in Emacs (agenda views, exports, etc.), but the ones above look like good things to focus on. So much to try and learn!

Using Org Mode to keep a process journal

I (re)started keeping a journal in Org Mode – chronologically-ordered snippets on what I’m doing, how I’m doing things, and how I’m thinking of improving. I’d mentioned the idea previously on my blog. In this post, I want to share the workflow and configuration that makes it easier for me to log entries.

When I’m on my personal computer, I use Org Mode’s org-capture command to quickly capture notes. I’ve bound org-capture to C-c r, a remnant from the way I still think of it as related to the even older org-remember and remember functions. Anyway, org-capture allows you to define several org-capture-templates, and it will display these templates in a menu so that you can choose from them when creating your note.

Here’s the template I’ve been using for my captures:

(setq org-capture-templates
      '(;; other entries
        ("j" "Journal entry" plain
         (file+datetree+prompt "~/personal/")
         "%K - %a\n%i\n%?\n")
        ;; other entries

This stores a link to the currently-clocked task and to whatever context I was looking at when I started the journal entry. It also copies the active region (if any), then positions my cursor after that text. Unlike the default template, this template does not include an Org heading. That way, I don’t have to think of a headline, and I can also just clear the buffer and close the window without adding lots of half-filled-in entries in my journal.

The file+datetree+prompt keyword means that the entries will be stored in ~/personal/ in an outline corresponding to the year, month, and day that I specify. This makes it easy to write an entry for today or for any particular date. For example, I often find myself adding more notes for the previous day (-1) because of something I remembered.

I’m thinking of making Fridays my day for reviewing what I’ve learned and writing up more notes. With the date-tree structure, it’s easy to review my notes by day and look for little things to harvest.

If I know I want to revisit something, I can also add a TODO right in the journal entries using the regular Org syntax or as a keyword that I can search for. If it’s a separate heading (ex: *** TODO Take over the world), I can use org-refile to move it to its proper project.

When I want to flesh out those rough notes into a blog post, I can copy the entry to my blog post outline, fill in the details, and then use org2blog/wp-post-subtree to post it to WordPress. Alternatively, I might edit my rough notes in-place to make them ready to post, and then post them directly from that buffer (possibly changing the heading).

Since I’m not always on my personal computer, I need to be able to pull in notes from elsewhere. I can add quick notes to Evernote on my phone. So far, I’ve been okay with copying snippets manually. If I find that I’m creating lots of notes, though, I might look into reusing the code that I have for building my weekly review list from Evernote notes.

Time-wise, I find that spending 15-30 minutes at the end of the day helps me braindump the key points. If I take little writing breaks throughout the day, that helps me capture more details (especially in terms of development or technical troubleshooting). Re-reading my notes is part of my regular weekly review process, so it’s pretty quick (plus a little more time if I’m writing longer blog posts).

That’s how Org Mode helps me keep a process journal. It’s great to be able to quickly write notes in the same thing you use to do everything else, and to tweak your workflow. Whee!