Category Archives: geek

Emacs Hangout June 2015

Times may be off by a little bit, sorry!

Boo, I accidentally browsed in the Hangouts window before copying the text chat, so no copy of the text chat this time… =|

Finding missing dates in PostgreSQL

My analytics numbers were way off from what I expected them to be. When I did a day-by-day comparison of my numbers and the reference set of numbers, I realized that a few weeks of data were missing from the year of data I was analyzing – a couple of days here, two weeks there, and so on. I manually identified the missing dates so that I could backfill the data. Since this was the second time I ran into that problem, though, I realized I needed a better way to catch this error and identify gaps.

Initially, I verified the number of days in my PostgreSQL database table with a SQL statement along the lines of:

SELECT year, month, COUNT(*) AS num_days FROM
(SELECT date_part('year', day_ts) AS year,
 date_part('month', day_ts) AS month,
 day_ts FROM (SELECT DISTINCT day_ts FROM table_with_data) AS temp) AS temp2
ORDER BY year, month

I checked each row to see if it matched the number of days in the month.

It turns out there’s an even better way to look for missing dates. PostgreSQL has a generate_sequence command, so you can do something like this:

SELECT missing_date
FROM generate_series('2015-01-01'::date, CURRENT_DATE - INTERVAL '1 day') missing_date
WHERE missing_date NOT IN (SELECT DISTINCT day_ts FROM table_with_data)
ORDER BY missing_date

Neat, huh?

Using your own Emacs Lisp functions in Org Mode table calculations: easier dosage totals

UPDATE 2015-06-17: In the comments below, Will points out that if you use proper dates ([yyyy-mm-dd] instead of yyyy-mm-dd), Org will do the date arithmetic for you. Neato! Here’s what Will said:

Hi Sacha. Did you know you can do date arithmetic directly on org’s inactive or active timestamps? It can even give you an answer in fractional days if the time of day is different in the two timestamps:

| Start                  | End                    | Interval |
|------------------------+------------------------+----------|
| [2015-06-16 Tue]       | [2015-06-23 Tue]       |        7 |
| <2015-06-13 Sat>       | <2015-06-15 Mon>       |        2 |
| [2015-06-10 Wed 20:00] | [2015-06-17 Wed 08:00] |      6.5 |
#+TBLFM: $3=$2 - $1 

Here’s my previous convoluted way of doing things… =)
—-

I recently wrote about calculating how many doses you need to buy using an Org Mode table. On reflection, it’s easier and more flexible to do that calculation using an Emacs Lisp function instead of writing a function that processes and outputs entire tables.

First, we define a function that calculates the number of days between two dates, including the dates given. I put this in my Emacs config.

(defun my/org-days-between (start end)
  "Number of days between START and END.
This includes START and END."
  (1+ (- (calendar-absolute-from-gregorian (org-date-to-gregorian end))
         (calendar-absolute-from-gregorian (org-date-to-gregorian start)))))

Here’s the revised table. I moved the “Needed” column to the left of the medication type because this makes it much easier to read and confirm.

| Needed | Type         | Per day |      Start |        End | Stock |
|--------+--------------+---------+------------+------------+-------|
|     30 | Medication A |       2 | 2015-06-16 | 2015-06-30 |     0 |
|      2 | Medication B |     0.1 | 2015-06-16 | 2015-06-30 |   0.2 |
#+TBLFM: @2$1..@>$1='(ceiling (- (* (my/org-days-between $4 $5) (string-to-number $3)) (string-to-number $6)))

C-c C-c on the #+TBLFM: line updates the values in column 1.

@2$1..@>$1 means the cells from the second row (@2) to the last row (@>) in the first column ($1).  '  tells Org to evaluate the following expression as Emacs Lisp, substituting the values as specified ($4 is the fourth column’s value, etc.).

The table formula calculates the value of the first column (Needed) based on how many you need per day, the dates given (inclusive), and how much you already have in stock. It rounds numbers up by using the ceiling function.

