Converting fractions to decimal values
November 12, 2017 at 10:35 PM by Dr. Drang
Recently, I’ve been getting Excel spreadsheets from clients that contain measurements in inches and/or fractions of an inch. In some cases, the cells with these measurements actually contain numbers but are displayed as fractions using one of the many formatting options Excel has.
In these cases, it’s easy for me to change the formatting to a standard decimal representation before exporting the sheet as a CSV file.
Often, though, the measurements are entered as text. This is done by prefixing the data with a single quote mark, e.g.,
'2 5/16
or maybe
'2-5/16
These need to be converted into a decimal representation before exporting to CSV, so Pandas will recognize them as numbers rather than strings.1
To handle this situation, I created a Keyboard Maestro called Floatize. To use Floatize, I
- Select the column of data I want to convert.
- Copy it to the clipboard.
- Run Floatize, which converts the clipboard from a fractional representation to decimal.
- Paste the result back into the column.
Here’s the macro:
It takes the clipboard, sends it to a script as standard input, and then puts the output back into the clipboard. Here’s the Python script it runs:
python:
1: #!/usr/bin/env python
2:
3: from fractions import Fraction
4: import re
5: import sys
6:
7: def floatize(s):
8: if s in ['', 'n/a', 'none']:
9: return ''
10: else:
11: try:
12: num = float(s)
13: except ValueError:
14: t = re.sub(r'(.*\d) *- *(\d.*)', r'\1 \2', s)
15: if t[0] == '-':
16: num = -float(sum(Fraction(x) for x in t[1:].split(None, 1)))
17: else:
18: num = float(sum(Fraction(x) for x in t.split(None, 1)))
19: return "{:f}".format(num)
20:
21: for line in sys.stdin.read().splitlines():
22: item = line.strip().lower()
23: print floatize(item)
The bulk of the fraction parsing is done by the fractions module in either Line 16 or Line 18. The rest of the script is an attempt to manipulate the oddball types of data I get into a format that fractions understands. Here’s what it can handle:
- Blank,
n/aornoneentries get turned into an empty string (Lines 8–9). Note that entries are converted to lower case in Line 22, before being passed to thefloatizefunction, so this handlesN/AandNone, too. - Entries already in decimal format get converted to a
float(Line 12) and then output in decimal format (Line 19). This is basically a pass-through. - Entries with a dash between the whole and fractional parts get the dash removed (Line 14).
- Negative entries (Line 15) have their whole and fractional parts added together with the sign reversed (Line 16), and then output in decimal format (Line 19).
- Positive entries have their whole and fractional parts added together (Line 18), and then output in decimal format (Line 19).
- Entries that don’t look like any of the above cause an error.
The macro works perfectly in Numbers and BBEdit, but not in Excel. For some reason, nothing happens to the clipboard if I run the Floatize macro while Excel is the active application. I can, however, copy the column of fractions, activate another app, run Floatize, switch back to Excel and paste the converted clipboard. Another example of Excel not acting like a proper Mac app and another reason I move data out of Excel as quickly as I can.
Despite its inability to work inside Excel, this macro has been very handy over the past couple of weeks. I’ve used it to clean up several data sets that were loaded with fractions.
By the way, I will block anyone who tweets that I wouldn’t have this problem if I used the metric system.
Update Nov 13, 2017 10:04 PM The Twitter consensus is my problem in using this macro in Excel stems from Excel’s clipboard dumbassery. It apparently uses a private clipboard instead of the system clipboard until you bring another application to the front. Hence the macro’s inability to convert the clipboard unless I switched away from Excel.
Jimmy Hartington suggested this workaround:
@drdrang Excel does something weird with the clipboard as you noticed.
Try this. Copy in Excel. Press Escape. Run the macro.
By pressing Escape I think Excel stops messing with the clipboard.
— Jimmy Hartington (@jimmyhartington) Nov 12 2017 11:06 PM
This works perfectly and pointed me in the direction to make a better version of the macro, one that does all the copying, converting, and pasting:
Note the conditional action that simulates the pressing of the Escape key only if Excel is the front application. This is necessary because Escape causes Numbers to deselect the column, which screws up the paste action at the end of the macro.
With this macro I can now select the column that needs converting and just press ⌃⌥⌘F to do the conversion in a single step. Thanks, Jimmy!
-
There are probably ways to get Pandas to do the conversion as it imports the data, but I feel more comfortable—more in control—if I do it ahead of time. ↩
