## 2D Frequency Pattern Visualization

bensonli
Posts: 4
Joined: Fri Jan 19, 2018 11:04 am

### Re: 2D Frequency Pattern Visualization

Concept Description[/size]

Concept Description
I wanted to see if new movie releases in the Pirates of the Caribbean series would spark interest in rewatching or watching for the first time the previous movies in the series. So, I got from the SPL library data the number of checkouts per month for the first 4 Pirates of the Caribbean movies. To visualize the dataset, I decided to make a symmetric line graph, meaning I would reflect the graph about the time axis. Also I have encoded the absolute value of the change in checkouts in the colormap with respect to the maximum of each movie's maximum change in checkouts, with white being the highest and black being 0.

SQL Queries/Data Analysis

Code: Select all

``````SELECT
YEAR(cout), MONTH(cout), SUM(case when LOWER(spl_2016.inraw.title) like 'pirates of the caribbean the curse of the black pearl' THEN 1 ELSE 0 END) AS 'black pearl',
SUM(case when LOWER(spl_2016.inraw.title) like 'pirates of the caribbean dead mans chest' THEN 1 ELSE 0 END) as 'dead mans chest',
SUM(case when LOWER(spl_2016.inraw.title) like 'pirates of the caribbean at worlds end' THEN 1 ELSE 0 END) as 'at worlds end',
SUM(case when LOWER(spl_2016.inraw.title) like 'pirates of the caribbean on stranger tides' THEN 1 ELSE 0 END) as 'strangers tide'
FROM
spl_2016.inraw
WHERE
(year(cout) > 2005)
group by year(cout), month(cout)
``````
Processing Time: 134 seconds

To do some further data processing, I wrote this small python script that gives me the absolute value of the time derivatives for each of the movie columns. Sketches/Works in Progress I initally wanted to represent the checkout number by a width of a "river". But I figured that for functionality reasons, it would be better to use a line graph. Then, to keep the "river" idea, I decided to reflect the linegraph about the x-axis. Here is a picture of that, now with all the movies. And here is the final results Results and Analysis

The vertical lines that indicate movie release are releases for the library, meaning the montht that the movie was made available in the library. I chose this date because I wanted to measure the influence a new movie has after it has been watched by someone who borrowed the dvd from the library, rather than watching at the movie theater.

There seems to be a "lag" between when the movie is released and when the maximum checkout for that movie occurs. This is probably because people who want the DVD version of the movie are "casuals", who would not know(or care) when the movie will first be made available in the library. One supporting evidence for this claim is that the first local maximum is usually not the global maximum. In any case, the graph does not seem to indicate an increased interest in the previous movies as each new movie is released.

Note that the code(not yet there) is written in such a way that minimal modification is needed to visualize another dataset. Eventually, all that will be required is that modification of file names in the pirates.py file and the title names in the SQL query.
Attachments
pirates.zip
Last edited by bensonli on Tue Mar 06, 2018 11:52 pm, edited 2 times in total.

Posts: 5
Joined: Fri Jan 19, 2018 11:07 am

### Re: 2D Frequency Pattern Visualization

I changed my data inquiry within my mySQL to reflect a more intriguing data set. My original query, I felt would not yield an interesting 2D visual. So I changed my search to reflect the popularity of Seattle grunge music in 2006.

Code: Select all

``````final float margin_to_left = 25;
final float margin_to_top = 100;

Table overall_data;

int rows, cols;

float[][] data_matrix;

float max, min;
float cell_width, cell_height;

color checkout_color;

String[] titles;
String[] checkouts;

//Only run one time
void setup() {
size(1280, 720); // setup the size of the window
//In Processing 3, the size() function must be the first line of code inside setup()
//and needs to use numbers instead of variables.

surface.setResizable(true);

rows = overall_data.getRowCount();
cols = overall_data.getColumnCount();

println("Rows:"+rows);
println("Colums:"+cols);

cell_width = (width - margin_to_left * 19) / cols;
cell_height = (height - margin_to_top * 2) / rows;

data_matrix = new float[rows][cols-2];
titles = new String[rows];
////////////////////////

for(int i=0; i<rows; i++){
for(int j=0; j<cols-2; j++){
data_matrix[i][j] = overall_data.getFloat(i,j+2);

// //Find the max value of the whole data
// if(data_matrix[i][j] > max){
// max = data_matrix[i][j];
// }
//}

// put title and checkout data from the table to the array
titles[i] = overall_data.getString(i, 0);

}
}

///////////////////////// finds min max
for (int i=0; i<data_matrix.length; i++) {
for (int j=0; j<data_matrix.length; j++) {
data_matrix[i][j] = log(overall_data.getFloat(i, j+2)+1);
}
}

////////////////////draws matrix from data
min = 0;
println("Rows:"+rows);
println("Colums:"+cols);

for (int i=0; i<data_matrix.length; i++) {
for (int j=0; j<data_matrix.length; j++) {
if (data_matrix[i][j]>max) {
max = data_matrix[i][j];
}
}
}

min = 0;

println("Max:"+max);
println("Min:"+min);

println(data_matrix.length);
println(data_matrix.length);
}

