2D Frequency Pattern Visualization

rluo
Posts: 21
Joined: Tue Oct 14, 2014 10:57 am

2D Frequency Pattern Visualization

Post by rluo » Tue Jan 02, 2018 1:17 pm

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/hcl-picker/#/hlc/6/1/21313E/EFEE69

LABELS and TEXTS: Use standard sans-serif 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 work-in-progress 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.

qiaodong
Posts: 4
Joined: Fri Jan 19, 2018 11:06 am

Re: 2D Frequency Pattern Visualization

Post by qiaodong » Wed Jan 31, 2018 11:32 pm

Objective and concept:
I’m interested in the popularity of four sci-fi 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)
The query takes about 20 seconds. And the results look like:
Image

Here is my initial bar graph:
Image

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.

Image

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:

Image

Also, I’d like to restore the initial arrangement of the data set when user desired. Here is the result:

Image

Also, to translate when a different movie is isolated:
Image

Image

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("2012-01-01 12:00:00", '%Y-%m-%d %H:%M:%S')
BuckEnd = datetime.strptime("2018-01-01 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()
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:

Image

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 159 times
a6.gif
a5.gif
a4.gif
a3.gif
a1.gif
Scrreen1.png
Picture1.png
Screen Shot 2018-01-31 at 9.57.10 PM.png
Last edited by qiaodong on Thu Feb 01, 2018 6:47 am, edited 1 time in total.

zhangweidilydia
Posts: 12
Joined: Fri Jan 19, 2018 11:09 am

Re: 2D Frequency Pattern Visualization

Post by zhangweidilydia » Thu Feb 01, 2018 12:04 am

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
SKETCH.jpg
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:
color.png
color.png (11.2 KiB) Viewed 6449 times
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 '2008-1', SUM(CASE
WHEN (YEAR(cout) = 2008 2nd month(cout) = 2) THEN 1
ELSE 0
END) AS '2008-2', SUM(CASE
WHEN (YEAR(cout) = 2008 2nd month(cout) = 3) THEN 1
ELSE 0
END) AS '2008-3', SUM(CASE
WHEN (YEAR(cout) = 2008 2nd month(cout) = 4) THEN 1
ELSE 0
END) AS '2008-4', SUM(CASE
WHEN (YEAR(cout) = 2008 2nd month(cout) = 5) THEN 1
ELSE 0
END) AS '2008-5', SUM(CASE
WHEN (YEAR(cout) = 2008 2nd month(cout) = 6) THEN 1
ELSE 0
END) AS '2008-6', SUM(CASE
WHEN (YEAR(cout) = 2008 2nd month(cout) = 7) THEN 1
ELSE 0
END) AS '2008-7', SUM(CASE
WHEN (YEAR(cout) = 2008 2nd month(cout) = 8) THEN 1
ELSE 0
END) AS '2008-8', SUM(CASE
WHEN (YEAR(cout) = 2008 2nd month(cout) = 9) THEN 1
ELSE 0
END) AS '2008-9', SUM(CASE
WHEN (YEAR(cout) = 2008 2nd month(cout) = 10) THEN 1
ELSE 0
END) AS '2008-10', SUM(CASE
WHEN (YEAR(cout) = 2008 2nd month(cout) = 11) THEN 1 ELSE 0
END) AS '2008-11', SUM(CASE
WHEN (YEAR(cout) = 2008 2nd month(cout) = 12) THEN 1 ELSE 0
END) AS '2008-12',
SUM(CASE
WHEN (YEAR(cout) = 2009 2nd month(cout) = 1) THEN 1 ELSE 0 END) AS '2009-1', SUM(CASE
WHEN (YEAR(cout) = 2009 2nd month(cout) = 2) THEN 1
ELSE 0
END) AS '2009-2', SUM(CASE
WHEN (YEAR(cout) = 2009 2nd month(cout) = 3) THEN 1
ELSE 0
END) AS '2009-3', SUM(CASE
WHEN (YEAR(cout) = 2009 2nd month(cout) = 4) THEN 1
ELSE 0
END) AS '2009-4', SUM(CASE
WHEN (YEAR(cout) = 2009 2nd month(cout) = 5) THEN 1
ELSE 0
END) AS '2009-5', SUM(CASE
WHEN (YEAR(cout) = 2009 2nd month(cout) = 6) THEN 1
ELSE 0
END) AS '2009-6', SUM(CASE
WHEN (YEAR(cout) = 2009 2nd month(cout) = 7) THEN 1
ELSE 0
END) AS '2009-7', SUM(CASE
WHEN (YEAR(cout) = 2009 2nd month(cout) = 8) THEN 1
ELSE 0
END) AS '2009-8', SUM(CASE
WHEN (YEAR(cout) = 2009 2nd month(cout) = 9) THEN 1
ELSE 0
END) AS '2009-9', SUM(CASE
WHEN (YEAR(cout) = 2009 2nd month(cout) = 10) THEN 1
ELSE 0
END) AS '2009-10', SUM(CASE
WHEN (YEAR(cout) = 2009 2nd month(cout) = 11) THEN 1 ELSE 0
END) AS '2009-11', SUM(CASE
WHEN (YEAR(cout) = 2009 2nd month(cout) = 12) THEN 1
ELSE 0
END) AS '2009-12',
SUM(CASE
WHEN (YEAR(cout) = 2010 2nd month(cout) = 1) THEN 1 ELSE 0 END) AS '2010-1', SUM(CASE
WHEN (YEAR(cout) = 2010 2nd month(cout) = 2) THEN 1
ELSE 0
END) AS '2010-2', SUM(CASE
WHEN (YEAR(cout) = 2010 2nd month(cout) = 3) THEN 1

ELSE 0
END) AS '2010-3', SUM(CASE
WHEN (YEAR(cout) = 2010 2nd month(cout) = 4) THEN 1
ELSE 0
END) AS '2010-4', SUM(CASE
WHEN (YEAR(cout) = 2010 2nd month(cout) = 5) THEN 1
ELSE 0
END) AS '2010-5', SUM(CASE
WHEN (YEAR(cout) = 2010 2nd month(cout) = 6) THEN 1
ELSE 0
END) AS '2010-6', SUM(CASE
WHEN (YEAR(cout) = 2010 2nd month(cout) = 7) THEN 1
ELSE 0
END) AS '2010-7', SUM(CASE
WHEN (YEAR(cout) = 2010 2nd month(cout) = 8) THEN 1
ELSE 0
END) AS '2010-8', SUM(CASE
WHEN (YEAR(cout) = 2010 2nd month(cout) = 9) THEN 1
ELSE 0
END) AS '2010-9', SUM(CASE
WHEN (YEAR(cout) = 2010 2nd month(cout) = 10) THEN 1
ELSE 0
END) AS '2010-10', SUM(CASE
WHEN (YEAR(cout) = 2010 2nd month(cout) = 11) THEN 1 ELSE 0
END) AS '2010-11', SUM(CASE
WHEN (YEAR(cout) = 2010 2nd month(cout) = 12) THEN 1
ELSE 0
END) AS '2010-12',
SUM(CASE
WHEN (YEAR(cout) = 2011 2nd month(cout) = 1) THEN 1 ELSE 0 END) AS '2011-1', SUM(CASE
WHEN (YEAR(cout) = 2011 2nd month(cout) = 2) THEN 1
ELSE 0
END) AS '2011-2', SUM(CASE
WHEN (YEAR(cout) = 2011 2nd month(cout) = 3) THEN 1
ELSE 0
END) AS '2011-3', SUM(CASE
WHEN (YEAR(cout) = 2011 2nd month(cout) = 4) THEN 1
ELSE 0
END) AS '2011-4', SUM(CASE
WHEN (YEAR(cout) = 2011 2nd month(cout) = 5) THEN 1
ELSE 0

END) AS '2011-5', SUM(CASE
WHEN (YEAR(cout) = 2011 2nd month(cout) = 6) THEN 1
ELSE 0
END) AS '2011-6', SUM(CASE
WHEN (YEAR(cout) = 2011 2nd month(cout) = 7) THEN 1
ELSE 0
END) AS '2011-7', SUM(CASE
WHEN (YEAR(cout) = 2011 2nd month(cout) = 8) THEN 1
ELSE 0
END) AS '2011-8', SUM(CASE
WHEN (YEAR(cout) = 2011 2nd month(cout) = 9) THEN 1
ELSE 0
END) AS '2011-9', SUM(CASE
WHEN (YEAR(cout) = 2011 2nd month(cout) = 10) THEN 1
ELSE 0
END) AS '2011-10', SUM(CASE
WHEN (YEAR(cout) = 2011 2nd month(cout) = 11) THEN 1 ELSE 0
END) AS '2011-11', SUM(CASE
WHEN (YEAR(cout) = 2011 2nd month(cout) = 12) THEN 1
ELSE 0
END) AS '2011-12',
SUM(CASE
WHEN (YEAR(cout) = 2012 2nd month(cout) = 1) THEN 1 ELSE 0 END) AS '2012-1', SUM(CASE
WHEN (YEAR(cout) = 2012 2nd month(cout) = 2) THEN 1
ELSE 0
END) AS '2012-2', SUM(CASE
WHEN (YEAR(cout) = 2012 2nd month(cout) = 3) THEN 1
ELSE 0
END) AS '2012-3', SUM(CASE
WHEN (YEAR(cout) = 2012 2nd month(cout) = 4) THEN 1
ELSE 0
END) AS '2012-4', SUM(CASE
WHEN (YEAR(cout) = 2012 2nd month(cout) = 5) THEN 1
ELSE 0
END) AS '2012-5', SUM(CASE
WHEN (YEAR(cout) = 2012 2nd month(cout) = 6) THEN 1
ELSE 0
END) AS '2012-6', SUM(CASE
WHEN (YEAR(cout) = 2012 2nd month(cout) = 7) THEN 1
ELSE 0
END) AS '2012-7', SUM(CASE

WHEN (YEAR(cout) = 2012 2nd month(cout) = 8) THEN 1 ELSE 0
END) AS '2012-8', SUM(CASE
WHEN (YEAR(cout) = 2012 2nd month(cout) = 9) THEN 1 ELSE 0
END) AS '2012-9', SUM(CASE
WHEN (YEAR(cout) = 2012 2nd month(cout) = 10) THEN 1
ELSE 0
END) AS '2012-10', SUM(CASE
WHEN (YEAR(cout) = 2012 2nd month(cout) = 11) THEN 1 ELSE 0
END) AS '2012-11', SUM(CASE
WHEN (YEAR(cout) = 2012 2nd month(cout) = 12) THEN 1
ELSE 0
END) AS '2012-12',
SUM(CASE
WHEN (YEAR(cout) = 2013 2nd month(cout) = 1) THEN 1 ELSE 0 END) AS '2013-1', SUM(CASE
WHEN (YEAR(cout) = 2013 2nd month(cout) = 2) THEN 1
ELSE 0
END) AS '2013-2', SUM(CASE
WHEN (YEAR(cout) = 2013 2nd month(cout) = 3) THEN 1
ELSE 0
END) AS '2013-3', SUM(CASE
WHEN (YEAR(cout) = 2013 2nd month(cout) = 4) THEN 1
ELSE 0
END) AS '2013-4', SUM(CASE
WHEN (YEAR(cout) = 2013 2nd month(cout) = 5) THEN 1
ELSE 0
END) AS '2013-5', SUM(CASE
WHEN (YEAR(cout) = 2013 2nd month(cout) = 6) THEN 1
ELSE 0
END) AS '2013-6', SUM(CASE
WHEN (YEAR(cout) = 2013 2nd month(cout) = 7) THEN 1
ELSE 0
END) AS '2013-7', SUM(CASE
WHEN (YEAR(cout) = 2013 2nd month(cout) = 8) THEN 1
ELSE 0
END) AS '2013-8', SUM(CASE
WHEN (YEAR(cout) = 2013 2nd month(cout) = 9) THEN 1
ELSE 0
END) AS '2013-9', SUM(CASE
WHEN (YEAR(cout) = 2013 2nd month(cout) = 10) THEN 1

ELSE 0
END) AS '2013-10', SUM(CASE
WHEN (YEAR(cout) = 2013 2nd month(cout) = 11) THEN 1 ELSE 0
END) AS '2013-11', SUM(CASE
WHEN (YEAR(cout) = 2013 2nd month(cout) = 12) THEN 1
ELSE 0
END) AS '2013-12', SUM(CASE
WHEN (YEAR(cout) = 2014 2nd month(cout) = 1) THEN 1
ELSE 0
END) AS '2014-1', SUM(CASE
WHEN (YEAR(cout) = 2014 2nd month(cout) = 2) THEN 1
ELSE 0
END) AS '2014-2', SUM(CASE
WHEN (YEAR(cout) = 2014 2nd month(cout) = 3) THEN 1
ELSE 0
END) AS '2014-3', SUM(CASE
WHEN (YEAR(cout) = 2014 2nd month(cout) = 4) THEN 1
ELSE 0
END) AS '2014-4', SUM(CASE
WHEN (YEAR(cout) = 2014 2nd month(cout) = 5) THEN 1
ELSE 0
END) AS '2014-5', SUM(CASE
WHEN (YEAR(cout) = 2014 2nd month(cout) = 6) THEN 1
ELSE 0
END) AS '2014-6', SUM(CASE
WHEN (YEAR(cout) = 2014 2nd month(cout) = 7) THEN 1
ELSE 0
END) AS '2014-7', SUM(CASE
WHEN (YEAR(cout) = 2014 2nd month(cout) = 8) THEN 1
ELSE 0
END) AS '2014-8', SUM(CASE
WHEN (YEAR(cout) = 2014 2nd month(cout) = 9) THEN 1
ELSE 0
END) AS '2014-9', SUM(CASE
WHEN (YEAR(cout) = 2014 2nd month(cout) = 10) THEN 1
ELSE 0
END) AS '2014-10', SUM(CASE
WHEN (YEAR(cout) = 2014 2nd month(cout) = 11) THEN 1 ELSE 0
END) AS '2014-11', SUM(CASE
WHEN (YEAR(cout) = 2014 2nd month(cout) = 12) THEN 1 ELSE 0

END) AS '2014-12',
SUM(CASE
WHEN (YEAR(cout) = 2015 2nd month(cout) = 1) THEN 1 ELSE 0 END) AS '2015-1', SUM(CASE
WHEN (YEAR(cout) = 2015 2nd month(cout) = 2) THEN 1
ELSE 0
END) AS '2015-2', SUM(CASE
WHEN (YEAR(cout) = 2015 2nd month(cout) = 3) THEN 1
ELSE 0
END) AS '2015-3', SUM(CASE
WHEN (YEAR(cout) = 2015 2nd month(cout) = 4) THEN 1
ELSE 0
END) AS '2015-4', SUM(CASE
WHEN (YEAR(cout) = 2015 2nd month(cout) = 5) THEN 1
ELSE 0
END) AS '2015-5', SUM(CASE
WHEN (YEAR(cout) = 2015 2nd month(cout) = 6) THEN 1
ELSE 0
END) AS '2015-6', SUM(CASE
WHEN (YEAR(cout) = 2015 2nd month(cout) = 7) THEN 1
ELSE 0
END) AS '2015-7', SUM(CASE
WHEN (YEAR(cout) = 2015 2nd month(cout) = 8) THEN 1
ELSE 0
END) AS '2015-8', SUM(CASE
WHEN (YEAR(cout) = 2015 2nd month(cout) = 9) THEN 1
ELSE 0
END) AS '2015-9', SUM(CASE
WHEN (YEAR(cout) = 2015 2nd month(cout) = 10) THEN 1
ELSE 0
END) AS '2015-10', SUM(CASE
WHEN (YEAR(cout) = 2015 2nd month(cout) = 11) THEN 1 ELSE 0
END) AS '2015-11', SUM(CASE
WHEN (YEAR(cout) = 2015 2nd month(cout) = 12) THEN 1
ELSE 0
END) AS '2015-12',
SUM(CASE
WHEN (YEAR(cout) = 2016 2nd month(cout) = 1) THEN 1 ELSE 0 END) AS '2016-1', SUM(CASE
WHEN (YEAR(cout) = 2016 2nd month(cout) = 2) THEN 1
ELSE 0
END) AS '2016-2', SUM(CASE

WHEN (YEAR(cout) = 2016 2nd month(cout) = 3) THEN 1 ELSE 0
END) AS '2016-3', SUM(CASE
WHEN (YEAR(cout) = 2016 2nd month(cout) = 4) THEN 1 ELSE 0
END) AS '2016-4', SUM(CASE
WHEN (YEAR(cout) = 2016 2nd month(cout) = 5) THEN 1
ELSE 0
END) AS '2016-5', SUM(CASE
WHEN (YEAR(cout) = 2016 2nd month(cout) = 6) THEN 1
ELSE 0
END) AS '2016-6', SUM(CASE
WHEN (YEAR(cout) = 2016 2nd month(cout) = 7) THEN 1
ELSE 0
END) AS '2016-7', SUM(CASE
WHEN (YEAR(cout) = 2016 2nd month(cout) = 8) THEN 1
ELSE 0
END) AS '2016-8', SUM(CASE
WHEN (YEAR(cout) = 2016 2nd month(cout) = 9) THEN 1
ELSE 0
END) AS '2016-9', SUM(CASE
WHEN (YEAR(cout) = 2016 2nd month(cout) = 10) THEN 1
ELSE 0
END) AS '2016-10', SUM(CASE
WHEN (YEAR(cout) = 2016 2nd month(cout) = 11) THEN 1 ELSE 0
END) AS '2016-11', SUM(CASE
WHEN (YEAR(cout) = 2016 2nd month(cout) = 12) THEN 1
ELSE 0
END) AS '2016-12',
SUM(CASE
WHEN (YEAR(cout) = 2017 2nd month(cout) = 1) THEN 1 ELSE 0 END) AS '2017-1', SUM(CASE
WHEN (YEAR(cout) = 2017 2nd month(cout) = 2) THEN 1
ELSE 0
END) AS '2017-2', SUM(CASE
WHEN (YEAR(cout) = 2017 2nd month(cout) = 3) THEN 1
ELSE 0
END) AS '2017-3', SUM(CASE
WHEN (YEAR(cout) = 2017 2nd month(cout) = 4) THEN 1
ELSE 0
END) AS '2017-4', SUM(CASE
WHEN (YEAR(cout) = 2017 2nd month(cout) = 5) THEN 1

ELSE 0
END) AS '2017-5', SUM(CASE
WHEN (YEAR(cout) = 2017 2nd month(cout) = 6) THEN 1
ELSE 0
END) AS '2017-6', SUM(CASE
WHEN (YEAR(cout) = 2017 2nd month(cout) = 7) THEN 1
ELSE 0
END) AS '2017-7', SUM(CASE
WHEN (YEAR(cout) = 2017 2nd month(cout) = 8) THEN 1
ELSE 0
END) AS '2017-8', SUM(CASE
WHEN (YEAR(cout) = 2017 2nd month(cout) = 9) THEN 1
ELSE 0
END) AS '2017-9', SUM(CASE
WHEN (YEAR(cout) = 2017 2nd month(cout) = 10) THEN 1
ELSE 0
END) AS '2017-10', SUM(CASE
WHEN (YEAR(cout) = 2017 2nd month(cout) = 11) THEN 1 ELSE 0
END) AS '2017-11', SUM(CASE
WHEN (YEAR(cout) = 2017 2nd month(cout) = 12) THEN 1 ELSE 0
END) AS '2017-12'
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
PROCESSING TIME
https://www.youtube.com/watch?v=BTXMjutjOW8
This is my video documentation.
screenshot1.jpg
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.
screenshot2.jpg
screenshot3.jpg
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 162 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.

chantelchan
Posts: 8
Joined: Wed Apr 12, 2017 5:15 pm

Re: 2D Frequency Pattern Visualization

Post by chantelchan » Thu Feb 01, 2018 9:34 am

Pie.png
DESCRIPTION
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
sketch.jpg
Originally, I wanted there to be parabola-like 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
PROCESSING TIME
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 152 times

aprilcai
Posts: 4
Joined: Fri Jan 19, 2018 11:06 am

Re: 2D Frequency Pattern Visualization

Post by aprilcai » Thu Feb 01, 2018 11:46 am

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):

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 '2006-1',
    SUM(CASE
        WHEN (YEAR(cout) = 2006 AND MONTH(cout) = 2) THEN 1
        ELSE 0
    END) AS '2006-2',
    SUM(CASE
        WHEN (YEAR(cout) = 2006 AND MONTH(cout) = 3) THEN 1
        ELSE 0
    END) AS '2006-3',
    SUM(CASE
        WHEN (YEAR(cout) = 2006 AND MONTH(cout) = 4) THEN 1
        ELSE 0
    END) AS '2006-4',
    SUM(CASE
        WHEN (YEAR(cout) = 2006 AND MONTH(cout) = 5) THEN 1
        ELSE 0
    END) AS '2006-5',
    SUM(CASE
        WHEN (YEAR(cout) = 2006 AND MONTH(cout) = 6) THEN 1
        ELSE 0
    END) AS '2006-6',
    SUM(CASE
        WHEN (YEAR(cout) = 2006 AND MONTH(cout) = 7) THEN 1
        ELSE 0
    END) AS '2006-7',
    SUM(CASE
        WHEN (YEAR(cout) = 2006 AND MONTH(cout) = 8) THEN 1
        ELSE 0
    END) AS '2006-8',
    SUM(CASE
        WHEN (YEAR(cout) = 2006 AND MONTH(cout) = 9) THEN 1
        ELSE 0
    END) AS '2006-9',
    SUM(CASE
        WHEN (YEAR(cout) = 2006 AND MONTH(cout) = 10) THEN 1
        ELSE 0
    END) AS '2006-10',
    SUM(CASE
        WHEN (YEAR(cout) = 2006 AND MONTH(cout) = 11) THEN 1
        ELSE 0
    END) AS '2006-11',
    SUM(CASE
        WHEN (YEAR(cout) = 2006 AND MONTH(cout) = 12) THEN 1
        ELSE 0
    END) AS '2006-12',
    SUM(CASE
        WHEN (YEAR(cout) = 2007 AND MONTH(cout) = 1) THEN 1
        ELSE 0
    END) AS '2007-1',
    SUM(CASE
        WHEN (YEAR(cout) = 2007 AND MONTH(cout) = 2) THEN 1
        ELSE 0
    END) AS '2007-2',
    SUM(CASE
        WHEN (YEAR(cout) = 2007 AND MONTH(cout) = 3) THEN 1
        ELSE 0
    END) AS '2007-3',
    SUM(CASE
        WHEN (YEAR(cout) = 2007 AND MONTH(cout) = 4) THEN 1
        ELSE 0
    END) AS '2007-4',
    SUM(CASE
        WHEN (YEAR(cout) = 2007 AND MONTH(cout) = 5) THEN 1
        ELSE 0
    END) AS '2007-5',
    SUM(CASE
        WHEN (YEAR(cout) = 2007 AND MONTH(cout) = 6) THEN 1
        ELSE 0
    END) AS '2007-6',
    SUM(CASE
        WHEN (YEAR(cout) = 2007 AND MONTH(cout) = 7) THEN 1
        ELSE 0
    END) AS '2007-7',
    SUM(CASE
        WHEN (YEAR(cout) = 2007 AND MONTH(cout) = 8) THEN 1
        ELSE 0
    END) AS '2007-8',
    SUM(CASE
        WHEN (YEAR(cout) = 2007 AND MONTH(cout) = 9) THEN 1
        ELSE 0
    END) AS '2007-9',
    SUM(CASE
        WHEN (YEAR(cout) = 2007 AND MONTH(cout) = 10) THEN 1
        ELSE 0
    END) AS '2007-10',
    SUM(CASE
        WHEN (YEAR(cout) = 2007 AND MONTH(cout) = 11) THEN 1
        ELSE 0
    END) AS '2007-11',
    SUM(CASE
        WHEN (YEAR(cout) = 2007 AND MONTH(cout) = 12) THEN 1
        ELSE 0
    END) AS '2007-12',
    SUM(CASE
        WHEN (YEAR(cout) = 2008 AND MONTH(cout) = 1) THEN 1
        ELSE 0
    END) AS '2008-1',
    SUM(CASE
        WHEN (YEAR(cout) = 2008 AND MONTH(cout) = 2) THEN 1
        ELSE 0
    END) AS '2008-2',
    SUM(CASE
        WHEN (YEAR(cout) = 2008 AND MONTH(cout) = 3) THEN 1
        ELSE 0
    END) AS '2008-3',
    SUM(CASE
        WHEN (YEAR(cout) = 2008 AND MONTH(cout) = 4) THEN 1
        ELSE 0
    END) AS '2008-4',
    SUM(CASE
        WHEN (YEAR(cout) = 2008 AND MONTH(cout) = 5) THEN 1
        ELSE 0
    END) AS '2008-5',
    SUM(CASE
        WHEN (YEAR(cout) = 2008 AND MONTH(cout) = 6) THEN 1
        ELSE 0
    END) AS '2008-6',
    SUM(CASE
        WHEN (YEAR(cout) = 2008 AND MONTH(cout) = 7) THEN 1
        ELSE 0
    END) AS '2008-7',
    SUM(CASE
        WHEN (YEAR(cout) = 2008 AND MONTH(cout) = 8) THEN 1
        ELSE 0
    END) AS '2008-8',
    SUM(CASE
        WHEN (YEAR(cout) = 2008 AND MONTH(cout) = 9) THEN 1
        ELSE 0
    END) AS '2008-9',
    SUM(CASE
        WHEN (YEAR(cout) = 2008 AND MONTH(cout) = 10) THEN 1
        ELSE 0
    END) AS '2008-10',
    SUM(CASE
        WHEN (YEAR(cout) = 2008 AND MONTH(cout) = 11) THEN 1
        ELSE 0
    END) AS '2008-11',
    SUM(CASE
        WHEN (YEAR(cout) = 2008 AND MONTH(cout) = 12) THEN 1
        ELSE 0
    END) AS '2008-12',
    SUM(CASE
        WHEN (YEAR(cout) = 2009 AND MONTH(cout) = 1) THEN 1
        ELSE 0
    END) AS '2009-1',
    SUM(CASE
        WHEN (YEAR(cout) = 2009 AND MONTH(cout) = 2) THEN 1
        ELSE 0
    END) AS '2009-2',
    SUM(CASE
        WHEN (YEAR(cout) = 2009 AND MONTH(cout) = 3) THEN 1
        ELSE 0
    END) AS '2009-3',
    SUM(CASE
        WHEN (YEAR(cout) = 2009 AND MONTH(cout) = 4) THEN 1
        ELSE 0
    END) AS '2009-4',
    SUM(CASE
        WHEN (YEAR(cout) = 2009 AND MONTH(cout) = 5) THEN 1
        ELSE 0
    END) AS '2009-5',
    SUM(CASE
        WHEN (YEAR(cout) = 2009 AND MONTH(cout) = 6) THEN 1
        ELSE 0
    END) AS '2009-6',
    SUM(CASE
        WHEN (YEAR(cout) = 2009 AND MONTH(cout) = 7) THEN 1
        ELSE 0
    END) AS '2009-7',
    SUM(CASE
        WHEN (YEAR(cout) = 2009 AND MONTH(cout) = 8) THEN 1
        ELSE 0
    END) AS '2009-8',
    SUM(CASE
        WHEN (YEAR(cout) = 2009 AND MONTH(cout) = 9) THEN 1
        ELSE 0
    END) AS '2009-9',
    SUM(CASE
        WHEN (YEAR(cout) = 2009 AND MONTH(cout) = 10) THEN 1
        ELSE 0
    END) AS '2009-10',
    SUM(CASE
        WHEN (YEAR(cout) = 2009 AND MONTH(cout) = 11) THEN 1
        ELSE 0
    END) AS '2009-11',
    SUM(CASE
        WHEN (YEAR(cout) = 2009 AND MONTH(cout) = 12) THEN 1
        ELSE 0
    END) AS '2009-12',
    SUM(CASE
        WHEN (YEAR(cout) = 2010 AND MONTH(cout) = 1) THEN 1
        ELSE 0
    END) AS '2010-1',
    SUM(CASE
        WHEN (YEAR(cout) = 2010 AND MONTH(cout) = 2) THEN 1
        ELSE 0
    END) AS '2010-2',
    SUM(CASE
        WHEN (YEAR(cout) = 2010 AND MONTH(cout) = 3) THEN 1
        ELSE 0
    END) AS '2010-3',
    SUM(CASE
        WHEN (YEAR(cout) = 2010 AND MONTH(cout) = 4) THEN 1
        ELSE 0
    END) AS '2010-4',
    SUM(CASE
        WHEN (YEAR(cout) = 2010 AND MONTH(cout) = 5) THEN 1
        ELSE 0
    END) AS '2010-5',
    SUM(CASE
        WHEN (YEAR(cout) = 2010 AND MONTH(cout) = 6) THEN 1
        ELSE 0
    END) AS '2010-6',
    SUM(CASE
        WHEN (YEAR(cout) = 2010 AND MONTH(cout) = 7) THEN 1
        ELSE 0
    END) AS '2010-7',
    SUM(CASE
        WHEN (YEAR(cout) = 2010 AND MONTH(cout) = 8) THEN 1
        ELSE 0
    END) AS '2010-8',
    SUM(CASE
        WHEN (YEAR(cout) = 2010 AND MONTH(cout) = 9) THEN 1
        ELSE 0
    END) AS '2010-9',
    SUM(CASE
        WHEN (YEAR(cout) = 2010 AND MONTH(cout) = 10) THEN 1
        ELSE 0
    END) AS '2010-10',
    SUM(CASE
        WHEN (YEAR(cout) = 2010 AND MONTH(cout) = 11) THEN 1
        ELSE 0
    END) AS '2010-11',
    SUM(CASE
        WHEN (YEAR(cout) = 2010 AND MONTH(cout) = 12) THEN 1
        ELSE 0
    END) AS '2010-12',
    SUM(CASE
        WHEN (YEAR(cout) = 2011 AND MONTH(cout) = 1) THEN 1
        ELSE 0
    END) AS '2011-1',
    SUM(CASE
        WHEN (YEAR(cout) = 2011 AND MONTH(cout) = 2) THEN 1
        ELSE 0
    END) AS '2011-2',
    SUM(CASE
        WHEN (YEAR(cout) = 2011 AND MONTH(cout) = 3) THEN 1
        ELSE 0
    END) AS '2011-3',
    SUM(CASE
        WHEN (YEAR(cout) = 2011 AND MONTH(cout) = 4) THEN 1
        ELSE 0
    END) AS '2011-4',
    SUM(CASE
        WHEN (YEAR(cout) = 2011 AND MONTH(cout) = 5) THEN 1
        ELSE 0
    END) AS '2011-5',
    SUM(CASE
        WHEN (YEAR(cout) = 2011 AND MONTH(cout) = 6) THEN 1
        ELSE 0
    END) AS '2011-6',
    SUM(CASE
        WHEN (YEAR(cout) = 2011 AND MONTH(cout) = 7) THEN 1
        ELSE 0
    END) AS '2011-7',
    SUM(CASE
        WHEN (YEAR(cout) = 2011 AND MONTH(cout) = 8) THEN 1
        ELSE 0
    END) AS '2011-8',
    SUM(CASE
        WHEN (YEAR(cout) = 2011 AND MONTH(cout) = 9) THEN 1
        ELSE 0
    END) AS '2011-9',
    SUM(CASE
        WHEN (YEAR(cout) = 2011 AND MONTH(cout) = 10) THEN 1
        ELSE 0
    END) AS '2011-10',
    SUM(CASE
        WHEN (YEAR(cout) = 2011 AND MONTH(cout) = 11) THEN 1
        ELSE 0
    END) AS '2011-11',
    SUM(CASE
        WHEN (YEAR(cout) = 2011 AND MONTH(cout) = 12) THEN 1
        ELSE 0
    END) AS '2011-12',
    SUM(CASE
        WHEN (YEAR(cout) = 2012 AND MONTH(cout) = 1) THEN 1
        ELSE 0
    END) AS '2012-1',
    SUM(CASE
        WHEN (YEAR(cout) = 2012 AND MONTH(cout) = 2) THEN 1
        ELSE 0
    END) AS '2012-2',
    SUM(CASE
        WHEN (YEAR(cout) = 2012 AND MONTH(cout) = 3) THEN 1
        ELSE 0
    END) AS '2012-3',
    SUM(CASE
        WHEN (YEAR(cout) = 2012 AND MONTH(cout) = 4) THEN 1
        ELSE 0
    END) AS '2012-4',
    SUM(CASE
        WHEN (YEAR(cout) = 2012 AND MONTH(cout) = 5) THEN 1
        ELSE 0
    END) AS '2012-5',
    SUM(CASE
        WHEN (YEAR(cout) = 2012 AND MONTH(cout) = 6) THEN 1
        ELSE 0
    END) AS '2012-6',
    SUM(CASE
        WHEN (YEAR(cout) = 2012 AND MONTH(cout) = 7) THEN 1
        ELSE 0
    END) AS '2012-7',
    SUM(CASE
        WHEN (YEAR(cout) = 2012 AND MONTH(cout) = 8) THEN 1
        ELSE 0
    END) AS '2012-8',
    SUM(CASE
        WHEN (YEAR(cout) = 2012 AND MONTH(cout) = 9) THEN 1
        ELSE 0
    END) AS '2012-9',
    SUM(CASE
        WHEN (YEAR(cout) = 2012 AND MONTH(cout) = 10) THEN 1
        ELSE 0
    END) AS '2012-10',
    SUM(CASE
        WHEN (YEAR(cout) = 2012 AND MONTH(cout) = 11) THEN 1
        ELSE 0
    END) AS '2012-11',
    SUM(CASE
        WHEN (YEAR(cout) = 2012 AND MONTH(cout) = 12) THEN 1
        ELSE 0
    END) AS '2012-12',
    SUM(CASE
        WHEN (YEAR(cout) = 2013 AND MONTH(cout) = 1) THEN 1
        ELSE 0
    END) AS '2013-1',
    SUM(CASE
        WHEN (YEAR(cout) = 2013 AND MONTH(cout) = 2) THEN 1
        ELSE 0
    END) AS '2013-2',
    SUM(CASE
        WHEN (YEAR(cout) = 2013 AND MONTH(cout) = 3) THEN 1
        ELSE 0
    END) AS '2013-3',
    SUM(CASE
        WHEN (YEAR(cout) = 2013 AND MONTH(cout) = 4) THEN 1
        ELSE 0
    END) AS '2013-4',
    SUM(CASE
        WHEN (YEAR(cout) = 2013 AND MONTH(cout) = 5) THEN 1
        ELSE 0
    END) AS '2013-5',
    SUM(CASE
        WHEN (YEAR(cout) = 2013 AND MONTH(cout) = 6) THEN 1
        ELSE 0
    END) AS '2013-6',
    SUM(CASE
        WHEN (YEAR(cout) = 2013 AND MONTH(cout) = 7) THEN 1
        ELSE 0
    END) AS '2013-7',
    SUM(CASE
        WHEN (YEAR(cout) = 2013 AND MONTH(cout) = 8) THEN 1
        ELSE 0
    END) AS '2013-8',
    SUM(CASE
        WHEN (YEAR(cout) = 2013 AND MONTH(cout) = 9) THEN 1
        ELSE 0
    END) AS '2013-9',
    SUM(CASE
        WHEN (YEAR(cout) = 2013 AND MONTH(cout) = 10) THEN 1
        ELSE 0
    END) AS '2013-10',
    SUM(CASE
        WHEN (YEAR(cout) = 2013 AND MONTH(cout) = 11) THEN 1
        ELSE 0
    END) AS '2013-11',
    SUM(CASE
        WHEN (YEAR(cout) = 2013 AND MONTH(cout) = 12) THEN 1
        ELSE 0
    END) AS '2013-12',
    SUM(CASE
        WHEN (YEAR(cout) = 2014 AND MONTH(cout) = 1) THEN 1
        ELSE 0
    END) AS '2014-1',
    SUM(CASE
        WHEN (YEAR(cout) = 2014 AND MONTH(cout) = 2) THEN 1
        ELSE 0
    END) AS '2014-2',
    SUM(CASE
        WHEN (YEAR(cout) = 2014 AND MONTH(cout) = 3) THEN 1
        ELSE 0
    END) AS '2014-3',
    SUM(CASE
        WHEN (YEAR(cout) = 2014 AND MONTH(cout) = 4) THEN 1
        ELSE 0
    END) AS '2014-4',
    SUM(CASE
        WHEN (YEAR(cout) = 2014 AND MONTH(cout) = 5) THEN 1
        ELSE 0
    END) AS '2014-5',
    SUM(CASE
        WHEN (YEAR(cout) = 2014 AND MONTH(cout) = 6) THEN 1
        ELSE 0
    END) AS '2014-6',
    SUM(CASE
        WHEN (YEAR(cout) = 2014 AND MONTH(cout) = 7) THEN 1
        ELSE 0
    END) AS '2014-7',
    SUM(CASE
        WHEN (YEAR(cout) = 2014 AND MONTH(cout) = 8) THEN 1
        ELSE 0
    END) AS '2014-8',
    SUM(CASE
        WHEN (YEAR(cout) = 2014 AND MONTH(cout) = 9) THEN 1
        ELSE 0
    END) AS '2014-9',
    SUM(CASE
        WHEN (YEAR(cout) = 2014 AND MONTH(cout) = 10) THEN 1
        ELSE 0
    END) AS '2014-10',
    SUM(CASE
        WHEN (YEAR(cout) = 2014 AND MONTH(cout) = 11) THEN 1
        ELSE 0
    END) AS '2014-11',
    SUM(CASE
        WHEN (YEAR(cout) = 2014 AND MONTH(cout) = 12) THEN 1
        ELSE 0
    END) AS '2014-12',
    SUM(CASE
        WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 1) THEN 1
        ELSE 0
    END) AS '2015-1',
    SUM(CASE
        WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 2) THEN 1
        ELSE 0
    END) AS '2015-2',
    SUM(CASE
        WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 3) THEN 1
        ELSE 0
    END) AS '2015-3',
    SUM(CASE
        WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 4) THEN 1
        ELSE 0
    END) AS '2015-4',
    SUM(CASE
        WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 5) THEN 1
        ELSE 0
    END) AS '2015-5',
    SUM(CASE
        WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 6) THEN 1
        ELSE 0
    END) AS '2015-6',
    SUM(CASE
        WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 7) THEN 1
        ELSE 0
    END) AS '2015-7',
    SUM(CASE
        WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 8) THEN 1
        ELSE 0
    END) AS '2015-8',
    SUM(CASE
        WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 9) THEN 1
        ELSE 0
    END) AS '2015-9',
    SUM(CASE
        WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 10) THEN 1
        ELSE 0
    END) AS '2015-10',
    SUM(CASE
        WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 11) THEN 1
        ELSE 0
    END) AS '2015-11',
    SUM(CASE
        WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 12) THEN 1
        ELSE 0
    END) AS '2015-12',
    SUM(CASE
        WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 1) THEN 1
        ELSE 0
    END) AS '2016-1',
    SUM(CASE
        WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 2) THEN 1
        ELSE 0
    END) AS '2016-2',
    SUM(CASE
        WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 3) THEN 1
        ELSE 0
    END) AS '2016-3',
    SUM(CASE
        WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 4) THEN 1
        ELSE 0
    END) AS '2016-4',
    SUM(CASE
        WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 5) THEN 1
        ELSE 0
    END) AS '2016-5',
    SUM(CASE
        WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 6) THEN 1
        ELSE 0
    END) AS '2016-6',
    SUM(CASE
        WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 7) THEN 1
        ELSE 0
    END) AS '2016-7',
    SUM(CASE
        WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 8) THEN 1
        ELSE 0
    END) AS '2016-8',
    SUM(CASE
        WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 9) THEN 1
        ELSE 0
    END) AS '2016-9',
    SUM(CASE
        WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 10) THEN 1
        ELSE 0
    END) AS '2016-10',
    SUM(CASE
        WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 11) THEN 1
        ELSE 0
    END) AS '2016-11',
    SUM(CASE
        WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 12) THEN 1
        ELSE 0
    END) AS '2016-12',
    SUM(CASE
        WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 1) THEN 1
        ELSE 0
    END) AS '2017-1',
    SUM(CASE
        WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 2) THEN 1
        ELSE 0
    END) AS '2017-2',
    SUM(CASE
        WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 3) THEN 1
        ELSE 0
    END) AS '2017-3',
    SUM(CASE
        WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 4) THEN 1
        ELSE 0
    END) AS '2017-4',
    SUM(CASE
        WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 5) THEN 1
        ELSE 0
    END) AS '2017-5',
    SUM(CASE
        WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 6) THEN 1
        ELSE 0
    END) AS '2017-6',
    SUM(CASE
        WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 7) THEN 1
        ELSE 0
    END) AS '2017-7',
    SUM(CASE
        WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 8) THEN 1
        ELSE 0
    END) AS '2017-8',
    SUM(CASE
        WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 9) THEN 1
        ELSE 0
    END) AS '2017-9',
    SUM(CASE
        WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 10) THEN 1
        ELSE 0
    END) AS '2017-10',
    SUM(CASE
        WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 11) THEN 1
        ELSE 0
    END) AS '2017-11',
    SUM(CASE
        WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 12) THEN 1
        ELSE 0
    END) AS '2017-12'
