Dealing with Files in AppleScript and Conditional Counts in Excel

March 20, 2016
Category: TIL
Tags: Excel, AppleScript, and Data Science

I took a 10-day break from my TILs and now I’m reinvigorated and back on track. In the past 10 days I spent a lot of time doing things I already knew how to do, but I also worked on a small project related to data visualization to see if I could apply some things I learned over the past few months. The result was my recent blog post on Steph Curry’s stats.

A new project

I got a lot of good feedback on Facebook on some more things I can explore (and about how little I understand basketball.) I decided to work on another personal project to apply some of the data science I’ve been learning by reading Joel Grus’s Data Science from Scratch. I decided to find some data (photo dates and locations), extract it (AppleScript), format it (grep FTW), analyze it (forthcoming in Excel and Python), then visualize the insights (Python and/or D3). You can follow my progress on GitHub.


Dealing with files in AppleScript

AppleScript natively deals with file paths with colons: Macintosh HD:usr:local:bin: If you get a file path then want to pass it to the shell, you’ll first need to turn it into a POSIX path:

set a to "Macintosh HD:usr:local:bin:" 
set p to POSIX path of a 
   -- Output: "/usr/local/bin/"

If you want to create a file in the same folder as a script you are running, you might have to jump back and forth between AppleScript paths and POSIX paths because it is easier to make files by using the command do shell script. Notice the use of quoted form of, which quotes the file path and keeps you clear of pesky errors caused by characters in the path that need to be escaped:

-- Creating the file in the same folder as this script
set scriptPath to POSIX path of ((path to me as text) & "::")
do shell script "> " & quoted form of scriptPath & "photo_dates_location.csv"

Then if you want to get the path to the file you just created and make an it an alias to reference later:

set filePath to ((path to me as Unicode text) & "::") & "photo_dates_location.csv"
set theFile to filePath as alias

Conditional counting in Excel

Suppose you have a [spreadsheet full of photos, the date they were taken, and the days of the week they were taken on] (https://github.com/cagrimmett/apple_photos_analysis) and you want to count how many were taken on Monday, Tuesday, etc. Then you’d use the function COUNTIFS(range,argument) to work it out.

Example: Suppose I have the days of the week in column A and cell H2 contained the word I was looking for, Monday. Then my formula would be:

=COUNTIFS(A2:A8500,H2)

I quickly repeated this for the cells that contained the values for the other days of the week and got instant results:

Day Count
Monday 900
Tuesday 906
Wednesday 1079
Thursday 1082
Friday 1186
Saturday 1918
Sunday 1331

Find this post useful?

Buy me a coffeeBuy me a coffee