2D Frequency Pattern Visualization
2D Frequency Pattern Visualization
2D Frequency Pattern Visualization
Assignment due:
Jan 30: Rough concept/sketch discussion
Feb 01: 1st version due
Description
2D MATRIX: Create a 2D visualization in Processing using results from a MySQL query, stored in a csv file. Each cell's vertical (Y) and horizontal (X) position and color value to be determined by the 3 csv columns.
SIZE/DATA: Length and Height of the matrix each represent a data value. The 3rd value is to be represented by coloring cells either using, color in RGB, or saturation, or brightness in HSB, or any other way where each cell has a x,y, location, and it is differentiated from the other cells through color, or scale or any other means.
COLOR CODING: 99% of science visualizations use the green – yellow red color system. Experiment with/invent a different color scheme. Try http://tristen.ca/hclpicker/#/hlc/6/1/21313E/EFEE69
LABELS and TEXTS: Use standard sansserif fonts from the Swiss Graphic Design tradition for your texts and labels. Limit font sizes to a few. These include any of the following: Arial, Helvetica, Futura, Univers and related fonts.
CENTER VISUALIZATION: Your visualization should be at the center of your screen.

Your REPLY POST should include the following:
Concept description

MySQL Queries
Processing time

Sketches and workinprogress screenshots of your project with descriptions

Final results & Analysis

Please zip your processing code to include data folder for "ready to run". Add the zip file as an attachment.
Assignment due:
Jan 30: Rough concept/sketch discussion
Feb 01: 1st version due
Description
2D MATRIX: Create a 2D visualization in Processing using results from a MySQL query, stored in a csv file. Each cell's vertical (Y) and horizontal (X) position and color value to be determined by the 3 csv columns.
SIZE/DATA: Length and Height of the matrix each represent a data value. The 3rd value is to be represented by coloring cells either using, color in RGB, or saturation, or brightness in HSB, or any other way where each cell has a x,y, location, and it is differentiated from the other cells through color, or scale or any other means.
COLOR CODING: 99% of science visualizations use the green – yellow red color system. Experiment with/invent a different color scheme. Try http://tristen.ca/hclpicker/#/hlc/6/1/21313E/EFEE69
LABELS and TEXTS: Use standard sansserif fonts from the Swiss Graphic Design tradition for your texts and labels. Limit font sizes to a few. These include any of the following: Arial, Helvetica, Futura, Univers and related fonts.
CENTER VISUALIZATION: Your visualization should be at the center of your screen.

Your REPLY POST should include the following:
Concept description

MySQL Queries
Processing time

Sketches and workinprogress screenshots of your project with descriptions

Final results & Analysis

Please zip your processing code to include data folder for "ready to run". Add the zip file as an attachment.
Re: 2D Frequency Pattern Visualization
Objective and concept:
I’m interested in the popularity of four scifi movies: Star Trek Into Darkness, Cloud Atlas, Gravity and Prometheus. So I’d like to create a visualization which shows the popularity of those four movies over time. To measure the popularity, I divide the time into months, and retrieve how many times the movie is borrowed in one month. The more times the movie is borrowed, the more popular they are. I decide to use bar graph to visualize the result.
To query the dataset, I use the following scripts:
The query takes about 20 seconds. And the results look like:
Here is my initial bar graph:
After this, in order to figure out the relationship of each dataset, I think It may be better to show
the trend of individual movie, compared to the rest of the movie. To do this, I think maybe it’s better to show the trend of an individual movie on the top of the screen, and the show the trend of rest of three movies in the bottom.
This way the users can isolate the individual movie they are interested in.
To create a smooth transition between the movie from which all the movies are cluster together, to a stage where one movie is isolated, I want to create a smooth animation for this. To do this, we can think the individual rectangles of bar graph move vertically along y axis. The result looks like this:
Also, I’d like to restore the initial arrangement of the data set when user desired. Here is the result:
Also, to translate when a different movie is isolated:
Finally, I think another indication of popularity of a certain move is how long people borrow them. To do this, I want to figure out how long people keep a particular movie during a particular month. It’s unclear how to do this in sql, so I just grabbed all the borrowing record from the dataset, and then save the result to a .csv file, and then use python to compute the average time people borrowing the movie. The query part for this is easy:
SELECT title, cout, cin from spl_2016.inraw
Where title = “Star Trek Into Darkness” AND year(cout) > 2011
After this, I save the result to a csv file, and use the following python scripts to compute the average borrowing time for each movie during a particular month. It can be easily done by compute cin  count and aggregate them to each month.
To map how long the movie is borrowed into the visualization, I decided to color map them to the color of each bar. Here is the final result after color mapping the average borrowing time for a movie:
The darker the color, means people borrow the movie longer during that month.
Some analysis:
We can see generally if more movies are borrowed each month, people tend to keep them shorter. It’s however unclear how the popularity of each movie are related to each other, as the distribution of each movie over the month seems to be uncorrelated.
Code and Data:
Usage: after the programm is running, first press "r" to reset the layout. Then, press "1" , "2", "3", "4" to isloate individual movie. Press "s" to shuffle the arrangement of the movies.
I’m interested in the popularity of four scifi movies: Star Trek Into Darkness, Cloud Atlas, Gravity and Prometheus. So I’d like to create a visualization which shows the popularity of those four movies over time. To measure the popularity, I divide the time into months, and retrieve how many times the movie is borrowed in one month. The more times the movie is borrowed, the more popular they are. I decide to use bar graph to visualize the result.
To query the dataset, I use the following scripts:
Code: Select all
SELECT YEAR(cout) AS Year, MONTH(cout) AS Month,
SUM(CASE
WHEN title = 'Star Trek Into Darkness' Then 1
ELSE 0 END) AS 'Star Trek Into Darkness',
SUM(CASE
WHEN title = 'Cloud Atlas' Then 1
ELSE 0 END) AS 'Cloud Atlas',
SUM(CASE
WHEN title = 'gravity' Then 1
ELSE 0 END) AS 'gravity',
SUM(CASE
WHEN title = 'prometheus' Then 1
ELSE 0 END) AS 'prometheus'
FROM spl_2016.inraw
WHERE
(itemtype = 'acdvd' OR itemtype = 'acvhs')
AND YEAR(cout) >= '2011'
GROUP BY MONTH(cout), YEAR(cout)
ORDER BY YEAR(cout) , MONTH(cout)
Here is my initial bar graph:
After this, in order to figure out the relationship of each dataset, I think It may be better to show
the trend of individual movie, compared to the rest of the movie. To do this, I think maybe it’s better to show the trend of an individual movie on the top of the screen, and the show the trend of rest of three movies in the bottom.
This way the users can isolate the individual movie they are interested in.
To create a smooth transition between the movie from which all the movies are cluster together, to a stage where one movie is isolated, I want to create a smooth animation for this. To do this, we can think the individual rectangles of bar graph move vertically along y axis. The result looks like this:
Also, I’d like to restore the initial arrangement of the data set when user desired. Here is the result:
Also, to translate when a different movie is isolated:
Finally, I think another indication of popularity of a certain move is how long people borrow them. To do this, I want to figure out how long people keep a particular movie during a particular month. It’s unclear how to do this in sql, so I just grabbed all the borrowing record from the dataset, and then save the result to a .csv file, and then use python to compute the average time people borrowing the movie. The query part for this is easy:
SELECT title, cout, cin from spl_2016.inraw
Where title = “Star Trek Into Darkness” AND year(cout) > 2011
After this, I save the result to a csv file, and use the following python scripts to compute the average borrowing time for each movie during a particular month. It can be easily done by compute cin  count and aggregate them to each month.
Code: Select all
import csv
from datetime import datetime
from datetime import timedelta
import numpy as np
fileName = "./star_trek.csv"
cin = []
cout = []
def computeDiffInMonth(date1, date2):
yearDiff = date1.year  date2.year
monthDiff = date1.month  date2.month
return yearDiff*12 + monthDiff
with open(fileName, 'rb') as csvfile:
spamreader = csv.reader(csvfile, delimiter=',')
row1 = next(spamreader) # thrown away first row.
for row in spamreader:
cout.append(row[1])
cin.append(row[2])
cinP = []
coutP = []
for i in xrange(0, len(cin)):
cinP.append(datetime.strptime(cin[i], '%Y%m%d %H:%M:%S'))
coutP.append(datetime.strptime(cout[i], '%Y%m%d %H:%M:%S'))
BuckStart = datetime.strptime("20120101 12:00:00", '%Y%m%d %H:%M:%S')
BuckEnd = datetime.strptime("20180101 12:00:00", '%Y%m%d %H:%M:%S')
#print (BuckStart)
FirstCout = min(coutP)
#print (FirstCout)
totalBucket = computeDiffInMonth(BuckEnd, BuckStart)
#print (totalBucket)
bucketList = []
averageBorrowInDays = np.zeros([totalBucket])
for i in xrange(0, totalBucket):
TimeDiffList = []
bucketList.append(TimeDiffList)
for i in xrange(0, len(coutP)):
diffM = computeDiffInMonth(coutP[i], BuckStart)
bucketList[diffM].append(cinP[i]  coutP[i])
totalRecord = np.zeros([totalBucket])
for i in xrange(0, totalBucket):
numRecords = len(bucketList[i])
totalRecord[i] = numRecords
averageT = 0
#if (numRecords != 0):
# averageT = min(bucketList[i]).days
for Tdiff in bucketList[i]:
averageT += Tdiff.days
if (numRecords != 0):
averageT /= numRecords
#ri, averageT)
averageBorrowInDays[i] = averageT
fout = open("./Star_trek_AveDays.txt", "w")
for i in xrange(0, totalBucket):
fout.write("%d\n" % (int)(averageBorrowInDays[i]))
fout.close()
The darker the color, means people borrow the movie longer during that month.
Some analysis:
We can see generally if more movies are borrowed each month, people tend to keep them shorter. It’s however unclear how the popularity of each movie are related to each other, as the distribution of each movie over the month seems to be uncorrelated.
Code and Data:
Usage: after the programm is running, first press "r" to reset the layout. Then, press "1" , "2", "3", "4" to isloate individual movie. Press "s" to shuffle the arrangement of the movies.
 Attachments

 Qiaodong_hw2.zip
 (49.96 KiB) Downloaded 64 times
Last edited by qiaodong on Thu Feb 01, 2018 6:47 am, edited 1 time in total.

 Posts: 4
 Joined: Fri Jan 19, 2018 11:09 am