FROM
    spl_2016.outraw
WHERE
    title = 'Twilight' OR title = 'New moon'
        OR title = 'Eclipse'
        OR title = 'Breaking Dawn'
        OR title = 'Hunger Games'
        OR title = 'Mockingjay'
        OR title = 'Catching Fire'
        OR title = 'Enders game'
        OR title = 'Divergent'
GROUP BY title
ORDER BY CASE title
    WHEN 'Twilight' THEN 9
    WHEN 'New moon' THEN 8
    WHEN 'Eclipse' THEN 7
    WHEN 'Breaking Dawn' THEN 6
    WHEN 'Hunger Games' THEN 5
    WHEN 'Catching Fire' THEN 4
    WHEN 'Mockingjay' THEN 3
    WHEN 'Enders game' THEN 2
    WHEN 'Divergent' THEN 1
    ELSE 0
END DESC
Processing time(Duration/Fetch Time) 3.654 sec/0.000095 sec

Sketch
sketch.jpeg
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.

Work-in-Progress Screenshots

The first plot shows like this,
progress1.png
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 check-out rate.

Then, I applied different colors to different movies,
progress2.png
https://drive.google.com/open?id=1ZArRq ... BU5ieT9a7Q
Now it gives a better visual experience on varies movie check-out 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)
highlight.gif
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 133 times
Last edited by aprilcai on Thu Feb 22, 2018 12:11 am, edited 2 times in total.

