I recently became a fan of the Jason Bourne movies, so I was curious to see if the patrons of the Seattle Public Library also enjoyed the series. Also, I wanted to investigate the effect of a new movie release on the checkouts of the older titles. At the time of writing the series consists of five films with more on the way. The most recent one, Jason Bourne, was released at the beginning of 2017. I did not visualize its checkouts because it left a lot of negative space from 2006 through 2017.

Each of the first four titles are listed down the Y-axis, and time is labeled across the X-axis. Each title is given a color and a lane spanning horizontally. When a particular title is checked out, a rectangle is created that fills the lane vertically and spans the checkout period horizontally. So an item that was checked out for a long time will have a wider rectangle.

The query returns a lot of variables that I thought I would need when starting the project, but quite a few turned out to be unnecessary.

SELECT
	CASE
		WHEN ti.title LIKE "bourne identity" THEN 0
		WHEN ti.title LIKE "bourne supremacy" THEN 1
		WHEN ti.title LIKE "bourne ultimatum" THEN 2
		WHEN ti.title LIKE "bourne legacy" THEN 3
		WHEN ti.title LIKE "jason bourne" THEN 4 END
	AS title,
	# Unix timestamps
	unix_timestamp(tr.checkOut) as checkOut,
	unix_timestamp(tr.checkIn) AS checkIn,
	round(abs(unix_timestamp(tr.checkIn)- unix_timestamp(tr.checkOut))/60/60/24, 4) AS daysOut,
	meanTimes.meanDaysOut,
	# day, month, year of checkout
	day(tr.checkOut) as dayOut,
	month(tr.checkOut) as monthOut,
	year(tr.checkOut) as yearOut,
	# day, month, year of checkin
	day(tr.checkIn) as dayIn,
	month(tr.checkIn) as monthIn,
	year(tr.checkIn) as yearIn
FROM spl_2016.transactions as tr
JOIN spl_2016.title as ti
	ON tr.bibNumber = ti.bibNumber
JOIN
	(SELECT
		ti.title as title,
		unix_timestamp(tr.checkOut) as dayOut,
		round(avg(abs(unix_timestamp(tr.checkIn) - unix_timestamp(tr.checkOut))/60/60/24),4) AS meanDaysOut
	FROM spl_2016.transactions as tr
	JOIN spl_2016.title as ti
		ON tr.bibNumber = ti.bibNumber
	WHERE 
		year(tr.checkOut) >= 2005 AND
		tr.Checkin is not NULL AND
		(ti.title LIKE "bourne identity" OR
		ti.title LIKE "bourne supremacy" OR
		ti.title LIKE "bourne ultimatum" OR
		ti.title LIKE "bourne legacy" OR
		ti.title LIKE "jason bourne")
	GROUP BY
		title
	) as meanTimes
    ON meanTimes.title = ti.title
WHERE 
	year(tr.checkOut) >= 2005 AND
	tr.checkIn is not NULL AND
	(ti.title LIKE "bourne identity" OR
	ti.title LIKE "bourne supremacy" OR
	ti.title LIKE "bourne ultimatum" OR
	ti.title LIKE "bourne legacy" OR
	ti.title LIKE "jason bourne")
ORDER BY
	title asc,
    dayOut asc

	            

These are some pencil and paper sketches when I began the project. I originally wanted to split up each movie's horizontal lane by another variable.

My first iteration of the project had some presentation issues. The background image seemed like a good idea at first, but input from the class made it clear that it was just a distraction from the visualization. Also, in trying to split up each title's horizontal lane, I just made the visualization harder to interpet. In this version, each movie's lane was split up for the month of the year. This was a bad decision since I was mapping time to both the X and Y axes.

I decided that less is more, so I removed the background image. I am happy with the color choices in the final version. The first image below shows the visualization at startup, and the second shows the release dates plotted when the user presses the "R" key.

Interactivity:

• Up/Down arrows: Adjust transparency of the rectangles
• Mouse location: Displays the date in the lower right-hand corner.
• R key: Plots release dates as vertical lines with labels at the top of the visualization.
• J key: An easter egg.

It was very rewarding to see that when a new title was released, there would be an uptick in checkouts of the preceeding movies. By adjusting the transparancy, we can see that this trend occurs at varrying degrees for every new movie release.

However, I did not know that the Bourne movie series is actually based on an extensive novel series! This is why we see checkouts for the Bourne Ultimatum before it was released on DVD! If I were to redo the project, I would include the novel checkouts by splitting each horizontal lane in two: one lane for the movie version, and one for the corresponding novel. Overall, I am satisfied with the improvements made from the previous version to the final result.

Built with Processing 3.0.1
Source Code + Data