Re: 2D Frequency Pattern Visualization
INDIE ROCK vs SOUL
20 YEARS AFTER NIRVANA’S NEVERMIND, DOES SEATTLE STILL ROCK?
DESCRIPTION
This visualization compares the check out counts of two soul albums with two indie rock albums, which are ‘back to black’, ‘19’, ‘vampire weekend’ and 'fleetFoxes’. Examining the pattern of change of those four albums from 2008  2017 brings up analysis of correlation between soul album and indie rock, and push and pull between culture events and album checkout numbers.
SKETCH Design Idea behind those sketches: I was trying to use turntable as metaphor for the general shape. For the functional purpose, I want to compare 4 CDs at the same time, so I decided to split a CD like shape. Using two curve sides to compare each pair. One of the half CD is rotating based on the idea of turnable and comparison between four.
Color Schemes: QUERY
PROCESSING TIME
https://www.youtube.com/watch?v=BTXMjutjOW8
This is my video documentation.
This image shows how this visualization begins. With rulers on. The left side has brief description of this visualization while the right side has two moving bars which mark the max point for 2 CDs. The moving bars will bounce once it touches the peak 'year' of those 2CDs' checkouts.
Above two images show when the mouse move to the right, the description and ruler will disappear in order to see the animation clearly. The half CD upside is rotating, which provides variation both functionally and visually.
Analysis
For future development, I will connects those four max points to make the comparison more clear.
20 YEARS AFTER NIRVANA’S NEVERMIND, DOES SEATTLE STILL ROCK?
DESCRIPTION
This visualization compares the check out counts of two soul albums with two indie rock albums, which are ‘back to black’, ‘19’, ‘vampire weekend’ and 'fleetFoxes’. Examining the pattern of change of those four albums from 2008  2017 brings up analysis of correlation between soul album and indie rock, and push and pull between culture events and album checkout numbers.
SKETCH Design Idea behind those sketches: I was trying to use turntable as metaphor for the general shape. For the functional purpose, I want to compare 4 CDs at the same time, so I decided to split a CD like shape. Using two curve sides to compare each pair. One of the half CD is rotating based on the idea of turnable and comparison between four.
Color Schemes: QUERY
Code: Select all
SELECT
bibNumber, title, c2llNumber, COUNT(bibNumber) AS Counts,
SUM(CASE
WHEN (YEAR(cout) = 2008 2nd month(cout) = 1) THEN 1
ELSE 0
END) AS '20081', SUM(CASE
WHEN (YEAR(cout) = 2008 2nd month(cout) = 2) THEN 1
ELSE 0
END) AS '20082', SUM(CASE
WHEN (YEAR(cout) = 2008 2nd month(cout) = 3) THEN 1
ELSE 0
END) AS '20083', SUM(CASE
WHEN (YEAR(cout) = 2008 2nd month(cout) = 4) THEN 1
ELSE 0
END) AS '20084', SUM(CASE
WHEN (YEAR(cout) = 2008 2nd month(cout) = 5) THEN 1
ELSE 0
END) AS '20085', SUM(CASE
WHEN (YEAR(cout) = 2008 2nd month(cout) = 6) THEN 1
ELSE 0
END) AS '20086', SUM(CASE
WHEN (YEAR(cout) = 2008 2nd month(cout) = 7) THEN 1
ELSE 0
END) AS '20087', SUM(CASE
WHEN (YEAR(cout) = 2008 2nd month(cout) = 8) THEN 1
ELSE 0
END) AS '20088', SUM(CASE
WHEN (YEAR(cout) = 2008 2nd month(cout) = 9) THEN 1
ELSE 0
END) AS '20089', SUM(CASE
WHEN (YEAR(cout) = 2008 2nd month(cout) = 10) THEN 1
ELSE 0
END) AS '200810', SUM(CASE
WHEN (YEAR(cout) = 2008 2nd month(cout) = 11) THEN 1 ELSE 0
END) AS '200811', SUM(CASE
WHEN (YEAR(cout) = 2008 2nd month(cout) = 12) THEN 1 ELSE 0
END) AS '200812',
SUM(CASE
WHEN (YEAR(cout) = 2009 2nd month(cout) = 1) THEN 1 ELSE 0 END) AS '20091', SUM(CASE
WHEN (YEAR(cout) = 2009 2nd month(cout) = 2) THEN 1
ELSE 0
END) AS '20092', SUM(CASE
WHEN (YEAR(cout) = 2009 2nd month(cout) = 3) THEN 1
ELSE 0
END) AS '20093', SUM(CASE
WHEN (YEAR(cout) = 2009 2nd month(cout) = 4) THEN 1
ELSE 0
END) AS '20094', SUM(CASE
WHEN (YEAR(cout) = 2009 2nd month(cout) = 5) THEN 1
ELSE 0
END) AS '20095', SUM(CASE
WHEN (YEAR(cout) = 2009 2nd month(cout) = 6) THEN 1
ELSE 0
END) AS '20096', SUM(CASE
WHEN (YEAR(cout) = 2009 2nd month(cout) = 7) THEN 1
ELSE 0
END) AS '20097', SUM(CASE
WHEN (YEAR(cout) = 2009 2nd month(cout) = 8) THEN 1
ELSE 0
END) AS '20098', SUM(CASE
WHEN (YEAR(cout) = 2009 2nd month(cout) = 9) THEN 1
ELSE 0
END) AS '20099', SUM(CASE
WHEN (YEAR(cout) = 2009 2nd month(cout) = 10) THEN 1
ELSE 0
END) AS '200910', SUM(CASE
WHEN (YEAR(cout) = 2009 2nd month(cout) = 11) THEN 1 ELSE 0
END) AS '200911', SUM(CASE
WHEN (YEAR(cout) = 2009 2nd month(cout) = 12) THEN 1
ELSE 0
END) AS '200912',
SUM(CASE
WHEN (YEAR(cout) = 2010 2nd month(cout) = 1) THEN 1 ELSE 0 END) AS '20101', SUM(CASE
WHEN (YEAR(cout) = 2010 2nd month(cout) = 2) THEN 1
ELSE 0
END) AS '20102', SUM(CASE
WHEN (YEAR(cout) = 2010 2nd month(cout) = 3) THEN 1
ELSE 0
END) AS '20103', SUM(CASE
WHEN (YEAR(cout) = 2010 2nd month(cout) = 4) THEN 1
ELSE 0
END) AS '20104', SUM(CASE
WHEN (YEAR(cout) = 2010 2nd month(cout) = 5) THEN 1
ELSE 0
END) AS '20105', SUM(CASE
WHEN (YEAR(cout) = 2010 2nd month(cout) = 6) THEN 1
ELSE 0
END) AS '20106', SUM(CASE
WHEN (YEAR(cout) = 2010 2nd month(cout) = 7) THEN 1
ELSE 0
END) AS '20107', SUM(CASE
WHEN (YEAR(cout) = 2010 2nd month(cout) = 8) THEN 1
ELSE 0
END) AS '20108', SUM(CASE
WHEN (YEAR(cout) = 2010 2nd month(cout) = 9) THEN 1
ELSE 0
END) AS '20109', SUM(CASE
WHEN (YEAR(cout) = 2010 2nd month(cout) = 10) THEN 1
ELSE 0
END) AS '201010', SUM(CASE
WHEN (YEAR(cout) = 2010 2nd month(cout) = 11) THEN 1 ELSE 0
END) AS '201011', SUM(CASE
WHEN (YEAR(cout) = 2010 2nd month(cout) = 12) THEN 1
ELSE 0
END) AS '201012',
SUM(CASE
WHEN (YEAR(cout) = 2011 2nd month(cout) = 1) THEN 1 ELSE 0 END) AS '20111', SUM(CASE
WHEN (YEAR(cout) = 2011 2nd month(cout) = 2) THEN 1
ELSE 0
END) AS '20112', SUM(CASE
WHEN (YEAR(cout) = 2011 2nd month(cout) = 3) THEN 1
ELSE 0
END) AS '20113', SUM(CASE
WHEN (YEAR(cout) = 2011 2nd month(cout) = 4) THEN 1
ELSE 0
END) AS '20114', SUM(CASE
WHEN (YEAR(cout) = 2011 2nd month(cout) = 5) THEN 1
ELSE 0
END) AS '20115', SUM(CASE
WHEN (YEAR(cout) = 2011 2nd month(cout) = 6) THEN 1
ELSE 0
END) AS '20116', SUM(CASE
WHEN (YEAR(cout) = 2011 2nd month(cout) = 7) THEN 1
ELSE 0
END) AS '20117', SUM(CASE
WHEN (YEAR(cout) = 2011 2nd month(cout) = 8) THEN 1
ELSE 0
END) AS '20118', SUM(CASE
WHEN (YEAR(cout) = 2011 2nd month(cout) = 9) THEN 1
ELSE 0
END) AS '20119', SUM(CASE
WHEN (YEAR(cout) = 2011 2nd month(cout) = 10) THEN 1
ELSE 0
END) AS '201110', SUM(CASE
WHEN (YEAR(cout) = 2011 2nd month(cout) = 11) THEN 1 ELSE 0
END) AS '201111', SUM(CASE
WHEN (YEAR(cout) = 2011 2nd month(cout) = 12) THEN 1
ELSE 0
END) AS '201112',
SUM(CASE
WHEN (YEAR(cout) = 2012 2nd month(cout) = 1) THEN 1 ELSE 0 END) AS '20121', SUM(CASE
WHEN (YEAR(cout) = 2012 2nd month(cout) = 2) THEN 1
ELSE 0
END) AS '20122', SUM(CASE
WHEN (YEAR(cout) = 2012 2nd month(cout) = 3) THEN 1
ELSE 0
END) AS '20123', SUM(CASE
WHEN (YEAR(cout) = 2012 2nd month(cout) = 4) THEN 1
ELSE 0
END) AS '20124', SUM(CASE
WHEN (YEAR(cout) = 2012 2nd month(cout) = 5) THEN 1
ELSE 0
END) AS '20125', SUM(CASE
WHEN (YEAR(cout) = 2012 2nd month(cout) = 6) THEN 1
ELSE 0
END) AS '20126', SUM(CASE
WHEN (YEAR(cout) = 2012 2nd month(cout) = 7) THEN 1
ELSE 0
END) AS '20127', SUM(CASE
WHEN (YEAR(cout) = 2012 2nd month(cout) = 8) THEN 1 ELSE 0
END) AS '20128', SUM(CASE
WHEN (YEAR(cout) = 2012 2nd month(cout) = 9) THEN 1 ELSE 0
END) AS '20129', SUM(CASE
WHEN (YEAR(cout) = 2012 2nd month(cout) = 10) THEN 1
ELSE 0
END) AS '201210', SUM(CASE
WHEN (YEAR(cout) = 2012 2nd month(cout) = 11) THEN 1 ELSE 0
END) AS '201211', SUM(CASE
WHEN (YEAR(cout) = 2012 2nd month(cout) = 12) THEN 1
ELSE 0
END) AS '201212',
SUM(CASE
WHEN (YEAR(cout) = 2013 2nd month(cout) = 1) THEN 1 ELSE 0 END) AS '20131', SUM(CASE
WHEN (YEAR(cout) = 2013 2nd month(cout) = 2) THEN 1
ELSE 0
END) AS '20132', SUM(CASE
WHEN (YEAR(cout) = 2013 2nd month(cout) = 3) THEN 1
ELSE 0
END) AS '20133', SUM(CASE
WHEN (YEAR(cout) = 2013 2nd month(cout) = 4) THEN 1
ELSE 0
END) AS '20134', SUM(CASE
WHEN (YEAR(cout) = 2013 2nd month(cout) = 5) THEN 1
ELSE 0
END) AS '20135', SUM(CASE
WHEN (YEAR(cout) = 2013 2nd month(cout) = 6) THEN 1
ELSE 0
END) AS '20136', SUM(CASE
WHEN (YEAR(cout) = 2013 2nd month(cout) = 7) THEN 1
ELSE 0
END) AS '20137', SUM(CASE
WHEN (YEAR(cout) = 2013 2nd month(cout) = 8) THEN 1
ELSE 0
END) AS '20138', SUM(CASE
WHEN (YEAR(cout) = 2013 2nd month(cout) = 9) THEN 1
ELSE 0
END) AS '20139', SUM(CASE
WHEN (YEAR(cout) = 2013 2nd month(cout) = 10) THEN 1
ELSE 0
END) AS '201310', SUM(CASE
WHEN (YEAR(cout) = 2013 2nd month(cout) = 11) THEN 1 ELSE 0
END) AS '201311', SUM(CASE
WHEN (YEAR(cout) = 2013 2nd month(cout) = 12) THEN 1
ELSE 0
END) AS '201312', SUM(CASE
WHEN (YEAR(cout) = 2014 2nd month(cout) = 1) THEN 1
ELSE 0
END) AS '20141', SUM(CASE
WHEN (YEAR(cout) = 2014 2nd month(cout) = 2) THEN 1
ELSE 0
END) AS '20142', SUM(CASE
WHEN (YEAR(cout) = 2014 2nd month(cout) = 3) THEN 1
ELSE 0
END) AS '20143', SUM(CASE
WHEN (YEAR(cout) = 2014 2nd month(cout) = 4) THEN 1
ELSE 0
END) AS '20144', SUM(CASE
WHEN (YEAR(cout) = 2014 2nd month(cout) = 5) THEN 1
ELSE 0
END) AS '20145', SUM(CASE
WHEN (YEAR(cout) = 2014 2nd month(cout) = 6) THEN 1
ELSE 0
END) AS '20146', SUM(CASE
WHEN (YEAR(cout) = 2014 2nd month(cout) = 7) THEN 1
ELSE 0
END) AS '20147', SUM(CASE
WHEN (YEAR(cout) = 2014 2nd month(cout) = 8) THEN 1
ELSE 0
END) AS '20148', SUM(CASE
WHEN (YEAR(cout) = 2014 2nd month(cout) = 9) THEN 1
ELSE 0
END) AS '20149', SUM(CASE
WHEN (YEAR(cout) = 2014 2nd month(cout) = 10) THEN 1
ELSE 0
END) AS '201410', SUM(CASE
WHEN (YEAR(cout) = 2014 2nd month(cout) = 11) THEN 1 ELSE 0
END) AS '201411', SUM(CASE
WHEN (YEAR(cout) = 2014 2nd month(cout) = 12) THEN 1 ELSE 0
END) AS '201412',
SUM(CASE
WHEN (YEAR(cout) = 2015 2nd month(cout) = 1) THEN 1 ELSE 0 END) AS '20151', SUM(CASE
WHEN (YEAR(cout) = 2015 2nd month(cout) = 2) THEN 1
ELSE 0
END) AS '20152', SUM(CASE
WHEN (YEAR(cout) = 2015 2nd month(cout) = 3) THEN 1
ELSE 0
END) AS '20153', SUM(CASE
WHEN (YEAR(cout) = 2015 2nd month(cout) = 4) THEN 1
ELSE 0
END) AS '20154', SUM(CASE
WHEN (YEAR(cout) = 2015 2nd month(cout) = 5) THEN 1
ELSE 0
END) AS '20155', SUM(CASE
WHEN (YEAR(cout) = 2015 2nd month(cout) = 6) THEN 1
ELSE 0
END) AS '20156', SUM(CASE
WHEN (YEAR(cout) = 2015 2nd month(cout) = 7) THEN 1
ELSE 0
END) AS '20157', SUM(CASE
WHEN (YEAR(cout) = 2015 2nd month(cout) = 8) THEN 1
ELSE 0
END) AS '20158', SUM(CASE
WHEN (YEAR(cout) = 2015 2nd month(cout) = 9) THEN 1
ELSE 0
END) AS '20159', SUM(CASE
WHEN (YEAR(cout) = 2015 2nd month(cout) = 10) THEN 1
ELSE 0
END) AS '201510', SUM(CASE
WHEN (YEAR(cout) = 2015 2nd month(cout) = 11) THEN 1 ELSE 0
END) AS '201511', SUM(CASE
WHEN (YEAR(cout) = 2015 2nd month(cout) = 12) THEN 1
ELSE 0
END) AS '201512',
SUM(CASE
WHEN (YEAR(cout) = 2016 2nd month(cout) = 1) THEN 1 ELSE 0 END) AS '20161', SUM(CASE
WHEN (YEAR(cout) = 2016 2nd month(cout) = 2) THEN 1
ELSE 0
END) AS '20162', SUM(CASE
WHEN (YEAR(cout) = 2016 2nd month(cout) = 3) THEN 1 ELSE 0
END) AS '20163', SUM(CASE
WHEN (YEAR(cout) = 2016 2nd month(cout) = 4) THEN 1 ELSE 0
END) AS '20164', SUM(CASE
WHEN (YEAR(cout) = 2016 2nd month(cout) = 5) THEN 1
ELSE 0
END) AS '20165', SUM(CASE
WHEN (YEAR(cout) = 2016 2nd month(cout) = 6) THEN 1
ELSE 0
END) AS '20166', SUM(CASE
WHEN (YEAR(cout) = 2016 2nd month(cout) = 7) THEN 1
ELSE 0
END) AS '20167', SUM(CASE
WHEN (YEAR(cout) = 2016 2nd month(cout) = 8) THEN 1
ELSE 0
END) AS '20168', SUM(CASE
WHEN (YEAR(cout) = 2016 2nd month(cout) = 9) THEN 1
ELSE 0
END) AS '20169', SUM(CASE
WHEN (YEAR(cout) = 2016 2nd month(cout) = 10) THEN 1
ELSE 0
END) AS '201610', SUM(CASE
WHEN (YEAR(cout) = 2016 2nd month(cout) = 11) THEN 1 ELSE 0
END) AS '201611', SUM(CASE
WHEN (YEAR(cout) = 2016 2nd month(cout) = 12) THEN 1
ELSE 0
END) AS '201612',
SUM(CASE
WHEN (YEAR(cout) = 2017 2nd month(cout) = 1) THEN 1 ELSE 0 END) AS '20171', SUM(CASE
WHEN (YEAR(cout) = 2017 2nd month(cout) = 2) THEN 1
ELSE 0
END) AS '20172', SUM(CASE
WHEN (YEAR(cout) = 2017 2nd month(cout) = 3) THEN 1
ELSE 0
END) AS '20173', SUM(CASE
WHEN (YEAR(cout) = 2017 2nd month(cout) = 4) THEN 1
ELSE 0
END) AS '20174', SUM(CASE
WHEN (YEAR(cout) = 2017 2nd month(cout) = 5) THEN 1
ELSE 0
END) AS '20175', SUM(CASE
WHEN (YEAR(cout) = 2017 2nd month(cout) = 6) THEN 1
ELSE 0
END) AS '20176', SUM(CASE
WHEN (YEAR(cout) = 2017 2nd month(cout) = 7) THEN 1
ELSE 0
END) AS '20177', SUM(CASE
WHEN (YEAR(cout) = 2017 2nd month(cout) = 8) THEN 1
ELSE 0
END) AS '20178', SUM(CASE
WHEN (YEAR(cout) = 2017 2nd month(cout) = 9) THEN 1
ELSE 0
END) AS '20179', SUM(CASE
WHEN (YEAR(cout) = 2017 2nd month(cout) = 10) THEN 1
ELSE 0
END) AS '201710', SUM(CASE
WHEN (YEAR(cout) = 2017 2nd month(cout) = 11) THEN 1 ELSE 0
END) AS '201711', SUM(CASE
WHEN (YEAR(cout) = 2017 2nd month(cout) = 12) THEN 1 ELSE 0
END) AS '201712'
FROM spl_2016.outr2w
WHERE
SUBSTRING(itemType, 3, 4) = 'cd' 2nd title = 'V2mpire Weekend' or
title = 'b2ck to bl2ck' or title = '19' or
title = 'fleet foxes'
GROUP BY bibNumber , title, c2llNumber ORDER BY Counts DESC
https://www.youtube.com/watch?v=BTXMjutjOW8
This is my video documentation.
This image shows how this visualization begins. With rulers on. The left side has brief description of this visualization while the right side has two moving bars which mark the max point for 2 CDs. The moving bars will bounce once it touches the peak 'year' of those 2CDs' checkouts.
Above two images show when the mouse move to the right, the description and ruler will disappear in order to see the animation clearly. The half CD upside is rotating, which provides variation both functionally and visually.
Analysis
For future development, I will connects those four max points to make the comparison more clear.
 Attachments

 soulVSrock.zip
 (140.87 KiB) Downloaded 69 times