sihwapark
Posts: 4
Joined: Fri Jan 19, 2018 11:05 am

Re: 2D Frequency Pattern Visualization

Post by sihwapark » Thu Feb 01, 2018 1:04 pm

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.

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)
Processing time
Duration : 463.161 sec

Sketches
IMG_3411.JPG
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 y-axis of a linear timeline, in other words, values reflecting checkouts, and placed DVD and OST's data together along with the x-axis 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.

Work-in-progress screenshots (1st version only)
Screen Shot 2018-02-01 at 12.16.58 PM.png
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 date-based 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 bar-shaped 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 88 times
Last edited by sihwapark on Thu Feb 15, 2018 4:24 pm, edited 3 times in total.

yankong
Posts: 4
Joined: Fri Jan 19, 2018 11:08 am

Re: 2D Frequency Pattern Visualization

Post by yankong » Thu Feb 01, 2018 2:55 pm

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
--

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
Processing time
--
Durations: 77.894 sec

Sketches and work-in-progress screenshots of your project with descriptions
--
Sketches:
WechatIMG1.jpeg
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.

Work-in-progress screenshots
Screen Shot 2018-02-01 at 1.54.25 PM.png
Final results & Analysis
--
2018-02-01 14_05_23.gif
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 82 times

chengyuan
Posts: 4
Joined: Fri Jan 19, 2018 11:03 am

