REPORT PARAMETERS

Report not found

There was a problem finding this report.
Retrosheet - Plotting MLB Win Totals
April 6, 2014 · Refreshed about 4 years ago
Description
The graphic plots wins for every MLB team for every season since 2004. For win-loss records with fewer than 5 teams, the win filter automatically widens to include at least 5 teams.

Collaborators

Run History
SELECT a.year, a.team, a.game_number, COUNT(win.game_id) AS wins FROM ( SELECT DATE_TRUNC('year',a.date) AS year, a.team, a.game_number FROM benn.retrosheet_games_by_team a WHERE a.date >= '1999-01-01' AND a.game_type = 'regular_season' ) a LEFT JOIN ( SELECT DATE_TRUNC('year',win.date) AS year, win.game_id, win.team, win.game_number FROM benn.retrosheet_games_by_team win WHERE win.winner = TRUE AND win.date >= '1999-01-01' AND win.game_type = 'regular_season' ) win ON win.year = a.year AND win.team = a.team AND win.game_number <= a.game_number GROUP BY 1,2,3 ORDER BY 1,2,3
SELECT a.year, a.team, a.game_number, COUNT(win.game_id) AS wins FROM ( SELECT DATE_TRUNC('year',a.date) AS year, a.team, a.game_number FROM benn.retrosheet_games_by_team a WHERE a.date >= '{{ start_year }}' AND a.game_type = 'regular_season' ) a LEFT JOIN ( SELECT DATE_TRUNC('year',win.date) AS year, win.game_id, win.team, win.game_number FROM benn.retrosheet_games_by_team win WHERE win.winner = TRUE AND win.date >= '{{ start_year }}' AND win.game_type = 'regular_season' ) win ON win.year = a.year AND win.team = a.team AND win.game_number <= a.game_number GROUP BY 1,2,3 ORDER BY 1,2,3 {% form %} start_year: type: text default: 2004-01-01 {% endform %}
<style> p { margin-top: 5px;} body { font: 12px; font-family: Helvetica, Arial, sans-serif; } .axis path { display: none; } .axis line { shape-rendering: crispEdges; stroke: #000; } .axis .minor line { stroke: #e2e2e2; } .dot { stroke: #23415A; fill: steelblue; opacity: 0.15; } .game-bar { stroke:none; fill:#cccccc; opacity: 0; } #info-bar { font-size: 16px; font-family: Geneva; text-anchor: middle; } .game-bar:hover { opacity: .5; } .c99 { opacity: 0.4; fill: #FBE6D9;} .c95 { opacity: 0.4; fill: #F4C19F;} .c68 { opacity: 0.4; fill: #EE9C65;} .mean-line { stroke: Black; stroke-width: 2px; } .legend { font-size: 14px; background-color: blue; } .highlight { color: red; } input { height: 20px; width: 30px; padding: 6px 12px; font-size: 14px;; color: #555555; vertical-align: middle; background-color: #ffffff; border: 1px solid #c9cdce; border-radius: 4px; -webkit-transition: border-color ease-in-out .15s, box-shadow ease-in-out .15s; transition: border-color ease-in-out .15s, box-shadow ease-in-out .15s; text-align: center; } .btn { color: #fff; background-color: #ea833f; border: none; height:30px; width: 30px; cursor: pointer; padding: 2px 0px 2px 0px; margin-bottom: 0px; border-radius: 20px; vertical-align: middle; font-size: 18px; } .btn:hover { background: #ef9c69; color: #555555;; } .btn-header { text-align: center; margin-bottom: 5px; } .d3-tip { line-height: 1.5; padding: 12px; background: rgba(0, 0, 0, 0.8); color: #fff; border-radius: 2px; text-align: center; } .d3-tip:after { display: inline; position: absolute; } </style> <body> <div style="text-align: center; width: 800px;"> <div style="width:300px; margin:0 auto;"> <div style="float:left; width:auto"> <p class="btn-header">Wins</p> <input style="float:left padding: 10px" class="btn games" type="submit" id="w-1" value="-"> <input style="float:left padding: 10px" type="text" class="number-input" id="wins" value="7"> <input style="float:left padding: 10px" class="btn games" type="submit" id="w+1" value="+"> </div> <div style="float:left; width:auto; padding-left: 50px" > <p class="btn-header">Losses</p> <input style="float:left padding: 10px" class="btn games" type="submit" id="l-1" value="-"> <input style="float:left padding: 10px" type="text" class="number-input" id="losses" value="2"> <input style="float:left padding: 10px" class="btn games" type="submit" id="l+1" value="+"> </div> <div style="clear:both"></div> </div> </div> <div id="graph"></div> <script src="//cdnjs.cloudflare.com/ajax/libs/underscore.js/1.6.0/underscore-min.js"></script> <script src="//cdnjs.cloudflare.com/ajax/libs/crossfilter/1.3.7/crossfilter.min.js"></script> <script src="//cdnjs.cloudflare.com/ajax/libs/d3-tip/0.6.3/d3-tip.min.js"></script> <script src="//ajax.googleapis.com/ajax/libs/jquery/1.6.4/jquery.min.js" type="text/javascript"></script> <script> var winNumber = 7; var lossNumber = 2; var minTeamNumber = 5; var selections = {wins: winNumber, losses: lossNumber} var margin = {top: 70, right: 200, bottom: 30, left: 10}, width = 800 - margin.left - margin.right, height = 550 - margin.top - margin.bottom; var static68 = {gn: 162, high: 92.5683146006061, low: 69.36025682796532, mean: 80.96428571428571}, static95 = {gn: 162, high: 103.70818233147367, low: 58.22038909709775, mean: 80.96428571428571}, static99 = {gn: 162, high: 111.13476081871872, low: 50.793810609852706, mean: 80.96428571428571}; var formatNumber = d3.format(".1f"); var x = d3.scale.linear() .domain([0,162]) .range([0, width]); var y = d3.scale.linear() .domain([0,120]) .range([height, 0]); var xAxis = d3.svg.axis() .scale(x) .orient("bottom"); var yAxis = d3.svg.axis() .scale(y) .tickFormat(formatY) .tickSize(width) .orient("right"); var svg = d3.select("#graph").append("svg") .attr("width", width + margin.left + margin.right) .attr("height", height + margin.top + margin.bottom) .append("g") .attr("transform", "translate(" + margin.left + "," + margin.top + ")"); svg.append("g") .attr("class", "x axis") .attr("transform", "translate(0," + height + ")") .call(xAxis) .append("text") .attr("class", "label") .attr("x", width) .attr("y", -6) .style("text-anchor", "end") .text("Games"); gy = svg.append("g") .attr("class", "y axis") .call(yAxis); gy.selectAll("text") .attr("x", 4) .attr("dy", -4); gy.selectAll("g").filter(function(d) { return d; }) .classed("minor", true); svg.append("text") .attr("x",width/2) .attr("y",-20) .attr("id","info-bar") .attr("class","legend") .text("Rendering"); var bars = svg.selectAll("rect") .data(["#EE9C65","#F4C19F","#FBE6D9"]) .enter() .append("rect") .attr("x",width + 10) .attr("y", function (d,i) { return 330 + (i * 30); }) .attr("height", function (d) { return 20; }) .attr("width", function (d) { return 20; }) .style("fill", function(d) { return d; }); svg.append("text") .attr("x",width + 35) .attr("y",330 + 15) .attr("class","legend") .text("68% confidence"); svg.append("text") .attr("x",width + 35) .attr("y",330 + 45) .attr("class","legend") .text("95% confidence"); svg.append("text") .attr("x",width + 35) .attr("y",330 + 75) .attr("class","legend") .text("99% confidence"); var tip = d3.tip() .attr('class', 'd3-tip') .offset(function(d) { if (d.gn > 115) { return [-150, -10]; } else { return [-150, 10]; } }) .direction(function(d) { if (d.gn > 115) { return 'w'; } else { return 'e'; } }) .html(function(d) { return "Game in season: " + d.gn + "</br>Average wins: " + d3.round(d.mean,1) + "</br>95% win interval: " + d3.round(d.low,1) + " - " + d3.round(d.high,1); }) function formatY(d) { if (d === y.domain()[1]) { return d + " wins"; } else if (d === y.domain()[0]) { return ""; } else { return d;} } full = dataset.content; full = addSeasonId(full); // Set crossfilters var cross = crossfilter(full), winsCross = cross.dimension(function(d) { return +d.wins; }), gamesCross = cross.dimension(function(d) { return +d.game_number; }); winsCross.filterExact(winNumber); gamesCross.filterExact(lossNumber); // Set starting data and render var currentData = filterGames(full,selections,winsCross,gamesCross); render(currentData,selections); // For editing input $(".number-input").change( function() { // Assign new selection var val = $(this).attr('value') console.log(val); if ( $(this).attr('id') == 'wins') { val = Math.max(Math.min(+val,162),0); selections.wins = Math.min(162 - selections.losses,val); console.log(selections.wins); } if ( $(this).attr('id') == 'losses') { val = Math.max(Math.min(+val,162),0); selections.losses = Math.min(162 - selections.wins,val); } // Remove, filter and render new removeGraphic(); currentData = filterGames(full,selections,winsCross,gamesCross); render(currentData,selections); }); // For clicking plus and minus $(".games").click( function() { var id = $(this).attr('id') var type = id.slice(0,1) var val = +id.slice(1,3) // Update selections and input value if (type == "w") { var newWins = selections.wins + val; var windowedWins = Math.max(Math.min(newWins,162),0) selections.wins = windowedWins; $("#wins").val(windowedWins); } if (type == "l") { var newLosses = selections.losses + val; var windowedLosses = Math.max(Math.min(newLosses,162),0) selections.losses = windowedLosses; $("#losses").val(windowedLosses); } // Remove, filter and render new removeGraphic(); currentData = filterGames(full,selections,winsCross,gamesCross); render(currentData,selections); }); // FUNCTIONS function filter(data,wins,games,winCross,gameCross) { // Set empy array var arr = []; // Filter out to match selected win and games if (games == 0) { winsCross.filterAll(); gamesCross.filterAll(); } else { winsCross.filterExact(wins); gamesCross.filterExact(games); } // Determine teams to include var teamsToInclude = []; var included = winsCross.top(100000); if (included.length < minTeamNumber) { bigSample = getBiggerSample(wins,games,minTeamNumber,winsCross); included = bigSample.array; maxWins = bigSample.maxWins; minWins = bigSample.minWins; } else { maxWins = wins; minWins = wins; } included.forEach(function(d) { var team = d.team; var year = d.year + ''; teamEntry = team.concat(year) teamsToInclude.push(teamEntry); }) // Loop over data and include matching teams data.forEach(function(d) { teamYear = d.team.concat(d.year + "") if (teamsToInclude.indexOf(teamYear) != -1) { arr.push(d) } }) return arr } function filterGames(data,selections,winCross,gameCross) { // Determine wins and losses var winNumber = selections.wins; var gameNumber = selections.wins + selections.losses; // Apply filter var filteredData = filter(data,winNumber,gameNumber,winCross,gameCross); return filteredData; } function removeGraphic() { d3.selectAll(".interval").remove(); d3.selectAll(".game-bar").remove(); d3.selectAll(".dot").remove(); d3.selectAll(".dist-legend").remove(); } function renderDots(data) { data.forEach(function(d) { svg.append("circle") .attr("class", "dot") .attr("r", 2.5) .attr("cx", function() { return x(d.g); }) .attr("cy", function() { return y(d.wins); }); }) } function render(gameData,selections) { // Get selections var winNumber = selections.wins; var gameNumber = selections.wins + selections.losses; // Fitler data var intervalCross = crossfilter(gameData), gamesIntervalCross = intervalCross.dimension(function(d) { return +d.game_number; }); // Set empty confidence interval arrays var dotsArray = []; conf68 = [], conf95 = [], conf99 = []; // Loop over all games var g = 1; while (g <= 162) { var newG = g; // Set empty confidence interval array var CIArray = []; // Fitler and make array gamesIntervalCross.filterExact(g); var gameNumberDistribution = gamesIntervalCross.top(1000); // This checks if game 162 is missing, in a janky as hell way if (g == 1) { var totalTeams = gameNumberDistribution.length; var teamArray = _.pluck(gameNumberDistribution,'season_id') } if (g == 162 && gameNumberDistribution.length != totalTeams) { var finalTeamArray = _.pluck(gameNumberDistribution,'season_id') missing = _.difference(teamArray,finalTeamArray); gamesIntervalCross.filterExact(161); var replaceDist = gamesIntervalCross.top(1000); replaceDist.forEach(function(d) { xxx = d.season_id; if (missing.indexOf(d.season_id) >= 0) { var entry = {game_number:162, season_id:d.season_id, team:d.team, year:d.year, wins:d.wins}; gameNumberDistribution.push(entry); } }) } // Make array for dots and intervals gameNumberDistribution.forEach( function(d) { CIArray.push(+d.wins); dotEntry = {"g":g,"wins":+d.wins} dotsArray.push(dotEntry); }) getConfidenceIntervals(CIArray,g); if (g == 162) { updateTable(CIArray,maxWins,minWins); } g+=1; } // Render dots and intervals renderPolygons(conf99,"c99"); renderPolygons(conf95,"c95"); renderPolygons(conf68,"c68"); renderDots(dotsArray); drawHoverLines(conf95); drawAxisDistribution(static99,"c99",true,false,true); drawAxisDistribution(static95,"c95",true,true,false); drawAxisDistribution(static68,"c68",true,false,false); drawAxisDistribution(conf99[161],"c99",false,false,true); drawAxisDistribution(conf95[161],"c95",false,true,false); drawAxisDistribution(conf68[161],"c68",false,false,false); if (mode && mode.resize) { mode.resize(); } } function getConfidenceIntervals(data,gn) { // Get confidence interval var sd1 = getConfidenceInterval(data,1); var sd2 = getConfidenceInterval(data,1.96); var sd3 = getConfidenceInterval(data,2.6); // Create object var ci68 = {gn:gn,high:sd1.high,low:sd1.low, mean:sd1.mean} var ci95 = {gn:gn,high:sd2.high,low:sd2.low, mean:sd2.mean} var ci99 = {gn:gn,high:sd3.high,low:sd3.low, mean:sd3.mean} // Push object to intervals conf68.push(ci68) conf95.push(ci95) conf99.push(ci99) } function drawHoverLines(confidenceIntervals) { svg.call(tip); var barWidth = x(1) - x(0); svg.selectAll("rect") .data(confidenceIntervals) .enter().append("rect") .attr("class","game-bar") .attr("x",function(d) { return x(d.gn) - barWidth/2; }) .attr("y",0) .attr("height",height) .attr("width",barWidth) .on('mouseover', tip.show) .on('mouseout', tip.hide); } function updateTable(confidenceInterval,maxWins,minWins) { // Count games and get interval var teams = confidenceInterval.length; var games = selections.wins + selections.losses; var sd = getConfidenceInterval(confidenceInterval,1.96); var maxWins = Math.min(maxWins,games); var minWins = Math.max(minWins,0); if (maxWins == minWins) { d3.select('#info-bar').text("Showing " + teams + " teams with " + selections.wins + " wins in " + games + " games."); } else { d3.select('#info-bar').text("Showing " + teams + " teams with between " + minWins + " and " + maxWins +" wins in " + games + " games."); } } function cleanSpanText() { var text = document.getElementsByClassName('span-text'), i = text.length; while(i--) { text[i].parentNode.removeChild(text[i]); } } function renderPolygons(confidenceArray,polygonClass) { // Create null strings bigString = ""; bigBackwardsString = "" // Add to strings - one forward, one back confidenceArray.forEach(function(d) { var game = x(d.gn), high = y(d.high) low = y(d.low); bigString = bigString + game + "," + high + " "; bigBackwardsString = " " + game + "," + low + bigBackwardsString; }) // Combine strings fullString = bigString + bigBackwardsString // Render confidence interval polygon svg.append('polygon') .attr('class','interval ' + polygonClass) .attr('points',fullString) } function getConfidenceInterval(game,z) { average = function(a) { var r = {mean: 0, variance: 0, deviation: 0}, t = a.length; for(var m, s = 0, l = t; l--; s += a[l]); for(m = r.mean = s / t, l = t, s = 0; l--; s += Math.pow(a[l] - m, 2)); return r.deviation = Math.sqrt(r.variance = s / t), r; } var values = average(game); var mean = values.mean, high = values.mean + z*values.deviation, low = values.mean - z*values.deviation; return {mean:mean,high:high,low:low} } function addSeasonId(data) { var arr = []; data.forEach( function(d) { var seasonId = d.team.concat(d.year+""); entry = {year:d.year,team:d.team,game_number:d.game_number,wins:d.wins,season_id:seasonId} arr.push(entry) }) return arr; } function drawAxisDistribution(confidenceObj,polygonClass,isStatic,showText,drawLine) { if (isStatic == true) { var distance = 80; } else { var distance = 50; } string = "" var x1 = width + distance; var x2 = width + distance + 20; var y1 = y(confidenceObj.low); var y2 = y(confidenceObj.high); var mean = y(confidenceObj.mean); svg.append('polygon') .attr('class','interval axis-box ' + polygonClass) .attr('points',x1 + "," + y1 + " " + x1 + "," + y2 + " " + x2 + "," + y2 + " " + x2 + "," + y1 + " " + x1 + "," + y1) .style("opacity",1); svg.append('line') .attr('class','interval mean-line') .attr('x1',x1) .attr('x2',x2) .attr('y1',mean) .attr('y2',mean); if (showText == true) { showDistLabels(confidenceObj,isStatic,distance); } if (drawLine == true) { svg.append("line") .attr("class","interval") .attr("x1",function() { if (isStatic == true) { return x1 + 5; } else { return x2 - 5; } }) .attr("x2",function() { if (isStatic == true) { return x1 + 5; } else { return x2 - 5; } }) .attr("y1",-8) .attr("y2",y2-2) .style("stroke","Black"); } } function showDistLabels(confidenceObj,isStatic,distance) { var x1 = width + distance; var x2 = width + distance + 20; var y1 = y(confidenceObj.low); var y2 = y(confidenceObj.high); var mean = y(confidenceObj.mean); svg.append("text") .attr("x",function() { if (isStatic == true) { return x1; } else { return x2; } }) .attr("y",-10) .attr("class","legend dist-legend") .text(function() { if (isStatic == true) { return "All teams"; } else { return "Selected teams"; } }) .style("text-anchor",function() { if (isStatic == false) { return "end"; } }) .style("font-style","italic"); svg.append("text") .attr("x",function() { if (isStatic == true) { return x2 + 5; } else { return x1 - 5; } }) .attr("y",mean) .attr("dy", ".35em") .attr("class","legend dist-legend") .text(function() { if (isStatic == true) { return "Avg wins: " + d3.round(confidenceObj.mean,1); } else { return d3.round(confidenceObj.mean,1); } }) .style("text-anchor",function() { if (isStatic == false) { return "end"; } }); svg.append("text") .attr("x",function() { if (isStatic == true) { return x2 + 5; } else { return x1 - 5; } }) .attr("y",Math.max(y1,mean + 15)) .attr("dy", ".35em") .attr("class","legend dist-legend") .text(function() { if (isStatic == true) { return d3.round(confidenceObj.low,1); } else { return d3.round(confidenceObj.low,1); } }) .style("text-anchor",function() { if (isStatic == false) { return "end"; } }); svg.append("text") .attr("x",function() { if (isStatic == true) { return x2 + 5; } else { return x1 - 5; } }) .attr("y",Math.min(y2,mean - 15)) .attr("dy", ".35em") .attr("class","legend dist-legend") .text(function() { if (isStatic == true) { return d3.round(confidenceObj.high,1); } else { return d3.round(confidenceObj.high,1); } }) .style("text-anchor",function() { if (isStatic == false) { return "end"; } }); } function getBiggerSample(wins,games,minTeamNumber,winsCross) { var w = wins; var teams = 0; var windowSize = 1; while (teams < 10) { var filterWindow = windowSize; winsCross.filterRange([w - filterWindow,w + filterWindow]); var newIncluded = winsCross.top(100000); teams = newIncluded.length; windowSize += 1; } return { array:newIncluded,minWins:w - filterWindow,maxWins:w + filterWindow }; } </script>
{{ dataSourceName(params.queryId) }}

The dataset is too large to view in browser

Export

Looks like something went wrong with your query.

{{ DS.queryRuns[params.queryId].errorMessage }}
This query was cancelled