[The extension tif has been deactivated and can no longer be displayed.]

[The extension tif has been deactivated and can no longer be displayed.]

[The extension tif has been deactivated and can no longer be displayed.]
Last edited by zhangweidilydia on Thu Feb 22, 2018 12:20 pm, edited 1 time in total.

 Posts: 8
 Joined: Wed Apr 12, 2017 5:15 pm
Re: 2D Frequency Pattern Visualization
The Charlie and the Chocolate Factory franchise has been a popular story that has consistently been checked out of the Seattle Public Library each month, excluding VHS. This visualization will look at the different item types (books, CDs, DVDs, and VHS) in regards to number of copies available, and the number of times a unique item has been checked out. By doing so, analysis and estimations can be inferred.
SKETCH Originally, I wanted there to be parabolalike shapes sticking out of the circle, but because there were too many data points, they would end up looking like lines anyway. Pie charts are a great way to compare the percentage makeup of each item type, which would be distinguished by different colors. Outlined circles will trace the max, average, and median data values.
QUERY
My code only outputs the itemType, itemNumber, and the number of times that item has been checked out.
Code: Select all
SELECT
itemType, itemNumber, COUNT(bibNumber) AS Counts
FROM
spl_2016.inraw
WHERE
(title = 'Charlie and the Chocolate Factory'
OR title = 'Willy Wonka and the Chocolate Factory')
AND (itemType LIKE '%jcvhs%'
OR itemType LIKE '%jcdvd%'
OR itemType LIKE '%jcbk%'
OR itemType LIKE '%jccd%')
GROUP BY itemType, itemNumber
ORDER BY Counts DESC
0.078 sec
ANALYSIS
Because the median is below the average, we can infer that the majority of the items are below the checkout average. Also the bar chart is ordered by acquisition date, going clockwise. We can see most of the items at the rightmost end of the pie chart’s sections have small check out numbers, maybe because they were introduced to the library’s system recently.
 Attachments

 Assignment 2.zip
 (122.4 KiB) Downloaded 61 times
