Data analysis exercise using D3

analysis.js

// Oran Chirapuntu

$( document ).ready(function() {

	loadDefaultDataset();

}); // end document ready

// load dataset and process
function loadDataset(dataFile) {
  var data = d3.csv.parse(dataFile)
  processData(data);
}

function loadDefaultDataset() {
	d3.csv("small.csv", function(data) {
  		processData(data);
	});
}


function processData(data){
	// print out the first record
	data.forEach(function(d) {
	d.Duration = +d.Duration;
	d["Zip Code"] = +d["Zip Code"];
	});
	//console.log(data[0]);

	// min and max dates 
	var minStartDateString = d3.min(data, function(d) { return d["Start Date"]; });
	var maxEndDateString = d3.max(data, function(d) { return d["End Date"]; });
	//var durationExtent = d3.extent(data, function(d) { return d.Duration });


	// filter by local (Bay Area) zips
	var localZips = data.filter(function(d) { return d["Zip Code"] > 94000 && d["Zip Code"] < 95500; });
	// filter out bad zip codes and include non Bay Area zips
	var nonLocalZips = data.filter(function(d) { return d["Zip Code"] > 10000 && d["Zip Code"] < 100000 && (d["Zip Code"] <= 94000 || d["Zip Code"] >= 95500); });

	//console.log(localZips[0]);

	// group by start station
	var localMetricsStart = d3.nest()
		.key(function(d) { return d["Start Station"]; })
		.rollup(function(v) { return {
			count: v.length,
			min: d3.min(v, function(d) {return d.Duration}),
			max: d3.max(v, function(d) { return d.Duration; }),
			avg: d3.mean(v, function(d) { return d.Duration; }).toFixed(3),
			median: d3.median(v, function(d) { return d.Duration;})
		}; })
		.entries(localZips);

	// sort start stations desc by count
	localMetricsStart.sort(function(a,b){
		if(a.values.count > b.values.count) { return -1; }
		else if(a.values.count < b.values.count) {return 1; }
		else return 0;
	});
	//console.log(JSON.stringify(localMetricsStart));

	// group by end station
	var localMetricsEnd = d3.nest()
		.key(function(d) { return d["End Station"]; })
		.rollup(function(v) { return {
			count: v.length,
			min: d3.min(v, function(d) {return d.Duration}),
			max: d3.max(v, function(d) { return d.Duration; }),
			avg: d3.mean(v, function(d) { return d.Duration; }).toFixed(3),
			median: d3.median(v, function(d) { return d.Duration;})
		}; })
		.entries(localZips);

	// sort end stations desc by count
	localMetricsEnd.sort(function(a,b){
		if(a.values.count > b.values.count) { return -1; }
		else if(a.values.count < b.values.count) {return 1; }
		else return 0;
	})
	//console.log(JSON.stringify(localMetricsEnd));

	// var localZipCount = d3.nest()
	//   .key(function(d) { return d["Zip Code"]; })
	//   .rollup(function(v) { return v.length; })
	//   .entries(localZips);
	//console.log("Local zips: " + JSON.stringify(localZipCount));

	// var nonLocalZipCount = d3.nest()
	//   .key(function(d) { return d["Zip Code"]; })
	//   .rollup(function(v) { return v.length; })
	//   .entries(nonLocalZips);
	//console.log("Non local zips: " + JSON.stringify(nonLocalZipCount));
	var localPercent = (localZips.length / data.length).toFixed(3) * 100;
	var nonLocalPercent = (nonLocalZips.length / data.length).toFixed(3) * 100;
	console.log("Number of local zip trips: " + localZips.length + "(" + localPercent + "%); non local zip trips: " 
		+ nonLocalZips.length + "(" + nonLocalPercent + "%)");

	var percentMessage = "<p>The data presented here focuses on local (Bay Area) data as determined by the Zip Code data. This is because the local data accounts " + 
		"for <font color='red'>" + localPercent + "%</font> of the total trips.</p>" + 
		"<p>The Bay Area zip codes were determined to be the zip codes between 94000 and 95500.</p>" + 
		"<p>The minimum, maximum, average, and median durations are also presented. These are values in seconds. I did not filter out the extreme values because the outliers " + 
		"should not dramatically affect the aggregated values. It can also be interesting to see the origin and destination of someone who may have \"forgotten\" to " + 
		"return a bicycle.</p>"
	$("#localPercentage").html(percentMessage);

	var summaryMessage = "<p>I wanted to take a look at usage data. I wanted to know where the users start and end their bicycle journeys.</p>" + 
		"<p>Knowing the users of the bicycle service helps to identify where to concentrate resources. Because local users account for the lion's share of the usage, " + 
		"I decided to focus on the top 10 starting and ending stations as well as the bottom 10 starting and ending stations.</p>" + 
		"<p>Hovering over each data row will show the hourly breakdown for the respective pickup or dropoff location.</p>" + 
		"<p>While the numbers may look significant, it is important to note that this is aggregated data from <font color='red'>" + minStartDateString + 
		"</font> to <font color='red'>" + maxEndDateString + "</font></p>" + 
		"<p>With the hourly breakdown, we can see that the most journeys originate and end at the Caltrain Station. This tells us that the users either take Caltrain " + 
		"and/or one of the many buses that serves the station. The Caltrain station has a peak start in the morning and a peak end in the afternoon. Because the numbers " +
		"of start and ending journeys do not match up, there is a longer term inventory problem. Slowly and organically, the number of bicycles at the Caltrain station " + 
		"will grow.</p>";

	$("#summary").html(summaryMessage);

	// 
	// break down time usage for the top local stations
	//
	var timeRegex = /([0-9])+\:/
	var timeDelimiterRegex = /\:/;
	var localZipsCopy = localZips;

	// top start
	var topStartStationMap = d3.map(localMetricsStart.slice(0,10), function(d) { return d.key; });
	var topStartStationsByLocalZips = localZipsCopy.filter(function(d) {
		return topStartStationMap.has(d["Start Station"]);
	})
	console.log(JSON.stringify(topStartStationsByLocalZips));

	var startStationTopBreakdownByHour = d3.nest()
		.key(function(d) { return d["Start Station"]; })
		.key(function(d) { return d["Start Date"].match(timeRegex)[0].replace(timeDelimiterRegex, ''); })
		.rollup(function(v) { return {
			count: v.length
		}; })
		.map(topStartStationsByLocalZips);
	console.log(JSON.stringify(startStationTopBreakdownByHour));

	// top end
	var topEndStationMap = d3.map(localMetricsEnd.slice(0,10), function(d) { return d.key; });
	var topEndStationsByLocalZips = localZipsCopy.filter(function(d) {
		return topEndStationMap.has(d["End Station"]);
	})
	console.log(JSON.stringify(endStationTopBreakdownByHour));

	var endStationTopBreakdownByHour = d3.nest()
		.key(function(d) { return d["End Station"]; })
		.key(function(d) { return d["End Date"].match(timeRegex)[0].replace(timeDelimiterRegex, ''); })
		.rollup(function(v) { return {
			count: v.length
		}; })
		.map(topEndStationsByLocalZips);
	console.log(JSON.stringify(endStationTopBreakdownByHour));

	// bottom start
	var bottomStartStationMap = d3.map(localMetricsStart.slice(localMetricsStart.length - 11,localMetricsStart.length - 1)
		.sort(function(a,b){
			if(a.values.count > b.values.count) { return 1; }
			else if(a.values.count < b.values.count) {return -1; }
			else return 0;}), function(d) { return d.key; });
	var bottomStartStationsByLocalZips = localZipsCopy.filter(function(d) {
		return bottomStartStationMap.has(d["Start Station"]);
	})
	//console.log(JSON.stringify(bottomStartStationsByLocalZips));

	var startStationBottomBreakdownByHour = d3.nest()
		.key(function(d) { return d["Start Station"]; })
		.key(function(d) { return d["Start Date"].match(timeRegex)[0].replace(timeDelimiterRegex, ''); })
		.rollup(function(v) { return {
			count: v.length
		}; })
		.map(bottomStartStationsByLocalZips);
	//console.log(JSON.stringify(startStationBottomBreakdownByHour));


	// bottom end
	var bottomEndStationMap = d3.map(localMetricsStart.slice(localMetricsStart.length - 11,localMetricsStart.length - 1)
		.sort(function(a,b){
			if(a.values.count > b.values.count) { return -1; }
			else if(a.values.count < b.values.count) {return 1; }
			else return 0;}), function(d) { return d.key; });
	var bottomEndStationsByLocalZips = localZipsCopy.filter(function(d) {
		return bottomEndStationMap.has(d["End Station"]);
	})
	//console.log(JSON.stringify(bottomEndStationsByLocalZips));

	var endStationBottomBreakdownByHour = d3.nest()
		.key(function(d) { return d["End Station"]; })
		.key(function(d) { return d["End Date"].match(timeRegex)[0].replace(timeDelimiterRegex, ''); })
		.rollup(function(v) { return {
			count: v.length
		}; })
		.map(bottomEndStationsByLocalZips);
	//console.log(JSON.stringify(endStationBottomBreakdownByHour));


	//
	// generate tables
	//

	// station table column definition
	var stationColumns = ["Station Name", "Count", "Min Duration", "Max Duration", "Avg Duration", "Med Duration"];
	var stationDetailCoumns = ["Morning Hour", "Morning Count", "Afternoon Hour", "Afternoon Count"];

	// clear old tables
	$("#startStationTop").empty();
	$("#endStationTop").empty();
	$("#startStationBottom").empty();
	$("#endStationBottom").empty();

	// render the station tables
	var startStationTableTop = tabulateStations("startStationTop", localMetricsStart.slice(0,10), stationColumns, startStationTopBreakdownByHour, stationDetailCoumns);
	var endStationTableTop = tabulateStations("endStationTop", localMetricsEnd.slice(0,10), stationColumns, endStationTopBreakdownByHour, stationDetailCoumns);
	// flip the sort order to asc
	var startStationTableBottom = tabulateStations("startStationBottom", localMetricsStart.slice(localMetricsStart.length - 11,localMetricsStart.length - 1).sort(function(a,b){
		if(a.values.count > b.values.count) { return 1; }
		else if(a.values.count < b.values.count) {return -1; }
		else return 0;}), stationColumns, startStationBottomBreakdownByHour, stationDetailCoumns);
	var endStationTableBottom = tabulateStations("endStationBottom", localMetricsEnd.slice(localMetricsEnd.length - 11, localMetricsEnd.length - 1).sort(function(a,b){
		if(a.values.count > b.values.count) { return 1; }
		else if(a.values.count < b.values.count) {return -1; }
		else return 0;}), stationColumns, endStationBottomBreakdownByHour, stationDetailCoumns);

	//
	// alternate row colors
	//
	d3.selectAll("#startStationTop tr").classed("alt", function(d, i) {
  		return i % 2 ? false : true;
	});
	d3.selectAll("#endStationTop  tr").classed("alt", function(d, i) {
  		return i % 2 ? false : true;
	});
	d3.selectAll("#startStationBottom tr").classed("alt", function(d, i) {
  		return i % 2 ? false : true;
	});
	d3.selectAll("#endStationBottom tr").classed("alt", function(d, i) {
  		return i % 2 ? false : true;
	});

};
// end process data

