Sorting an org-table numerically, ignoring commas (org-table-sort-lines)

Question

I have the following org-table:

| Row # | Facility                 | Recycled Off-site |
|-------+--------------------------+-------------------+
|     1 | 1234, 12th st, jamestown |           216,574 |
|     2 | 1234, 12th st, jamestown |               760 |
|     3 | 1234, 12th st, jamestown |                 0 |
|     4 | 1234, 12th st, jamestown |               464 |
|     5 | 1234, 12th st, jamestown |                 0 |
|     6 | 1234, 12th st, jamestown |             2,280 |

and I want to sort the table by the last column. I perform M-x org-table-sort-lines n to sort the column numerically, and here is my output:

| Row # | Facility                 | Recycled Off-site |
|-------+--------------------------+-------------------+
|     3 | 1234, 12th st, jamestown |                 0 |
|     5 | 1234, 12th st, jamestown |                 0 |
|     6 | 1234, 12th st, jamestown |             2,280 |
|     1 | 1234, 12th st, jamestown |           216,574 |
|     4 | 1234, 12th st, jamestown |               464 |
|     2 | 1234, 12th st, jamestown |               760 |

Unfortuntaly, the commas are a thousands place holder, not a decimal. Is there a setting where I can have org-table sort the rows using the commas as a thousands placeholder? For example, here is my desired result:

| Row # | Facility                 | Recycled Off-site |
|-------+--------------------------+-------------------+
|     3 | 1234, 12th st, jamestown |                 0 |
|     5 | 1234, 12th st, jamestown |                 0 |
|     4 | 1234, 12th st, jamestown |               464 |
|     2 | 1234, 12th st, jamestown |               760 |
|     6 | 1234, 12th st, jamestown |             2,280 |
|     1 | 1234, 12th st, jamestown |           216,574 |

Answer 1

The most simple solution is to define your own key function that removes the comma before it transforms the string to a number for sorting:

(defun string-to-number-remove-comma (str)
  "Convert string STR to number but remove commas beforehand."
  (string-to-number (replace-regexp-in-string "," "" str)))

This function can be specified when you call org-table-sort-lines with the type letter f.

If you do that with point in the third column of your table you get the sorting in the third table of your description.

---

You can also use the following advice of org-table-sort-lines. One could make the regular expression "," and its replacement "" to options. Therewith one would be more flexible. But, for your purpose the advice below is sufficient. Note, that I need with-current-fset more often -- therefore it is a separate macro.

(defmacro with-current-fset (backup symbol function &rest body)
  "Save SYMBOL's function definition in BACKUP,
set it temporarily to FUNCTION, and execute BODY.
After the execution of BODY the function definition of SYMBOL is restored."
  (declare (debug (sexp sexp function-form body)) (indent 3))
  `(let ((,backup (symbol-function ',symbol)))
     (unwind-protect
     (progn
       (fset ',symbol ,function)
       ,@body)
       (fset ',symbol ,backup))))

(defadvice org-table-sort-lines (around ignore-comma activate)
  "Ignore commas when sorting."
  (with-current-fset
      string-to-number-original
      string-to-number
      (lambda (str) (funcall string-to-number-original (replace-regexp-in-string "," "" str)))
    ad-do-it))

---

If you do not like both solutions above you can also use an org formula to create a fourth column with commas removed from the numbers. You can then sort with respect to this column. This is only an option if you do not mind the extra column.

Use the table formula below, iterate the table, and apply org-table-sort-lines with type n to the last column.

| Row # | Facility                 | Recycled Off-site |        |
|-------+--------------------------+-------------------+--------|
|     3 | 1234, 12th st, jamestown |                 0 |      0 |
|     5 | 1234, 12th st, jamestown |                 0 |      0 |
|     4 | 1234, 12th st, jamestown |               464 |    464 |
|     2 | 1234, 12th st, jamestown |               760 |    760 |
|     6 | 1234, 12th st, jamestown |             2,280 |   2280 |
|     1 | 1234, 12th st, jamestown |           216,574 | 216574 |
#+TBLFM: $4='(replace-regexp-in-string "," "" $3)

Answer 2

I don't have a very good answer for you, but org-mode is just calling string-to-number on the cell when it sorts it. string-to-number just ignores anything after the first non-digit character, so "216,574" is converted to 216. I don't know if this is always the case; internally it's calling strtoumax (or atof if it looks like a floating-point number) which is likely to change its behavior in different locales.

You can see what language environment you're in by running M-x describe-language-environment RET. You can change it with set-language-environment, or by setting the LANG environment variable before you start emacs.