Re: 2D Frequency Pattern Visualization
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
The mySQL query (Reference: Rodger Luo):
Processing time(Duration/Fetch Time) 3.654 sec/0.000095 sec
Sketch 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.
WorkinProgress Screenshots
The first plot shows like this, https://drive.google.com/open?id=1Ye4sL ... aDSm0D2BHf
It is just the grayscale plot and we can still see the trend very clearly of the boost of checkout rate.
Then, I applied different colors to different movies, https://drive.google.com/open?id=1ZArRq ... BU5ieT9a7Q
Now it gives a better visual experience on varies movie checkout trend.
Final results & Analysis
Finally, I added a highlight to the time frame, so that we the mouse is hovered, the year’s background will be brightened. (Code reference: Junxiang) https://drive.google.com/open?id=19Xuia ... 99il5AVnmq
With the help of the 2D visualization, we can clear see a pattern/trend of the adaptation movies' influence on original books. Especially if it is a book series, such as the example here for Hunger Games and Twilight Saga when the first movie released, the check out rate of the same book and the following book will boost.
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
The mySQL query (Reference: Rodger Luo):
Code: Select all
SELECT
title,
COUNT(bibNumber) AS Counts,
SUM(CASE
WHEN (YEAR(cout) = 2006 AND MONTH(cout) = 1) THEN 1
ELSE 0
END) AS '20061',
SUM(CASE
WHEN (YEAR(cout) = 2006 AND MONTH(cout) = 2) THEN 1
ELSE 0
END) AS '20062',
SUM(CASE
WHEN (YEAR(cout) = 2006 AND MONTH(cout) = 3) THEN 1
ELSE 0
END) AS '20063',
SUM(CASE
WHEN (YEAR(cout) = 2006 AND MONTH(cout) = 4) THEN 1
ELSE 0
END) AS '20064',
SUM(CASE
WHEN (YEAR(cout) = 2006 AND MONTH(cout) = 5) THEN 1
ELSE 0
END) AS '20065',
SUM(CASE
WHEN (YEAR(cout) = 2006 AND MONTH(cout) = 6) THEN 1
ELSE 0
END) AS '20066',
SUM(CASE
WHEN (YEAR(cout) = 2006 AND MONTH(cout) = 7) THEN 1
ELSE 0
END) AS '20067',
SUM(CASE
WHEN (YEAR(cout) = 2006 AND MONTH(cout) = 8) THEN 1
ELSE 0
END) AS '20068',
SUM(CASE
WHEN (YEAR(cout) = 2006 AND MONTH(cout) = 9) THEN 1
ELSE 0
END) AS '20069',
SUM(CASE
WHEN (YEAR(cout) = 2006 AND MONTH(cout) = 10) THEN 1
ELSE 0
END) AS '200610',
SUM(CASE
WHEN (YEAR(cout) = 2006 AND MONTH(cout) = 11) THEN 1
ELSE 0
END) AS '200611',
SUM(CASE
WHEN (YEAR(cout) = 2006 AND MONTH(cout) = 12) THEN 1
ELSE 0
END) AS '200612',
SUM(CASE
WHEN (YEAR(cout) = 2007 AND MONTH(cout) = 1) THEN 1
ELSE 0
END) AS '20071',
SUM(CASE
WHEN (YEAR(cout) = 2007 AND MONTH(cout) = 2) THEN 1
ELSE 0
END) AS '20072',
SUM(CASE
WHEN (YEAR(cout) = 2007 AND MONTH(cout) = 3) THEN 1
ELSE 0
END) AS '20073',
SUM(CASE
WHEN (YEAR(cout) = 2007 AND MONTH(cout) = 4) THEN 1
ELSE 0
END) AS '20074',
SUM(CASE
WHEN (YEAR(cout) = 2007 AND MONTH(cout) = 5) THEN 1
ELSE 0
END) AS '20075',
SUM(CASE
WHEN (YEAR(cout) = 2007 AND MONTH(cout) = 6) THEN 1
ELSE 0
END) AS '20076',
SUM(CASE
WHEN (YEAR(cout) = 2007 AND MONTH(cout) = 7) THEN 1
ELSE 0
END) AS '20077',
SUM(CASE
WHEN (YEAR(cout) = 2007 AND MONTH(cout) = 8) THEN 1
ELSE 0
END) AS '20078',
SUM(CASE
WHEN (YEAR(cout) = 2007 AND MONTH(cout) = 9) THEN 1
ELSE 0
END) AS '20079',
SUM(CASE
WHEN (YEAR(cout) = 2007 AND MONTH(cout) = 10) THEN 1
ELSE 0
END) AS '200710',
SUM(CASE
WHEN (YEAR(cout) = 2007 AND MONTH(cout) = 11) THEN 1
ELSE 0
END) AS '200711',
SUM(CASE
WHEN (YEAR(cout) = 2007 AND MONTH(cout) = 12) THEN 1
ELSE 0
END) AS '200712',
SUM(CASE
WHEN (YEAR(cout) = 2008 AND MONTH(cout) = 1) THEN 1
ELSE 0
END) AS '20081',
SUM(CASE
WHEN (YEAR(cout) = 2008 AND MONTH(cout) = 2) THEN 1
ELSE 0
END) AS '20082',
SUM(CASE
WHEN (YEAR(cout) = 2008 AND MONTH(cout) = 3) THEN 1
ELSE 0
END) AS '20083',
SUM(CASE
WHEN (YEAR(cout) = 2008 AND MONTH(cout) = 4) THEN 1
ELSE 0
END) AS '20084',
SUM(CASE
WHEN (YEAR(cout) = 2008 AND MONTH(cout) = 5) THEN 1
ELSE 0
END) AS '20085',
SUM(CASE
WHEN (YEAR(cout) = 2008 AND MONTH(cout) = 6) THEN 1
ELSE 0
END) AS '20086',
SUM(CASE
WHEN (YEAR(cout) = 2008 AND MONTH(cout) = 7) THEN 1
ELSE 0
END) AS '20087',
SUM(CASE
WHEN (YEAR(cout) = 2008 AND MONTH(cout) = 8) THEN 1
ELSE 0
END) AS '20088',
SUM(CASE
WHEN (YEAR(cout) = 2008 AND MONTH(cout) = 9) THEN 1
ELSE 0
END) AS '20089',
SUM(CASE
WHEN (YEAR(cout) = 2008 AND MONTH(cout) = 10) THEN 1
ELSE 0
END) AS '200810',
SUM(CASE
WHEN (YEAR(cout) = 2008 AND MONTH(cout) = 11) THEN 1
ELSE 0
END) AS '200811',
SUM(CASE
WHEN (YEAR(cout) = 2008 AND MONTH(cout) = 12) THEN 1
ELSE 0
END) AS '200812',
SUM(CASE
WHEN (YEAR(cout) = 2009 AND MONTH(cout) = 1) THEN 1
ELSE 0
END) AS '20091',
SUM(CASE
WHEN (YEAR(cout) = 2009 AND MONTH(cout) = 2) THEN 1
ELSE 0
END) AS '20092',
SUM(CASE
WHEN (YEAR(cout) = 2009 AND MONTH(cout) = 3) THEN 1
ELSE 0
END) AS '20093',
SUM(CASE
WHEN (YEAR(cout) = 2009 AND MONTH(cout) = 4) THEN 1
ELSE 0
END) AS '20094',
SUM(CASE
WHEN (YEAR(cout) = 2009 AND MONTH(cout) = 5) THEN 1
ELSE 0
END) AS '20095',
SUM(CASE
WHEN (YEAR(cout) = 2009 AND MONTH(cout) = 6) THEN 1
ELSE 0
END) AS '20096',
SUM(CASE
WHEN (YEAR(cout) = 2009 AND MONTH(cout) = 7) THEN 1
ELSE 0
END) AS '20097',
SUM(CASE
WHEN (YEAR(cout) = 2009 AND MONTH(cout) = 8) THEN 1
ELSE 0
END) AS '20098',
SUM(CASE
WHEN (YEAR(cout) = 2009 AND MONTH(cout) = 9) THEN 1
ELSE 0
END) AS '20099',
SUM(CASE
WHEN (YEAR(cout) = 2009 AND MONTH(cout) = 10) THEN 1
ELSE 0
END) AS '200910',
SUM(CASE
WHEN (YEAR(cout) = 2009 AND MONTH(cout) = 11) THEN 1
ELSE 0
END) AS '200911',
SUM(CASE
WHEN (YEAR(cout) = 2009 AND MONTH(cout) = 12) THEN 1
ELSE 0
END) AS '200912',
SUM(CASE
WHEN (YEAR(cout) = 2010 AND MONTH(cout) = 1) THEN 1
ELSE 0
END) AS '20101',
SUM(CASE
WHEN (YEAR(cout) = 2010 AND MONTH(cout) = 2) THEN 1
ELSE 0
END) AS '20102',
SUM(CASE
WHEN (YEAR(cout) = 2010 AND MONTH(cout) = 3) THEN 1
ELSE 0
END) AS '20103',
SUM(CASE
WHEN (YEAR(cout) = 2010 AND MONTH(cout) = 4) THEN 1
ELSE 0
END) AS '20104',
SUM(CASE
WHEN (YEAR(cout) = 2010 AND MONTH(cout) = 5) THEN 1
ELSE 0
END) AS '20105',
SUM(CASE
WHEN (YEAR(cout) = 2010 AND MONTH(cout) = 6) THEN 1
ELSE 0
END) AS '20106',
SUM(CASE
WHEN (YEAR(cout) = 2010 AND MONTH(cout) = 7) THEN 1
ELSE 0
END) AS '20107',
SUM(CASE
WHEN (YEAR(cout) = 2010 AND MONTH(cout) = 8) THEN 1
ELSE 0
END) AS '20108',
SUM(CASE
WHEN (YEAR(cout) = 2010 AND MONTH(cout) = 9) THEN 1
ELSE 0
END) AS '20109',
SUM(CASE
WHEN (YEAR(cout) = 2010 AND MONTH(cout) = 10) THEN 1
ELSE 0
END) AS '201010',
SUM(CASE
WHEN (YEAR(cout) = 2010 AND MONTH(cout) = 11) THEN 1
ELSE 0
END) AS '201011',
SUM(CASE
WHEN (YEAR(cout) = 2010 AND MONTH(cout) = 12) THEN 1
ELSE 0
END) AS '201012',
SUM(CASE
WHEN (YEAR(cout) = 2011 AND MONTH(cout) = 1) THEN 1
ELSE 0
END) AS '20111',
SUM(CASE
WHEN (YEAR(cout) = 2011 AND MONTH(cout) = 2) THEN 1
ELSE 0
END) AS '20112',
SUM(CASE
WHEN (YEAR(cout) = 2011 AND MONTH(cout) = 3) THEN 1
ELSE 0
END) AS '20113',
SUM(CASE
WHEN (YEAR(cout) = 2011 AND MONTH(cout) = 4) THEN 1
ELSE 0
END) AS '20114',
SUM(CASE
WHEN (YEAR(cout) = 2011 AND MONTH(cout) = 5) THEN 1
ELSE 0
END) AS '20115',
SUM(CASE
WHEN (YEAR(cout) = 2011 AND MONTH(cout) = 6) THEN 1
ELSE 0
END) AS '20116',
SUM(CASE
WHEN (YEAR(cout) = 2011 AND MONTH(cout) = 7) THEN 1
ELSE 0
END) AS '20117',
SUM(CASE
WHEN (YEAR(cout) = 2011 AND MONTH(cout) = 8) THEN 1
ELSE 0
END) AS '20118',
SUM(CASE
WHEN (YEAR(cout) = 2011 AND MONTH(cout) = 9) THEN 1
ELSE 0
END) AS '20119',
SUM(CASE
WHEN (YEAR(cout) = 2011 AND MONTH(cout) = 10) THEN 1
ELSE 0
END) AS '201110',
SUM(CASE
WHEN (YEAR(cout) = 2011 AND MONTH(cout) = 11) THEN 1
ELSE 0
END) AS '201111',
SUM(CASE
WHEN (YEAR(cout) = 2011 AND MONTH(cout) = 12) THEN 1
ELSE 0
END) AS '201112',
SUM(CASE
WHEN (YEAR(cout) = 2012 AND MONTH(cout) = 1) THEN 1
ELSE 0
END) AS '20121',
SUM(CASE
WHEN (YEAR(cout) = 2012 AND MONTH(cout) = 2) THEN 1
ELSE 0
END) AS '20122',
SUM(CASE
WHEN (YEAR(cout) = 2012 AND MONTH(cout) = 3) THEN 1
ELSE 0
END) AS '20123',
SUM(CASE
WHEN (YEAR(cout) = 2012 AND MONTH(cout) = 4) THEN 1
ELSE 0
END) AS '20124',
SUM(CASE
WHEN (YEAR(cout) = 2012 AND MONTH(cout) = 5) THEN 1
ELSE 0
END) AS '20125',
SUM(CASE
WHEN (YEAR(cout) = 2012 AND MONTH(cout) = 6) THEN 1
ELSE 0
END) AS '20126',
SUM(CASE
WHEN (YEAR(cout) = 2012 AND MONTH(cout) = 7) THEN 1
ELSE 0
END) AS '20127',
SUM(CASE
WHEN (YEAR(cout) = 2012 AND MONTH(cout) = 8) THEN 1
ELSE 0
END) AS '20128',
SUM(CASE
WHEN (YEAR(cout) = 2012 AND MONTH(cout) = 9) THEN 1
ELSE 0
END) AS '20129',
SUM(CASE
WHEN (YEAR(cout) = 2012 AND MONTH(cout) = 10) THEN 1
ELSE 0
END) AS '201210',
SUM(CASE
WHEN (YEAR(cout) = 2012 AND MONTH(cout) = 11) THEN 1
ELSE 0
END) AS '201211',
SUM(CASE
WHEN (YEAR(cout) = 2012 AND MONTH(cout) = 12) THEN 1
ELSE 0
END) AS '201212',
SUM(CASE
WHEN (YEAR(cout) = 2013 AND MONTH(cout) = 1) THEN 1
ELSE 0
END) AS '20131',
SUM(CASE
WHEN (YEAR(cout) = 2013 AND MONTH(cout) = 2) THEN 1
ELSE 0
END) AS '20132',
SUM(CASE
WHEN (YEAR(cout) = 2013 AND MONTH(cout) = 3) THEN 1
ELSE 0
END) AS '20133',
SUM(CASE
WHEN (YEAR(cout) = 2013 AND MONTH(cout) = 4) THEN 1
ELSE 0
END) AS '20134',
SUM(CASE
WHEN (YEAR(cout) = 2013 AND MONTH(cout) = 5) THEN 1
ELSE 0
END) AS '20135',
SUM(CASE
WHEN (YEAR(cout) = 2013 AND MONTH(cout) = 6) THEN 1
ELSE 0
END) AS '20136',
SUM(CASE
WHEN (YEAR(cout) = 2013 AND MONTH(cout) = 7) THEN 1
ELSE 0
END) AS '20137',
SUM(CASE
WHEN (YEAR(cout) = 2013 AND MONTH(cout) = 8) THEN 1
ELSE 0
END) AS '20138',
SUM(CASE
WHEN (YEAR(cout) = 2013 AND MONTH(cout) = 9) THEN 1
ELSE 0
END) AS '20139',
SUM(CASE
WHEN (YEAR(cout) = 2013 AND MONTH(cout) = 10) THEN 1
ELSE 0
END) AS '201310',
SUM(CASE
WHEN (YEAR(cout) = 2013 AND MONTH(cout) = 11) THEN 1
ELSE 0
END) AS '201311',
SUM(CASE
WHEN (YEAR(cout) = 2013 AND MONTH(cout) = 12) THEN 1
ELSE 0
END) AS '201312',
SUM(CASE
WHEN (YEAR(cout) = 2014 AND MONTH(cout) = 1) THEN 1
ELSE 0
END) AS '20141',
SUM(CASE
WHEN (YEAR(cout) = 2014 AND MONTH(cout) = 2) THEN 1
ELSE 0
END) AS '20142',
SUM(CASE
WHEN (YEAR(cout) = 2014 AND MONTH(cout) = 3) THEN 1
ELSE 0
END) AS '20143',
SUM(CASE
WHEN (YEAR(cout) = 2014 AND MONTH(cout) = 4) THEN 1
ELSE 0
END) AS '20144',
SUM(CASE
WHEN (YEAR(cout) = 2014 AND MONTH(cout) = 5) THEN 1
ELSE 0
END) AS '20145',
SUM(CASE
WHEN (YEAR(cout) = 2014 AND MONTH(cout) = 6) THEN 1
ELSE 0
END) AS '20146',
SUM(CASE
WHEN (YEAR(cout) = 2014 AND MONTH(cout) = 7) THEN 1
ELSE 0
END) AS '20147',
SUM(CASE
WHEN (YEAR(cout) = 2014 AND MONTH(cout) = 8) THEN 1
ELSE 0
END) AS '20148',
SUM(CASE
WHEN (YEAR(cout) = 2014 AND MONTH(cout) = 9) THEN 1
ELSE 0
END) AS '20149',
SUM(CASE
WHEN (YEAR(cout) = 2014 AND MONTH(cout) = 10) THEN 1
ELSE 0
END) AS '201410',
SUM(CASE
WHEN (YEAR(cout) = 2014 AND MONTH(cout) = 11) THEN 1
ELSE 0
END) AS '201411',
SUM(CASE
WHEN (YEAR(cout) = 2014 AND MONTH(cout) = 12) THEN 1
ELSE 0
END) AS '201412',
SUM(CASE
WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 1) THEN 1
ELSE 0
END) AS '20151',
SUM(CASE
WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 2) THEN 1
ELSE 0
END) AS '20152',
SUM(CASE
WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 3) THEN 1
ELSE 0
END) AS '20153',
SUM(CASE
WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 4) THEN 1
ELSE 0
END) AS '20154',
SUM(CASE
WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 5) THEN 1
ELSE 0
END) AS '20155',
SUM(CASE
WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 6) THEN 1
ELSE 0
END) AS '20156',
SUM(CASE
WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 7) THEN 1
ELSE 0
END) AS '20157',
SUM(CASE
WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 8) THEN 1
ELSE 0
END) AS '20158',
SUM(CASE
WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 9) THEN 1
ELSE 0
END) AS '20159',
SUM(CASE
WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 10) THEN 1
ELSE 0
END) AS '201510',
SUM(CASE
WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 11) THEN 1
ELSE 0
END) AS '201511',
SUM(CASE
WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 12) THEN 1
ELSE 0
END) AS '201512',
SUM(CASE
WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 1) THEN 1
ELSE 0
END) AS '20161',
SUM(CASE
WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 2) THEN 1
ELSE 0
END) AS '20162',
SUM(CASE
WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 3) THEN 1
ELSE 0
END) AS '20163',
SUM(CASE
WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 4) THEN 1
ELSE 0
END) AS '20164',
SUM(CASE
WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 5) THEN 1
ELSE 0
END) AS '20165',
SUM(CASE
WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 6) THEN 1
ELSE 0
END) AS '20166',
SUM(CASE
WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 7) THEN 1
ELSE 0
END) AS '20167',
SUM(CASE
WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 8) THEN 1
ELSE 0
END) AS '20168',
SUM(CASE
WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 9) THEN 1
ELSE 0
END) AS '20169',
SUM(CASE
WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 10) THEN 1
ELSE 0
END) AS '201610',
SUM(CASE
WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 11) THEN 1
ELSE 0
END) AS '201611',
SUM(CASE
WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 12) THEN 1
ELSE 0
END) AS '201612',
SUM(CASE
WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 1) THEN 1
ELSE 0
END) AS '20171',
SUM(CASE
WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 2) THEN 1
ELSE 0
END) AS '20172',
SUM(CASE
WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 3) THEN 1
ELSE 0
END) AS '20173',
SUM(CASE
WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 4) THEN 1
ELSE 0
END) AS '20174',
SUM(CASE
WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 5) THEN 1
ELSE 0
END) AS '20175',
SUM(CASE
WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 6) THEN 1
ELSE 0
END) AS '20176',
SUM(CASE
WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 7) THEN 1
ELSE 0
END) AS '20177',
SUM(CASE
WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 8) THEN 1
ELSE 0
END) AS '20178',
SUM(CASE
WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 9) THEN 1
ELSE 0
END) AS '20179',
SUM(CASE
WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 10) THEN 1
ELSE 0
END) AS '201710',
SUM(CASE
WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 11) THEN 1
ELSE 0
END) AS '201711',
SUM(CASE
WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 12) THEN 1
ELSE 0
END) AS '201712'
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
Sketch 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.
WorkinProgress Screenshots
The first plot shows like this, https://drive.google.com/open?id=1Ye4sL ... aDSm0D2BHf
It is just the grayscale plot and we can still see the trend very clearly of the boost of checkout rate.
Then, I applied different colors to different movies, https://drive.google.com/open?id=1ZArRq ... BU5ieT9a7Q
Now it gives a better visual experience on varies movie checkout trend.
Final results & Analysis
Finally, I added a highlight to the time frame, so that we the mouse is hovered, the year’s background will be brightened. (Code reference: Junxiang) https://drive.google.com/open?id=19Xuia ... 99il5AVnmq
With the help of the 2D visualization, we can clear see a pattern/trend of the adaptation movies' influence on original books. Especially if it is a book series, such as the example here for Hunger Games and Twilight Saga when the first movie released, the check out rate of the same book and the following book will boost.
 Attachments

 sketch_2D_hw2_draft1.zip
 (10.59 MiB) Downloaded 51 times