//Refresh the canvas every frame
void draw() {

background(0);
// println(hour() + ":" + minute() + ":" + second());
// draw 2D matrix
for (int i=0; i<data_matrix.length; i++) {
for (int j=0; j<data_matrix.length; j++) {
stroke(50);

checkout_color = (int)map(data_matrix[i][j], min, max, 500, 20);

fill(checkout_color);
rect(margin_to_left+j*cell_width, margin_to_top+i*cell_height, cell_width, cell_height);
}
}

//Draw Title
textAlign(CENTER, CENTER);
textSize(16);
//stroke(255);
fill(255);
text("TOP GRUNGE 2006", width/15, 50/2);

// Draw vertical labels
for(int i=0; i<rows; i++){
textAlign(LEFT, CENTER);
fill(255);
textSize(20);
text(titles[i], 750, 120 +57*(0.20+i));
}

//show total number on the right side
// textAlign(LEFT, CENTER);
// text(checkouts[i], width-5+3,5 +5*(0.5+i));
//}

//Draw horizontal labels
for (int i=0; i<12; i++) {
int m = month();
textSize(10);
textAlign(CENTER, TOP);
fill(255);
text(1+i, 10 + 5*(6+12*i),
5 +5*9 + 5);

//draw the seperation lines
if (i != 0) {
stroke(#009EE5);

line(5 + 5*(12*i), 5 +5*9 + 3,
5 + 5*(12*i), 5 +5*9 + 20);

}
}

}``````
Attachments  TOP GRUNGE 2006.pdf
Last edited by admjahnke on Wed Feb 28, 2018 12:22 am, edited 1 time in total.

christinalast
Posts: 7
Joined: Fri Jan 19, 2018 11:15 am

### Re: 2D Frequency Pattern Visualization

Concept Description
I am developing spatializations that support spatial search across the Seattle Public Library. Library repositories offer unique insights into the travel patterns, destinations and location-specific material popular within the general public in Seattle.

Location-specific material (defined as books that occupy a Dewey class ranging from 910-919) from SPL will be spatialized into fields, object collections, and networks. Such spatializations will convey how location-specific material is related not just through geographic location, but through similarity (e.g. complementary topic neighbourhoods). The expected result is the development of search spaces that situate location-specific material in much more powerful ways than library shelves, supporting multiple paradigms of data discovery, including querying and browsing.

Sketch The visualisation strategy is inspired by travel, the idea of representing a flow of information from locations around the world to Seattle public library is used as a metaphor.

To obtain travel-based items in the Seattle Public library database, items with a Dewey Class ranging from 910 to 919 are particularly focussed on travel. To query the data I use the following script:

Code: Select all

``````SELECT
COUNT(outraw.itemNumber) AS NumberOfTimes,
FLOOR(deweyClass) * 100 AS Dewey,
title
FROM
spl_2016.outraw
WHERE
deweyClass >= 910
AND deweyClass <= 919
GROUP BY itemNumber , deweyClass , title
HAVING (COUNT(itemNumber) > 1)
ORDER BY NumberOfTimes DESC
LIMIT 100
``````
The duration of the query is around 31 seconds.

For its functional purpose, I want to show the topic relationships between library items, which is exhibited by the colour of each link. The name of the library item appears underneath the category of the Dewey Class. The arcs form links between a single library item and the other items in the selection that share the same Dewey Class (Reference: Rodger Luo).

Final Visualisation and Analysis   From the visualisation I notice that there are some items in SPL that are incorrectly located in certain Dewey Classes.

To continue with my metaphor, I want to add a feature to my visualisation that locates all of the items associated with travel in the Seattle Public Library to a particular geographic location in the world. When the item is selected, either from the topic link visualisation of the mapped visualisation. Items with the same Dewey Class will share a link with each other or with the Seattle public library.

From this, I aim to combine the topic link visualisation into a geographic perspective. To do this, when each library item is selected, others from that continent will be highlighted in their relative geographic positions.

To do this, each library item selected needed to be geocoded by the following query in R;

Code: Select all

``````library(httr)
stop_for_status(r)
result <- content(r)
first <- result\$results[]
df <- as.data.frame(list(lat=first\$geometry\$location\$lat, lon=first\$geometry\$location\$lng))
return(df)
}
result[1,] <- geocode("Central America")
…
result[129,]<- geocode("Spanish Islands")
write.csv(result, "geocoded.csv", row.names=FALSE)
``````
Although this remains a work in progress, I aim to produce a visualisation which allows user to navigate through search spaces in a topical way (exploring similar topic relations) and geographical relationships (exploring the relationship to the Seattle Public Library, or to the other items occupying its Dewey Class.
Attachments

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

mingyulin
Posts: 4
Joined: Wed Feb 07, 2018 1:31 pm

### Re: 2D Frequency Pattern Visualization

Concept description

In this assignment, I chose Harry Potter series to show the relationship of checkout times between different years and different books. Since Harry Potter series has seven books, I chose the seven classic colors in the rainbow. For the 2D design, I want to show the whole information at first peek, so I made a seven by seven matrix. I had two factors to reflect the numbers of checkout times which were transparency of colors and the size of the matrix cell. To have more interactive functionalities and specific numbers for each year in the plot, I added a function that if user click on a specific year, the plot will be redrawn into histogram.

---
MySQL Queries

Code: Select all

``````SELECT
title, COUNT(title) AS COUNTS,
SUM(CASE
WHEN (YEAR(cout) = 2005) THEN 1
ELSE 0
END) AS '2005',
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'
FROM
spl_2016.inraw
WHERE
( title = "Harry Potter and the Order of the Phoenix" OR
title = "Harry Potter and the half blood prince" OR
title = "Harry Potter and the deathly hallows" OR
title = "Harry Potter and the chamber of secrets" OR
title = "Harry Potter and the sorcerers stone" OR
title = "Harry Potter and the prisoner of Azkaban" OR
title = "Harry Potter and the goblet of fire" ) AND
itemtype LIKE "%bk" AND
title NOT LIKE "%soundtrack"
GROUP BY title
ORDER BY title;
``````
Processing time
Query time = 0.735s

Update Version:
Query from 2008 to 2015 monthly

Code: Select all

``````SELECT
title, COUNT(title) AS COUNTS,
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'
FROM
spl_2016.inraw
WHERE
( title = "Harry Potter and the Order of the Phoenix" OR
title = "Harry Potter and the half blood prince" OR
title = "Harry Potter and the deathly hallows" OR
title = "Harry Potter and the chamber of secrets" OR
title = "Harry Potter and the sorcerers stone" OR
title = "Harry Potter and the prisoner of Azkaban" OR
title = "Harry Potter and the goblet of fire" ) AND
itemtype LIKE "%bk" AND
title NOT LIKE "%soundtrack"
GROUP BY title
ORDER BY title;
``````
--
Sketches and work-in-progress screenshots of your project with descriptions Updated version2:
--
Final results & Analysis

It was interesting to find that seven books share with the same trend in 12 years. That is to say, the number of checkout for every installment has the same fluctuation pattern. In addition, I manually sorted every installment by published time. The bottom one, Sorcerers stones, was the first book and the top one, Deathly hallows, was the last book. The plot showed that there was no big difference in number of checkout for books published before 2005. Furthermore, seven books had similar numbers after 2012. One thing should be mentioned was that the number for the first book remained high compared to other books.

--
Attachments
MingyuLin-HarryPotterV2.zip
Assignment2_MingyuLin.zip
Last edited by mingyulin on Thu Mar 01, 2018 4:52 pm, edited 1 time in total.

zhenyuyang
Posts: 9
Joined: Fri Apr 01, 2016 2:34 pm

### Re: 2D Frequency Pattern Visualization

SOUNDS BEHIND PICTURES
SCORES BY BRIAN TYLER IN 20 YEARS
Zhenyu Yang

DESCRIPTION
This homework is a processing program that visualizes the numbers of cores written by Brian Tyler from 1997 to 2017. During this period, Brain wrote scores for 71 films, 8 TV films. 22 TV series, and 6 video games.

COLOR CODING
Red – green- blue color system

SKETCH & IDEA
I was trying to incorporate the shape of the solar system into this visualization project. In the beginning, I set each year as an individual sun, which is surrounded by planets. The number of planets is the number of scores that Brain Tyler wrote in the corresponding year.
Planets are in4 different colors, representing 4 different categories of scores: Films, TV Films, TV Series, and video games.
In addition to the color, each planet (represented as a point on the screen) has two more attributes: Normal velocity and tangential velocity. Both of them are bonded with a value that describes the degree of activity of Brain Tyler in each year.
The degree of activity is calculated with the equation below:
*New idea: When the project was initially implemented, I noticed that the spinning planets were not suitable to reveal data because the periodic circular motions may cause dizziness, which will impose uncomfortableness on the audience(Shown as the following).
To reduce this effect, I changed the solar system layout so that each planet no longer going around the center, but is ejecting from the center, simulating the effect of electronic transition. The center is no longer static but blinking, and the frequency is inverse proportional to the degree of activity.
QUERY
My code only outputs the itemType, itemNumber, and the number of times that item has been checked out.

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 = 'Bartender'
OR title = 'Six string samurai'
OR title = 'Settlement'
OR title LIKE '%4th Floor%'
OR title = 'Simon Sez'
OR title = 'Panic'
OR title = 'Four Dogs Playing Poker'
OR title = 'Terror Tract'
OR title = 'Frailty'
OR title = 'offsite'
OR title = 'Bubba Ho tep'
OR title LIKE '%Vampires%' and title like '%Los Muertos%'
OR title = 'Darkness Falls'
OR title = 'hunted'
OR title = 'Last Stand'
OR title = 'big empty'
OR title = 'Timeline'
OR title = 'Perfect Opposites'
OR title = 'Final Cut'
OR title = 'Godsend'
OR title = 'Paparazzi'
OR title = 'Clair obscur'
OR title = 'Constantine'
OR title = 'Panic'
OR title = 'Annapolis'
OR title = 'Bug'
OR title LIKE '%fast and the furious Tokyo drift%'
OR title = 'Partition'
OR title LIKE '%Finishing%' and title like '%Game%'
OR title = 'War'
OR title LIKE '%Aliens vs Predator Requiem%'
OR title = 'rambo'
OR title = 'Bangkok Dangerous'
OR title = 'Eagle Eye'
OR title = 'Lazarus Project'
OR title = 'Killing Room'
OR title = 'Dragonball Evolution'
OR title = 'fast and the furious'
OR title LIKE '%Middle Men%'
OR title = 'Final Destination'
OR title = 'Law Abiding Citizen'
OR title = 'Skyline'
OR title = 'Battle Los Angeles'
OR title = 'Tattoo'
OR title = 'Fast Five'
OR title = 'Final Destination 5'
OR title = 'John Dies at the End'
OR title LIKE '%Columbus%' and title like '%Circle%'
OR title = 'Brake'
OR title = 'Iron Man three'
OR title = 'Standing Up'
OR title = 'Thor The dark world'
OR title = 'Teenage Mutant Ninja Turtles'
OR title = 'Expendables'
OR title = 'Into the Storm'
OR title LIKE '%All Hail the King%'
OR title LIKE '%Furious 7%' or title = 'furious seven'
OR title LIKE '%Age of Ultron%'
OR title = 'Truth'
OR title LIKE '%Disappointments Room%'
OR title LIKE '%Return of Xander Cage%'
OR title = 'Criminal'
OR title = 'Now you see me 2'
OR title = 'Now you see me'
OR title = 'Power Rangers'
OR title LIKE '%Fate of the Furious%'
OR title = 'mummy'

OR title = 'Final Justice'
OR title = 'Sirens'
OR title = 'Jane Doe'
OR title = 'Trapped in a Purple Haze'
OR title = 'Last Call'
OR title = 'Thoughtcrimes'
OR title = 'Painkiller Jane'

OR title = 'Living in Captivity'
OR title = 'Jenny'
OR title = 'Level 9'
OR title = 'Fear Itself'
OR title like '%Star Trek Enterprise%'
OR title like '%hawaii five%'
OR title = 'Terra nova The complete series'
OR title like '%Sleepy Hollow%' and title like '%season%'
OR title like '%Scorpion%' and title like '%season%'
GROUP BY bibNumber , itemtype, title
ORDER BY Counts DESC``````
PROCESSING TIME
872.678 sec

FINAL RESULTS & ANALYSIS
Based on the final version of this homework, we can clearly see that Brain Tyler mainly wrote scores for films and TV films in the early stage of his career. Brian was most active during the year of 2003 and year of 2009 to 2014. It also can be seen that Brain started writing scores for video games in 2010, and remain productive for 2 years. All these trends matched the trends I obtained in the HW1’s chart, which is shown again as the below: