For this project, I was curious what the most popular titles in the Seattle Public Library were, and how the check out behavior of those titles changed over time. In performing the MySQL queries, it became apparent that the most checked out titles were not within Dewey categories, so I decided to perform two queries: what were the top 20 titles overall and the top 20 titles with a Dewey category number? Then, I used those bib numbers to search for their activity over time. I found that many of the most popular titles were not acquired until after 2008, so I began there. The query ends at 2013.

Most checked out titles in the database:
select * from (select deweyClass.deweyClass, deweyClass.bibNumber, title.title, x_checkOutCountBib.checkOutCount
from deweyClass, x_checkOutCountBib, title
where deweyClass.bibNumber = x_checkOutCountBib.bibNumber and deweyClass.bibNumber = title.bibNumber
order by x_checkOutCountBib.checkOutCount desc) as t
group by deweyClass
order by checkOutCount desc

Most checked out items with a dewey number:
select * from (select x_checkOutCountBib.bibNumber, title.title, x_checkOutCountBib.checkOutCount
from x_checkOutCountBib, title
where x_checkOutCountBib.bibNumber = title.bibNumber
order by x_checkOutCountBib.checkOutCount desc) as t
limit 50

I used the bib numbers obtained from these two queries to look at their check out activity across time. I grouped the data into weekly increments, so each row returned is the sum of one week of activity.

Most checked out titles frequency:
select YEAR(checkOut), MONTH(checkOut),
sum(case when bibNumber = 2469502 then 1 else 0 end) as "Into the wild",
sum(case when bibNumber = 2542732 then 1 else 0 end) as "Burn after reading",
sum(case when bibNumber = 2474843 then 1 else 0 end) as "Michael Clayton",
sum(case when bibNumber = 2482761 then 1 else 0 end) as "Atonement",
sum(case when bibNumber = 2560429 then 1 else 0 end) as "reader",
sum(case when bibNumber = 2560415 then 1 else 0 end) as "Doubt",
sum(case when bibNumber = 2472916 then 1 else 0 end) as "Darjeeling Limited",
sum(case when bibNumber = 2532278 then 1 else 0 end) as "Vicky Cristina Barcelona",
sum(case when bibNumber = 2474845 then 1 else 0 end) as "No country for old men",
sum(case when bibNumber = 2569682 then 1 else 0 end) as "curious case of Benjamin Button",
sum(case when bibNumber = 2485732 then 1 else 0 end) as "Juno",
sum(case when bibNumber = 2569703 then 1 else 0 end) as "Slumdog millionaire",
sum(case when bibNumber = 1205054 then 1 else 0 end) as "Uncataloged Folder or Bag BAL",
sum(case when bibNumber = 2560425 then 1 else 0 end) as "Milk",
sum(case when bibNumber = 2472920 then 1 else 0 end) as "Gone baby gone",
sum(case when bibNumber = 2461823 then 1 else 0 end) as "Once",
sum(case when bibNumber = 2560428 then 1 else 0 end) as "Rachel getting married",
sum(case when bibNumber = 2485720 then 1 else 0 end) as "Charlie Wilsons war",
sum(case when bibNumber = 2469492 then 1 else 0 end) as "Becoming Jane",
sum(case when bibNumber = 2480593 then 1 else 0 end) as "golden compass"
from transactions
where YEAR(checkOut) > 2007 and YEAR (checkOut) < 2014
GROUP BY YEAR(checkOut), MONTH(checkOut), mod(DAY(checkOut), 4)
ORDER BY YEAR(checkOut), MONTH(checkOut), DAY(checkOut)
-- ran in 289 sec