Last edited by aprilcai on Thu Feb 22, 2018 12:11 am, edited 2 times in total.
Re: 2D Frequency Pattern Visualization
Hans Zimmer's OST and Its Original Film Checkout Trend
Concept description
Some movies have reputations in terms of music, in other words, original soundtracks (OST) of a film. In this regard, comparing the popularity of a film with the popularity of its OST could be interesting. To narrow down a data range, I decided to focus on the works of Hans Zimmer, who is a renowned film composer and has received a range of honors and awards, and see the checkouts of OSTs, in which Hans participated or composed, and the checkouts of their original films from the Seattle Public Library dataset.
MySQL Queries
Due to a lack of a data field for authors in SPL dataset, I searched 'Hans Zimmer' in the SPL website (https://goo.gl/MyxFH6) and filtered to see only music CDs. The list includes not only his solo composition works but also works in which he partial contributed by writing only several songs. Based on the search result, I chose main movies for my visualization and queried as below.
Processing time
Duration : 463.161 sec
Sketches The main visual concept is to combine a circular timeline of an item's release date with a linear timeline of an item's checkout. This is because my main purpose in visualization is to see the trend of checkouts not its amount in detail since an item is issued and to compare the trend of two items, the OST and DVD of a film. For comparing two items, OST's trend is flipped in terms of the yaxis of a linear timeline, in other words, values reflecting checkouts, and placed DVD and OST's data together along with the xaxis of a linear timeline. And a film's checkout timeline starts at a certain point of a circular timeline which means the earliest released date among the film's OST and DVD. The length of an arc from the point of a released date to the end of a circular timeline is the same with the length of each film's linear timeline from the point.
Workinprogress screenshots (1st version only) Because of the length relationship between a circular timeline and linear timelines, placing visual objects are somewhat limited in a space. It requires an exact calculation for a radius of the circle and length of a timeline unit segment representing a month. And it has an overlapping issue among linear timelines because of a released datebased visualization approach. Visualizing checkout values as a bar graph, each checkout value is normalized based on the maximum checkout of an item. What I expected in this relative bar graph is to see when an item is the most popular in time and how gradually the popularity changes. Also, by placing a DVD's bar graph and OST's bar graph in a flipped direction, we can compare two items' trend together. A visual theme I intended is a barshaped waveform (https://goo.gl/XFHCmS).
In terms of color scheme, I differentiated a bar graph's main color according to an item for comparison. A red color represents OST checkout data and a white color is for DVDs. And its saturation is proportional to a bar's height which means the stronger color is the more popular an item is at the moment. But, I will do more modification and experiment in color for better visual aesthetic.
Analysis(1st version)
Although each item has a small bar graph, it is enough to discern the trend of checkout and to compare two items of an OST and DVD. In general, it shows the similar checkout trend between OSTs and DVDs. Usually, an OST is issued earlier than its DVD. This fact also can be confirmed by watching the first bar of a film's OST graph which exists prior to that of a film's DVD. Also, the popularity of the OST has a peak right after the issued date and gradually decreases even after the DVD is released.
The overlapping issue can be solved by mouse interaction that when a mouse cursor moves to an overlapping area, it spreads an arc between linear timelines. I will add this interaction with animation.
Final Version
 to be updated 
Concept description
Some movies have reputations in terms of music, in other words, original soundtracks (OST) of a film. In this regard, comparing the popularity of a film with the popularity of its OST could be interesting. To narrow down a data range, I decided to focus on the works of Hans Zimmer, who is a renowned film composer and has received a range of honors and awards, and see the checkouts of OSTs, in which Hans participated or composed, and the checkouts of their original films from the Seattle Public Library dataset.
MySQL Queries
Due to a lack of a data field for authors in SPL dataset, I searched 'Hans Zimmer' in the SPL website (https://goo.gl/MyxFH6) and filtered to see only music CDs. The list includes not only his solo composition works but also works in which he partial contributed by writing only several songs. Based on the search result, I chose main movies for my visualization and queried as below.
Code: Select all
SELECT
YEAR(cout) AS Year,
MONTH(cout) AS Month,
SUM(CASE
WHEN (title LIKE 'Blade Runner 2049%soundtrack%' and itemType LIKE '%cd') THEN 1
ELSE 0
END) AS 'Blade Runner 2049 OST',
SUM(CASE
WHEN (title LIKE 'Blade runner 2049%' and itemType LIKE '%dvd') THEN 1
ELSE 0
END) AS 'Blade Runner 2049',
SUM(CASE
WHEN (title LIKE 'Dunkirk%soundtrack%' and itemType LIKE '%cd') THEN 1
ELSE 0
END) AS 'Dunkirk OST',
SUM(CASE
WHEN (title = 'Dunkirk' and bibNumber = '3276118') THEN 1
ELSE 0
END) AS 'Dunkirk',
SUM(CASE
WHEN (title LIKE 'Hidden figures%original score%' and itemType LIKE '%cd') THEN 1
ELSE 0
END) AS 'Hidden Figures OST',
SUM(CASE
WHEN (title LIKE 'Hidden Figures%' and itemType LIKE '%dvd') THEN 1
ELSE 0
END) AS 'Hidden Figures',
SUM(CASE
WHEN (title LIKE 'Kung Fu Panda 3%music%' and itemType LIKE '%cd') THEN 1
ELSE 0
END) AS 'Kung Fu Panda 3 OST',
SUM(CASE
WHEN (title LIKE 'Kung Fu Panda 3%' and itemType LIKE '%dvd') THEN 1
ELSE 0
END) AS 'Kung Fu Panda 3',
SUM(CASE
WHEN (title LIKE 'Interstellar%soundtrack%' and itemType LIKE '%cd') THEN 1
ELSE 0
END) AS 'Interstellar OST',
SUM(CASE
WHEN (title LIKE 'interstellar%' and itemType LIKE '%dvd') THEN 1
ELSE 0
END) AS 'Interstellar',
SUM(CASE
WHEN (title LIKE 'Son of God%Motion Picture Soundtrack%' and itemType LIKE '%cd') THEN 1
ELSE 0
END) AS 'Son of God OST',
SUM(CASE
WHEN (title LIKE 'Son of God%' and itemType LIKE '%dvd') THEN 1
ELSE 0
END) AS 'Son of God',
SUM(CASE
WHEN (title LIKE 'Man of Steel%soundtrack%' and itemType LIKE '%cd') THEN 1
ELSE 0
END) AS 'Man of Steel OST',
SUM(CASE
WHEN (title LIKE 'Man of Steel%' and itemType LIKE '%dvd') THEN 1
ELSE 0
END) AS 'Man of Steel',
SUM(CASE
WHEN (title LIKE 'Inception%Music%Motion Picture%' and itemType LIKE '%cd') THEN 1
ELSE 0
END) AS 'Inception OST',
SUM(CASE
WHEN (title LIKE 'Inception%' and itemType LIKE '%dvd') THEN 1
ELSE 0
END) AS 'Inception',
SUM(CASE
WHEN (title LIKE 'Sherlock Holmes%motion picture soundtrack%' and itemType LIKE '%cd') THEN 1
ELSE 0
END) AS 'Sherlock Holmes OST',
SUM(CASE
WHEN (title = 'Sherlock Holmes' and itemType LIKE '%dvd') THEN 1
ELSE 0
END) AS 'Sherlock Holmes',
SUM(CASE
WHEN (title LIKE 'Madagascar%motion picture soundtrack%' and itemType LIKE '%cd') THEN 1
ELSE 0
END) AS 'Madagascar OST',
SUM(CASE
WHEN (title = 'Madagascar' and itemType LIKE '%dvd') THEN 1
ELSE 0
END) AS 'Madagascar',
SUM(CASE
WHEN (title LIKE 'Madagascar%2%Music%Motion Picture%' and itemType LIKE '%cd') THEN 1
ELSE 0
END) AS 'Madagascar Escape 2 Africa OST',
SUM(CASE
WHEN (title = 'Madagascar Escape 2 Africa' and itemType LIKE '%dvd') THEN 1
ELSE 0
END) AS 'Madagascar Escape 2 Africa',
SUM(CASE
WHEN (title LIKE 'Madagascar 3%Music%Motion Picture%' and itemType LIKE '%cd') THEN 1
ELSE 0
END) AS 'Madagascar 3 Europes Most Wanted OST',
SUM(CASE
WHEN (title = 'Madagascar 3 Europes most wanted' and itemType LIKE '%dvd') THEN 1
ELSE 0
END) AS 'Madagascar 3 Europes Most Wanted',
SUM(CASE
WHEN (title LIKE '12 Years A Slave%Music%Motion Picture%' and itemType LIKE '%cd') THEN 1
ELSE 0
END) AS '12 Years A Slave OST',
SUM(CASE
WHEN (title = '12 Years A Slave' and itemType LIKE '%dvd') THEN 1
ELSE 0
END) AS '12 Years A Slave'
FROM
spl_2016.outraw
GROUP BY YEAR(cout) , MONTH(cout)
ORDER BY YEAR(cout) , MONTH(cout)
Duration : 463.161 sec
Sketches The main visual concept is to combine a circular timeline of an item's release date with a linear timeline of an item's checkout. This is because my main purpose in visualization is to see the trend of checkouts not its amount in detail since an item is issued and to compare the trend of two items, the OST and DVD of a film. For comparing two items, OST's trend is flipped in terms of the yaxis of a linear timeline, in other words, values reflecting checkouts, and placed DVD and OST's data together along with the xaxis of a linear timeline. And a film's checkout timeline starts at a certain point of a circular timeline which means the earliest released date among the film's OST and DVD. The length of an arc from the point of a released date to the end of a circular timeline is the same with the length of each film's linear timeline from the point.
Workinprogress screenshots (1st version only) Because of the length relationship between a circular timeline and linear timelines, placing visual objects are somewhat limited in a space. It requires an exact calculation for a radius of the circle and length of a timeline unit segment representing a month. And it has an overlapping issue among linear timelines because of a released datebased visualization approach. Visualizing checkout values as a bar graph, each checkout value is normalized based on the maximum checkout of an item. What I expected in this relative bar graph is to see when an item is the most popular in time and how gradually the popularity changes. Also, by placing a DVD's bar graph and OST's bar graph in a flipped direction, we can compare two items' trend together. A visual theme I intended is a barshaped waveform (https://goo.gl/XFHCmS).
In terms of color scheme, I differentiated a bar graph's main color according to an item for comparison. A red color represents OST checkout data and a white color is for DVDs. And its saturation is proportional to a bar's height which means the stronger color is the more popular an item is at the moment. But, I will do more modification and experiment in color for better visual aesthetic.
Analysis(1st version)
Although each item has a small bar graph, it is enough to discern the trend of checkout and to compare two items of an OST and DVD. In general, it shows the similar checkout trend between OSTs and DVDs. Usually, an OST is issued earlier than its DVD. This fact also can be confirmed by watching the first bar of a film's OST graph which exists prior to that of a film's DVD. Also, the popularity of the OST has a peak right after the issued date and gradually decreases even after the DVD is released.
The overlapping issue can be solved by mouse interaction that when a mouse cursor moves to an overlapping area, it spreads an arc between linear timelines. I will add this interaction with animation.
Final Version
 to be updated 
 Attachments

 HansZimmer.zip
 (7.39 KiB) Downloaded 36 times
Last edited by sihwapark on Thu Feb 15, 2018 4:24 pm, edited 3 times in total.
Re: 2D Frequency Pattern Visualization
Quentin Tarantino's Films and His Oscar Years
Concept description

Quentin Tarantino is famous for his unique violence aesthetics. And his filmmaking style has won him tons of Academy awards nomination and several Oscars. In 2010, 2013 and 2016, his Inglorious Basterds, Unchained Django and the Hateful Eight are all nominated. It will be interesting to see the correlation between his nomination years and his films' checkouts in SPL in those years.
MySQL Queries

Processing time

Durations: 77.894 sec
Sketches and workinprogress screenshots of your project with descriptions

Sketches: My visualization strategy is inspired by radar and solar system. On a radar panel, a circular area is divided equally into squential sectors. The target will show up on the panel when it is being scanned and then fade out. Also, when the target is closer to the center, it is closer to the radar.
Similarly, in my visualization, years are represented as sequential sectors in a circle. As a pointer scans through these areas, the corresponding yearly checkouts, which is represented by points, will show up and gradually fade out when the pointer leaves. The quantity of checkouts is shown by the size of each point.
Quentin has 9 movies, so I assign every one of them a unique color and put it in a fixed orbit just like the solar system. Every film circles around the center, like 8 stars circles around the sun.
Workinprogress screenshots Final results & Analysis
 It's easy to distinguish that in 2010, 2013 and 2016, there are big dots and cluster lying in its area, which show drastic increases for its yearly checkouts. And checkouts for his old movies also increases a little bit in his Oscar years.
Concept description

Quentin Tarantino is famous for his unique violence aesthetics. And his filmmaking style has won him tons of Academy awards nomination and several Oscars. In 2010, 2013 and 2016, his Inglorious Basterds, Unchained Django and the Hateful Eight are all nominated. It will be interesting to see the correlation between his nomination years and his films' checkouts in SPL in those years.
MySQL Queries

Code: Select all
SELECT
bibNumber,
itemType,
title,
COUNT(bibNumber) AS Counts,
SUM(CASE
WHEN (YEAR(cout) = 2006) THEN 1
ELSE 0
END) AS '2006',
SUM(CASE
WHEN (YEAR(cout) = 2007) THEN 1
ELSE 0
END) AS '2007',
SUM(CASE
WHEN (YEAR(cout) = 2008) THEN 1
ELSE 0
END) AS '2008',
SUM(CASE
WHEN (YEAR(cout) = 2009) THEN 1
ELSE 0
END) AS '2009',
SUM(CASE
WHEN (YEAR(cout) = 2010) THEN 1
ELSE 0
END) AS '2010',
SUM(CASE
WHEN (YEAR(cout) = 2011) THEN 1
ELSE 0
END) AS '2011',
SUM(CASE
WHEN (YEAR(cout) = 2012) THEN 1
ELSE 0
END) AS '2012',
SUM(CASE
WHEN (YEAR(cout) = 2013) THEN 1
ELSE 0
END) AS '2013',
SUM(CASE
WHEN (YEAR(cout) = 2014) THEN 1
ELSE 0
END) AS '2014',
SUM(CASE
WHEN (YEAR(cout) = 2015) THEN 1
ELSE 0
END) AS '2015',
SUM(CASE
WHEN (YEAR(cout) = 2016) THEN 1
ELSE 0
END) AS '2016',
SUM(CASE
WHEN (YEAR(cout) = 2017) THEN 1
ELSE 0
END) AS '2017'
FROM
spl_2016.outraw
WHERE
title = 'Reservoir dogs'
or title = 'Pulp fiction'
OR title = 'Pulp fiction the complete story of Quentin Tarantinos masterpiece'
OR title = 'Quentin Tarantinos Death proof original soundtrack'
OR title = 'Kill Bill volume 1'
OR title = 'Kill Bill Vol 2 original soundtrack'
OR title = 'Kill Bill Vol 2'
OR title = 'Kill Bill Vol 1 original soundtrack'
OR title = 'Kill Bill diary the making of a Tarantino classic as seen through the eyes of a screen legend'
OR title = 'Jackie Brown a screenplay'
OR title = 'Jackie Brown a Quentin Tarantino film music from the Miramar motion picture'
OR title = 'Jackie Brown'
OR title = 'Inglourious Basterds'
OR title = 'hateful eight'
OR title = 'Django unchained original motion picture soundtrack'
OR title LIKE '%Django Unchained%'
OR title = 'Death proof'
OR title = 'Death proof original soundtrack'
GROUP BY bibNumber , itemtype, title
ORDER BY Counts DESC

Durations: 77.894 sec
Sketches and workinprogress screenshots of your project with descriptions

Sketches: My visualization strategy is inspired by radar and solar system. On a radar panel, a circular area is divided equally into squential sectors. The target will show up on the panel when it is being scanned and then fade out. Also, when the target is closer to the center, it is closer to the radar.
Similarly, in my visualization, years are represented as sequential sectors in a circle. As a pointer scans through these areas, the corresponding yearly checkouts, which is represented by points, will show up and gradually fade out when the pointer leaves. The quantity of checkouts is shown by the size of each point.
Quentin has 9 movies, so I assign every one of them a unique color and put it in a fixed orbit just like the solar system. Every film circles around the center, like 8 stars circles around the sun.
Workinprogress screenshots Final results & Analysis
 It's easy to distinguish that in 2010, 2013 and 2016, there are big dots and cluster lying in its area, which show drastic increases for its yearly checkouts. And checkouts for his old movies also increases a little bit in his Oscar years.
 Attachments

 HW2_2D_Visualization.zip
 (7.8 KiB) Downloaded 38 times
Re: 2D Frequency Pattern Visualization
Concept Description
I'm curious to see how politics influence people on their reading materials. If we search the name of the current president Donald Trump on Amazon books, we can find as many as six pages of results. He coauthored most of these books. I believe that if people are curious about their presidential candidate, frequent library visitors might be interested in book by him or about him.
Trump's campaign started in June 2015 and since then he has been a frequent visitor of media headlines. I assume that a correlation between major milestones during his campaign and the checkedin/out records of his books in the Seattle Public Library.
MySQL Queries
Since we only care about correlation between Trump's campaign and related titles, there is no need to count the same title Trump: The Art of the Deal in with four different bibNumbers. Even though they are different media, three books and one CD.
Processing time
0.01 second
The modified query combines different bibNumbers of the same title into one entry. Referenced the monthly analysis code from Rodger Luo's Blade Runner report, the data exported as a .cvs file from MySQL for further processing.
Processing time
0.07 second
Sketches
WorkinProgress 1st Version
I'm curious to see how politics influence people on their reading materials. If we search the name of the current president Donald Trump on Amazon books, we can find as many as six pages of results. He coauthored most of these books. I believe that if people are curious about their presidential candidate, frequent library visitors might be interested in book by him or about him.
Trump's campaign started in June 2015 and since then he has been a frequent visitor of media headlines. I assume that a correlation between major milestones during his campaign and the checkedin/out records of his books in the Seattle Public Library.
MySQL Queries
Since we only care about correlation between Trump's campaign and related titles, there is no need to count the same title Trump: The Art of the Deal in with four different bibNumbers. Even though they are different media, three books and one CD.
Code: Select all
SELECT
Title, COUNT(bibNumber) AS Counts, bibNumber, Itemtype
FROM
spl_2016.inraw
WHERE
title = 'Trump The Art of the Deal'
OR title = 'Trump Think Like a Billionaire'
OR title = 'Youve Been Trumped'
GROUP BY title , bibNumber , itemtype
ORDER BY Counts DESC
LIMIT 1000
0.01 second
The modified query combines different bibNumbers of the same title into one entry. Referenced the monthly analysis code from Rodger Luo's Blade Runner report, the data exported as a .cvs file from MySQL for further processing.
Code: Select all
SELECT
Title,
COUNT(title) AS Counts,
SUM(CASE
WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 1) THEN 1
ELSE 0
END) AS '20151',
SUM(CASE
WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 2) THEN 1
ELSE 0
END) AS '20152',
SUM(CASE
WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 3) THEN 1
ELSE 0
END) AS '20153',
SUM(CASE
WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 4) THEN 1
ELSE 0
END) AS '20154',
SUM(CASE
WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 5) THEN 1
ELSE 0
END) AS '20155',
SUM(CASE
WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 6) THEN 1
ELSE 0
END) AS '20156',
SUM(CASE
WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 7) THEN 1
ELSE 0
END) AS '20157',
SUM(CASE
WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 8) THEN 1
ELSE 0
END) AS '20158',
SUM(CASE
WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 9) THEN 1
ELSE 0
END) AS '20159',
SUM(CASE
WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 10) THEN 1
ELSE 0
END) AS '201510',
SUM(CASE
WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 11) THEN 1
ELSE 0
END) AS '201511',
SUM(CASE
WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 12) THEN 1
ELSE 0
END) AS '201512',
SUM(CASE
WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 1) THEN 1
ELSE 0
END) AS '20161',
SUM(CASE
WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 2) THEN 1
ELSE 0
END) AS '20162',
SUM(CASE
WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 3) THEN 1
ELSE 0
END) AS '20163',
SUM(CASE
WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 4) THEN 1
ELSE 0
END) AS '20164',
SUM(CASE
WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 5) THEN 1
ELSE 0
END) AS '20165',
SUM(CASE
WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 6) THEN 1
ELSE 0
END) AS '20166',
SUM(CASE
WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 7) THEN 1
ELSE 0
END) AS '20167',
SUM(CASE
WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 8) THEN 1
ELSE 0
END) AS '20168',
SUM(CASE
WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 9) THEN 1
ELSE 0
END) AS '20169',
SUM(CASE
WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 10) THEN 1
ELSE 0
END) AS '201610',
SUM(CASE
WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 11) THEN 1
ELSE 0
END) AS '201611',
SUM(CASE
WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 12) THEN 1
ELSE 0
END) AS '201612',
SUM(CASE
WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 1) THEN 1
ELSE 0
END) AS '20171',
SUM(CASE
WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 2) THEN 1
ELSE 0
END) AS '20172',
SUM(CASE
WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 3) THEN 1
ELSE 0
END) AS '20173',
SUM(CASE
WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 4) THEN 1
ELSE 0
END) AS '20174',
SUM(CASE
WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 5) THEN 1
ELSE 0
END) AS '20175',
SUM(CASE
WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 6) THEN 1
ELSE 0
END) AS '20176',
SUM(CASE
WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 7) THEN 1
ELSE 0
END) AS '20177',
SUM(CASE
WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 8) THEN 1
ELSE 0
END) AS '20178',
SUM(CASE
WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 9) THEN 1
ELSE 0
END) AS '20179',
SUM(CASE
WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 10) THEN 1
ELSE 0
END) AS '201710',
SUM(CASE
WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 11) THEN 1
ELSE 0
END) AS '201711',
SUM(CASE
WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 12) THEN 1
ELSE 0
END) AS '201712'
FROM
spl_2016.outraw
WHERE
title = 'Trump The Art of the Deal'
OR title = 'Trump Think Like a Billionaire'
OR title = 'Youve Been Trumped'
GROUP BY title
ORDER BY Counts DESC
0.07 second
Sketches
WorkinProgress 1st Version
Last edited by chengyuan on Thu Feb 08, 2018 11:10 pm, edited 1 time in total.
Re: 2D Frequency Pattern Visualization
CONCEPT
Feminism was named as the word of year for 2017 by the MerriamWebster dictionary. The online searches for the word, feminism, has exponentially increased by 70% compared to the previous year in 2016. Searches for the term found its largest spike following up to the Women’s March in January.
The most recent anniversary of the Women’s March has inspired me to extract data that includes similar phrases like ‘feminism’ or ‘feminist’ from the Seattle Public Library (SPL) database. I am exploring specific checkedout items that contains these similar phrases in the title. My objective is to explore whether or not the rising popularity of feminism have made a direct impact to the items being checkedout in the Seattle Public Library.
QUERY
Duration
0.380 sec / 0.0012 sec
RESULTS
I was inspired by the Tableau format. I mapped out the skeleton of the template by measuring the pixels. Although I ran into trouble with creating the top line graph from my initial template, it is still an element I plan on factoring in.
Feminism was named as the word of year for 2017 by the MerriamWebster dictionary. The online searches for the word, feminism, has exponentially increased by 70% compared to the previous year in 2016. Searches for the term found its largest spike following up to the Women’s March in January.
The most recent anniversary of the Women’s March has inspired me to extract data that includes similar phrases like ‘feminism’ or ‘feminist’ from the Seattle Public Library (SPL) database. I am exploring specific checkedout items that contains these similar phrases in the title. My objective is to explore whether or not the rising popularity of feminism have made a direct impact to the items being checkedout in the Seattle Public Library.
QUERY
Code: Select all
SELECT
title, bibNumber, COUNT(bibNumber) AS counts,
# 2006
SUM(CASE
WHEN (YEAR(cout) = 2006 and month (cout) = 1) THEN 1
ELSE 0
END) AS '20061',
SUM(CASE
WHEN (YEAR(cout) = 2006 and month(cout) = 2) THEN 1
ELSE 0
END) AS '20062',
SUM(CASE
WHEN (YEAR(cout) = 2006 and month(cout) = 3) THEN 1
ELSE 0
END) AS '20063',
SUM(CASE
WHEN (YEAR(cout) = 2006 and month(cout) = 4) THEN 1
ELSE 0
END) AS '20064',
SUM(CASE
WHEN (YEAR(cout) = 2006 and month(cout) = 5) THEN 1
ELSE 0
END) AS '20065',
SUM(CASE
WHEN (YEAR(cout) = 2006 and month(cout) = 6) THEN 1
ELSE 0
END) AS '20066',
SUM(CASE
WHEN (YEAR(cout) = 2006 and month(cout) = 7) THEN 1
ELSE 0
END) AS '20067',
SUM(CASE
WHEN (YEAR(cout) = 2006 and month(cout) = 8) THEN 1
ELSE 0
END) AS '20068',
SUM(CASE
WHEN (YEAR(cout) = 2006 and month(cout) = 9) THEN 1
ELSE 0
END) AS '20069',
SUM(CASE
WHEN (YEAR(cout) = 2006 and month(cout) = 10) THEN 1
ELSE 0
END) AS '200610',
SUM(CASE
WHEN (YEAR(cout) = 2006 and month(cout) = 11) THEN 1
ELSE 0
END) AS '200611',
SUM(CASE
WHEN (YEAR(cout) = 2006 and month(cout) = 12) THEN 1
ELSE 0
END) AS '200612',
# 2007
SUM(CASE
WHEN (YEAR(cout) = 2007 and month(cout) = 1) THEN 1
ELSE 0
END) AS '20071',
SUM(CASE
WHEN (YEAR(cout) = 2007 and month(cout) = 2) THEN 1
ELSE 0
END) AS '20072',
SUM(CASE
WHEN (YEAR(cout) = 2007 and month(cout) = 3) THEN 1
ELSE 0
END) AS '20073',
SUM(CASE
WHEN (YEAR(cout) = 2007 and month(cout) = 4) THEN 1
ELSE 0
END) AS '20074',
SUM(CASE
WHEN (YEAR(cout) = 2007 and month(cout) = 5) THEN 1
ELSE 0
END) AS '20075',
SUM(CASE
WHEN (YEAR(cout) = 2007 and month(cout) = 6) THEN 1
ELSE 0
END) AS '20076',
SUM(CASE
WHEN (YEAR(cout) = 2007 and month(cout) = 7) THEN 1
ELSE 0
END) AS '20077',
SUM(CASE
WHEN (YEAR(cout) = 2007 and month(cout) = 8) THEN 1
ELSE 0
END) AS '20078',
SUM(CASE
WHEN (YEAR(cout) = 2007 and month(cout) = 9) THEN 1
ELSE 0
END) AS '20079',
SUM(CASE
WHEN (YEAR(cout) = 2007 and month(cout) = 10) THEN 1
ELSE 0
END) AS '200710',
SUM(CASE
WHEN (YEAR(cout) = 2007 and month(cout) = 11) THEN 1
ELSE 0
END) AS '200711',
SUM(CASE
WHEN (YEAR(cout) = 2007 and month(cout) = 12) THEN 1
ELSE 0
END) AS '200712',
# 2008
SUM(CASE
WHEN (YEAR(cout) = 2008 and month(cout) = 1) THEN 1
ELSE 0
END) AS '20081',
SUM(CASE
WHEN (YEAR(cout) = 2008 and month(cout) = 2) THEN 1
ELSE 0
END) AS '20082',
SUM(CASE
WHEN (YEAR(cout) = 2008 and month(cout) = 3) THEN 1
ELSE 0
END) AS '20083',
SUM(CASE
WHEN (YEAR(cout) = 2008 and month(cout) = 4) THEN 1
ELSE 0
END) AS '20084',
SUM(CASE
WHEN (YEAR(cout) = 2008 and month(cout) = 5) THEN 1
ELSE 0
END) AS '20085',
SUM(CASE
WHEN (YEAR(cout) = 2008 and month(cout) = 6) THEN 1
ELSE 0
END) AS '20086',
SUM(CASE
WHEN (YEAR(cout) = 2008 and month(cout) = 7) THEN 1
ELSE 0
END) AS '20087',
SUM(CASE
WHEN (YEAR(cout) = 2008 and month(cout) = 8) THEN 1
ELSE 0
END) AS '20088',
SUM(CASE
WHEN (YEAR(cout) = 2008 and month(cout) = 9) THEN 1
ELSE 0
END) AS '20089',
SUM(CASE
WHEN (YEAR(cout) = 2008 and month(cout) = 10) THEN 1
ELSE 0
END) AS '200810',
SUM(CASE
WHEN (YEAR(cout) = 2008 and month(cout) = 11) THEN 1
ELSE 0
END) AS '200811',
SUM(CASE
WHEN (YEAR(cout) = 2008 and month(cout) = 12) THEN 1
ELSE 0
END) AS '200812',
# 2009
SUM(CASE
WHEN (YEAR(cout) = 2009 and month(cout) = 1) THEN 1
ELSE 0
END) AS '20091',
SUM(CASE
WHEN (YEAR(cout) = 2009 and month(cout) = 2) THEN 1
ELSE 0
END) AS '20092',
SUM(CASE
WHEN (YEAR(cout) = 2009 and month(cout) = 3) THEN 1
ELSE 0
END) AS '20093',
SUM(CASE
WHEN (YEAR(cout) = 2009 and month(cout) = 4) THEN 1
ELSE 0
END) AS '20094',
SUM(CASE
WHEN (YEAR(cout) = 2009 and month(cout) = 5) THEN 1
ELSE 0
END) AS '20095',
SUM(CASE
WHEN (YEAR(cout) = 2009 and month(cout) = 6) THEN 1
ELSE 0
END) AS '20096',
SUM(CASE
WHEN (YEAR(cout) = 2009 and month(cout) = 7) THEN 1
ELSE 0
END) AS '20097',
SUM(CASE
WHEN (YEAR(cout) = 2009 and month(cout) = 8) THEN 1
ELSE 0
END) AS '20098',
SUM(CASE
WHEN (YEAR(cout) = 2009 and month(cout) = 9) THEN 1
ELSE 0
END) AS '20099',
SUM(CASE
WHEN (YEAR(cout) = 2009 and month(cout) = 10) THEN 1
ELSE 0
END) AS '200910',
SUM(CASE
WHEN (YEAR(cout) = 2009 and month(cout) = 11) THEN 1
ELSE 0
END) AS '200911',
SUM(CASE
WHEN (YEAR(cout) = 2009 and month(cout) = 12) THEN 1
ELSE 0
END) AS '200912',
# 2010
SUM(CASE
WHEN (YEAR(cout) = 2010 and month(cout) = 1) THEN 1
ELSE 0
END) AS '20101',
SUM(CASE
WHEN (YEAR(cout) = 2010 and month(cout) = 2) THEN 1
ELSE 0
END) AS '20102',
SUM(CASE
WHEN (YEAR(cout) = 2010 and month(cout) = 3) THEN 1
ELSE 0
END) AS '20103',
SUM(CASE
WHEN (YEAR(cout) = 2010 and month(cout) = 4) THEN 1
ELSE 0
END) AS '20104',
SUM(CASE
WHEN (YEAR(cout) = 2010 and month(cout) = 5) THEN 1
ELSE 0
END) AS '20105',
SUM(CASE
WHEN (YEAR(cout) = 2010 and month(cout) = 6) THEN 1
ELSE 0
END) AS '20106',
SUM(CASE
WHEN (YEAR(cout) = 2010 and month(cout) = 7) THEN 1
ELSE 0
END) AS '20107',
SUM(CASE
WHEN (YEAR(cout) = 2010 and month(cout) = 8) THEN 1
ELSE 0
END) AS '20108',
SUM(CASE
WHEN (YEAR(cout) = 2010 and month(cout) = 9) THEN 1
ELSE 0
END) AS '20109',
SUM(CASE
WHEN (YEAR(cout) = 2010 and month(cout) = 10) THEN 1
ELSE 0
END) AS '201010',
SUM(CASE
WHEN (YEAR(cout) = 2010 and month(cout) = 11) THEN 1
ELSE 0
END) AS '201011',
SUM(CASE
WHEN (YEAR(cout) = 2010 and month(cout) = 12) THEN 1
ELSE 0
END) AS '201012',
# 2011
SUM(CASE
WHEN (YEAR(cout) = 2011 and month(cout) = 1) THEN 1
ELSE 0
END) AS '20111',
SUM(CASE
WHEN (YEAR(cout) = 2011 and month(cout) = 2) THEN 1
ELSE 0
END) AS '20112',
SUM(CASE
WHEN (YEAR(cout) = 2011 and month(cout) = 3) THEN 1
ELSE 0
END) AS '20113',
SUM(CASE
WHEN (YEAR(cout) = 2011 and month(cout) = 4) THEN 1
ELSE 0
END) AS '20114',
SUM(CASE
WHEN (YEAR(cout) = 2011 and month(cout) = 5) THEN 1
ELSE 0
END) AS '20115',
SUM(CASE
WHEN (YEAR(cout) = 2011 and month(cout) = 6) THEN 1
ELSE 0
END) AS '20116',
SUM(CASE
WHEN (YEAR(cout) = 2011 and month(cout) = 7) THEN 1
ELSE 0
END) AS '20117',
SUM(CASE
WHEN (YEAR(cout) = 2011 and month(cout) = 8) THEN 1
ELSE 0
END) AS '20118',
SUM(CASE
WHEN (YEAR(cout) = 2011 and month(cout) = 9) THEN 1
ELSE 0
END) AS '20119',
SUM(CASE
WHEN (YEAR(cout) = 2011 and month(cout) = 10) THEN 1
ELSE 0
END) AS '201110',
SUM(CASE
WHEN (YEAR(cout) = 2011 and month(cout) = 11) THEN 1
ELSE 0
END) AS '201111',
SUM(CASE
WHEN (YEAR(cout) = 2011 and month(cout) = 12) THEN 1
ELSE 0
END) AS '201112',
# 2012
SUM(CASE
WHEN (YEAR(cout) = 2012 and month(cout) = 1) THEN 1
ELSE 0
END) AS '20121',
SUM(CASE
WHEN (YEAR(cout) = 2012 and month(cout) = 2) THEN 1
ELSE 0
END) AS '20122',
SUM(CASE
WHEN (YEAR(cout) = 2012 and month(cout) = 3) THEN 1
ELSE 0
END) AS '20123',
SUM(CASE
WHEN (YEAR(cout) = 2012 and month(cout) = 4) THEN 1
ELSE 0
END) AS '20124',
SUM(CASE
WHEN (YEAR(cout) = 2012 and month(cout) = 5) THEN 1
ELSE 0
END) AS '20125',
SUM(CASE
WHEN (YEAR(cout) = 2012 and month(cout) = 6) THEN 1
ELSE 0
END) AS '20126',
SUM(CASE
WHEN (YEAR(cout) = 2012 and month(cout) = 7) THEN 1
ELSE 0
END) AS '20127',
SUM(CASE
WHEN (YEAR(cout) = 2012 and month(cout) = 8) THEN 1
ELSE 0
END) AS '20128',
SUM(CASE
WHEN (YEAR(cout) = 2012 and month(cout) = 9) THEN 1
ELSE 0
END) AS '20129',
SUM(CASE
WHEN (YEAR(cout) = 2012 and month(cout) = 10) THEN 1
ELSE 0
END) AS '201210',
SUM(CASE
WHEN (YEAR(cout) = 2012 and month(cout) = 11) THEN 1
ELSE 0
END) AS '201211',
SUM(CASE
WHEN (YEAR(cout) = 2012 and month(cout) = 12) THEN 1
ELSE 0
END) AS '201212',
# 2013
SUM(CASE
WHEN (YEAR(cout) = 2013 and month(cout) = 1) THEN 1
ELSE 0
END) AS '20131',
SUM(CASE
WHEN (YEAR(cout) = 2013 and month(cout) = 2) THEN 1
ELSE 0
END) AS '20132',
SUM(CASE
WHEN (YEAR(cout) = 2013 and month(cout) = 3) THEN 1
ELSE 0
END) AS '20133',
SUM(CASE
WHEN (YEAR(cout) = 2013 and month(cout) = 4) THEN 1
ELSE 0
END) AS '20134',
SUM(CASE
WHEN (YEAR(cout) = 2013 and month(cout) = 5) THEN 1
ELSE 0
END) AS '20135',
SUM(CASE
WHEN (YEAR(cout) = 2013 and month(cout) = 6) THEN 1
ELSE 0
END) AS '20136',
SUM(CASE
WHEN (YEAR(cout) = 2013 and month(cout) = 7) THEN 1
ELSE 0
END) AS '20137',
SUM(CASE
WHEN (YEAR(cout) = 2013 and month(cout) = 8) THEN 1
ELSE 0
END) AS '20138',
SUM(CASE
WHEN (YEAR(cout) = 2013 and month(cout) = 9) THEN 1
ELSE 0
END) AS '20139',
SUM(CASE
WHEN (YEAR(cout) = 2013 and month(cout) = 10) THEN 1
ELSE 0
END) AS '201310',
SUM(CASE
WHEN (YEAR(cout) = 2013 and month(cout) = 11) THEN 1
ELSE 0
END) AS '201311',
SUM(CASE
WHEN (YEAR(cout) = 2013 and month(cout) = 12) THEN 1
ELSE 0
END) AS '201312',
# 2014
SUM(CASE
WHEN (YEAR(cout) = 2014 and month(cout) = 1) THEN 1
ELSE 0
END) AS '20141',
SUM(CASE
WHEN (YEAR(cout) = 2014 and month(cout) = 2) THEN 1
ELSE 0
END) AS '20142',
SUM(CASE
WHEN (YEAR(cout) = 2014 and month(cout) = 3) THEN 1
ELSE 0
END) AS '20143',
SUM(CASE
WHEN (YEAR(cout) = 2014 and month(cout) = 4) THEN 1
ELSE 0
END) AS '20144',
SUM(CASE
WHEN (YEAR(cout) = 2014 and month(cout) = 5) THEN 1
ELSE 0
END) AS '20145',
SUM(CASE
WHEN (YEAR(cout) = 2014 and month(cout) = 6) THEN 1
ELSE 0
END) AS '20146',
SUM(CASE
WHEN (YEAR(cout) = 2014 and month(cout) = 7) THEN 1
ELSE 0
END) AS '20147',
SUM(CASE
WHEN (YEAR(cout) = 2014 and month(cout) = 8) THEN 1
ELSE 0
END) AS '20148',
SUM(CASE
WHEN (YEAR(cout) = 2014 and month(cout) = 9) THEN 1
ELSE 0
END) AS '20149',
SUM(CASE
WHEN (YEAR(cout) = 2014 and month(cout) = 10) THEN 1
ELSE 0
END) AS '201410',
SUM(CASE
WHEN (YEAR(cout) = 2014 and month(cout) = 11) THEN 1
ELSE 0
END) AS '201411',
SUM(CASE
WHEN (YEAR(cout) = 2014 and month(cout) = 12) THEN 1
ELSE 0
END) AS '201412',
# 2015
SUM(CASE
WHEN (YEAR(cout) = 2015 and month(cout) = 1) THEN 1
ELSE 0
END) AS '20151',
SUM(CASE
WHEN (YEAR(cout) = 2015 and month(cout) = 2) THEN 1
ELSE 0
END) AS '20152',
SUM(CASE
WHEN (YEAR(cout) = 2015 and month(cout) = 3) THEN 1
ELSE 0
END) AS '20153',
SUM(CASE
WHEN (YEAR(cout) = 2015 and month(cout) = 4) THEN 1
ELSE 0
END) AS '20154',
SUM(CASE
WHEN (YEAR(cout) = 2015 and month(cout) = 5) THEN 1
ELSE 0
END) AS '20155',
SUM(CASE
WHEN (YEAR(cout) = 2015 and month(cout) = 6) THEN 1
ELSE 0
END) AS '20156',
SUM(CASE
WHEN (YEAR(cout) = 2015 and month(cout) = 7) THEN 1
ELSE 0
END) AS '20157',
SUM(CASE
WHEN (YEAR(cout) = 2015 and month(cout) = 8) THEN 1
ELSE 0
END) AS '20158',
SUM(CASE
WHEN (YEAR(cout) = 2015 and month(cout) = 9) THEN 1
ELSE 0
END) AS '20159',
SUM(CASE
WHEN (YEAR(cout) = 2015 and month(cout) = 10) THEN 1
ELSE 0
END) AS '201510',
SUM(CASE
WHEN (YEAR(cout) = 2015 and month(cout) = 11) THEN 1
ELSE 0
END) AS '201511',
SUM(CASE
WHEN (YEAR(cout) = 2015 and month(cout) = 12) THEN 1
ELSE 0
END) AS '201512',
# 2016
SUM(CASE
WHEN (YEAR(cout) = 2016 and month(cout) = 1) THEN 1
ELSE 0
END) AS '20161',
SUM(CASE
WHEN (YEAR(cout) = 2016 and month(cout) = 2) THEN 1
ELSE 0
END) AS '20162',
SUM(CASE
WHEN (YEAR(cout) = 2016 and month(cout) = 3) THEN 1
ELSE 0
END) AS '20163',
SUM(CASE
WHEN (YEAR(cout) = 2016 and month(cout) = 4) THEN 1
ELSE 0
END) AS '20164',
SUM(CASE
WHEN (YEAR(cout) = 2016 and month(cout) = 5) THEN 1
ELSE 0
END) AS '20165',
SUM(CASE
WHEN (YEAR(cout) = 2016 and month(cout) = 6) THEN 1
ELSE 0
END) AS '20166',
SUM(CASE
WHEN (YEAR(cout) = 2016 and month(cout) = 7) THEN 1
ELSE 0
END) AS '20167',
SUM(CASE
WHEN (YEAR(cout) = 2016 and month(cout) = 8) THEN 1
ELSE 0
END) AS '20168',
SUM(CASE
WHEN (YEAR(cout) = 2016 and month(cout) = 9) THEN 1
ELSE 0
END) AS '20169',
SUM(CASE
WHEN (YEAR(cout) = 2016 and month(cout) = 10) THEN 1
ELSE 0
END) AS '201610',
SUM(CASE
WHEN (YEAR(cout) = 2016 and month(cout) = 11) THEN 1
ELSE 0
END) AS '201611',
SUM(CASE
WHEN (YEAR(cout) = 2016 and month(cout) = 12) THEN 1
ELSE 0
END) AS '201612',
# 2017
SUM(CASE
WHEN (YEAR(cout) = 2017 and month(cout) = 1) THEN 1
ELSE 0
END) AS '20171',
SUM(CASE
WHEN (YEAR(cout) = 2017 and month(cout) = 2) THEN 1
ELSE 0
END) AS '20172',
SUM(CASE
WHEN (YEAR(cout) = 2017 and month(cout) = 3) THEN 1
ELSE 0
END) AS '20173',
SUM(CASE
WHEN (YEAR(cout) = 2017 and month(cout) = 4) THEN 1
ELSE 0
END) AS '20174',
SUM(CASE
WHEN (YEAR(cout) = 2017 and month(cout) = 5) THEN 1
ELSE 0
END) AS '20175',
SUM(CASE
WHEN (YEAR(cout) = 2017 and month(cout) = 6) THEN 1
ELSE 0
END) AS '20176',
SUM(CASE
WHEN (YEAR(cout) = 2017 and month(cout) = 7) THEN 1
ELSE 0
END) AS '20177',
SUM(CASE
WHEN (YEAR(cout) = 2017 and month(cout) = 8) THEN 1
ELSE 0
END) AS '20178',
SUM(CASE
WHEN (YEAR(cout) = 2017 and month(cout) = 9) THEN 1
ELSE 0
END) AS '20179',
SUM(CASE
WHEN (YEAR(cout) = 2017 and month(cout) = 10) THEN 1
ELSE 0
END) AS '201710',
SUM(CASE
WHEN (YEAR(cout) = 2017 and month(cout) = 11) THEN 1
ELSE 0
END) AS '201711',
SUM(CASE
WHEN (YEAR(cout) = 2017 and month(cout) = 12) THEN 1
ELSE 0
END) AS '201712'
FROM
spl_2016.outraw
WHERE
(bibNumber = '3008726') #1
OR (bibNumber = '3077814') #2
OR (bibNumber = '2290181') #3
OR (bibNumber = '2368106') #4
OR (bibNumber = '3201929') #5
OR (bibNumber = '3255843') #6
OR (bibNumber = '1969986') #7
OR (bibNumber = '2430480') #8
OR (bibNumber = '3122735') #9
OR (bibNumber = '2627029') #10 !!!!
OR (bibNumber = '3167619') #11
OR (bibNumber = '2617983') #12
OR (bibNumber = '2969106') #13
OR (bibNumber = '2332111') #14
OR (bibNumber = '3171454') #15 ****
OR (bibNumber = '2603334') #16
OR (bibNumber = '2539072') #17
OR (bibNumber = '2112099') #18
OR (bibNumber = '2662999') #19
OR (bibNumber = '2940670') #20 !!!!
OR (bibNumber = '2458866') #21
OR (bibNumber = '2086099') #22
OR (bibNumber = '2953130') #25
OR (bibNumber = '3107624') #26
OR (bibNumber = '2369568') #27
OR (bibNumber = '3002627') #28
OR (bibNumber = '2805744') #29
OR (bibNumber = '1775275') #30 !!!!
GROUP BY bibNumber , title
ORDER BY counts DESC
0.380 sec / 0.0012 sec
RESULTS
I was inspired by the Tableau format. I mapped out the skeleton of the template by measuring the pixels. Although I ran into trouble with creating the top line graph from my initial template, it is still an element I plan on factoring in.
 Attachments

 proj2_mat259.zip
 (15.16 KiB) Downloaded 35 times

 Posts: 4
 Joined: Fri Jan 19, 2018 11:14 am
