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.
Nice vizes Alastair! Interesting that most shots are at min/max extent – I wonder whether that means we should make more use of fixed lenses, or whether the zoom flexibility is still worth the extra cost.
You can really only get this kiind of insight using Tableau, particularly in the time quoted. I guess with a little more work you might be able to use the insights as a guide to which lenses you might need for a particular scene. Of course, you might know that by gut feel already!
Thanks for sharing.
Glad to have been an inspiration. Playing with the photo metadata was the first thing I ever did with Tableau, back before I was officially part of the family. In fact, during the interview process, Chris Stolte asked what I’d done with the product so far, and I think I happily burbled on for a few minutes about realizing my habits as a photog and getting to see those trends. Thinking about redoing those experiments, now that I have some idea of what I’m really doing….