Most Popular Items by Checkouts 2006-2018
in Seattle Public Library

MAT 259, 2019
Jiaheng Tang

For this project, I decided to explore the most popular checked out items of each year, from 2006 to 2018. I picked the top 50 most popular item of each year, as well as the top 50 of the total checkouts.

I first constructed my SQL query to get the top 1000 most checkout items, below is the query I used.

This query took really long, for about 45 minutes.

I then ran a query to get the popular checkout items each year.

I just changed the YEAR(cout) to each year.

But I realized that I should get each checkout records of the top 50 most popular items to make it more interesting. Therefore I wrote a Python script to read the item number from the CSV file I got back, and dynamically construct the SQL query I needed. Here is the Python script,

and one of the queries it produced,

Preliminary sketches and process
I started out with the idea that I want to use a tunnel-like shape to represent time passing by. Then I will plot each record as a line, from the checkout date to check-in date. Here's my first draft.

I used different colors for the Dewey classes. However, I wasn't using x and y-axis for any information. For each record, I just generated a random point inside the circle.

Final result
I decided to draw the checkout records as arcs around the circle. The circle starts from 0 to 360 degrees, and the degree can be mapped to month, from January to December. I also used brighter colors for different Dewey classes. Below are some screenshots.

I also added features like toggles for Dewey classes and year, and a dropdown list for selecting the most popular titles of each year.

I made some updates to add the hover text using a slider, since I found it rather hard to check if the mouse is on an arc. and some screenshots.

From the visualization, we can clearly see that the checkout times dropped year by year, which matches the conclusions that some of my peers draw. Also, a large portion of the popular checkouts is from non-dewey class, which means they don't have a Dewey class associated with them. I also think that Peasycam and ControlP5 are two very good libraries and it really saved me a lot of time when developing this application.

I want to add hover text on each record for future improvements. I'm also considering adding a presentation mode which moves the camera slowly to show each year's titles.

All work is developed within Processing
Source Code + Data