function tabulateStations(id, data, columns, detailData, detailDataColumns) {
 var table = d3.select("#" + id).append("table"),
			thead = table.append("thead"),
			tbody = table.append("tbody");

	// append the header row
	thead.append("tr")
		.selectAll("th")
		.data(columns)
		.enter()
		.append("th")
			.text(function(column) {
			return column; 
			});

	var tds =[];
	data.forEach(function(d){
		var innerTd = {};
		innerTd["Station Name"] = d.key;
		innerTd["Count"]=  d.values.count;
		innerTd["Min Duration"] = d.values.min;
		innerTd["Max Duration"] = d.values.max;
		innerTd["Avg Duration"] = d.values.avg;
		innerTd["Med Duration"] = d.values.median;
		tds.push(innerTd);
	});

	// create a row for each object in the data
	var rows = tbody.selectAll("tr")
		//.data(data)
		.data(tds)
		.enter()
		.append("tr")
		.on('mouseover', function(d){ 
			d3.select(this).classed("selected", true); 
			var map = d3.map(detailData);
			var stationHourly = map.get(d["Station Name"]);
			var detailable = tabulateStationDetails(id, stationHourly, detailDataColumns);
		})
        .on('mouseout', function(d){ 
        	d3.select(this).classed("selected", false); 
        	$("#" + id + "TimeBreakdown").empty();
        });

	// create a cell in each row for each column
	var cells = rows.selectAll("td")
		.data(function(row) {
			return columns.map(function(column) {
				return {column: column, value: row[column]};
			});
		})
		.enter()
		.append("td")
			.text(function(d) {
				return d.value; 
			});

    return table;
}