Re: 2D Frequency Pattern Visualization
Concept:
I realized that my last data query needed to be expanded, so I decided to choose a different point of focus. I looked at four of the top most popular political philosophy texts in US universities, which were Capital (Marx), Leviathan(Hobbes), The Communist Manifesto (Marx), and the Republic (Plato). I was interested in seeing the trends of the books and which ones would sustain popularity over time.
Queries:
Here is an example of the types of queries I ran. The first is to look for items, while the second isolated checkouts per year. They are all basically the same, I just switched out book titles. Each query took about 40 seconds.
Analysis:
I'm not sure if I made a mistake or if the SPL library has a hiccup, but in my results Capital did not have ANY checkouts at all in 2010 or 2011. Generally all the checkout numbers were very low. Also, it seemed that most checkouts were peak for Leviathan, or that it has a higher total amount of checkouts over 12 years. I found it kind of strange that the numbers were so low all across the board, but I think that may be because the books are most likely available as PDFs online, or they are given out by college professors.
Work in Progress:
Here is what my first version looks like. I want to make a key on the right hand side later that is a gradient.
I realized that my last data query needed to be expanded, so I decided to choose a different point of focus. I looked at four of the top most popular political philosophy texts in US universities, which were Capital (Marx), Leviathan(Hobbes), The Communist Manifesto (Marx), and the Republic (Plato). I was interested in seeing the trends of the books and which ones would sustain popularity over time.
Queries:
Here is an example of the types of queries I ran. The first is to look for items, while the second isolated checkouts per year. They are all basically the same, I just switched out book titles. Each query took about 40 seconds.
Code: Select all
SELECT
Title, COUNT(bibNumber) AS Counts, bibNumber, itemtype
FROM
spl_2016.inraw
WHERE
title LIKE 'communist manifesto'
AND (itemtype = 'acbk')
GROUP BY title , bibnumber, itemtype
ORDER BY Counts DESC
LIMIT 1000
Title, COUNT(bibNumber) AS Counts, bibNumber, itemtype, year(cout)
FROM
spl_2016.inraw
WHERE
title LIKE 'communist manifesto'
AND (itemtype = 'acbk')
AND year(cout) > 2005
GROUP BY title , bibnumber, itemtype, year(cout)
ORDER BY year(cout) DESC
LIMIT 1000
I'm not sure if I made a mistake or if the SPL library has a hiccup, but in my results Capital did not have ANY checkouts at all in 2010 or 2011. Generally all the checkout numbers were very low. Also, it seemed that most checkouts were peak for Leviathan, or that it has a higher total amount of checkouts over 12 years. I found it kind of strange that the numbers were so low all across the board, but I think that may be because the books are most likely available as PDFs online, or they are given out by college professors.
Work in Progress:
Here is what my first version looks like. I want to make a key on the right hand side later that is a gradient.
 Attachments

 Echo_TheoharAssignment1 2.zip
 (44.5 KiB) Downloaded 36 times