Most checked out dewey-categorized titles frequency:
select YEAR(checkOut), MONTH(checkOut),
sum(case when bibNumber = 2450933 then 1 else 0 end) as "Sicko",
sum(case when bibNumber = 2549448 then 1 else 0 end) as "Religulous",
sum(case when bibNumber = 2317605 then 1 else 0 end) as "March of the penguins",
sum(case when bibNumber = 2532708 then 1 else 0 end) as "Man on wire",
sum(case when bibNumber = 2337880 then 1 else 0 end) as "omnivores dilemma a natural history of four meals",
sum(case when bibNumber = 2608838 then 1 else 0 end) as "Food Inc",
sum(case when bibNumber = 2336383 then 1 else 0 end) as "Junebug",
sum(case when bibNumber = 2380691 then 1 else 0 end) as "inconvenient truth a global warning",
sum(case when bibNumber = 2127084 then 1 else 0 end) as "Pimsleur language programs Spanish 1 A the complete course",
sum(case when bibNumber = 2264211 then 1 else 0 end) as "Blink the power of thinking without thinking",
sum(case when bibNumber = 2513376 then 1 else 0 end) as "Outliers the story of success",
sum(case when bibNumber = 2327416 then 1 else 0 end) as "Grizzly man",
sum(case when bibNumber = 2327750 then 1 else 0 end) as "Eat pray love one womans search for everything across Italy India and Indonesia",
sum(case when bibNumber = 2285693 then 1 else 0 end) as "Freakonomics a rogue economist explores the hidden side of everything",
sum(case when bibNumber = 2333524 then 1 else 0 end) as "Three cups of tea one mans mission to fight terrorism and build nations one school at a time",
sum(case when bibNumber = 2407581 then 1 else 0 end) as "Back to black",
sum(case when bibNumber = 2398887 then 1 else 0 end) as "Jesus camp",
sum(case when bibNumber = 2430935 then 1 else 0 end) as "Planet Earth The complete series",
sum(case when bibNumber = 1923072 then 1 else 0 end) as "Guinness world records",
sum(case when bibNumber = 2331997 then 1 else 0 end) as "Chart your success on the COMPASS test"
from transactions
where YEAR(checkOut) > 2007 and YEAR(checkOut) < 2014
GROUP BY YEAR(checkOut), MONTH(checkOut), mod(DAY(checkOut), 4)
ORDER BY YEAR(checkOut), MONTH(checkOut), DAY(checkOut)
-- ran in 295 secs

While browsing The TimeViz Browser I came across the Kiviat Tube (also described here). These sketches show how I was thinking about placing vertices for each data point to form a row of polygons. These would be stitched together along the year axis, then rotated around a central point to form the Kiviat diagram. I also have some notes about how I will color each vertex.

In Processing, I wrote a function to simply place vertex points directly into the scene, looping 20 times to get through each title of the pillar. I arranged each pillar to contain the check out data of the top 20 most popular items either across the entire library, or constrained to within Dewey. One vertical polygon equals one week, and its distance from the center (height) is how often it was checked out that week.

The data was normalized appropriately based on what is being displayed. Circles were added to indicate the year, and at each year the names of each title fade in as the camera comes close. Color was chosen based on the rank of each title, and polygons become transparent when there is no activity. A shaded effect was achieved by assigning darker values to polygons at the base of each strip.

This visualization clearly shows some large scale trends in the library: the popularity of non-Dewey categorized titles far outweighs that of the Dewey categorized titles, and most of the popular items were acquired in 2008 or 2009, suggesting the library is experiencing an overall decline in activity. Of note, "Uncataloged folder or Bag BAL" came up as the 12th most checked out title in the system. Its activity is much lower than its competitors but is also more consistent, eventually rising above them by 2013 as their popularity wanes. This begs the question: what's in the "Bag?"

I used Processing with Peasycam.

Control: Click the toggle boxes under "Most Checked Out Titles" to show or hide each pillar. The sliders below this adjust the rotation and separation of each pillar. "Flip labels" will flip the text labels depending on if the user is looking "down" or "up" the diagram, and "Toggle labels" turns off both the text labels and the legend Top Dewey Classes legend.

Source Code + Data