function tabulateStationDetails(id, data, columns) {
	var table = d3.select("#" + id + "TimeBreakdown").append("table"),
			thead = table.append("thead"),
			tbody = table.append("tbody");

	// append the header row
	thead.append("tr")
		.selectAll("th")
		.data(columns)
		.enter()
		.append("th")
			.text(function(column) {
			return column; 
			});

	var tds =[];
	var map = d3.map(data);
	for(var i=0; i<12; i++) {
		var hourlyDataMorning = map.get(i);
		var hourlyDataAfternoon = map.get(i+12);
		var hourlyMapMorning = d3.map(hourlyDataMorning);
		var hourlyMapAfternoon = d3.map(hourlyDataAfternoon);
		//console.log(JSON.stringify(hourlyData));
		var innerTd = {};
		innerTd["Morning Hour"] = i;
		innerTd["Afternoon Hour"] = i + 12;
		var countMor = hourlyMapMorning.get("count")
		innerTd["Morning Count"] = (countMor !== undefined) ? countMor : 0;
		var countAft = hourlyMapAfternoon.get("count");
		innerTd["Afternoon Count"] = (countAft !== undefined) ? countAft : 0;
		tds.push(innerTd);
	}

	// create a row for each object in the data
	var rows = tbody.selectAll("tr")
		//.data(data)
		.data(tds)
		.enter()
		.append("tr")
		.on('mouseover', function(){ d3.select(this).classed("selected", true); })
        .on('mouseout', function(){ d3.select(this).classed("selected", false); });

	// create a cell in each row for each column
	var cells = rows.selectAll("td")
		.data(function(row) {
			return columns.map(function(column) {
				return {column: column, value: row[column]};
			});
		})
		.enter()
		.append("td")
			.text(function(d) {
				return d.value; 
			});


	return table;
}