Re: 2D Frequency Pattern Visualization

Post by chengyuan » Thu Feb 01, 2018 3:19 pm

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 co-authored 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 checked-in/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
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.

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 '2015-1',
    SUM(CASE
        WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 2) THEN 1
        ELSE 0
    END) AS '2015-2',
    SUM(CASE
        WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 3) THEN 1
        ELSE 0
    END) AS '2015-3',
    SUM(CASE
        WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 4) THEN 1
        ELSE 0
    END) AS '2015-4',
    SUM(CASE
        WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 5) THEN 1
        ELSE 0
    END) AS '2015-5',
    SUM(CASE
        WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 6) THEN 1
        ELSE 0
    END) AS '2015-6',
    SUM(CASE
        WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 7) THEN 1
        ELSE 0
    END) AS '2015-7',
    SUM(CASE
        WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 8) THEN 1
        ELSE 0
    END) AS '2015-8',
    SUM(CASE
        WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 9) THEN 1
        ELSE 0
    END) AS '2015-9',
    SUM(CASE
        WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 10) THEN 1
        ELSE 0
    END) AS '2015-10',
    SUM(CASE
        WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 11) THEN 1
        ELSE 0
    END) AS '2015-11',
    SUM(CASE
        WHEN (YEAR(cout) = 2015 AND MONTH(cout) = 12) THEN 1
        ELSE 0
    END) AS '2015-12',
    SUM(CASE
        WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 1) THEN 1
        ELSE 0
    END) AS '2016-1',
    SUM(CASE
        WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 2) THEN 1
        ELSE 0
    END) AS '2016-2',
    SUM(CASE
        WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 3) THEN 1
        ELSE 0
    END) AS '2016-3',
    SUM(CASE
        WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 4) THEN 1
        ELSE 0
    END) AS '2016-4',
    SUM(CASE
        WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 5) THEN 1
        ELSE 0
    END) AS '2016-5',
    SUM(CASE
        WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 6) THEN 1
        ELSE 0
    END) AS '2016-6',
    SUM(CASE
        WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 7) THEN 1
        ELSE 0
    END) AS '2016-7',
    SUM(CASE
        WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 8) THEN 1
        ELSE 0
    END) AS '2016-8',
    SUM(CASE
        WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 9) THEN 1
        ELSE 0
    END) AS '2016-9',
    SUM(CASE
        WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 10) THEN 1
        ELSE 0
    END) AS '2016-10',
    SUM(CASE
        WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 11) THEN 1
        ELSE 0
    END) AS '2016-11',
    SUM(CASE
        WHEN (YEAR(cout) = 2016 AND MONTH(cout) = 12) THEN 1
        ELSE 0
    END) AS '2016-12',
    SUM(CASE
        WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 1) THEN 1
        ELSE 0
    END) AS '2017-1',
    SUM(CASE
        WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 2) THEN 1
        ELSE 0
    END) AS '2017-2',
    SUM(CASE
        WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 3) THEN 1
        ELSE 0
    END) AS '2017-3',
    SUM(CASE
        WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 4) THEN 1
        ELSE 0
    END) AS '2017-4',
    SUM(CASE
        WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 5) THEN 1
        ELSE 0
    END) AS '2017-5',
    SUM(CASE
        WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 6) THEN 1
        ELSE 0
    END) AS '2017-6',
    SUM(CASE
        WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 7) THEN 1
        ELSE 0
    END) AS '2017-7',
    SUM(CASE
        WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 8) THEN 1
        ELSE 0
    END) AS '2017-8',
    SUM(CASE
        WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 9) THEN 1
        ELSE 0
    END) AS '2017-9',
    SUM(CASE
        WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 10) THEN 1
        ELSE 0
    END) AS '2017-10',
    SUM(CASE
        WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 11) THEN 1
        ELSE 0
    END) AS '2017-11',
    SUM(CASE
        WHEN (YEAR(cout) = 2017 AND MONTH(cout) = 12) THEN 1
        ELSE 0
    END) AS '2017-12'
