Harry Potter Books vs Movie Checkouts
This visualization makes a comparison over the number of checkouts per month through time on the Harry Potter series. Surprisingly, there were checkouts in the 1970s even though the first book was not published until 1997. A Google search shows that 1970 is an important year in the Harry Potter Series Timeline, such as the First Wizarding War against Lord Voldemort, which could mean an abnormality in the database or fans having fun. Furthermore, there are no checkouts in the 1990s; the first checkout occurring in 2004. This was found through initial queries to the database for single books/movies which resulted in various files that were condensed into one file through a Python script.
Query Times:
movie 1 = 4.571 / 85.180
movie 2 = 2.041 / 55.817
movie 3 = 0.990 / 55.873
movie 4 = 1.722 / 54.279
movie 5 = 10.379 / 49.689
movie 6 = 25.976 / 32.707
movie 7 = 39.783 / 19.633
movie 8 = 45.162 / 13.898
book 1 = 8.434 / 73.689
book 2 = 6.752 / 70.252
book 3 = 7.034 / 70.564
book 4 = 6.105 / 73.784
book 5 = 3.755 / 76.823
book 6 = 1.472 / 73.619
book 7 = 13.705 / 64.365
The y-axis has the Harry Potter Book or Movie, while the x has the months ordered for each year. The number of checkouts for that month gives each cell its transparency value. Red is affected by the numerical value of the month, green by if it is a book or movie, and blue by the year.
The query in sql that does something similar (excluding the 1970s because it's impossible) to what the python script did is below, however, it does not run all together at once because the query takes more than 600 seconds.
Query Times: 208.733 ; 186.213 ; 188.482 ; 189.655 ; 185.660 ; 183.450 ; 187.194 ; 188.523 ; 190.531 ; 188.359 ; 188.204 ; 186.845 ; 186.194 ; 185.098 ; 188.341
Combined Query Time: 2831.482
Code: Select all
SELECT
YEAR(cout),
MONTH(cout),
SUM(CASE
WHEN
(title LIKE '%harry potter%'
AND title LIKE '%sorcerers stone%'
AND title NOT LIKE '%soundtrack%'
AND itemtype LIKE '%bk%')
THEN
1
ELSE 0
END) AS Book1,
SUM(CASE
WHEN
(title LIKE '%harry potter%'
AND title LIKE '%sorcerers stone%'
AND title NOT LIKE '%soundtrack%'
AND (itemtype LIKE '%dvd%'
OR itemtype LIKE '%vhs%'))
THEN
1
ELSE 0
END) AS Movie1,
SUM(CASE
WHEN
(title LIKE '%harry potter%'
AND title LIKE '%chamber of secrets%'
AND title NOT LIKE '%soundtrack%'
AND itemtype LIKE '%bk%')
THEN
1
ELSE 0
END) AS Book2,
SUM(CASE
WHEN
(title LIKE '%harry potter%'
AND title LIKE '%chamber of secrets%'
AND title NOT LIKE '%soundtrack%'
AND (itemtype LIKE '%dvd%'
OR itemtype LIKE '%vhs%'))
THEN
1
ELSE 0
END) AS Movie2,
SUM(CASE
WHEN
(title LIKE '%harry potter%'
AND title LIKE '%prisoner of azkaban%'
AND title NOT LIKE '%soundtrack%'
AND itemtype LIKE '%bk%')
THEN
1
ELSE 0
END) AS Book3,
SUM(CASE
WHEN
(title LIKE '%harry potter%'
AND title LIKE '%prisoner of azkaban%'
AND title NOT LIKE '%soundtrack%'
AND (itemtype LIKE '%dvd%'
OR itemtype LIKE '%vhs%'))
THEN
1
ELSE 0
END) AS Movie3,
SUM(CASE
WHEN
(title LIKE '%harry potter%'
AND title LIKE '%goblet of fire%'
AND title NOT LIKE '%soundtrack%'
AND itemtype LIKE '%bk%')
THEN
1
ELSE 0
END) AS Book4,
SUM(CASE
WHEN
(title LIKE '%harry potter%'
AND title LIKE '%goblet of fire%'
AND title NOT LIKE '%soundtrack%'
AND (itemtype LIKE '%dvd%'
OR itemtype LIKE '%vhs%'))
THEN
1
ELSE 0
END) AS Movie4,
SUM(CASE
WHEN
(title LIKE '%harry potter%'
AND title LIKE '%order of the phoenix%'
AND title NOT LIKE '%soundtrack%'
AND itemtype LIKE '%bk%')
THEN
1
ELSE 0
END) AS Book5,
SUM(CASE
WHEN
(title LIKE '%harry potter%'
AND title LIKE '%order of the phoenix%'
AND title NOT LIKE '%soundtrack%'
AND (itemtype LIKE '%dvd%'
OR itemtype LIKE '%vhs%'))
THEN
1
ELSE 0
END) AS Movie5,
SUM(CASE
WHEN
(title LIKE '%harry potter%'
AND title LIKE '%half%' AND title LIKE '%prince%'
AND title NOT LIKE '%soundtrack%'
AND itemtype LIKE '%bk%')
THEN
1
ELSE 0
END) AS Book6,
SUM(CASE
WHEN
(title LIKE '%harry potter%'
AND title LIKE '%half%' AND title LIKE '%prince%'
AND title NOT LIKE '%soundtrack%'
AND (itemtype LIKE '%dvd%'
OR itemtype LIKE '%vhs%'))
THEN
1
ELSE 0
END) AS Movie6,
SUM(CASE
WHEN
(title LIKE '%harry potter%'
AND title LIKE '%deathly hallows%'
AND title NOT LIKE '%soundtrack%'
AND itemtype LIKE '%bk%')
THEN
1
ELSE 0
END) AS Book7,
SUM(CASE
WHEN
(title LIKE '%harry potter%'
AND title LIKE '%deathly hallows%'
AND title LIKE '%part 1%'
AND title NOT LIKE '%soundtrack%'
AND (itemtype LIKE '%dvd%'
OR itemtype LIKE '%vhs%'))
THEN
1
ELSE 0
END) AS Movie7,
SUM(CASE
WHEN
(title LIKE '%harry potter%'
AND title LIKE '%deathly hallows%'
AND title LIKE '%part 2%'
AND title NOT LIKE '%soundtrack%'
AND (itemtype LIKE '%dvd%'
OR itemtype LIKE '%vhs%'))
THEN
1
ELSE 0
END) AS Movie8
FROM
spl2.inraw
WHERE
DATE(cout) >= '2004-1-1'
GROUP BY YEAR(cout) , MONTH(cout)
Result 1
- First Result
Result 2
- Second Result
The Processing code for the first and second results is below, commenting out the fun fonts and signature.
Code: Select all
int [][] dataMatrix = null;
int numRows = 15; //7 books and 8 movies
int numColumns = 576; //1970, 2004-2014 (12) are the included years * 12 months * 4 data entries
PFont funFont1 = createFont("Courier", 14, true);
PFont funFont2 = createFont("Chalkboard", 24, true);
PFont funFont3 = createFont("Apple Chancery", 24, true);
PFont font1 = createFont("Arial", 12, true); //:(
PFont font2 = createFont("Helvetica", 18, true); //:(
Table myTable;
int cellWidth = 9;
int cellHeight = 18;
int rowMargin = 100;
int colMargin = 100;
void setup()
{
size(1490, 560);
background(255);
smooth();
println("SetUp Done!");
myTable = loadTable("HP_Summary.csv", "header");
println("Rows: " + myTable.getRowCount());
println("Columns: " + myTable.getColumnCount());
println(144 * 15);
dataMatrix = new int[numRows][numColumns];
int k = 0;
int m = 0;
println("---------------------BEGIN--------------------");
for(int i = 0; i < myTable.getRowCount(); i++)
{
for(int j = 1; j < myTable.getColumnCount(); j++)
{
dataMatrix[k][m] = myTable.getInt(i,j);
m++;
//print("(K,M: " + k + "," + m + "), (i,j: " + i + "," + j + "), " + myTable.getInt(i,j) + ", ");
//println(k + ", " + m);
if(m == 576)
{
m = 0;
k++;
}
}
}
}
void draw()
{
background(255);
stroke(0, 0, 0, 100);
strokeWeight(0.2);
//Background with Transparency
fill(255,0,0,10);
rect(0, 0, width, height);
//Background for Cells
fill(255);
rect(rowMargin, colMargin, cellWidth * (numColumns/4), cellHeight * numRows);
//Outline Border for Cells
noFill();
rect(rowMargin, colMargin, cellWidth * (numColumns/4), cellHeight * numRows);
//Title
textAlign(CENTER, CENTER);
//textFont(funFont2);
textFont(font2);
fill(101, 55, 253);
text("Harry Potter Book and Movie Checkouts", width / 2, height * 0.14);
//Data Cells
//colorMode(HSB); //looks ugly =[
for(int i = 0, k = 0; i < numColumns; i+= 4, k++)
{
for(int j = 0; j < numRows; j++)
{
stroke(255, 153, 255, 100);
strokeWeight(0.014);
fill(((float)dataMatrix[j][i + 1] * 255.0) / 12.0, //month
(((float)dataMatrix[j][i] + 1.0) * 14.0) / 2.0, //book or movie
dataMatrix[j][i + 2] % 255, //year
((float)dataMatrix[j][i + 3]/494.0) * 255.0); //checkouts
rect(rowMargin + k * cellWidth, colMargin + j * cellHeight, cellWidth, cellHeight);
}
}
//Line Annual Separators
for(int i = 0, k = 0; i < numColumns; i += 4, k++)
{
stroke(101, 55, 253, 100);
strokeWeight(1);
if(k == 0)
line(rowMargin + (k * cellWidth),
colMargin + numRows * cellHeight,
rowMargin + (k * cellWidth),
colMargin + numRows * cellHeight + 15);
if((k + 1) % 12 == 0)
line(rowMargin + ( (k + 1) * cellWidth),
colMargin + numRows * cellHeight,
rowMargin + ( (k + 1) * cellWidth),
colMargin + numRows * cellHeight + 15);
}
//Row Labels
String[] labels = {"Book 1", "Film 1", "Book 2", "Film 2", "Book 3", "Film 3",
"Book 4", "Film 4", "Book 5", "Film 5", "Book 6", "Film 6",
"Book 7", "Film 7", "Film 8"};
textAlign(CENTER, CENTER);
//textFont(funFont1);
textFont(font1);
fill(101, 55, 253, 200);
//fill(128, 3, 253);
for(int i = 0; i < 1500; i += 100)
text(labels[i/100],
rowMargin * 0.7,
colMargin + cellHeight / 2 + i / 100 * cellHeight);
//Column Labels
textAlign(RIGHT, CENTER);
String[] yearL = {"1970", "2004", "2005", "2006", "2007", "2008", "2009", "2010", "2011",
"2012", "2013", "2014"};
for(int i = 1; i <= 144; i++)
{
if(i % 12 == 0 )
{
text(yearL[i / 12 - 1],
rowMargin - (cellWidth / 2) + (i * cellWidth) - (cellWidth * 3.5),
height - 1.8 * colMargin);
}
}
//Signature
/*
textAlign(CENTER, CENTER);
textFont(funFont3);
fill(101, 55, 253, 214);
text("by Nataly Moreno", width - (width * 0.15), height - (height * 0.15) );
*/
}
Analysis
From the image it can be seen that the Harry Potter movies were checked out with more frequency after the third movie and the book that was checked out the most frequently was the last book in 2007. However, the movie that was checked out the most was the sixth. The colors also show that frequencies are more solid (less transparent) during the initial period that they begin to appear in the library; the colors fade out as the years move on. Lastly, the database abnormality of the 1970s still remains somewhat of a mystery and the only boxes filled in for that year are in the first column, January. It is also interesting to note that the sixth book did not have many checkouts in 1970 and the last movie appears to have none.
Revision
Here are new results using HSB.
- Revised_0
- Revised_1
- Revised_2
- Revised_3
- Revised_4
- Revised_5
Here is the new processing code for the new results using the last .csv generated excluding 2004 and 2014. To get the results above just change "colorTheme" to a value from 0-5.
Code: Select all
///////////////////////////////////////////////////////////////////////////////////////////
// MAT 259 - Assignment 1 Revision
// by Nataly Moreno
///////////////////////////////////////////////////////////////////////////////////////////
int [][] dataMatrix = null;
int numRows = 15; //7 books and 8 movies
int numColumns = 108; //2004-2013: 9 years * 12 months
//Fun Fonts for me :D
PFont funFont1 = createFont("Courier", 14, true);
PFont funFont2 = createFont("Chalkboard", 24, true);
PFont funFont3 = createFont("Apple Chancery", 24, true);
//Other Fonts
PFont font1 = createFont("Arial", 12, true);
PFont font2 = createFont("Helvetica", 18, true);
Table myTable;
int maxCheckOuts = -1;
int cellWidth = 9;
int cellHeight = 18;
int rowMargin = 100;
int colMargin = 100;
int barPosX = 675;
int barPosY = 450;
int barLength = 400;
int barHeight = 30;
int barColor = -1;
int colorTheme = 1;
int colorMode[] = {HSB, HSB, HSB, HSB, HSB, HSB};
color backgroundColor[] = {color(0),
color(0),
color(0),
color(0),
color(114, 114, 114, 100),
color(0)
};
color cellBackground[] = {color(128),
color(128),
color(128),
color(128),
color(128),
color(128)
};
color cellOutline[] = {color(255, 153, 255, 100), //scheme 0
color(180, 153, 255, 100), //scheme 1
color(180, 150, 255, 100), //scheme 2
color(180, 150, 255, 100),
color(180, 150, 255, 100),
color(180, 150, 255, 100)
};
color dataCells[] = {color(180, 155, 255), //scheme 0
color(180, 150, 255, 100), //scheme 1
color(255, 150, 50), //scheme 2
color(255, 150, 50),
color(170),
color(178, 255, 102)
};
color lineSeparator[] = {color(30, 144, 255, 100), //scheme 0
color(100, 255, 100, 100), //scheme 1
color(255, 150, 50, 100), //scheme 2
color(255, 150, 50, 100),
color(255, 150, 50, 100),
color(255, 235, 0, 100)
};
color colorBarOutline[] = {color(30, 144, 255,200), //scheme 0
color(180, 155, 255, 100), //scheme 1
color(255, 150, 50, 100), //scheme 2
color(255, 150, 50, 100),
color(255, 150, 50, 100),
color(255, 150, 50, 100)
};
color colorLabel[] = {color(30, 144, 255), //scheme 0
color(100, 255, 100, 100), //scheme 1
color(255, 150, 50), //scheme 2
color(255, 150, 50),
color(0),
color(255, 220, 0)
};
color outlineBorder[] = {color(30, 144, 255, 130), //scheme 0
color(200, 255, 100, 100), //scheme 1
color(255, 150, 50, 130), //scheme 2
color(255, 150, 50, 130),
color(0),
color(255, 255, 0, 130)
};
int barDiv[] = {100, 100, 100, 100, 100, 100};
///////////////////////////////////////////////////////////////////////////////////////////
// Helper Functions
///////////////////////////////////////////////////////////////////////////////////////////
void printDataMatrix()
{
for(int i = 0; i < numRows; i++)
{
for(int j = 0; j < numColumns; j++)
{
print(dataMatrix[i][j] + " ");
}
println("\n");
}
}
void cellColor(int i, int j)
{
switch(colorTheme)
{
case 0:
fill(red(dataCells[colorTheme]), green(dataCells[colorTheme]), dataMatrix[j][i]);
break;
case 1:
if(dataMatrix[j][i] != 0)
{
fill( red(dataCells[colorTheme]) * dataMatrix[j][i] / maxCheckOuts,
green(dataCells[colorTheme]),
blue(dataCells[colorTheme]) * dataMatrix[j][i] / maxCheckOuts);
}
else
{
fill(0, 0, 0);
}
break;
case 2:
if(dataMatrix[j][i] != 0)
{
fill( hue(dataCells[colorTheme]) * dataMatrix[j][i] / maxCheckOuts,
saturation(dataCells[colorTheme]),
brightness(dataCells[colorTheme]) * dataMatrix[j][i] / maxCheckOuts);
}
else
{
fill(0, 0, 0);
}
break;
case 3:
if(dataMatrix[j][i] != 0)
{
fill( red(dataCells[colorTheme]) * dataMatrix[j][i] / maxCheckOuts,
green(dataCells[colorTheme]),
blue(dataCells[colorTheme]) * 4);
}
else
fill(0);
break;
case 4:
if(dataMatrix[j][i] != 0)
{
fill(red(dataCells[colorTheme]) * dataMatrix[j][i] / maxCheckOuts);
}
else
fill(0);
break;
case 5:
if((j < 13) && (j % 2 == 0))
{
if(dataMatrix[j][i] != 0)
{
fill( hue(dataCells[colorTheme]) - 30,
saturation(dataCells[colorTheme]) + 70,
brightness(dataCells[colorTheme]) * dataMatrix[j][i] / maxCheckOuts);
}
else
fill(0);
}
else
{
if(dataMatrix[j][i] != 0)
{
fill( hue(dataCells[colorTheme]),
saturation(dataCells[colorTheme]) + 30,
brightness(dataCells[colorTheme]) * dataMatrix[j][i] / maxCheckOuts);
}
else
fill(0);
}
break;
default:
break;
}
}
void colorBarFill(int i)
{
switch(colorTheme)
{
case 0:
fill( red(dataCells[colorTheme]), green(dataCells[colorTheme]),
blue(dataCells[colorTheme]) * i / barDiv[colorTheme]);
break;
case 1:
fill( red(dataCells[colorTheme]) * i / barDiv[colorTheme],
green(dataCells[colorTheme]),
blue(dataCells[colorTheme]) * i / barDiv[colorTheme]);
break;
case 2:
fill( hue(dataCells[colorTheme]) * i / barDiv[colorTheme],
saturation(dataCells[colorTheme]),
brightness(dataCells[colorTheme]) * i / barDiv[colorTheme]);
break;
case 3:
fill( red(dataCells[colorTheme]) * i / barDiv[colorTheme],
green(dataCells[colorTheme]),
blue(dataCells[colorTheme]) * 4);
break;
case 4:
fill( red(dataCells[colorTheme]) * i / barDiv[colorTheme]);
break;
case 5:
if(barColor == 0) //it's a book
{
fill( hue(dataCells[colorTheme]) - 30,
saturation(dataCells[colorTheme]) + 70,
brightness(dataCells[colorTheme]) * i / barDiv[colorTheme]);
}
else // it's a film
{
fill( hue(dataCells[colorTheme]),
saturation(dataCells[colorTheme]) + 30,
brightness(dataCells[colorTheme]) * i / barDiv[colorTheme]);
}
break;
default:
break;
}
}
///////////////////////////////////////////////////////////////////////////////////////////
// Program's Basic Functions
///////////////////////////////////////////////////////////////////////////////////////////
void setup()
{
size(1190, 560);
background(255);
smooth();
println("SetUp Done!");
myTable = loadTable("XcelCombined.csv", "header");
println("Rows: " + myTable.getColumnCount());
println("Columns: " + myTable.getRowCount());
dataMatrix = new int[numRows][numColumns];
int k = 0; //row
int m = 0; //column
for(int i = 0; i < myTable.getRowCount(); i++)
{
for(int j = 2; j < myTable.getColumnCount(); j++)
{
maxCheckOuts = max(myTable.getInt(i,j), maxCheckOuts);
dataMatrix[k][m] = myTable.getInt(i,j);
k++;
if(k == 15)
{
k = 0;
m++;
}
}
}
}
void draw()
{
colorMode(RGB);
background(255);
//Background Over-Color
fill(backgroundColor[colorTheme]);
rect(0, 0, width, height);
//Background for Cells
fill(cellBackground[colorTheme]);
rect(rowMargin, colMargin, cellWidth * numColumns, cellHeight * numRows);
//Data Cells
for(int i = 0, k = 0; i < numColumns; i++, k++)
{
for(int j = 0; j < numRows; j++)
{
colorMode(RGB);
stroke(cellOutline[colorTheme]);
strokeWeight(0.014);
colorMode(colorMode[colorTheme]);
cellColor(i,j);
colorMode(RGB);
rect(rowMargin + k * cellWidth, colMargin + j * cellHeight, cellWidth, cellHeight);
}
}
//Line Annual Separators
stroke(lineSeparator[colorTheme]);
fill(colorLabel[colorTheme]);
for(int i = 0, k = 0; i < numColumns; i++, k++)
{
strokeWeight(1);
if(k == 0)
line(rowMargin + (k * cellWidth),
colMargin + numRows * cellHeight,
rowMargin + (k * cellWidth),
colMargin + numRows * cellHeight + 15);
if((k + 1) % 12 == 0)
line(rowMargin + ( (k + 1) * cellWidth),
colMargin + numRows * cellHeight,
rowMargin + ( (k + 1) * cellWidth),
colMargin + numRows * cellHeight + 15);
}
//Row Labels
String[] labels = {"Book 1", "Film 1", "Book 2", "Film 2", "Book 3", "Film 3",
"Book 4", "Film 4", "Book 5", "Film 5", "Book 6", "Film 6",
"Book 7", "Film 7", "Film 8"};
textAlign(CENTER, CENTER);
textFont(font1);
for(int i = 0; i < 1500; i += 100)
text(labels[i/100],
rowMargin * 0.7,
colMargin + cellHeight / 2 + i / 100 * cellHeight);
//Column Labels
textAlign(RIGHT, CENTER);
String[] yearL = {"2005", "2006", "2007", "2008", "2009", "2010", "2011",
"2012", "2013"};
for(int i = 1; i <= numColumns; i++)
{
if(i % 12 == 0 ) //12 months
{
text(yearL[i / 12 - 1],
rowMargin - (cellWidth / 2) + (i * cellWidth) - (cellWidth * 3.5),
height - 1.8 * colMargin);
}
}
//Title
textAlign(CENTER, CENTER);
textFont(font2);
text("Harry Potter Book and Movie Checkouts", width / 2, height * 0.14);
//Outline Border for Cells
stroke(outlineBorder[colorTheme]);
strokeWeight(0.5);
noFill();
rect(rowMargin, colMargin, cellWidth * numColumns, cellHeight * numRows);
//Color Bar
if(colorTheme < 5)
{
noStroke();
for(int i = 0; i < barDiv[colorTheme]; i+=1)
{
colorMode(colorMode[colorTheme]);
colorBarFill(i);
rect(barPosX + (i * (barLength / barDiv[colorTheme])), barPosY,
barLength / barDiv[colorTheme], barHeight);
}
}
else //Fill the two color bars
{
noStroke();
for(int i = 0; i < barDiv[colorTheme]; i+=1)
{
colorMode(colorMode[colorTheme]);
barColor = 0;
colorBarFill(i);
rect(barPosX + (i * (barLength / barDiv[colorTheme])), barPosY,
barLength / barDiv[colorTheme], barHeight / 2);
barColor = 1;
colorBarFill(i);
rect(barPosX + (i * (barLength / barDiv[colorTheme])), barPosY + 30,
barLength / barDiv[colorTheme], barHeight / 2);
}
}
//Color Bar Outline
if(colorTheme < 5)
{
noFill();
colorMode(RGB);
stroke(colorBarOutline[colorTheme]);
strokeWeight(0.4);
rect(barPosX, barPosY, barLength, barHeight);
}
else //This is for the double bar
{
noFill();
colorMode(RGB);
stroke(colorBarOutline[colorTheme]);
strokeWeight(0.4);
rect(barPosX, barPosY, barLength, barHeight / 2);
rect(barPosX, barPosY + 30, barLength, barHeight / 2);
}
//Color Bar Label
if(colorTheme < 5)
{
fill(colorLabel[colorTheme]);
textAlign(CENTER, CENTER);
textFont(font1);
text("Least checkouts", barPosX, barPosY + 45);
text("Most checkouts", barPosX + barLength, barPosY + 45);
}
else //This is for the double bar
{
fill(colorLabel[colorTheme]);
textAlign(CENTER, CENTER);
textFont(font1);
text("Least checkouts", barPosX + 45, barPosY + 65);
text("Most checkouts", barPosX + barLength - 40, barPosY + 65);
text("Books", barPosX - 25, barPosY + 5);
text("Films", barPosX - 25, barPosY + 35);
}
}