Fun exploring EXIF data with Tableau!

Woke up this morning to a tweet from Vanya Tucherov about extracting the EXIF data from photographs and plugging into the information visualisation software Tableau. Worth a try! An hour later, we had the following charts up on the web, currently just using a sample of 450 photos:

How to

We use Bibble as our RAW convertor/DAM, and a peek inside their image library files shows that it’s all stored in SQLite, a portable/flat file database format. That’s good – it means it should be easy to transform it into a format Tableau understands. For this purpose I’m using Tableau Public rather than Desktop, and Public is happy working with Excel, Access and CSV files.

First up I used the SQLite Browser to get into the library files, and exported the Exif table from the base file into CSV format. I also exported the Master tables as I’d hoped there were some datestamps in there, but as I was working from a JPEG library on my laptop they correspond to the date the JPEGs were output, rather than the capture date of the shot.

I imported the CSV into Excel and did some manipulation – the Exif data is all stored inside a simple XML record, so it’d be neater to set up a script that parses the records and outputs a more strongly structured CSV file, but for this test we just throw some formulae at it (messy but it worked: to start at the right place, =MID($C2,FIND(D$1,$C2)+2+LEN(D$1),100), where C2 is the record, and D1 is the label you’re looking for, and the following to stop the output after the double quotes in the previous cell =MID(D2,1,FIND(“”"”,D2,1)-1)). Thanks to FlyingBinary‘s quick thinking, I was able to convert the unfamiliar aperture (39/8) and focal length (300/1) values into decimal format using =VALUE(LEFT(J2,FIND(“/”,J2)-1))/VALUE(MID(J2,FIND(“/”,J2)+1,LEN(J2))).

Just about ready for Tableau – we now have an Excel file which gives us camera Make, Model, ImageWidth, ImageHeight, ISO, Exposure Bias/Compensation, Aperture, Focal Length, and Shutter Speed. I downloaded Tableau Public, installed and opened up the Excel workbook as a data source. After a bit of playing around and excluding null/invalid values, I came up with the above. It’s clear that most shots are taken at the minimum or maximum extent of the lenses we’ve used over the years: 18-55, 70-300, 100-400.

Later, I’ll run the same process over all our RAW files and extract some more data, particularly the datestamps, to see what – if anything – has changed over time.