FROM
    spl_2016.outraw
WHERE
    title = '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
Processing time
0.07 second

Sketches
lens_processed_180201143856312-1 2.jpg
This is the initial sketch when I tried to figure out how to present information from four different sources: Seattle Public Library, Google Trend, Campaign Milestones and Time magazine covers on a single timeline. I realized that if all these elements have to stay on the same plane, then what I can bring to make this flat plane more vivid is some interactive effects, like actions triggered by mouse movements.
lens_processed_180201143856312-1.jpg
During the process of sketching ideas for 2D representation, I thought of a way to present the magazine covers for the 3D version of the visualization.
Work-in-Progress 1st Version
Screen Shot 2018-01-31 at 23.16.23.png
I spent a lot time coding and little time designing. This first draft helped me realize how important coherent color means to a visualization work.
Screen Shot 2018-02-01 at 14.31.23.png
This, looks more like it. I believe this would be a presentable first version of my topics.
Untitled.gif
This is the GIF animation that shows how user's mouse movement can bring up the bars that shows the checking records of Trump related books and DVDs at he Seattle Public Library. When the mouse hover over certain months that include an campaign event, text on top right will give extra information to show the significance of this month, so the user may find an correlation between the Google Trend curve and the library data.
Matrix2D_HW3.zip
(46.22 KiB) Downloaded 91 times
Last edited by chengyuan on Thu Feb 08, 2018 11:10 pm, edited 1 time in total.