Because this equation uses the values from each row, the start and end date must be filled in for all rows. To quickly duplicate values downwards, set org-table-copy-increment to nil, then use S-return (shift-return) in the table cell you want to copy. Keep typing S-return to copy more.

This treats the calculation inputs as strings, so I used string-to-number to convert some of them to numbers for multiplication and subtraction. If you were only dealing with numbers, you can convert them automatically by using the ;N flag, like this:

| Needed | Type         | Per day | Days | Stock |
|--------+--------------+---------+------+-------|
|      6 | Medication A |       2 |    3 |     0 |
|      1 | Medication B |     0.1 |    3 |   0.2 |
#+TBLFM: @2$1..@>$1='(ceiling (- (* $3 $4) $5)));N

Providing values to functions in org-capture-templates

Over at the Emacs StackExchange, Raam Dev asked how to define functions for org-capture-templates that could take arguments. For example, it would be useful to have a function that creates a Ledger entry for the specified account. Functions used in org-capture-templates can’t take any arguments, but you can use property lists instead. Here’s the answer I posted.

You can specify your own properties in the property list for the template, and then you can access those properties with plist-get and org-capture-plist. Here’s a brief example:

Here’s a brief example:

(defun my/expense-template ()
  (format "Hello world %s" (plist-get org-capture-plist :account)))
(setq org-capture-templates '(("x" "Test entry 1" plain
                               (file "~/tmp/test.txt")
                               (function my/expense-template)
                               :account "Account:Bank")
                              ("y" "Test entry 2" plain
                               (file "~/tmp/test.txt")
                               (function my/expense-template)
                               :account "Account:AnotherBank")))

I hope that helps!

Thinking about problem-solving and sequencing

We’ve been helping J- with her culminating project in Grade 11 programming class, a text-based blackjack game. She was getting stuck because she wasn’t comfortable enough with Java or programming. She didn’t know where to start or what to do. I didn’t write the program for her (and that was never on the table, anyway), but I offered to guide her through the process. More experienced learners can do that kind of planning on their own, but when you’re new to a subject (and especially if you’re under time pressure), it helps to have that kind of scaffolding.

The first thing I did was to properly set up Eclipse on her computer. She had already tried setting it up, but it kept quitting with a cryptic error message. I realized that she didn’t have the Java software development kit installed. Once we added that, Eclipse started working.

Then I set up a sequence of tiny, tiny steps that she could implement with a little guidance. This was not the same sequence of events that would be in the final game: betting, shuffling, dealing, scoring, and so on. Instead, I focused on the steps she could build on bit by bit. It went something like this:

  1. Display a random number.
  2. Initialize the deck array based on the face array and suit array J- had already set up. This would contain strings like “ace of hearts”.
  3. Print the deck array.
  4. Set up a parallel array of card values, and print that as well. This would contain integers such as 1 for ace.
  5. Shuffle the deck by swapping each card with a random card, swapping the same card’s value as well.
  6. Write a function that takes an array of card values and returns the value of the hand.
  7. Modify the function to take aces into account, since aces could be either 1 or 11.
  8. Handle the initial dealing of two cards each by keeping track of the top card and updating a status array.
  9. Display the cards for a specified hand based on the status array.
  10. Update the hand value function to take the status array into account.
  11. Deal cards to the player as requested.
  12. Check if the player has lost.
  13. Follow the rules for dealing additional cards to the dealer.
  14. Check if the dealer has lost.
  15. Figure out who won that round.
  16. Read the bet.
  17. Update the bet after the player has won or lost the round.
  18. Check if the player has doubled their money or lost all their money.
  19. Add more error-checking.
  20. Add more flexibility.

2015-06-11a Blackjack implementation sequence -- index card #sequencing #problem-solving

2015-06-11a Blackjack implementation sequence – index card #sequencing #problem-solving

