How Movie Releases Influence the Cheakout Times of Original Books
MAT 259A, 2018
April Cai

Concept
When a book being made into big screens, there will always be a discussion on whether the book or the film is more popular. Audience are born to be critics and curiosity drives people to explore and compare the book and its movie. To some extent, a book and its movie make a great pair together. The book always have a detailed story and well rounded portrayal of characters. The movie is more visually appealing and offers a multidimensional take on the original work. With the Seattle Public Library dataset we have, I am interested in looking into the possible boost that a film adaptation on the original book.
For the purpose of better 2D visualization, I picked out 9 movies that adapted from books. They are listed below with the release date:

    Twilight 2008/11/21
    New moon 2009/11/20
    Eclipse 2010/6/24
    Breaking Dawn 2011/11/18
    Hunger Games 2012/03/23
    Catching Fire 2013/11/20
    Mockingjay 2014/11/21 2015/11/20
    Enders game 2013/11/01
    Divergent 2014/3/21


Query
SELECT title, COUNT(bibNumber) AS Counts, SUM(CASE WHEN (YEAR(cout) = 2006 AND MONTH(cout) = 1) THEN 1 ELSE 0 END) AS '2006-1', SUM(CASE WHEN (YEAR(cout) = 2006 AND MONTH(cout) = 2) THEN 1 ELSE 0 END) AS '2006-2', SUM(CASE WHEN (YEAR(cout) = 2006 AND MONTH(cout) = 3) THEN 1 ELSE 0 END) AS '2006-3', SUM(CASE WHEN (YEAR(cout) = 2006 AND MONTH(cout) = 4) THEN 1 ELSE 0 END) AS '2006-4', SUM(CASE WHEN (YEAR(cout) = 2006 AND MONTH(cout) = 5) THEN 1 ELSE 0 END) AS '2006-5', SUM(CASE WHEN (YEAR(cout) = 2006 AND MONTH(cout) = 6) THEN 1 ELSE 0 END) AS '2006-6', SUM(CASE WHEN (YEAR(cout) = 2006 AND MONTH(cout) = 7) THEN 1 ELSE 0 END) AS '2006-7', SUM(CASE WHEN (YEAR(cout) = 2006 AND MONTH(cout) = 8) THEN 1 ELSE 0 END) AS '2006-8', SUM(CASE WHEN (YEAR(cout) = 2006 AND MONTH(cout) = 9) THEN 1 ELSE 0 END) AS '2006-9', SUM(CASE WHEN (YEAR(cout) = 2006 AND MONTH(cout) = 10) THEN 1 ELSE 0 END) AS '2006-10', SUM(CASE WHEN (YEAR(cout) = 2006 AND MONTH(cout) = 11) THEN 1 ELSE 0 END) AS '2006-11', SUM(CASE WHEN (YEAR(cout) = 2006 AND MONTH(cout) = 12) THEN 1 ELSE 0 END) AS '2006-12', SUM(CASE WHEN (YEAR(cout) = 2007 AND MONTH(cout) = 1) THEN 1 ELSE 0 END) AS '2007-1', SUM(CASE WHEN (YEAR(cout) = 2007 AND MONTH(cout) = 2) THEN 1 ELSE 0 END) AS '2007-2', SUM(CASE WHEN (YEAR(cout) = 2007 AND MONTH(cout) = 3) THEN 1 ELSE 0 END) AS '2007-3', SUM(CASE WHEN (YEAR(cout) = 2007 AND MONTH(cout) = 4) THEN 1 ELSE 0 END) AS '2007-4', SUM(CASE WHEN (YEAR(cout) = 2007 AND MONTH(cout) = 5) THEN 1 ELSE 0 END) AS '2007-5', SUM(CASE WHEN (YEAR(cout) = 2007 AND MONTH(cout) = 6) THEN 1 ELSE 0 END) AS '2007-6', SUM(CASE WHEN (YEAR(cout) = 2007 AND MONTH(cout) = 7) THEN 1 ELSE 0 END) AS '2007-7', SUM(CASE WHEN (YEAR(cout) = 2007 AND MONTH(cout) = 8) THEN 1 ELSE 0 END) AS '2007-8', SUM(CASE WHEN (YEAR(cout) = 2007 AND MONTH(cout) = 9) THEN 1 ELSE 0 END) AS '2007-9', SUM(CASE WHEN (YEAR(cout) = 2007 AND MONTH(cout) = 10) THEN 1 ELSE 0 END) AS '2007-10', SUM(CASE WHEN (YEAR(cout) = 2007 AND MONTH(cout) = 11) THEN 1 ELSE 0 END) AS '2007-11', SUM(CASE WHEN (YEAR(cout) = 2007 AND MONTH(cout) = 12) THEN 1 ELSE 0 END) AS '2007-12', SUM(CASE WHEN (YEAR(cout) = 2008 AND MONTH(cout) = 1) THEN 1 ELSE 0 END) AS '2008-1', SUM(CASE WHEN (YEAR(cout) = 2008 AND MONTH(cout) = 2) THEN 1 ELSE 0 END) AS '2008-2', SUM(CASE WHEN (YEAR(cout) = 2008 AND MONTH(cout) = 3) THEN 1 ELSE 0 END) AS '2008-3', SUM(CASE WHEN (YEAR(cout) = 2008 AND MONTH(cout) = 4) THEN 1 ELSE 0 END) AS '2008-4', SUM(CASE WHEN (YEAR(cout) = 2008 AND MONTH(cout) = 5) THEN 1 ELSE 0 END) AS '2008-5', SUM(CASE WHEN (YEAR(cout) = 2008 AND MONTH(cout) = 6) THEN 1 ELSE 0 END) AS '2008-6', SUM(CASE WHEN (YEAR(cout) = 2008 AND MONTH(cout) = 7) THEN 1 ELSE 0 END) AS '2008-7', SUM(CASE WHEN (YEAR(cout) = 2008 AND MONTH(cout) = 8) THEN 1 ELSE 0 END) AS '2008-8', SUM(CASE WHEN (YEAR(cout) = 2008 AND MONTH(cout) = 9) THEN 1 ELSE 0 END) AS '2008-9', SUM(CASE WHEN (YEAR(cout) = 2008 AND MONTH(cout) = 10) THEN 1 ELSE 0 END) AS '2008-10', SUM(CASE WHEN (YEAR(cout) = 2008 AND MONTH(cout) = 11) THEN 1 ELSE 0 END) AS '2008-11', SUM(CASE WHEN (YEAR(cout) = 2008 AND MONTH(cout) = 12) THEN 1 ELSE 0 END) AS '2008-12', SUM(CASE WHEN (YEAR(cout) = 2009 AND MONTH(cout) = 1) THEN 1 ELSE 0 END) AS '2009-1', SUM(CASE WHEN (YEAR(cout) = 2009 AND MONTH(cout) = 2) THEN 1 ELSE 0 END) AS '2009-2', SUM(CASE WHEN (YEAR(cout) = 2009 AND MONTH(cout) = 3) THEN 1 ELSE 0 END) AS '2009-3', SUM(CASE WHEN (YEAR(cout) = 2009 AND MONTH(cout) = 4) THEN 1 ELSE 0 END) AS '2009-4', SUM(CASE WHEN (YEAR(cout) = 2009 AND MONTH(cout) = 5) THEN 1 ELSE 0 END) AS '2009-5', SUM(CASE WHEN (YEAR(cout) = 2009 AND MONTH(cout) = 6) THEN 1 ELSE 0 END) AS '2009-6', SUM(CASE WHEN (YEAR(cout) = 2009 AND MONTH(cout) = 7) THEN 1 ELSE 0 END) AS '2009-7', SUM(CASE WHEN (YEAR(cout) = 2009 AND MONTH(cout) = 8) THEN 1 ELSE 0 END) AS '2009-8', SUM(CASE WHEN (YEAR(cout) = 2009 AND MONTH(cout) = 9) THEN 1 ELSE 0 END) AS '2009-9', SUM(CASE WHEN (YEAR(cout) = 2009 AND MONTH(cout) = 10) THEN 1 ELSE 0 END) AS '2009-10', SUM(CASE WHEN (YEAR(cout) = 2009 AND MONTH(cout) = 11) THEN 1 ELSE 0 END) AS '2009-11', SUM(CASE WHEN (YEAR(cout) = 2009 AND MONTH(cout) = 12) THEN 1 ELSE 0 END) AS '2009-12', SUM(CASE WHEN (YEAR(cout) = 2010 AND MONTH(cout) = 1) THEN 1 ELSE 0 END) AS '2010-1', SUM(CASE WHEN (YEAR(cout) = 2010 AND MONTH(cout) = 2) THEN 1 ELSE 0 END) AS '2010-2', SUM(CASE WHEN (YEAR(cout) = 2010 AND MONTH(cout) = 3) THEN 1 ELSE 0 END) AS '2010-3', SUM(CASE WHEN (YEAR(cout) = 2010 AND MONTH(cout) = 4) THEN 1 ELSE 0 END) AS '2010-4', SUM(CASE WHEN (YEAR(cout) = 2010 AND MONTH(cout) = 5) THEN 1 ELSE 0 END) AS '2010-5', SUM(CASE WHEN (YEAR(cout) = 2010 AND MONTH(cout) = 6) THEN 1 ELSE 0 END) AS '2010-6', SUM(CASE WHEN (YEAR(cout) = 2010 AND MONTH(cout) = 7) THEN 1 ELSE 0 END) AS '2010-7', SUM(CASE WHEN (YEAR(cout) = 2010 AND MONTH(cout) = 8) THEN 1 ELSE 0 END) AS '2010-8', SUM(CASE WHEN (YEAR(cout) = 2010 AND MONTH(cout) = 9) THEN 1 ELSE 0 END) AS '2010-9', SUM(CASE WHEN (YEAR(cout) = 2010 AND MONTH(cout) = 10) THEN 1 ELSE 0 END) AS '2010-10', SUM(CASE WHEN (YEAR(cout) = 2010 AND MONTH(cout) = 11) THEN 1 ELSE 0 END) AS '2010-11', SUM(CASE WHEN (YEAR(cout) = 2010 AND MONTH(cout) = 12) THEN 1 ELSE 0 END) AS '2010-12', SUM(CASE WHEN (YEAR(cout) = 2011 AND MONTH(cout) = 1) THEN 1 ELSE 0 END) AS '2011-1', SUM(CASE WHEN (YEAR(cout) = 2011 AND MONTH(cout) = 2) THEN 1 ELSE 0 END) AS '2011-2', SUM(CASE WHEN (YEAR(cout) = 2011 AND MONTH(cout) = 3) THEN 1 ELSE 0 END) AS '2011-3', SUM(CASE WHEN (YEAR(cout) = 2011 AND MONTH(cout) = 4) THEN 1 ELSE 0 END) AS '2011-4', SUM(CASE WHEN (YEAR(cout) = 2011 AND MONTH(cout) = 5) THEN 1 ELSE 0 END) AS '2011-5', SUM(CASE WHEN (YEAR(cout) = 2011 AND MONTH(cout) = 6) THEN 1 ELSE 0 END) AS '2011-6', SUM(CASE WHEN (YEAR(cout) = 2011 AND MONTH(cout) = 7) THEN 1 ELSE 0 END) AS '2011-7', SUM(CASE WHEN (YEAR(cout) = 2011 AND MONTH(cout) = 8) THEN 1 ELSE 0 END) AS '2011-8', SUM(CASE WHEN (YEAR(cout) = 2011 AND MONTH(cout) = 9) THEN 1 ELSE 0 END) AS '2011-9', SUM(CASE WHEN (YEAR(cout) = 2011 AND MONTH(cout) = 10) THEN 1 ELSE 0 END) AS '2011-10', SUM(CASE WHEN (YEAR(cout) = 2011 AND MONTH(cout) = 11) THEN 1 ELSE 0 END) AS '2011-11', SUM(CASE WHEN (YEAR(cout) = 2011 AND MONTH(cout) = 12) THEN 1 ELSE 0 END) AS '2011-12', SUM(CASE WHEN (YEAR(cout) = 2012 AND MONTH(cout) = 1) THEN 1 ELSE 0 END) AS '2012-1', SUM(CASE WHEN (YEAR(cout) = 2012 AND MONTH(cout) = 2) THEN 1 ELSE 0 END) AS '2012-2', SUM(CASE WHEN (YEAR(cout) = 2012 AND MONTH(cout) = 3) THEN 1 ELSE 0 END) AS '2012-3', SUM(CASE WHEN (YEAR(cout) = 2012 AND MONTH(cout) = 4) THEN 1 ELSE 0 END) AS '2012-4', SUM(CASE WHEN (YEAR(cout) = 2012 AND MONTH(cout) = 5) THEN 1 ELSE 0 END) AS '2012-5', SUM(CASE WHEN (YEAR(cout) = 2012 AND MONTH(cout) = 6) THEN 1 ELSE 0 END) AS '2012-6', SUM(CASE WHEN (YEAR(cout) = 2012 AND MONTH(cout) = 7) THEN 1 ELSE 0 END) AS '2012-7', SUM(CASE WHEN (YEAR(cout) = 2012 AND MONTH(cout) = 8) THEN 1 ELSE 0 END) AS '2012-8', SUM(CASE WHEN (YEAR(cout) = 2012 AND MONTH(cout) = 9) THEN 1 ELSE 0 END) AS '2012-9', SUM(CASE WHEN (YEAR(cout) = 2012 AND MONTH(cout) = 10) THEN 1 ELSE 0 END) AS '2012-10', SUM(CASE WHEN (YEAR(cout) = 2012 AND MONTH(cout) = 11) THEN 1 ELSE 0 END) AS '2012-11', SUM(CASE WHEN (YEAR(cout) = 2012 AND MONTH(cout) = 12) THEN 1 ELSE 0 END) AS '2012-12', SUM(CASE WHEN (YEAR(cout) = 2013 AND MONTH(cout) = 1) THEN 1 ELSE 0 END) AS '2013-1', SUM(CASE WHEN (YEAR(cout) = 2013 AND MONTH(cout) = 2) THEN 1 ELSE 0 END) AS '2013-2', SUM(CASE WHEN (YEAR(cout) = 2013 AND MONTH(cout) = 3) THEN 1 ELSE 0 END) AS '2013-3', SUM(CASE WHEN (YEAR(cout) = 2013 AND MONTH(cout) = 4) THEN 1 ELSE 0 END) AS '2013-4', SUM(CASE WHEN (YEAR(cout) = 2013 AND MONTH(cout) = 5) THEN 1 ELSE 0 END) AS '2013-5', SUM(CASE WHEN (YEAR(cout) = 2013 AND MONTH(cout) = 6) THEN 1 ELSE 0 END) AS '2013-6', SUM(CASE WHEN (YEAR(cout) = 2013 AND MONTH(cout) = 7) THEN 1 ELSE 0 END) AS '2013-7', SUM(CASE WHEN (YEAR(cout) = 2013 AND MONTH(cout) = 8) THEN 1 ELSE 0 END) AS '2013-8', SUM(CASE WHEN (YEAR(cout) = 2013 AND MONTH(cout) = 9) THEN 1 ELSE 0 END) AS '2013-9', SUM(CASE WHEN (YEAR(cout) = 2013 AND MONTH(cout) = 10) THEN 1 ELSE 0 END) AS '2013-10', SUM(CASE WHEN (YEAR(cout) = 2013 AND MONTH(cout) = 11) THEN 1 ELSE 0 END) AS '2013-11', SUM(CASE WHEN (YEAR(cout) = 2013 AND MONTH(cout) = 12) THEN 1 ELSE 0 END) AS '2013-12', SUM(CASE WHEN (YEAR(cout) = 2014 AND MONTH(cout) = 1) THEN 1 ELSE 0 END) AS '2014-1', SUM(CASE WHEN (YEAR(cout) = 2014 AND MONTH(cout) = 2) THEN 1 ELSE 0 END) AS '2014-2', SUM(CASE WHEN (YEAR(cout) = 2014 AND MONTH(cout) = 3) THEN 1 ELSE 0 END) AS '2014-3', SUM(CASE WHEN (YEAR(cout) = 2014 AND MONTH(cout) = 4) THEN 1 ELSE 0 END) AS '2014-4', SUM(CASE WHEN (YEAR(cout) = 2014 AND MONTH(cout) = 5) THEN 1 ELSE 0 END) AS '2014-5', SUM(CASE WHEN (YEAR(cout) = 2014 AND MONTH(cout) = 6) THEN 1 ELSE 0 END) AS '2014-6', SUM(CASE WHEN (YEAR(cout) = 2014 AND MONTH(cout) = 7) THEN 1 ELSE 0 END) AS '2014-7', SUM(CASE WHEN (YEAR(cout) = 2014 AND MONTH(cout) = 8) THEN 1 ELSE 0 END) AS '2014-8', SUM(CASE WHEN (YEAR(cout) = 2014 AND MONTH(cout) = 9) THEN 1 ELSE 0 END) AS '2014-9', SUM(CASE WHEN (YEAR(cout) = 2014 AND MONTH(cout) = 10) THEN 1 ELSE 0 END) AS '2014-10', SUM(CASE WHEN (YEAR(cout) = 2014 AND MONTH(cout) = 11) THEN 1 ELSE 0 END) AS '2014-11', SUM(CASE WHEN (YEAR(cout) = 2014 AND MONTH(cout) = 12) THEN 1 ELSE 0 END) AS '2014-12', SUM(CASE WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 1) THEN 1 ELSE 0 END) AS '2015-1', SUM(CASE WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 2) THEN 1 ELSE 0 END) AS '2015-2', SUM(CASE WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 3) THEN 1 ELSE 0 END) AS '2015-3', SUM(CASE WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 4) THEN 1 ELSE 0 END) AS '2015-4', SUM(CASE WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 5) THEN 1 ELSE 0 END) AS '2015-5', SUM(CASE WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 6) THEN 1 ELSE 0 END) AS '2015-6', SUM(CASE WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 7) THEN 1 ELSE 0 END) AS '2015-7', SUM(CASE WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 8) THEN 1 ELSE 0 END) AS '2015-8', SUM(CASE WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 9) THEN 1 ELSE 0 END) AS '2015-9', SUM(CASE WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 10) THEN 1 ELSE 0 END) AS '2015-10', SUM(CASE WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 11) THEN 1 ELSE 0 END) AS '2015-11', SUM(CASE WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 12) THEN 1 ELSE 0 END) AS '2015-12', SUM(CASE WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 1) THEN 1 ELSE 0 END) AS '2016-1', SUM(CASE WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 2) THEN 1 ELSE 0 END) AS '2016-2', SUM(CASE WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 3) THEN 1 ELSE 0 END) AS '2016-3', SUM(CASE WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 4) THEN 1 ELSE 0 END) AS '2016-4', SUM(CASE WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 5) THEN 1 ELSE 0 END) AS '2016-5', SUM(CASE WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 6) THEN 1 ELSE 0 END) AS '2016-6', SUM(CASE WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 7) THEN 1 ELSE 0 END) AS '2016-7', SUM(CASE WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 8) THEN 1 ELSE 0 END) AS '2016-8', SUM(CASE WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 9) THEN 1 ELSE 0 END) AS '2016-9', SUM(CASE WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 10) THEN 1 ELSE 0 END) AS '2016-10', SUM(CASE WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 11) THEN 1 ELSE 0 END) AS '2016-11', SUM(CASE WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 12) THEN 1 ELSE 0 END) AS '2016-12', SUM(CASE WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 1) THEN 1 ELSE 0 END) AS '2017-1', SUM(CASE WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 2) THEN 1 ELSE 0 END) AS '2017-2', SUM(CASE WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 3) THEN 1 ELSE 0 END) AS '2017-3', SUM(CASE WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 4) THEN 1 ELSE 0 END) AS '2017-4', SUM(CASE WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 5) THEN 1 ELSE 0 END) AS '2017-5', SUM(CASE WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 6) THEN 1 ELSE 0 END) AS '2017-6', SUM(CASE WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 7) THEN 1 ELSE 0 END) AS '2017-7', SUM(CASE WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 8) THEN 1 ELSE 0 END) AS '2017-8', SUM(CASE WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 9) THEN 1 ELSE 0 END) AS '2017-9', SUM(CASE WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 10) THEN 1 ELSE 0 END) AS '2017-10', SUM(CASE WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 11) THEN 1 ELSE 0 END) AS '2017-11', SUM(CASE WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 12) THEN 1 ELSE 0 END) AS '2017-12' FROM spl_2016.outraw WHERE title = 'Twilight' OR title = 'New moon' OR title = 'Eclipse' OR title = 'Breaking Dawn' OR title = 'Hunger Games' OR title = 'Mockingjay' OR title = 'Catching Fire' OR title = 'Enders game' OR title = 'Divergent' GROUP BY title ORDER BY CASE title WHEN 'Twilight' THEN 9 WHEN 'New moon' THEN 8 WHEN 'Eclipse' THEN 7 WHEN 'Breaking Dawn' THEN 6 WHEN 'Hunger Games' THEN 5 WHEN 'Catching Fire' THEN 4 WHEN 'Mockingjay' THEN 3 WHEN 'Enders game' THEN 2 WHEN 'Divergent' THEN 1 ELSE 0 END DESC

Preliminary sketches



My original idea was to have the area of a circle represents the check out rate of books according to the timeline. However, when I implemented on processing, it didn’t look that good. So I switched to a more direct way to just use the matrix and the strength of the color will reflect the check out rate.

Process
Please put your work-in-progress images or screen shots here with some explanations.



It is just the grayscale plot and we can still see the trend very clearly of the boost of check-out rate. Then, I applied different colors to different movies,



Now it gives a better visual experience on varies movie check-out trend.


Final result
Finally, I added a highlight to the time frame, so that we the mouse is hovered, the year’s background will be brightened. The first plot shows like this,






Code
All work is developed within Processing 3
Source Code + Data