annikatan
Posts: 6
Joined: Fri Jan 19, 2018 11:03 am

Re: 2D Frequency Pattern Visualization

Post by annikatan » Thu Feb 01, 2018 3:36 pm

CONCEPT
Feminism was named as the word of year for 2017 by the Merriam-Webster 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 checked-out 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 checked-out 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 '2006-1',

 SUM(CASE
	WHEN (YEAR(cout) = 2006 and month(cout) = 2) THEN 1
	ELSE 0
 END) AS '2006-2',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2006 and month(cout) = 3) THEN 1
	ELSE 0
 END) AS '2006-3',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2006 and month(cout) = 4) THEN 1
	ELSE 0
 END) AS '2006-4',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2006 and month(cout) = 5) THEN 1
	ELSE 0
 END) AS '2006-5',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2006 and month(cout) = 6) THEN 1
	ELSE 0
 END) AS '2006-6',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2006 and month(cout) = 7) THEN 1
	ELSE 0
 END) AS '2006-7',

 SUM(CASE
	WHEN (YEAR(cout) = 2006 and month(cout) = 8) THEN 1
	ELSE 0
 END) AS '2006-8',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2006 and month(cout) = 9) THEN 1
	ELSE 0
 END) AS '2006-9',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2006 and month(cout) = 10) THEN 1
	ELSE 0
 END) AS '2006-10',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2006 and month(cout) = 11) THEN 1
	ELSE 0 
 END) AS '2006-11',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2006 and month(cout) = 12) THEN 1
	ELSE 0
 END) AS '2006-12',
 
 # 2007
 SUM(CASE
	WHEN (YEAR(cout) = 2007 and month(cout) = 1) THEN 1
	ELSE 0
 END) AS '2007-1',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2007 and month(cout) = 2) THEN 1
	ELSE 0
 END) AS '2007-2',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2007 and month(cout) = 3) THEN 1
	ELSE 0
 END) AS '2007-3',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2007 and month(cout) = 4) THEN 1
	ELSE 0
 END) AS '2007-4',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2007 and month(cout) = 5) THEN 1
	ELSE 0
 END) AS '2007-5',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2007 and month(cout) = 6) THEN 1
	ELSE 0
 END) AS '2007-6',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2007 and month(cout) = 7) THEN 1
	ELSE 0
 END) AS '2007-7',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2007 and month(cout) = 8) THEN 1
	ELSE 0
 END) AS '2007-8',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2007 and month(cout) = 9) THEN 1
	ELSE 0
 END) AS '2007-9',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2007 and month(cout) = 10) THEN 1
	ELSE 0