// handle upload button
function upload_button(el, callback) {
  var uploader = document.getElementById(el);  
  var reader = new FileReader();

  reader.onload = function(e) {
    var contents = e.target.result;
    callback(contents);
  };

  uploader.addEventListener("change", handleFiles, false);  

  function handleFiles() {
    var file = this.files[0];

    try {
    	reader.readAsText(file);

    	$("#startStationTop").empty();
		$("#endStationTop").empty();
		$("#startStationBottom").empty();
		$("#endStationBottom").empty();

		$("#startStationTop").text("loading...");
		$("#endStationTop").text("loading...");
		$("#startStationBottom").text("loading...");
		$("#endStationBottom").text("loading...");
    } 
    catch(err) {
    	console.log(err.message);
    }
  };
};

index.html

<!doctype html>
<html>
	<head>
		<link rel="stylesheet" type="text/css" href="styles.css">
	</head>
	<body>
	
	<p>
	Upload a csv file. It needs to be in the format (with header line):
	</p>
	<blockquote>
		<div>
			Trip ID,Duration,Start Date,Start Station,Start Terminal,End Date,End Station,End Terminal,Bike #,Subscriber Type,Zip Code<br/>
			261618,457,4/26/14 9:38,Santa Clara County Civic Center,80,4/26/14 9:46,Ryland Park,84,294,Subscriber,95112
		</div>
	</blockquote>

	
	<input type="file" id="uploader">

	<div id="#container">
		<hr>
		<br/>
		<div id="localPercentage"></div>
		<br/>
		<div id="summary"></div>
		<br/>
		<hr>
		<br/><br/>
		<h1>Top Usage Stations</h1>
		<hr>
		<h3>Start Stations</h3>
		<div id="tablesDiv">
			<div id="startStationTop" class="left"></div>
			<div id="startStationTopTimeBreakdown" class="right"></div>
		</div>
		<div style="clear:both;"></div>

		<br/><br/>
		<h3>End Stations</h3>
		<div id="tablesDiv">
			<div id="endStationTop" class="left"></div>
			<div id="endStationTopTimeBreakdown" class="right"></div>
		</div>
		<div style="clear:both;"></div>

		<br/><br/>
		<h1>Bottom Usage Stations</h1>
		<hr>
		<h3>Start Stations</h3>
		<div id="tablesDiv">
			<div id="startStationBottom" class="left"></div>
			<div id="startStationBottomTimeBreakdown" class="right"></div>
		</div>
		<div style="clear:both;"></div>

		<br/><br/>
		<h3>End Stations</h3>
		<div id="tablesDiv">
			<div id="endStationBottom" class="left"></div>
			<div id="endStationBottomTimeBreakdown" class="right"></div>
		</div>
		<div style="clear:both;"></div>

	</div>

	<br/><br/><br/>

	<script src="https://cdnjs.cloudflare.com/ajax/libs/d3/3.5.6/d3.min.js" charset="utf-8"></script>
	<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
	<script src='analysis.js'></script>

	<script>
		upload_button("uploader", loadDataset);
	</script>

	</body>
</html>

small.csv

styles.css

table {
	border-collapse: collapse;
}

th, td {
	border: 1px solid black;
    padding: 2px 4px;
}

th {
    font-weight: bold;
    background-color: #0A0A0A;
    color: #ffffff;
}

tr.alt {
	color: #000000;
    background-color: #D6D6D6;
}

tr.selected {
	background-color: brown;
	color: #ffffff;
}

.left {
	float: left;
}

.right {
	float: right;
}

blockquote {
	font: 12px/18px normal "Courier New", sans-serif;
	padding-left: 70px;
	padding-top: 2px;
	padding-bottom: 2px;
	background-color: #D6D6D6;
	color: #000000;
	border-top: 1px solid #ccc;
	border-bottom: 1px solid #ccc;
	margin: 5px;
	background-position: middle left;
	background-repeat: no-repeat;
}

blockquote div {
	padding-right: 50px;
	display: block;
	background-repeat: no-repeat;
	background-position: bottom right;
}