PROJ 1: VISUALIZING the CULTURE ANALYSIS in 2D (PROCESSING)

lliu
Posts: 9
Joined: Wed Jan 06, 2016 1:41 pm

Re: PROJ 1b: VISUALIZING the CULTURE ANALYSIS in 2D (PROCESS

Post by lliu » Mon Jan 25, 2016 3:23 am

FILM ADAPTATION INFLUENCE
The Trend of Checkout Numbers of Books During Corresponding Adaptation Film Released Time

Books and movies remain the most popular cultural represent forms.
Will they have mutual impact on each other?
For example: When a film adapted from a book released,
will the checkout number of this book in library increase?

I chose several adapted films respectively released on 2006, 2009, 2012 and 2014.
Compare the checkout number of specific book in the normal time and
the time after adaptation film released to find the trend over months and years.


Description:
The horizon axis represent the time(from 2006 to 2014).
And the number of rectangular represent the number of checkout per month.

Press "s" OR "S" on keyboard to show the whole trend of four books.
Click the specific movie item to see more details information about the adaptation movie
and its correspond book.
QQ20160125-3@2x.png
QQ20160125-4@2x.png
QQ20160125-5@2x.png
QQ20160125-6@2x.png
MySQL Example Code:

Code: Select all

SELECT 
    YEAR(checkOut) AS CheckoutYear,
    MONTH(checkout) AS CheckoutMonth,
    COUNT(checkOut) AS CheckoutCount
FROM
    spl3._rawXmlDataCheckIns

WHERE
    title LIKE '%Hunger Games%'
	   AND SUBSTRING(itemType, 3, 4) = 'bk'
GROUP BY CheckoutYear , CheckoutMonth

Processing Code:

visualizationHWtwo

Code: Select all

//Margin
float constHoriMarg=120;
float constVertMarg=80;

int rows=20, columns=132;

//2D matrix 
Table table1, table2, table3, table4;
float[][] dataMatrix1;
float[][] dataMatrix2;
float[][] dataMatrix3;
float[][] dataMatrix4;
float maxValue, minValue;
int numRows1, numRows2, numRows3, numRows4;
int numColumns1, numColumns2, numColumns3, numColumns4;

//width and height for cell
float cellWidth, cellHeight;

//year
int startYear=2006;

boolean colorApplied=false;
boolean labelApplied=false;
boolean rectOne=false;
boolean rectTwo=false;
boolean rectThree=false;
boolean rectFour=false;

void setup() {
  size(1280, 720);

  //Put data into Processing table from csv
  table1 =loadTable("the da vinci code.csv", "header");
  table2=loadTable("the time travelers wife.csv", "header");
  table3=loadTable("the hunger games.csv", "header");
  table4=loadTable("fault in our stars.csv", "header");
  numRows1 = table1.getRowCount();
  numRows2 = table2.getRowCount();
  numRows3 = table3.getRowCount();
  numRows4 = table4.getRowCount();
  numColumns1 = table1.getColumnCount();
  numColumns2 = table2.getColumnCount();
  numColumns3 = table3.getColumnCount();
  numColumns4 = table4.getColumnCount();
  dataMatrix1=new float[numRows1][numColumns1-2];
  dataMatrix2=new float[numRows2][numColumns2-2];
  dataMatrix3=new float[numRows3][numColumns3-2];
  dataMatrix4=new float[numRows4][numColumns4-2];
  //println("Rows:"+numRows1+"Columns:"+numColumns1);

  //set the background Matrix
  for (int i=0; i<numRows1; i++) {
    for (int j=0; j<numColumns1-2; j++) {
      dataMatrix1[i][j] = table1.getFloat(i, j+2);
      //println(dataMatrix1[i][j]);
    }
  }

  for (int i=0; i<numRows2; i++) {
    for (int j=0; j<numColumns2-2; j++) {
      dataMatrix2[i][j] = table2.getFloat(i, j+2);
      //println(dataMatrix2[i][j]);
    }
  }

  for (int i=0; i<numRows3; i++) {
    for (int j=0; j<numColumns3-2; j++) {
      dataMatrix3[i][j] = table3.getFloat(i, j+2);
      //println(dataMatrix3[i][j]);
    }
  }

  for (int i=0; i<numRows4; i++) {
    for (int j=0; j<numColumns4-2; j++) {
      dataMatrix4[i][j] = table4.getFloat(i, j+2);
      //println(dataMatrix4[i][j]);
    }
  }
}

void draw() {
  background(#236e9c);

  cellWidth = (width - constHoriMarg*2)/columns;
  cellHeight = (height - constVertMarg*4)/rows;

  //Draw second layer background
  stroke(#1f6088);
  strokeWeight(3);
  fill(#1f6088);
  rect(constHoriMarg, constVertMarg+50, cellWidth*columns, cellHeight*(rows-8));

  //Draw Year
  for (int i=0; i<12; i+=3) {
    textAlign(CENTER, CENTER);
    textSize(18);
    fill(#e9f0f4);
    text(startYear+i, constHoriMarg+(12+(12*i))*cellWidth, 
      constVertMarg+35);
  }

  //Draw TiTle
  textAlign(LEFT, CENTER);
  textSize(26);
  fill(#e9f0f4);
  text("FILM ADAPTATION INFLUENCE", constHoriMarg, 
    constVertMarg-40);
  fill(#81e298);
  text("ADAPTATION", constHoriMarg+66, 
    constVertMarg-40);
  textSize(14);
  fill(#e9f0f4);
  text("From Seattle Public Library", constHoriMarg, constVertMarg-60);
  text("The Trend of Checkout Numbers of Books During Corresponding Adaptation Film Released Time",
  constHoriMarg, constVertMarg-15);

  if (labelApplied==true)labelAppeared();
  if (colorApplied==true)colorAppeared();
}
Introduction

Code: Select all

void introduction1() {
  stroke(#1f587b);
  fill(#1f587b);
  rect(800, 405, 360, 160);
  textAlign(LEFT, BOTTOM);
  textSize(19);
  fill(#a0b9c9);
  text("The Da Vinci Code", 810, 435);
  textSize(15);
  text("The Da Vinci Code is a mystery-detective", 810, 460);
  text("novel written by Dan Brown written in 2003.", 810, 480);
  text("The adaptation film was released", 810, 500 );
  text("on May 19, 2006.", 810, 520 );

  noStroke();
  strokeWeight(3);
  fill(0, 70);
  rect(constHoriMarg+6*cellWidth, constVertMarg+50, 
    cellWidth*12, cellHeight*(rows-8));
  stroke(255);
  fill(255);
  triangle(constHoriMarg+12*cellWidth, constVertMarg+38+cellHeight*(rows-8), 
    constHoriMarg+11*cellWidth, constVertMarg+48+cellHeight*(rows-8), 
    constHoriMarg+13*cellWidth, constVertMarg+48+cellHeight*(rows-8));
}

void introduction2() {
  stroke(#1f587b);
  fill(#1f587b);
  rect(800, 405, 360, 160);
  textAlign(LEFT, BOTTOM);
  textSize(19);
  fill(#a0b9c9);
  text("The Time Traveler's Wife", 810, 435);
  textSize(15);
  text("The Time Traveler’s Wife is a novel", 810, 460);
  text("by Audrey Niffenegger published in 2003. ", 810, 480);
  text("The adaptation film was released", 810, 500 );
  text("on 14 August 2009.", 810, 520 );

  noStroke();
  strokeWeight(3);
  fill(0, 70);
  rect(constHoriMarg+42*cellWidth, constVertMarg+50, 
    cellWidth*12, cellHeight*(rows-8));
  stroke(255);
  fill(255);
  triangle(constHoriMarg+48*cellWidth, constVertMarg+38+cellHeight*(rows-8), 
    constHoriMarg+47*cellWidth, constVertMarg+48+cellHeight*(rows-8), 
    constHoriMarg+49*cellWidth, constVertMarg+48+cellHeight*(rows-8));
}

void introduction3() {
  stroke(#1f587b);
  fill(#1f587b);
  rect(800, 405, 360, 225);
  textAlign(LEFT, BOTTOM);
  textSize(19);
  fill(#a0b9c9);
  text("Hunger Games", 810, 435);
  textSize(15);
  text("Hunger Games is a series of three adventure ", 810, 460);
  text("novels written by Suzanne Collins,", 810, 480);
  text("which respectively published ", 810, 500 );
  text("in 2008, 2009 and 2010.", 810, 520 );
  text("And the corresponding adaptation film was", 810, 540 );
  text("respectively released on March 23, 2012", 810, 560 );
  text("2013, 2014 and 2015 ", 810, 580 );
  text("(Mockingjay has been divided into two parts)", 810, 600 );
  
  noStroke();
  strokeWeight(3);
  fill(0, 70);
  rect(constHoriMarg+78*cellWidth, constVertMarg+50, 
    cellWidth*12, cellHeight*(rows-8));
  stroke(255);
  fill(255);
  triangle(constHoriMarg+84*cellWidth, constVertMarg+38+cellHeight*(rows-8), 
    constHoriMarg+83*cellWidth, constVertMarg+48+cellHeight*(rows-8), 
    constHoriMarg+85*cellWidth, constVertMarg+48+cellHeight*(rows-8));
}

void introduction4() {
  stroke(#1f587b);
  fill(#1f587b);
  rect(800, 405, 360, 160);
  textAlign(LEFT, BOTTOM);
  textSize(19);
  fill(#a0b9c9);
  text("Fault in Our Stars", 810, 435);
  textSize(15);
  text("Fault in Our Stars is a novel by John Green ", 810, 460);
  text("published in 2012.", 810, 480);
  text("The adaptation film was released", 810, 500 );
  text("June 6, 2014. ", 810, 520 );

  noStroke();
  strokeWeight(3);
  fill(0, 70);
  rect(constHoriMarg+102*cellWidth, constVertMarg+50, 
    cellWidth*12, cellHeight*(rows-8));
  stroke(255);
  fill(255);
  triangle(constHoriMarg+108*cellWidth, constVertMarg+38+cellHeight*(rows-8), 
    constHoriMarg+107*cellWidth, constVertMarg+48+cellHeight*(rows-8), 
    constHoriMarg+109*cellWidth, constVertMarg+48+cellHeight*(rows-8));
}

colorApplied

Code: Select all

void colorAppeared() {

  //color the hunger games
  for (int i=0; i<numRows3; i++) {
    for (int j=0; j<numColumns3-2; j++) {
      dataMatrix3[i][j] = table3.getFloat(i, j+2);
      //println(dataMatrix3[i][j]);
    }
  }

  for (int m=0; m<numRows3; m++) {
    for (int n=0; n<((int)(dataMatrix3[m][0]/40)); n++) {
      stroke(#1f6088);
      strokeWeight(3);
      //fill(#2F5474);
      fill(#DE5176);
      rect(constHoriMarg + cellWidth*(m+38), constVertMarg
        + cellHeight*n + 50, cellWidth, cellHeight);
    }
  }

  //color the time travelers wife
  for (int i=0; i<numRows2; i++) {
    for (int j=0; j<numColumns2-2; j++) {
      dataMatrix2[i][j] = table2.getFloat(i, j+2);
      //println(dataMatrix2[i][j]);
    }
  }

  for (int m=0; m<numRows2; m++) {
    for (int n=0; n<((int)(dataMatrix2[m][0]/2)); n++) {
      stroke(#1f6088);
      strokeWeight(3);
      //fill(#2F5474);
      fill(#10D5E5);
      rect(constHoriMarg + cellWidth*(m+18), constVertMarg
        + cellHeight*n + 50, cellWidth, cellHeight);
    }
  }

  //color the fault in our stars
  for (int i=0; i<numRows4; i++) {
    for (int j=0; j<numColumns4-2; j++) {
      dataMatrix4[i][j] = table4.getFloat(i, j+2);
      //println(dataMatrix4[i][j]);
    }
  }

  for (int m=0; m<numRows4; m++) {
    for (int n=0; n<((int)(dataMatrix4[m][0]/20)); n++) {
      stroke(#1f6088);
      strokeWeight(3);
      //fill(#2F5474);
      fill(#F2A744);
      rect(constHoriMarg + cellWidth*(m+78), constVertMarg
        + cellHeight*n + 50, cellWidth, cellHeight);
    }
  }

  //color the da vinci code
  for (int i=0; i<numRows1; i++) {
    for (int j=0; j<numColumns1-2; j++) {
      dataMatrix1[i][j] = table1.getFloat(i, j+2);
      //println(dataMatrix1[i][j]);
    }
  }

  for (int m=0; m<numRows1; m++) {
    for (int n=0; n<((int)(dataMatrix1[m][0]/10)); n++) {
      stroke(#1f6088);
      strokeWeight(3);
      //fill(#2F5474);
      fill(#9264AA);
      rect(constHoriMarg + cellWidth*m, constVertMarg
        + cellHeight*n + 50, cellWidth, cellHeight);
    }
  }
  if (rectOne)introduction1();
  if (rectTwo)introduction2();
  if (rectThree)introduction3();
  if (rectFour)introduction4();
}
labelApplied

Code: Select all

void labelAppeared() {
  // Draw the interpretation panel color

  for (int i=0; i<4; i++) {
    if ((mouseX>130)&&(mouseX<315)&&(mouseY>500+i*40)&&(mouseY<500+i*40+cellHeight)) {
      stroke(#264d64);
      fill(#264d64);
      rect(130, 500+i*40, 185, cellHeight);
    } else {
      stroke(#295772);
      fill(#295772);
      rect(130, 500+i*40, 185, cellHeight);
      rect(143, 450, 142, cellHeight);
    }
  }

  stroke(#9264AA);
  fill(#9264AA);
  rect(123, 500, cellWidth-2, cellHeight);

  stroke(#10D5E5);
  fill(#10D5E5);
  rect(123, 540, cellWidth-2, cellHeight);

  stroke(#DE5176);
  fill(#DE5176);
  rect(123, 580, cellWidth-2, cellHeight);

  stroke(#F2A744);
  fill(#F2A744);
  rect(123, 620, cellWidth-2, cellHeight);

  // Draw the interpretation panel text
  textAlign(LEFT, BOTTOM);
  textSize(14);
  fill(#a0b9c9);
  text("The Da Vinci Code", 140, 520);
  text("The Time Traveler's Wife", 140, 560);
  text("Hunger Games", 140, 600);
  text("Fault in Our Stars", 140, 640);
  textSize(16);
  text("Search Details", 160, 470);
}
Interaction

Code: Select all

void keyPressed() {
  if (key=='s' || key =='S') {
    colorApplied=!colorApplied;
    labelApplied=!labelApplied;
    rectOne = false;
    rectTwo = false;
    rectThree = false;
    rectFour = false;
  }
}

void mousePressed() {
  for (int i=0; i<4; i++) {
    if ((mouseX>130)&&(mouseX<315)&&(mouseY>500+i*40)&&(mouseY<500+i*40+cellHeight)) {
      stroke(#285c7c);
      fill(#285c7c);
      if (i==0) { 
        rectOne = true;
        rectTwo = false;
        rectThree = false;
        rectFour = false;
      } else if (i==1) {
        rectTwo=true;
        rectOne = false;
        rectThree = false;
        rectFour= false;
      } else if (i==2) {
        rectThree=true;
        rectTwo = false;
        rectOne = false;
        rectFour= false;
      } else {
        rectFour=true;
        rectTwo = false;
        rectThree = false;
        rectOne= false;
      }
    }
  }
}
Attachments
fault in our stars.csv
(424 Bytes) Downloaded 211 times
the hunger games.csv
(872 Bytes) Downloaded 205 times
the time travelers wife.csv
(668 Bytes) Downloaded 195 times
the da vinci code.csv
(1.09 KiB) Downloaded 205 times

hkung
Posts: 4
Joined: Wed Jan 06, 2016 12:47 pm

Re: PROJ 1b: VISUALIZING the CULTURE ANALYSIS in 2D (PROCESS

Post by hkung » Mon Jan 25, 2016 9:14 pm

Following up to my last queries, I found that there were falls in the numbers of travel books checked out during 2010 and 2012. Therefore I wondered if we can also observe similar phenomena among other book categories.

***Query***
My query runs as follows:

Code: Select all

SELECT
SUM(CASE WHEN YEAR(checkOut) = 2006 THEN 1 ELSE 0 END) `2006`, 
SUM(CASE WHEN YEAR(checkOut) = 2007 THEN 1 ELSE 0 END) `2007`, 
SUM(CASE WHEN YEAR(checkOut) = 2008 THEN 1 ELSE 0 END) `2008`, 
SUM(CASE WHEN YEAR(checkOut) = 2009 THEN 1 ELSE 0 END) `2009`, 
SUM(CASE WHEN YEAR(checkOut) = 2010 THEN 1 ELSE 0 END) `2010`, 
SUM(CASE WHEN YEAR(checkOut) = 2011 THEN 1 ELSE 0 END) `2011`, 
SUM(CASE WHEN YEAR(checkOut) = 2012 THEN 1 ELSE 0 END) `2012`, 
SUM(CASE WHEN YEAR(checkOut) = 2013 THEN 1 ELSE 0 END) `2013`, 
SUM(CASE WHEN YEAR(checkOut) = 2014 THEN 1 ELSE 0 END) `2014` 
FROM spl3._rawXmlDataCheckIns 
WHERE 
deweyClass >= 000 AND deweyClass < 100 
AND DATE(checkOut) >= '20060101' 
AND DATE(checkOut) <= '20141231'
I modified deweyClass for each Dewey Classification. For example, the above query will return the number of books checked out in the category of Computer Science, Information and General Works.

My processing code for visualizing the data runs as follows.

Code: Select all

/********************************************************
 * Data Visualization - 2D Matrix Demo                  *
 * Tested in Processing 3                               *     
 *                                                      *
 * Author: Han-Wei Kung                                 *
 *                                                      *
 ********************************************************/

/*
Step One: Upload CSV to Table & Get basic information of data. 
Step Two: Write data from Table to 2D Array & Have max and min value of data.
Step Three: Visualize 2D Matrix.
Step Four: Draw labels and title.
*/

Table table;
/* row = category, col = year */
int numClass, numYear;  

float[][] dataMatrix;
float maxVal, minVal;

// rects
int horMargin = 160;
int verMargin = 100;
float cellWidth, cellHeight;
float ellipseWidth, ellipseHeight;

// labels
int startYear = 2006;
int endYear = 2014;

PImage colorBar;

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

  table = loadTable("MAT259_DataQuery.csv", "header");
  numClass = table.getRowCount();
  numYear = table.getColumnCount() - 1;
  println("Rows: " + numClass + " Columns: " + numYear);
  
  colorBar = loadImage("color_spectrum.jpg");
  
  
  maxVal = 0;
  dataMatrix = new float[numClass][numYear];
  for (int j = 0; j < numClass; j++) {
    for (int i = 0; i < numYear; i++) {
      dataMatrix[j][i] = table.getFloat(j, i + 1);
    }
  }
  
  maxVal = max2D(dataMatrix);
  minVal = min2D(dataMatrix);
}

//Refresh the canvas every frame
void draw() {  
  // println(hour() + ":" + minute() + ":" + second());
  background(255);
  
  // Calculate the width and height for each cell
  cellWidth = ((width - 2*horMargin) / numYear);
  cellHeight = (height - 2*verMargin) / numClass;
  
  // Draw 2D Matrix on canvas
  for (int i = 0; i < numYear; i ++) {
    for (int j = 0; j < numClass; j++) {
      color c = colorBar.get( (int)map(j, 0, numClass, 1, colorBar.width) - 1, colorBar.height/2 );

      ellipseWidth = map(dataMatrix[j][i], minVal, maxVal, 10, cellWidth);
      ellipseHeight = map(dataMatrix[j][i], minVal, maxVal, 5, cellHeight);
      
      fill(c);
      ellipse(horMargin + cellWidth * j + cellWidth / 2, verMargin + cellHeight * i + cellHeight / 2, ellipseWidth, ellipseHeight);
    }
  }
  
  fill(0);
  textSize(32);
  textAlign(CENTER, BOTTOM);
  text("# of books checked out in Seattle Public Library", width / 2, verMargin / 2);
  
  // Add YEAR labels
  fill(0);
  textSize(11);
  for (int i = 0; i <= endYear - startYear; i ++) {
    textAlign(RIGHT, CENTER);
    text(startYear + i, horMargin, verMargin + cellHeight / 2 + cellHeight * i);
  }
  
  drawLabels();
}

void drawLabels() {
  int gap = 40;
  int labelWidth = 75;
  String labelName = "None";
  
  for (int j = 0; j < numClass; j++) {
    color c = colorBar.get( (int)map(j, 0, numClass, 2, colorBar.width-2), colorBar.height/2 );
    fill(c);
    rect(horMargin/2 + j * (labelWidth + gap), height - verMargin/2, labelWidth, cellWidth / (numClass+1));
    
    fill(0);
    textAlign(LEFT, TOP);
    switch(j) {
      case 0: 
        labelName = "Computer Science";
        break;
      case 1: 
        labelName = "Philosophy";
        break;
      case 2: 
        labelName = "Religion";
        break;
      case 3: 
        labelName = "Social Science";
        break;
      case 4: 
        labelName = "Language";
        break;
      case 5: 
        labelName = "Science";
        break;
      case 6: 
        labelName = "Technology";
        break;
      case 7: 
        labelName = "Arts";
        break;
      case 8: 
        labelName = "Literature";
        break;
      case 9: 
        labelName = "History";
        break;        
    }        
    text(labelName, horMargin/2 + j * (labelWidth + gap), height - verMargin/2.5);    
  }
  
}

//Function to get max value from a 2d array
float max2D(float[][] array2D) {
  float max = 0;
  for (int i=0; i<array2D.length; i++) {
    for (int j=0; j<array2D[i].length; j++) {
      if (array2D[i][j] > max) {
        max = array2D[i][j];
      }
    }
  }
  return max;
}

//Function to get min value from a 2d array
float min2D(float[][] array2D) {
  float max = max2D(array2D);
  float min = max;
  for (int i=0; i<array2D.length; i++) {
    for (int j=0; j<array2D[i].length; j++) {
      if (array2D[i][j] < min) {
        min = array2D[i][j];
      }
    }
  }
  return min;
}
***Results***
The results show that the numbers of books checked out dropped sharply in all categories between 2010 and 2012. During this two-year period, the drop rate of books checked out in the category of Science remains the least while the rate appears the most in the category of Religion.

***Commentary***
The reason that the amount of books checked out decreased significantly from 2010 to 2012 could be the serious financial crisis which happened in late 2008. There could be funding cuts in Seattle so that the public library cannot afford to buy new books.
Attachments
mat259_proj1_mysql_query.pdf
(34.92 KiB) Downloaded 324 times
allCategories.xlsx
(65.15 KiB) Downloaded 217 times
Matrix2D.jpg
[UPDATE MAT 259] Data Query.pdf
(385.94 KiB) Downloaded 286 times
MAT259_2DMatrix_UPDATE.zip
(154.02 KiB) Downloaded 201 times
Last edited by hkung on Wed Feb 03, 2016 6:35 pm, edited 1 time in total.

thomasahervey
Posts: 4
Joined: Wed Jan 06, 2016 12:50 pm

Visualizing SPL Book Age, Popularity and Adoption Rates

Post by thomasahervey » Thu Jan 28, 2016 10:49 am

Content Description
The following results are a continuation from the last step of my project, determining the relationship between media checkout frequency, age, and time. In summary, my project aims to understand the relationship between several quantifications of physical checkout frequency and media age in an evolving age of online content competition.

Based on previous limitations, the original dataset has been restricted to physical books published since the new library’s opening. Variables have been generated by observing the temporal differences in publication date, first checkout, last checkout and total checkout counts. Please see last submission for further background information.

Data Collection
Thus far, this has been the most time consuming portion of this project. Paired with data from the Seattle Public Library database, I have gathered Amazon book data via API calls. Through customized constructed tools with several machines making calls, I was able to retrieve roughly 118,000 unique ISBN records. This was needed to retrieve publicationDate data. Paired with Seattle Public Library data for minCheckOut maxCheckOut itemType checkOutCount with the assistance from bibNumber and ISBN for joining purposes. Ultimately, these five data elements can give us insight into the following questions:

Is there a relationship between book adoption rate and checkout frequency?
Does publication date play a role on adoption and/or frequency?
Are there any noticeably different trends between book genre types?


Queries
Preliminary

isbn_query.sql all unique isbn values & according bib (used for Amazon API)

Secondary

count_and_type.sql retrieves checkout count & item type (for each bib)
min_max_count.sql retrieves min & max checkout dates (for each bib)

SQL query processing times include" 768sec, 557sec, and accordingly. Joining has reduced optimal record counts from ~108000 to ~3600 to ~3300 to ~1600 final records. After several joins, the final dataset has the following columns:

bib | unique identifier
isbn | unique identifier
publishDate | publish or release date
minCheckOut | first recorded checkout
maxCheckOut | most recent recorded checkout
checkOutCount | number of total checkouts
itemType | book subtype
adoption | media adoption (minCheckOut - publishDate)
age | media 'age' (maxCheckOut - publishDate)
relativeAge | media relative 'age' (maxCheckOut - 'oldest' media publishDate)
fullFrequency | checkout frequency (checkOutCount / (maxCheckOut - publishDate)) *100
partialFrequency | partial checkout frequency (checkOutCount / (maxCheckOut - minCheckOut)) *100
Query Errors

There have been several errors that have reduced the amount of data visualized. This could, in fact, be a positive thing because I am uncertain how processing would have handled upwards of 100000 records. These errors include and may not be limited to: accidental duplication query from Amazon API, improper table join characteristics, accidental type conversion on joins. In addition, as always final data acquisition is dependent on every step of the process. Original web crawling data had a limited number of ISBN records to search Amazon’s API for and ultimately join back.

Analysis
Based on initial observations, it appears that checkout frequency, normalized by media age (checkout count /(last checkout - publication date)) and relative age (checkout count /(last checkout - first checkout)) has not significantly changed over time. This agrees with my hypothesis that book checkout will not decrease (and may increase) with time, even with the introduction of e-media. To further investigate this, I plan on pulling in more variables, including genre types.

I am satisfied with this visualization because immediately the colors cooperate, and don’t distract from the data’s powerful visual distribution. Interactive options including: toggling ‘x’ values between age and relative age, toggling ‘y’ values between full and partial frequency, and toggling color between publication date and book type are both easy to transition between and engaging to cycle between. Additionally, animation effects, such as movement of points when toggled, give a clear sense of order and relationships.

Limitations
As of this writing, the data collection and visualization have been a success. However, due to time constraints and alteration in design foundations, this project will need revisiting. The dataset has several opinionated variables, and further data normalization needs to occur. To do this, I will integrate heavier statistical methods acquired through my parallel Geography 210B assignments. This will include, normalization through ranks, gamma and other distributions. I hope to apply several forms of regression to this dataset, and explore how statistical summaries can be creatively visualized. This element should be applicable to the 3D visualization portion of this course.

MAIN CODE, SKETCHES, IMAGES
The first image below show my sketch of the interpretive scatter plot. Notes about interaction capabilities can be found on the right. The second image shows a user's mouse interaction displaying book id upon hover, grouped by book type. The third image shows differently plotted 'x' and 'y' variables, as well as color coding each data element to their publication date. Below includes a zipped folder containing all running code, .csv files, and .sql queries

Code: Select all

// Layout variables 
int margin = 20;
float plotW = width - margin*2;
float plotH = height - margin*2;

// interaction variables
String x_variable = "age", y_variable = "full_frequency", color_representation = "bookType";
float hoverDistance = plotW;

float max_age = 0, max_relative_age = 0, max_fullFrequency = 0, max_partialFrequency = 0;
float min_age = 10000, min_relative_age = 10000, min_fullFrequency = 10000, min_partialFrequency = 10000;
int min_publishOrder = 1000, max_publishOrder = 0;

float valX = 0, valY = 0, start1_X = 0, stop1_X = 0, start2_X = 0, stop2_X = 0, start1_Y = 0, stop1_Y = 0, start2_Y = 0, stop2_Y = 0;



Table table;
int numRows, numCols;

DataPoint[] dataPoints;

String publishDate,itemType;
int bib, checkOutCount, adoption, age, relative_age, publishOrder;
String minCheckOut, maxCheckOut;
float fullFrequency, partialFrequency;

void setup() {
  size(1440, 900);
  smooth();
  //frameRate(60);
  surface.setResizable(true); // make the screen resizable
  
  table = loadTable("final_cleaned.csv", "header"); // put data into Processing table from csv
  numRows = table.getRowCount(); // get number of data points
  numCols = table.getColumnCount(); // get number of attributes per data point
  
  // create data points and fill array
  dataPoints = new DataPoint[numRows+1]; // initialize data points array length
  int counter = 0;
  for (TableRow row : table.rows()){
    counter +=1;
    createDataPoint(row, counter); 
  }
}

void draw() {
  background(50);
  
  for(int i=1; i<dataPoints.length; i++){
    checkXVariable(i);
    checkYVariable(i);
    // //checkColor(i);
    // map values to x and y axis
    float posX = map(valX, start1_X, stop1_X, start2_X, stop2_X);
    float posY = map(valY, start1_Y, stop1_Y, start2_Y, stop2_Y);
    
    if(mouseX >= posX - hoverDistance && mouseX <= posX + hoverDistance  && mouseY >= posY - hoverDistance && mouseY <= posY + hoverDistance){
      dataPoints[i].displayLabel();
      if(color_representation == "bookType"){
        String bookType = dataPoints[i].itemType;
        dataPoints[i].setColor(i, bookType);
      }
    } else { dataPoints[i].setColor(i, "null"); }
    
    dataPoints[i].setPosition(posX, posY);
    dataPoints[i].display();
  }
  
  drawBoarders();
  drawLabels();
  drawTitles();
  drawAxis();
  drawInformation();
}

void drawBoarders(){
  fill(100);
  rect(0,0,margin,height);
  rect(0,0,width,margin);
  rect(0,height-margin,width,margin);
  rect(width-margin,margin,margin,height);
}

void drawLabels(){
  rect(0,0, margin, height);
  fill(50);
}

void drawTitles(){
  textAlign(LEFT, CENTER);
  textSize(12);
  fill(200);
  text("Book Age (days)", width/2, .5*margin);
  text("Checkout Frequency (occurances)", margin*2, height/2);
}

void drawAxis(){
  float x_axis_distance = (stop2_X - start2_X) / 10;
  float y_axis_distance = (start2_Y - stop2_Y) / 10;
  
  float x_axis_value = (stop1_X - start1_X) / 10;
  float y_axis_value = (stop1_Y - start1_Y) / 10;
  
  // print x axis
  for(int k=1; k<11; k++){
    textAlign(CENTER, CENTER);
    textSize(8);
    fill(200);
    text(k*x_axis_value, k*x_axis_distance, margin*2);
  }
  
  // print y axis
  for(int l=1; l<11; l++){
    textAlign(CENTER, CENTER);
    textSize(8);
    fill(200);
    text(l*y_axis_value, margin+20, height - (l*y_axis_distance));
  }
  
}

void drawInformation(){
  textSize(20);
  rotate(.5);
  translate(-100, -1*height/1.5 + 50);
  textAlign(CENTER, CENTER);
  text("Are Books Still Prevelant in an e-Media World?", 5*(width/6), margin*6);
  textSize(12);
  
  if(x_variable.equals("age")){ text("X-Variable: Age", 5*(width/6), margin*7); }
  else {text("X_Variable: Relative Age", 5*(width/6), margin*7);}
  
  if(y_variable.equals("full_frequency")){ text("Y-Variable: Checkout Frequency", 5*(width/6), margin*8); }
  else {text("Y_Variable: Relative Checkout Frequency", 5*(width/6), margin*8);}
  
  if(color_representation.equals("bookType")){ text("Color Range: Book Type", 5*(width/6), margin*9); }
  else{ text("Color Range: Publication Date", 5*(width/6), margin*9); }
}
Attachments
MAT259_Proj1b_HERVEY.zip
Zipped project w/out preprocessed .csv
(73.81 KiB) Downloaded 191 times
Screen Shot 2016-01-28 at 10.22.55 AM.png
sketch
Screen Shot 2016-01-28 at 10.22.35 AM.png
mouse interaction
sketch.jpg
changed data representation

xindi
Posts: 8
Joined: Wed Jan 06, 2016 1:39 pm

PROJECT 1 Xindi Kang

Post by xindi » Fri Feb 05, 2016 11:33 am

Aurora Activity --- Iceland

[img]
final colored.png
Final Effect (with color scheme)
[/img]

This projects focuses on finding the trend of the popularity of Iceland (as a country) and Icelandic (as a language) in comparison to the Aurora Borealis (polar light) activity at the North Pole of the earth.

As we all know, Iceland is a popular travel destination for tourists who are interested in exotic natural landscapes, such as volcanos and glaciers, and aurora, the northern light.

Therefore, photographers, artists, Geophysics scholars and people from all kinds of industries and academic fields travel to Iceland.

In this research, I am using MySQL to extract data from the Seattle Public Library database to look for trends in the popularity of the topic "Iceland" in Seattle. Further more, I am also looking for the influence of the Aurora Borealis activity on this trend.

The MySQL query for Iceland is shown below:

Code: Select all

SELECT 
    FLOOR(deweyClass) AS Dewey,
    DATE_FORMAT(checkOut, '%Y-%m') AS checkOutMonth,
    COUNT(checkout) AS CheckoutCount
    ,TITLE
    ,bibNumber
FROM
    spl3._rawXmlDataCheckOuts
WHERE
    title LIKE '%iceland%'
        OR title LIKE '%icelandic%'
GROUP BY checkOutMonth#FLOOR(deweyClass)
ORDER BY SUM(CASE
    WHEN
        (YEAR(checkOut) >= 2006
            OR YEAR(checkOut) <= 2016)
    THEN
        1
    ELSE 0
END) DESC

The MySQL query for Aurora Borealis is shown below:

Code: Select all

SELECT 
    FLOOR(deweyClass) AS Dewey,
    DATE_FORMAT(checkOut, '%Y-%m') AS checkOutMonth,
    COUNT(checkout) AS CheckoutCount
    ,TITLE
    ,bibNumber
FROM
    spl3._rawXmlDataCheckOuts
WHERE
    title LIKE '%aurora%'
        OR title LIKE '%northern light%'
GROUP BY checkOutMonth#FLOOR(deweyClass)
ORDER BY SUM(CASE
    WHEN
        (YEAR(checkOut) >= 2006
            OR YEAR(checkOut) <= 2016)
    THEN
        1
    ELSE 0
END) DESC

the results of these queries are uploaded in CSV file.

The visualized data is shown below:
Initial Visualization
[img]
initial.png
I tried to make it look as similar as an Aurora Borealis.
[/img]

Adding the lable:
[img]
lable.png
the lables are the dates of each data. Together in an array the dates become a timeline.
[/img]

Final Effect:

The final effect includes multiple animations and interactions:

1. the background has a "starry sky" effect, white dots shine in the background to simulate the blinking light from the stars. This effect is purely for aesthetic purposes.

2. the yellow dot works as a flashlight, when user move their mouse to point to the array of white dots the "light" shines on the area above and reveals the date of the data. (I'm still working on making the dates more visible and easy to read)

3. when key "c" is pressed, a color scheme is applied to the Aurora shaped data to show the influence of the Aurora activity on the main trend of the keyword "Iceland". (right now I'm still using data form SPL for Aurora Activity, which is not a really accurate report of the actually Aurora Activity. I will try to obtain real life scientific data online or through personal connection, in order to provide a more accurate and realistic data visualizaiton)

4. There's also a shining effect of for the aurora shaped data. when the user press "bar", the Aurora shape will shine like actual aurora in the sky. this effect is in both color mode. Unfortunately I can not capture that since it requires a different method to take a screenshot and i am a little too lazy to try to find a new software to take screen shots. Sorry ;\

[img]
final green.png
Final Effect (Green, without color scheme)
[/img]

[img]
final colored.png
Final Effect (with color scheme)
[/img]


Below is the processing code:

Main file

Code: Select all

float constantMargin = 80;
float monthWidth, barWidth;
float[] barHeight, sparkHeight;
//float r = random(255);
//float g = random(255);
//float b = random(255);
//float starLocation = random(280);

Table table;
int numRows, numColumns;
int [][] dataMatrix;
int glow = 3;

int randX= int(random(1280));
int randY= int(random(720));
int randStarSize = int(random(15));

PFont alien;
//PFont herald;

boolean colorScheme = false; 


void setup() {
  size(1280, 720);
  smooth();
  surface.setResizable(true);
  frameRate(1000);
  background(0, 0, 0, 30);

  table = new Table();
  table = loadTable("IceByMonth.csv", "header");

  alien = createFont("AlienLeague.ttf", 10);
  //herald = createFont("HeraldRegular.ttf", 23);

  numRows = table.getRowCount();
  numColumns = table.getColumnCount();

  dataMatrix = new int[numRows][numColumns];

  for (int i = 0; i<numRows; i++) {
    for (int j = 0; j<numColumns; j++) {
      dataMatrix[i][j] = table.getInt(i, j);
      println("data value:"+ dataMatrix[i][j]);
    }
  }
  monthWidth = (width-2*constantMargin)/109;
  barWidth = monthWidth*1/4;
  barHeight = new float[numRows];                 //why 'new' everytime?
  //sparkHeight = new float[numRows];
  println(numRows + "Rows" );
  //println("R:"+r);
  //println("G:"+g);
  //println("B:"+b);
}

void draw() {


  aurora();
  scope();
  lables();
  starFilter();
  int randX= int(random(1280));
  int randY= int(random(720));
  star(randX, randY);
  title();
  //if (keyPressed) {
  //  if (key == 'c' || key == 'C') {
  //    ColorScheme();
  //  }
  //}
  if(colorScheme) ColorScheme();
  //true shortcut:(coScheme)
  //false shortcut:(!colorScheme)
  //only used in if statment
}

void title() {

  textAlign(CENTER, CENTER);
  textSize(20);
  fill(255);
  textFont(alien, 40);
  text("Aurora Activity --- Iceland", width/2, constantMargin/2+45);
  fill(255, 255, 0, 70);
  text("Aurora Activity --- Iceland", width/2+2, constantMargin/2+45);

  //fill(255,255,0,30);
  //rect(width/2, constantMargin/2,400,30);
  //fill(255,255,0,20);
  //rect(width/2, constantMargin/2,400+glow*6,30+glow*2);
  //fill(255,255,0,10);
  //rect(width/2, constantMargin/2,400+glow*15,30+glow*5);
  //fill(255,255,0,5);
  //rect(width/2, constantMargin/2,400+glow*24,30+glow*8);
}
//
void aurora() {
  for (int i=0; i<numRows; i++) {
    //bars
    barHeight[i] = map(dataMatrix[i][1], 8, 135, 0, height/2);
    fill(36.644108, 241.41037, 141.97496);

    stroke(0);
    strokeWeight(1);
    if (keyPressed) {
        if(key == ' '){
          stroke(0,0,0,random(255));
        }
    }
    //fill(random(255),random(255),random(255));

    ellipse(constantMargin+monthWidth/2-barWidth/2+monthWidth*i, height*3/4-barHeight[i]-20, 
      barWidth+glow*3, barHeight[i]);  
    ellipse(constantMargin+monthWidth/2-barWidth/2+monthWidth*i, height*3/4-barHeight[i]-20, 
      barWidth+glow*2, barHeight[i]);  
    ellipse(constantMargin+monthWidth/2-barWidth/2+monthWidth*i, height*3/4-barHeight[i]-20, 
      barWidth+glow, barHeight[i]);  
    ellipse(constantMargin+monthWidth/2-barWidth/2+monthWidth*i, height*3/4-barHeight[i]-20, 
      barWidth, barHeight[i]);
  }
}

void lables() {
  //lables
  for (int i=0; i<numRows; i++) {
    textAlign(CENTER);
    textSize(10);
    fill(0);
    pushMatrix();

    //here i tried using fixed value for translate, didn't work, why?
    //tilt labes
    translate(constantMargin+monthWidth/2+monthWidth*i, height*3/4);
    //translate(2, 2);
    rotate(PI/2.5);
    translate(-(constantMargin+monthWidth/2+monthWidth*i), -(height*3/4));
    //translate(-2, -2);
    textFont(alien);
    text(dataMatrix[i][0], constantMargin+monthWidth/2+monthWidth*i, height*3/4);
    popMatrix(); 

    fill(255);
    text("o", constantMargin+monthWidth/2+monthWidth*i, height*4/5);
  }
}

void scope() {

  fill(200, 200, 0, 70);
  stroke(200, 200, 0, 70);
  ellipse(mouseX-10, mouseY-20, 60, 60);
  fill(200, 200, 0, 50);
  stroke(200, 200, 0, 50);
  ellipse(mouseX-10, mouseY-20, 70, 70);
  fill(200, 200, 0, 30);
  stroke(200, 200, 0, 30);
  ellipse(mouseX-10, mouseY-20, 80, 80);
  fill(200, 200, 0, 10);
  stroke(200, 200, 0, 10);
  ellipse(mouseX-10, mouseY-20, 90, 90);
}

void starFilter() {
  fill(0, 0, 0, 30);
  stroke(0, 0, 0, 30);
  strokeWeight(1);
  rect(0, 0, 1280, 720);
}

void star(int randX, int randY)
{

  stroke(255, 255, 255, random(255));
  strokeWeight(randStarSize);
  point(randX, randY);
}
Color Scheme

Code: Select all

String[] colorPath = {"bwReBlend129b.jpg", "bwReBlend129d.jpg", "bwReBlend129h.jpg", "Colormap4.jpg"};

PImage colorBar;

int colorMode = 0;

void ColorScheme() {
  colorBar = loadImage(colorPath[colorMode]);

  for (int i=0; i<numRows; i++) {
    stroke(0);
    if (keyPressed) {
        if(key == ' '){
          stroke(0,0,0,random(255));
        }
    }

        strokeWeight(1);
        //float greyScale = map(dataMatrix[i][j], minValue, maxValue, 255, 0);
        //fill(greyScale);
        color c = colorBar.get((int)map(dataMatrix[i][1], 8, 135, 0, colorBar.width-1), colorBar.height/2);
        fill(c);

        ellipse(constantMargin+monthWidth/2-barWidth/2+monthWidth*i, height*3/4-barHeight[i]-20, 
          barWidth+glow*3, barHeight[i]);  
        ellipse(constantMargin+monthWidth/2-barWidth/2+monthWidth*i, height*3/4-barHeight[i]-20, 
          barWidth+glow*2, barHeight[i]);  
        ellipse(constantMargin+monthWidth/2-barWidth/2+monthWidth*i, height*3/4-barHeight[i]-20, 
          barWidth+glow, barHeight[i]);  
        ellipse(constantMargin+monthWidth/2-barWidth/2+monthWidth*i, height*3/4-barHeight[i]-20, 
          barWidth, barHeight[i]);
        //text(int(dataMatrix[i][j]), constantHorizontalMargin + cellWidth*j, constantVerticalMargin 
        //+ cellHeight*i);
        
        //if (keyPressed) {
        //  if (key == ' ') {
        //    stroke(0, 0, 0, random(255));
        //  }
        //}
      //}
    //}
  }
}
Interaction

Code: Select all

void keyPressed(){
    if (key == 'c' || key == 'C') {
       if(!colorScheme){
       colorScheme = true;
       }
       else if(colorScheme){
       colorScheme = false;
       }
    }
}
Attachments
IceByMonth.csv
data for Iceland
(1.1 KiB) Downloaded 194 times
Aurora.csv
data for Aurora Activity
(6.39 KiB) Downloaded 197 times

d.aleman.24.da
Posts: 4
Joined: Wed Jan 06, 2016 1:40 pm

Re: PROJ 1: VISUALIZING the CULTURE ANALYSIS in 2D (PROCESSI

Post by d.aleman.24.da » Tue Feb 09, 2016 11:18 am

Using a similar Query from my previous project, I instead added more specific Dewey Classes that are still related to technology. The idea of my project is to visualize the relevance of technology by counting the amount of check outs per year in accordance to a specific Dewey Classes. The dewey numbers are displayed in the visual located underneath the matrix. I used the idea of color and size to show data quantity. The dewey numbers are as followed:

000 to 100 (Computer Science)
600 to 610 (Technology)
620 to 630 (Engineering)
740 to 741 (Graphic Arts)
776 to 777 (Computer/Digital Art)

Processing Code Below:

Code: Select all

float constantHorizontalMargin = 160;
float constantVerticalMargin = 100;

// 2D matrix table related variables
Table table;
int numRows, numColumns;
float[][] dataMatrix;
float maxValue, minValue;
int dewey;
float DeweyWidth = 11;
PFont myFont;


// width and height for each cell
float cellWidth, cellHeight;

// text labels
int startYear = 2006;

boolean normalization = false;
boolean colorApplied = false; 
boolean reorder = false; 

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. 
   String[] fontList = PFont.list();
  printArray(fontList);
  myFont = createFont("Consola.ttf",5);
  smooth();
  // Make the screen resizable.                     
  surface.setResizable(true);
  
  //Put data into Processing table from csv
  table = loadTable("techdata.csv", "header");
  numRows = table.getRowCount(); 
  numColumns = table.getColumnCount();
  dataMatrix = new float[numColumns-1][numRows];
  println("Rows: " + numRows + " Columns: " + numColumns);
  

  for (int i=0; i<numColumns-1; i++) {
    for (int j=0; j<numRows; j++) {
      dataMatrix[i][j] = table.getFloat(j, i+1);
    }
  }

  maxValue = max2D(dataMatrix);
  minValue = min2D(dataMatrix);

  println("Max Value is " + maxValue);
  println("Min Value is " + minValue);
}

void draw() {

  //refresh the canvas every frame
  background(50,50,50);
 // font = loadFont("Consola.ttf");

  //modifying the cell size according to window size
  cellWidth = (width - constantHorizontalMargin*2)/numRows;
  cellHeight = (height - constantVerticalMargin*2)/(numColumns-1);

  //Draw 2D Matrix
  for (int i=0; i<numColumns-1; i++) {
    for (int j=0; j<numRows; j++) {
      //stroke(255);
      noStroke();
      //strokeWeight(1);
      float greyScale = map(dataMatrix[i][j], minValue, maxValue, 255, 0);
      fill(greyScale);
      //color c = colorBar.get((int)map(dataMatrix[i][j], minValue, maxValue, 2, colorBar.width-2), colorBar.height/2);
      ellipseMode(CORNER);
      ellipse(constantHorizontalMargin + cellWidth*j, constantVerticalMargin 
        + cellHeight*i, dataMatrix[i][j], dataMatrix[i][j]*4);
    }
  }
  
  //hover 2D Matrix
  for (int i=0; i <=numRows-1; i++){
  noFill();
  noStroke();
  rectMode(CORNER);
  rect(constantHorizontalMargin+cellWidth*i+2,
   height*.89-12, 8, 18);
  }

  
  //use println(mouseX, mouseY); to figure out the coordinates to choose where i can hover...
  
  //look into case language for hovering functions...processing.org

  //Draw vertical labels
  for (int i=0; i<numColumns-1; i++) {
    textFont(myFont);
    textAlign(RIGHT, CENTER);
    fill(57,255,20);
    textSize(15);
    text(startYear+i, constantHorizontalMargin-8, 
      constantVerticalMargin + cellHeight/2 + cellHeight*i-12);
  }
  
  //draw horizontal labels
  for (int j = 0; j<numRows; j++){
      float x = constantHorizontalMargin+5+cellWidth*j;
      float y = height*.88;
     textFont(myFont);
     textAlign(LEFT, CENTER);
     fill(57,255,20);
     textSize(12);
     pushMatrix();
     translate(x,
     y);
     rotate(3*PI/2);
     translate(-(x), -(y));
     text(Dewey[j], x, y);
   
     //constantHorizontalMargin + cellWidth/2 + cellWidth*j 
      //constantHorizontalMargin + cellWidth/2 + cellWidth*j
      //height*.90 
     popMatrix();
  }

  //Draw Title
  textFont(myFont);
  textAlign(CENTER, CENTER);
  textSize(30);
  text("technology's_relevance_through_the_years", width/2, constantVerticalMargin/2);
  
  //Draw Horizontal Title
  textFont(myFont);
  textAlign(CENTER, CENTER);
  textSize(15);
  text("dewey_classification_numbers", width/2, height*.95);
  
  //Draw vertical title
  textFont(myFont);
  textAlign(CENTER, CENTER);
  textSize(15);
  pushMatrix();
  translate(constantHorizontalMargin/2, 
      height/2);
  rotate(3*PI/2);
  translate(-(constantHorizontalMargin/2),-(height/2));
  text("year", constantHorizontalMargin/2, 
      height/2);
  popMatrix();
  
  if (normalization == true) normalization();
  else if(normalization == false) resetData(); 
  
  if (reorder == true) reorder();
  
  if(colorApplied == true) colorScheme();
  
  println(mouseX,mouseY);
  
  //650 - 630 for Y same for all coordinates
  // 1-8 for X value start with 162-170
  
  
}
Query Code Below:

Code: Select all

SELECT 
    deweyClass AS Dewey,
    SUM(CASE
        WHEN YEAR(checkOut) = 2006 THEN 1
        ELSE 0
    END) AS '2006',
    SUM(CASE
        WHEN YEAR(checkOut) = 2007 THEN 1
        ELSE 0
    END) AS '2007',
    SUM(CASE
        WHEN YEAR(checkOut) = 2008 THEN 1
        ELSE 0
    END) AS '2008',
    SUM(CASE
        WHEN YEAR(checkOut) = 2009 THEN 1
        ELSE 0
    END) AS '2009',
    SUM(CASE
        WHEN YEAR(checkOut) = 2010 THEN 1
        ELSE 0
    END) AS '2010',
    SUM(CASE
        WHEN YEAR(checkOut) = 2011 THEN 1
        ELSE 0
    END) AS '2011',
    SUM(CASE
        WHEN YEAR(checkOut) = 2012 THEN 1
        ELSE 0
    END) AS '2012',
    SUM(CASE
        WHEN YEAR(checkOut) = 2013 THEN 1
        ELSE 0
    END) AS '2013',
    SUM(CASE
        WHEN YEAR(checkOut) = 2014 THEN 1
        ELSE 0
    END) AS '2014'
FROM
    spl3._rawXmlDataCheckOuts
WHERE
    (deweyClass > 000 AND deweyClass < 100)
        OR (deweyClass >= 600 AND deweyClass < 610)
        OR (deweyClass >= 620 AND deweyClass < 630)
        OR (deweyClass >= 740 AND deweyClass < 741)
        OR (deweyClass >= 776 AND deweyClass < 777)
GROUP BY deweyClass
ORDER BY SUM(CASE
    WHEN
        (YEAR(checkOut) >= 2006
            OR YEAR(checkOut) <= 2014)
    THEN
        1
    ELSE 0
END) DESC
LIMIT 100
Here are some screenshots of the canvas:

[img]
Screen Shot 2016-02-09 at 11.14.13 AM.png
[/img]
Grey Scale Visual

[img]
Screen Shot 2016-02-09 at 11.14.30 AM.png
[/img]
MultiColor Visual

Additional Thoughts:

In a later version I hope to add a hovering over mouse function. When the mouse is over a specific dewey number, the affiliated Dewey Class name appears for an easier comprehension.
Attachments
Matrix2D_Personal.zip
(305.34 KiB) Downloaded 173 times

Post Reply