END) AS '2007-10',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2007 and month(cout) = 11) THEN 1
	ELSE 0 
 END) AS '2007-11',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2007 and month(cout) = 12) THEN 1
	ELSE 0
 END) AS '2007-12',
 
 # 2008
 SUM(CASE
	WHEN (YEAR(cout) = 2008 and month(cout) = 1) THEN 1
	ELSE 0
 END) AS '2008-1',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2008 and month(cout) = 2) THEN 1
	ELSE 0
END) AS '2008-2',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2008 and month(cout) = 3) THEN 1
	ELSE 0
 END) AS '2008-3',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2008 and month(cout) = 4) THEN 1
	ELSE 0
 END) AS '2008-4',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2008 and month(cout) = 5) THEN 1
	ELSE 0
 END) AS '2008-5',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2008 and month(cout) = 6) THEN 1
	ELSE 0
 END) AS '2008-6',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2008 and month(cout) = 7) THEN 1
	ELSE 0
 END) AS '2008-7',

 SUM(CASE
	WHEN (YEAR(cout) = 2008 and month(cout) = 8) THEN 1
	ELSE 0
 END) AS '2008-8',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2008 and month(cout) = 9) THEN 1
	ELSE 0
 END) AS '2008-9',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2008 and month(cout) = 10) THEN 1
	ELSE 0
 END) AS '2008-10',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2008 and month(cout) = 11) THEN 1
	ELSE 0 
 END) AS '2008-11',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2008 and month(cout) = 12) THEN 1
	ELSE 0
 END) AS '2008-12',
 
 # 2009
 SUM(CASE
	WHEN (YEAR(cout) = 2009 and month(cout) = 1) THEN 1
	ELSE 0
 END) AS '2009-1',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2009 and month(cout) = 2) THEN 1
	ELSE 0
 END) AS '2009-2',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2009 and month(cout) = 3) THEN 1
	ELSE 0
 END) AS '2009-3',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2009 and month(cout) = 4) THEN 1
	ELSE 0
 END) AS '2009-4',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2009 and month(cout) = 5) THEN 1
	ELSE 0
 END) AS '2009-5',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2009 and month(cout) = 6) THEN 1
	ELSE 0
END) AS '2009-6',

 SUM(CASE
	WHEN (YEAR(cout) = 2009 and month(cout) = 7) THEN 1
	ELSE 0
 END) AS '2009-7',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2009 and month(cout) = 8) THEN 1
	ELSE 0
 END) AS '2009-8',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2009 and month(cout) = 9) THEN 1
	ELSE 0
 END) AS '2009-9',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2009 and month(cout) = 10) THEN 1
	ELSE 0
 END) AS '2009-10',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2009 and month(cout) = 11) THEN 1
	ELSE 0 
 END) AS '2009-11',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2009 and month(cout) = 12) THEN 1
	ELSE 0
 END) AS '2009-12',

# 2010
SUM(CASE
	WHEN (YEAR(cout) = 2010 and month(cout) = 1) THEN 1
	ELSE 0
 END) AS '2010-1',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2010 and month(cout) = 2) THEN 1
	ELSE 0
 END) AS '2010-2',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2010 and month(cout) = 3) THEN 1
	ELSE 0
 END) AS '2010-3',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2010 and month(cout) = 4) THEN 1
	ELSE 0
 END) AS '2010-4',

SUM(CASE
	WHEN (YEAR(cout) = 2010 and month(cout) = 5) THEN 1
	ELSE 0
 END) AS '2010-5',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2010 and month(cout) = 6) THEN 1
	ELSE 0
 END) AS '2010-6',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2010 and month(cout) = 7) THEN 1
	ELSE 0
 END) AS '2010-7',

 SUM(CASE
	WHEN (YEAR(cout) = 2010 and month(cout) = 8) THEN 1
	ELSE 0
 END) AS '2010-8',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2010 and month(cout) = 9) THEN 1
	ELSE 0
 END) AS '2010-9',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2010 and month(cout) = 10) THEN 1
	ELSE 0
 END) AS '2010-10',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2010 and month(cout) = 11) THEN 1
	ELSE 0 
 END) AS '2010-11',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2010 and month(cout) = 12) THEN 1
	ELSE 0
 END) AS '2010-12',
 
 # 2011
 SUM(CASE
	WHEN (YEAR(cout) = 2011 and month(cout) = 1) THEN 1
	ELSE 0
 END) AS '2011-1',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2011 and month(cout) = 2) THEN 1
	ELSE 0
 END) AS '2011-2',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2011 and month(cout) = 3) THEN 1
	ELSE 0
 END) AS '2011-3',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2011 and month(cout) = 4) THEN 1
	ELSE 0
 END) AS '2011-4',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2011 and month(cout) = 5) THEN 1
	ELSE 0
 END) AS '2011-5',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2011 and month(cout) = 6) THEN 1
	ELSE 0
 END) AS '2011-6',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2011 and month(cout) = 7) THEN 1
	ELSE 0
 END) AS '2011-7',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2011 and month(cout) = 8) THEN 1
	ELSE 0
 END) AS '2011-8',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2011 and month(cout) = 9) THEN 1
	ELSE 0
 END) AS '2011-9',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2011 and month(cout) = 10) THEN 1
	ELSE 0
 END) AS '2011-10',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2011 and month(cout) = 11) THEN 1
	ELSE 0 
 END) AS '2011-11',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2011 and month(cout) = 12) THEN 1
	ELSE 0
 END) AS '2011-12',
 
 # 2012
 SUM(CASE
	WHEN (YEAR(cout) = 2012 and month(cout) = 1) THEN 1
	ELSE 0
 END) AS '2012-1',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2012 and month(cout) = 2) THEN 1
	ELSE 0
 END) AS '2012-2',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2012 and month(cout) = 3) THEN 1
	ELSE 0
 END) AS '2012-3',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2012 and month(cout) = 4) THEN 1
	ELSE 0
 END) AS '2012-4',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2012 and month(cout) = 5) THEN 1
	ELSE 0
 END) AS '2012-5',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2012 and month(cout) = 6) THEN 1
	ELSE 0
 END) AS '2012-6',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2012 and month(cout) = 7) THEN 1
	ELSE 0
 END) AS '2012-7',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2012 and month(cout) = 8) THEN 1
	ELSE 0
 END) AS '2012-8',
 
