Seattle is home to a widespread community garden program. I was curious if any patterns could be discerned from Seattle Public Library checkout habits within the Dewey class 630: Agriculture. MySQL was used to query each Dewey number, from 630 to 631, accumulating the number of checkouts per month across 8 years, from 2006 to 2013.
In Processing I arranged the data vertically by Dewey number, and within each Dewey number, 8 rows represent each successive year. Months of the year are represented horizontally. I implemented a slightly unorthodox method of coloring the data. There is a lot of variation within each Dewey class across time, but some Dewey classes dominate the data, for example 635 gets well into the thousands per month, whereas 630 stays in the hundreds. By modulating not only the brightness but also the transparency of each cell in the matrix, more variation can be revealed in the low range while maintaining variation in the high ranges. I made the alpha channel have a minimum value of 20 (out of 255) for legibility and aesthetic reasons. Hue varies based on Dewey number.
Analysis
From the visualization it becomes obvious that the most activity occurs during the Spring, with March being the most important month for those interested in horticulture. Checkouts tend to dwindle as winter approaches, but items related to hunting and fishing see a slight increase before Fall as people prepare for the hunting season. Interest in insect culture regularly spikes during the month of June as gardeners likely seek defense against summer pests. Overall, fewer items have been checked out across all Dewey numbers in 2012 and 2013 compared to prior years.
MySQL query:
Code: Select all
SELECT
MONTH(cout),
YEAR(cout),
COUNT(IF( deweyClass >=630 AND deweyClass < 631, 1, NULL)) as "630",
COUNT(IF( deweyClass >=631 AND deweyClass < 632, 1, NULL)) as "631",
COUNT(IF( deweyClass >=632 AND deweyClass < 633, 1, NULL)) as "632",
COUNT(IF( deweyClass >=633 AND deweyClass < 634, 1, NULL)) as "633",
COUNT(IF( deweyClass >=634 AND deweyClass < 635, 1, NULL)) as "634",
COUNT(IF( deweyClass >=635 AND deweyClass < 636, 1, NULL)) as "635",
COUNT(IF( deweyClass >=636 AND deweyClass < 637, 1, NULL)) as "636",
COUNT(IF( deweyClass >=637 AND deweyClass < 638, 1, NULL)) as "637",
COUNT(IF( deweyClass >=638 AND deweyClass < 639, 1, NULL)) as "638",
COUNT(IF( deweyClass >=639 AND deweyClass < 640, 1, NULL)) as "639"
FROM
spl2.inraw
WHERE
deweyClass >= 630 AND deweyClass < 640
AND DATE(cout) >= '20060101'
AND DATE(cout) <= '20131231'
AND (itemtype = "acbk" OR itemtype = "arbk")
GROUP BY MONTH(cout), YEAR(cout)
ORDER BY YEAR(cout), MONTH(cout)
Processing code:
Code: Select all
int radius = 100;
int xPosition = 20;
int pos = 0;
int [][] dataMatrix = null;
int maxCount;
int numRows, numColumns;
int cellWidth, cellHeight, cellMarginHorizontal, cellMarginVertical, rowSpacing;
String [] deweyNames = new String[10];
PFont fontHeader = createFont("SukhumvitSet-SemiBold", 30, true);
PFont fontHeader2 = createFont("SourceSansPro-Semibold", 14, true);
PFont fontHeader3 = createFont("SukhumvitSet-SemiBold", 16, true);
PFont fontBracket = createFont("SourceSansPro-Regular", 80, true);
PFont fontMain = createFont("SourceSansPro-Semibold", 12, true);
color bgColor, textColor;
Table myTable;
void setup()
{
size(1200,900);
smooth();
cellWidth = 70;
cellHeight = 5;
cellMarginHorizontal = 220;
cellMarginVertical = 140;
rowSpacing = 60;
bgColor = color(234,233,237);
println("setup done");
myTable = loadTable("hw1_yearmonth.csv", "header");
numRows = myTable.getRowCount();
numColumns = myTable.getColumnCount() - 2;
dataMatrix = new int[numRows][numColumns];
maxCount = dataMatrix[0][0];
for (int i = 0; i < numRows; i++)
{
for (int j = 0; j < numColumns ; j++) //skipping two columns j=2
{
dataMatrix[i][j] = myTable.getInt(i,(j+2));
print(dataMatrix[i][j] + " " );
if (dataMatrix[i][j] > maxCount)
maxCount = dataMatrix[i][j];
}
println();
}
println("Rows: " + numRows + ", Columns: " + numColumns + ", max: " + maxCount);
println();
println();
deweyNames[0] = "Agriculture & related technology";
deweyNames[1] = "Apparatus, equipment, materials";
deweyNames[2] = "Plant injuries, diseases, pests";
deweyNames[3] = "Field & plantation crops";
deweyNames[4] = "Orchards, fruits, forestry";
deweyNames[5] = "Garden crops, horticulture";
deweyNames[6] = "Animal husbandry";
deweyNames[7] = "Processing dairy";
deweyNames[8] = "Insect culture";
deweyNames[9] = "Hunting, fishing, conservation";
}
void draw()
{
background(bgColor);
noStroke();
for (int i = 0; i < numColumns; i++)
{
for (int j = 0; j < 12; j++)
{
for (int k = 0; k < 8; k++)
{
colorMode(HSB);
float fillColor = 255 * dataMatrix[(k+(j*8))][i] / maxCount;
fill(255*i/12 + (k*1), 255, (fillColor+30), (fillColor*20)+20);
rect(cellMarginHorizontal + j * cellWidth * 1 , cellMarginVertical + (k * (cellHeight)) + (i * 60),
cellWidth, cellHeight);
fill(255,100);
colorMode(RGB);
}
}
}
// Y label
textFont(fontMain);
fill(84,81,109);
for (int i = 0; i < numColumns; i++)
{
textFont(fontHeader3);
textAlign(RIGHT);
text("63" + i, cellMarginHorizontal-10, cellMarginVertical + (i * rowSpacing) + 15);
textFont(fontMain);
textLeading(14);
text(deweyNames[i], cellMarginHorizontal-10, cellMarginVertical + (i * rowSpacing) + 35);
}
for (int i = 0; i < 13; i++) //vertical lines
{
if (i > 0)
text(i, (cellMarginHorizontal - 35) + (i*71), height-(cellMarginVertical*1.2)); //X label
stroke(84,81,109,100);
if (i != 0 && i != 12)
line(cellMarginHorizontal + (i*70), cellMarginVertical, cellMarginHorizontal + (i*70), cellMarginVertical + (118*cellHeight));
}
for (int i = 0; i< numColumns; i++) //horizontal lines
{
stroke(84,81,109,100);
line(cellMarginHorizontal-100, cellMarginVertical + cellHeight*8 + (i*rowSpacing),
width-(cellMarginHorizontal*.64), cellMarginVertical + cellHeight*8 + (i*rowSpacing));
}
//TITLE
textFont(fontHeader);
textAlign(LEFT);
text("Urban Agriculture in Seattle", cellMarginHorizontal, cellMarginVertical - 40);
textFont(fontHeader2);
text("Number of items checked out monthly with Dewey Decimal classifcation 630: Agriculture, 2006-2013",
cellMarginHorizontal, cellMarginVertical - 20);
// LEGEND
textFont(fontMain);
pushMatrix();
textAlign(LEFT);
translate(width-(cellMarginHorizontal*.4), cellMarginVertical);
textLeading(14);
translate(0,32);
text("Checkouts\nper month",-35,-25);
colorMode(HSB);
noStroke();
for(int i = 0; i<50; i++)
{
float fillColor = (255*i)/49;
fill(120, 255, fillColor+30, (fillColor*20)+20);
rect(-cellWidth*.5, i*cellHeight, cellWidth*.2, cellHeight);
}
stroke(84,81,109,130);
fill(0, 0);
rect(-cellWidth*.5, 0, cellWidth*.2, cellHeight*50);
translate(0,10);
for(int i = 0; i<5; i++)
{
fill(84,81,109);
text(i*1000, -15, i * 60);
}
colorMode(RGB);
popMatrix();
}