This sequence meant that she could write and test the less-interactive parts first (preparing the deck, shuffling the cards, calculating the score) before slowing down her compile-run-test cycle with input. It also let her work on the simplest parts first without writing a lot of redundant code and with just a little prompting from me.

Instead of this zigzag path, we could have followed the chronological flow of the program, especially if I introduced her to the practice of stubbing functions until you’re ready to work on them. This shuffled order felt a bit better in terms of demonstrable progress, and she seems to have been able to follow along with the construction process. In retrospect, the chronological flow might have been easier for her to learn and apply to other projects, though, since breaking down and shuffling the order of tasks is a skill that programming newbies probably need a while to develop.

Anyway, helping J- with her project got me thinking about how I work my way through programming challenges on my personal projects and in my consulting. I tend to try to figure things out by myself instead of asking mentors or the Internet. I also tend to write things in a bottom-up instead of top-down order: starting with small things I can write and test quickly, and then gradually building more elaborate processes around them.

2015-06-09e What do I mean by sequencing -- index card #learning #problem-solving #sequencing

2015-06-09e What do I mean by sequencing – index card #learning #problem-solving #sequencing

I think of the process of breaking down tasks and organizing them into a useful order as “sequencing”, which is part of the general domain of problem-solving. There’s probably an official term for it, but until I find it, that’s the term makes sense to me. When I was teaching undergraduate computer science, I noticed that students often struggled with the following aspects:

  • Imagining a good goal, if they were doing a self-directed project (not too big, not too small, etc.)
  • Fleshing out their goal into specifications
  • Identifying possible chunks along the path
  • Breaking those chunks down into smaller chunks as needed
  • Figuring out how to bridge those chunks together
  • Prioritizing chunks
  • Re-evaluating paths, progress, and goals as they learned more
  • Managing motivation
  • Finding resources
  • Translating or adapting those resources to what they needed to do
  • Figuring out what they could start with (what they already knew, or that first hand-hold on a chunk)

2015-06-09h Sequencing challenges -- index card #learning #problem-solving #sequencing #challenges

2015-06-09h Sequencing challenges – index card #learning #problem-solving #sequencing #challenges

I sometimes have a hard time with these aspects too, especially when I’m learning a new toolkit or language. I think I’m getting better at picking ridiculously tiny steps and celebrating that progress instead of getting frustrated by blocks. When I can’t figure tiny steps out, I know that going through tutorials and reading other people’s code will help me build my vocabulary. That way, I can get better at finding resources and planning chunks. I have a better idea of what’s out there, what things are called, and maybe even what’s probably harder and what’s probably easier.

2015-06-09f How do I develop my own sequencing skills -- index card #learning #problem-solving #sequencing

2015-06-09f How do I develop my own sequencing skills – index card #learning #problem-solving #sequencing

Programming gives me plenty of opportunities to develop my sequencing skills. By writing notes with embedded code snippets and TODOs, I can break large chunks down into smaller chunks that fit in my working memory. Sometimes I read programming documentation or source code without any particular project in mind, just collecting ideas and expanding my imagination. If I focus on writing tiny chunks that start off as “just good enough” rather than making them as elaborate as I can, that lets me move on to the rest of the program and get feedback faster. Saving snippets of unused or evolving code (either manually in my notes or automatically via a version contro system) lets me reuse them elsewhere.

I imagine that getting even better at sequencing might involve:

  • Sharing intermediate or neighbouring products to get feedback faster and create more value
  • Figuring out (and remembering!) sequences for getting into various areas such as Angular or D3
  • Becoming fluent with many styles of sequencing, like working top-down instead of just bottom-up
  • Building reliable automated tests along the way, even as my understanding of the project evolves

What about helping other people get better at sequencing?

2015-06-09g How can I help other people develop sequencing skills -- index card #learning #problem-solving #sequencing