SUM(CASE
	WHEN (YEAR(cout) = 2012 and month(cout) = 9) THEN 1
	ELSE 0
 END) AS '2012-9',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2012 and month(cout) = 10) THEN 1
	ELSE 0
 END) AS '2012-10',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2012 and month(cout) = 11) THEN 1
	ELSE 0 
 END) AS '2012-11',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2012 and month(cout) = 12) THEN 1
	ELSE 0
 END) AS '2012-12',
 
 # 2013
 SUM(CASE
	WHEN (YEAR(cout) = 2013 and month(cout) = 1) THEN 1
	ELSE 0
 END) AS '2013-1',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2013 and month(cout) = 2) THEN 1
	ELSE 0
 END) AS '2013-2',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2013 and month(cout) = 3) THEN 1
	ELSE 0
 END) AS '2013-3',
 
SUM(CASE
	WHEN (YEAR(cout) = 2013 and month(cout) = 4) THEN 1
	ELSE 0
 END) AS '2013-4',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2013 and month(cout) = 5) THEN 1
	ELSE 0
 END) AS '2013-5',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2013 and month(cout) = 6) THEN 1
	ELSE 0
 END) AS '2013-6',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2013 and month(cout) = 7) THEN 1
	ELSE 0
 END) AS '2013-7',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2013 and month(cout) = 8) THEN 1
	ELSE 0
 END) AS '2013-8',
 
SUM(CASE
	WHEN (YEAR(cout) = 2013 and month(cout) = 9) THEN 1
	ELSE 0
 END) AS '2013-9',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2013 and month(cout) = 10) THEN 1
	ELSE 0
 END) AS '2013-10',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2013 and month(cout) = 11) THEN 1
	ELSE 0 
 END) AS '2013-11',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2013 and month(cout) = 12) THEN 1
	ELSE 0
 END) AS '2013-12',
 
 # 2014
 SUM(CASE
	WHEN (YEAR(cout) = 2014 and month(cout) = 1) THEN 1
	ELSE 0
 END) AS '2014-1',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2014 and month(cout) = 2) THEN 1
	ELSE 0
 END) AS '2014-2',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2014 and month(cout) = 3) THEN 1
	ELSE 0
 END) AS '2014-3',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2014 and month(cout) = 4) THEN 1
	ELSE 0
 END) AS '2014-4',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2014 and month(cout) = 5) THEN 1
	ELSE 0
 END) AS '2014-5',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2014 and month(cout) = 6) THEN 1
	ELSE 0
 END) AS '2014-6',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2014 and month(cout) = 7) THEN 1
	ELSE 0
 END) AS '2014-7',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2014 and month(cout) = 8) THEN 1
	ELSE 0
 END) AS '2014-8',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2014 and month(cout) = 9) THEN 1
	ELSE 0
 END) AS '2014-9',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2014 and month(cout) = 10) THEN 1
	ELSE 0
 END) AS '2014-10',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2014 and month(cout) = 11) THEN 1
	ELSE 0 
 END) AS '2014-11',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2014 and month(cout) = 12) THEN 1
	ELSE 0
 END) AS '2014-12',
 
 # 2015
 SUM(CASE
	WHEN (YEAR(cout) = 2015 and month(cout) = 1) THEN 1
	ELSE 0
 END) AS '2015-1',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2015 and month(cout) = 2) THEN 1
	ELSE 0
 END) AS '2015-2',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2015 and month(cout) = 3) THEN 1
	ELSE 0
 END) AS '2015-3',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2015 and month(cout) = 4) THEN 1
	ELSE 0
 END) AS '2015-4',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2015 and month(cout) = 5) THEN 1
	ELSE 0
 END) AS '2015-5',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2015 and month(cout) = 6) THEN 1
	ELSE 0
 END) AS '2015-6',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2015 and month(cout) = 7) THEN 1
	ELSE 0
 END) AS '2015-7',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2015 and month(cout) = 8) THEN 1
	ELSE 0
 END) AS '2015-8',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2015 and month(cout) = 9) THEN 1
	ELSE 0
 END) AS '2015-9',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2015 and month(cout) = 10) THEN 1
	ELSE 0
 END) AS '2015-10',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2015 and month(cout) = 11) THEN 1
	ELSE 0 
 END) AS '2015-11',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2015 and month(cout) = 12) THEN 1
	ELSE 0
 END) AS '2015-12',

# 2016
SUM(CASE
	WHEN (YEAR(cout) = 2016 and month(cout) = 1) THEN 1
	ELSE 0
 END) AS '2016-1',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2016 and month(cout) = 2) THEN 1
	ELSE 0
 END) AS '2016-2',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2016 and month(cout) = 3) THEN 1
	ELSE 0
END) AS '2016-3',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2016 and month(cout) = 4) THEN 1
	ELSE 0
 END) AS '2016-4',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2016 and month(cout) = 5) THEN 1
	ELSE 0
 END) AS '2016-5',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2016 and month(cout) = 6) THEN 1
	ELSE 0
 END) AS '2016-6',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2016 and month(cout) = 7) THEN 1
	ELSE 0
 END) AS '2016-7',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2016 and month(cout) = 8) THEN 1
	ELSE 0
 END) AS '2016-8',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2016 and month(cout) = 9) THEN 1
	ELSE 0
 END) AS '2016-9',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2016 and month(cout) = 10) THEN 1
	ELSE 0
 END) AS '2016-10',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2016 and month(cout) = 11) THEN 1
	ELSE 0 
 END) AS '2016-11',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2016 and month(cout) = 12) THEN 1
	ELSE 0
 END) AS '2016-12',
 
 # 2017
 SUM(CASE
	WHEN (YEAR(cout) = 2017 and month(cout) = 1) THEN 1
	ELSE 0
 END) AS '2017-1',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2017 and month(cout) = 2) THEN 1
	ELSE 0
 END) AS '2017-2',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2017 and month(cout) = 3) THEN 1
	ELSE 0
 END) AS '2017-3',

 SUM(CASE
	WHEN (YEAR(cout) = 2017 and month(cout) = 4) THEN 1
	ELSE 0
 END) AS '2017-4',

 SUM(CASE
	WHEN (YEAR(cout) = 2017 and month(cout) = 5) THEN 1
	ELSE 0
 END) AS '2017-5',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2017 and month(cout) = 6) THEN 1
	ELSE 0
 END) AS '2017-6',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2017 and month(cout) = 7) THEN 1
	ELSE 0
 END) AS '2017-7',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2017 and month(cout) = 8) THEN 1
	ELSE 0
 END) AS '2017-8',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2017 and month(cout) = 9) THEN 1
	ELSE 0
 END) AS '2017-9',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2017 and month(cout) = 10) THEN 1
	ELSE 0
 END) AS '2017-10',
 
SUM(CASE
	WHEN (YEAR(cout) = 2017 and month(cout) = 11) THEN 1
	ELSE 0 
 END) AS '2017-11',
 
 SUM(CASE
	WHEN (YEAR(cout) = 2017 and month(cout) = 12) THEN 1
	ELSE 0
 END) AS '2017-12'

FROM
 spl_2016.outraw
 
WHERE
	(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 
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.
Attachments
proj2_mat259.zip
(15.16 KiB) Downloaded 79 times
Screen Shot 2018-02-01 at 3.32.43 PM.png
Screen Shot 2018-01-31 at 11.23.49 PM.png
IMG_1487.jpg
IMG_1489.jpg
IMG_1488.jpg

echotheohar
Posts: 4
Joined: Fri Jan 19, 2018 11:14 am

Re: 2D Frequency Pattern Visualization

Post by echotheohar » Thu Feb 01, 2018 4:08 pm

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.

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


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.

Image
Attachments
Screen Shot 2018-02-01 at 3.37.25 PM.png
Echo_TheoharAssignment1 2.zip
(44.5 KiB) Downloaded 74 times

Post Reply