Converting fractions to decimal values

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.

Excel cell formatting options

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


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

Here’s the macro:

Floatize Keyboard Maestro 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:

 1:  #!/usr/bin/env python
 3:  from fractions import Fraction
 4:  import re
 5:  import sys
 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)
21:  for line in
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:

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 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:

New Floatize Keyboard Maestro macro

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!

  1. 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. ↩︎

A modest proposal

With Apple sales and graphing on my mind, I’d like to make a small complaint and suggestion to the folks at MacStories: some of your quarterly graphs could use a little scrubbing; they’d be much easier on the eyes if they were cleaned up a bit.

I’m talking about the ones that look like this:

MacStories unit sales graph

My complaint is with the clutter along the x-axis. That uniform gray mass of text detracts from the data above it. And although putting a label at each quarter seems to provide more information to the reader, the reader actually gets less out of it because it’s too hard to read. The axis would be improved if it had just yearly Q1 labels. Maybe even one label every other year. Fewer labels would mean they could be turned back horizontal, which would be easier to read and would give more vertical space for the data.

I suspect every data point is labeled because that’s the way Numbers wants to do it, and MacStories’ automated system for generating these graphs follows the Numbers defaults. But if you were making these graphs by hand, you’d never label the x-axis this way. An automated system shouldn’t, either.

On the Mac, Numbers has a way to skip labels on the x-axis by selecting Custom Category Intervals from the Category Labels popup menu and then choosing how often to put the labels.

Mac Numbers axis options

Sadly, I can’t find that option in the iOS version. Maybe it’s tucked away in a less obvious place.

iOS Numbers axis options

Personally, I’m not a fan of either version of Numbers when it comes to making graphs because I like way more control than they give. But Numbers (like most graphing programs and libraries) can make nice graphs if you’re willing to break away from its defaults.

I don’t really want to pick on MacStories. Their graphs are as good as, if not better than, what you typically find in blogs and news sites. Most graphs, unfortunately, are based on the default settings of the software that made them, and come out looking a little clumsy.

I read MacStories because it has stylish writing. I’d like its graphs to be just as stylish.

Update Nov 7, 2017 8:55 PM
I was informed on Twitter by two authoritative sources (this one and this one) that the MacStories graphs are made with Excel, not Numbers. I haven’t used Excel for graphing since the mid-90s, but even then it had more customization options than Numbers has now. So I’m crossing my fingers for cleaner x-axes in January’s graphs.

Apple sales graphs and the iPhone 7

Last week Apple released its 2017 Q4 (which everyone else’s calendar says is 2017 Q3) sales and revenue figures, which means a boatload of graphs from all your fave rave Apple-oriented bloggers. I’m a few days late, as usual, despite having long ago written scripts to generate these graphs.

I don’t make as many graphs as everyone else, partly because I’m lazy, but mainly because don’t care about revenue. My concern is the popularity of the devices I use, not how expensive they are. I want third-party developers to keep writing apps for them, and that means there has to be a market for them out there.

Here’s the summary graph, with the sales figures for the three main Apple products. The dots are the quarterly sales and the lines are the four-quarter moving averages.

Apple sales

The iPhone dominates the scale of this graphs, so it’s helpful to also see the iPad and Mac on their own graphs.

iPad sales

Mac sales

The good news is the second straight quarter of year-over-year increase in iPad sales after its long, well-documented slide since 2013. Additional good news is the Mac’s slow but consistent rise over the past year—four straight quarters of year-over-year sales increases. Imagine how much better the figures would be if people really liked the Touch Bar.

I think its fair to say, though, that the iPhone 7 was something of a dud. It had a good start, but its non-intro quarters had almost exactly the same sales as the 6S had. Look at the final three quarters of each.

Apple sales annotated

As I’ve argued before, the iPhone 6S sales looked lackluster only in comparison to the gangbusters popularity of the iPhone 6. If one ignored the year of the 6, the 6S’s sales were more or less on the same upward trend as the previous three editions. The iPhone 7 was on that same trend for its first quarter but went dead flat after that.

Apple’s report in January will be very interesting.

Another one-off Keyboard Maestro macro

Do you use Keyboard Maestro (or AppleScript or whatever) for one-time, throwaway macros as often as you should? I know I don’t, but I did put one together a couple of days ago and used a feature I’d never tried before.

I had one of those recalcitrant PDFs that I often get from clients. This one was 25–30 pages long, each an E-sized floor plan drawing for a building. The drawings were all black-and-white, but the PDF had color annotations added. I needed to add my own annotations to most of the pages, but something about the format of the file made it very cumbersome to work with. I tried Preview, PDF Expert, and PDfpen Pro, and they all were glacially slow when panning, zooming, and switching pages.

So I broke the file up into individual pages using PDFtk:

pdftk drawings.pdf burst

The single-page files didn’t make me wait for the spinning beach ball, so I was able to add my annotations quickly in PDF Expert. Then came an impasse.

I wanted to email the drawings with my annotations back to the client and to some other parties, but they were too big to fit in an email. I could use multiple emails, but that’s a recipe for losing some of the files. I could use a Dropbox link, but I had a sense that one of the other parties wouldn’t understand how that worked. What seemed best was to convert the files to JPEGs at the lowest legible resolution, zip them together, and send the zipped file in a single email.1

My normal practice would be to use sips for this, because I can issue a single command to convert any number of files. But I soon learned that sips doesn’t handle annotations properly when converting the format of a file from PDF to JPEG. In my brief testing, I found that neither my annotations or the ones that came from the client were visible in the converted JPEGs.

Preview, though, can export a PDF as a JPEG with the annotations intact and visible. Which presumably means that sips and Preview are using different code bases for the conversion. Whatever.

The problem with using Preview is I’d have to convert every file by hand. Not the most burdensome job I’ve ever had, but one that’s boring and susceptible to error. Enter Keyboard Maestro.

Here’s the macro that exports the current file in Preview to a JPEG and closes it:

Convert to JPEG macro

You’ll note there’s no step for setting the resolution for the exported file. That’s because once it’s set, it doesn’t change from one export to the next.

I didn’t try to have the macro open each PDF in turn, because I didn’t trust myself to do that right. I just opened all of them and then ran the macro by pressing ⌃⌥⌘J repeatedly. The macro closed each file after exporting it, leaving the next window ready to be operated on.

The new (for me) thing with this macro was the “and drag to” part at the bottom of the third step. That’s what moved the popup menu selection from PDF up to JPEG.

Preview export sheet

I figured out how much to drag by taking a screenshot like the one above and using a selection in Acorn to measure the vertical distance from the center of PDF to the center of JPEG. No trial and error.

With 25–30 files to convert, it’s possible I did save time with this macro. But the main reason I made it was to avoid the tedium and the likelihood of error on my part. These are not independent—I’m far more prone to make errors when the task is repetitive and doesn’t maintain my attention.

  1. No, a Dropbox link is really no more complicated than a zip file, but zip files are more familiar to more people. And although zipping JPEGs doesn’t make them smaller, it does package them up in a way that naive Windows users are usually comfortable with. It would be lovely if I always worked with people whose computer skills were trustworthy, but that’s not the world I live in. ↩︎