2015-06-09g How can I help other people develop sequencing skills – index card #learning #problem-solving #sequencing

  1. If I share a pre-defined sequence, that helps people with less experience follow a possibly more efficient path.
  2. If I customize the sequence for someone I’m helping, that’s even more useful.
  3. Talking about how we’re customizing the sequence exposes sequencing as a skill.
  4. Guiding someone through the breakdown of a small chunk (from a task to pseudocode, for example) can help them get the hang of turning things into specifics.
  5. Guiding someone through the breakdown of a larger chunk into smaller chunks helps them think of larger chunks as doable.
  6. Once someone has identified a few chunks, I can help with prioritizing them based on effort, relationship between chunks, etc.
  7. In preparation for independent learning, it’s good to practice the skill of figuring out those prioritization factors: small experiments, research, and so on.
  8. And at some point (woohoo!), I can help someone reflect on and tweak a plan they developed.

I’m not yet at the point of being able to do even step 1 well, but maybe someday (with lots of practice)!

Since this is a super-useful skill and not everyone’s into programming, it might be interesting to look around for non-programming situations that can help develop sequencing skills.

2015-06-09i Non-programming examples of sequencing -- index card #learning #problem-solving #sequencing

2015-06-09i Non-programming examples of sequencing – index card #learning #problem-solving #sequencing

On the passive side, it’s interesting to learn about how things work or how they’re made. Better yet, you can actively practise sequencing with math problems, video games with quests… Writing is like this too, if you think of key points and use tools such as outlines or index cards. Making things involves imagining what you want and figuring out lots of small steps along the way. Life is filled with little opportunities for everyday problem-solving.

I’d love to learn more about this. Chunk decomposition? Problem-solving order? Problem decomposition? Step-wise refinement? I’m more comfortable with bottom-up problem-solving, but top-down solving has its merits (focus, for one), so maybe I can practice that too. Using both approaches can be helpful. (Ooh, there’s a Wikipedia article on top-down and bottom-up design…) Time to check out research into the psychology of problem-solving!

Using Emacs Org Mode tables to calculate doses to buy

I got tired of manually calculating how many I needed to buy based on a daily protocol and how many I had in stock, so I wrote a little bit of Emacs Lisp to figure it out. You can specify the type, daily dose, start and end dates (inclusive; defaults to the last specified date if blank), and how many you have in stock.

First, define a table of this form, and give it a name.

#+NAME: input
| Type         | Per day |      Start |        End | Stock |
|--------------+---------+------------+------------+-------|
| Medication A |       2 | 2015-06-09 | 2015-06-16 |     5 |
| Medication B |       1 |            |            |     0 |
| Medication C |     0.1 | 2015-06-12 | 2015-06-16 |   0.2 |
Type Per day Start End Stock
Medication A 2 2015-06-09 2015-06-16 5
Medication B 1 0
Medication C 0.1 2015-06-12 2015-06-16 0.2

To call the code from the bottom of this post, use something like:

#+CALL: calculate-meds-needed(meds=input) :hlines yes :colnames yes
Type Total In stock Needed
Medication A 16 5 11
Medication B 8 0 8
Medication C 0.5 0.2 1

Here’s the code that processes it:

#+name: calculate-meds-needed :var meds=meds :colnames yes :hlines yes
#+begin_src emacs-lisp
(let (start end)
  (append
   (list (list "Type" "Total" "In stock" "Needed"))
   (list 'hline)
   (sort (delq nil (mapcar
                    (lambda (row)
                      (unless (or (eq row 'hline) (string= (elt row 0) "Type"))
                        (let (total)
                          (setq start (if (string< "" (elt row 3)) (elt row 3) start)
                                end (if (string< "" (elt row 2)) (elt row 2) end)
                                total (* (elt row 1)
                                         (- (calendar-absolute-from-gregorian (org-date-to-gregorian start))
                                            (calendar-absolute-from-gregorian (org-date-to-gregorian end))
                                            -1)))
                          (list
                           (elt row 0)
                           total
                           (elt row 4)
                           (max 0 (ceiling (- total (elt row 4))))))))
                    meds)) (lambda (a b) (string< (car a) (car b))))))
#+end_src