A visualization of the unfunded accrued actuarial pension liability for public pensions.
Data shown is for the Texas Teacher's Retirement System.
Visualization based on original by Mary Pat Campbell: http://www.actuarialoutpost.com/actuarial_discussion_forum/showthread.php?t=287451
| <!DOCTYPE html> | |
| <meta charset="utf-8"> | |
| <style> | |
| body { | |
| font-family: "Helvetica Neue", Helvetica, Arial, sans-serif; | |
| } | |
| .axis text { | |
| font: 10px sans-serif; | |
| } | |
| .axis path, | |
| .axis line { | |
| fill: none; | |
| stroke: #000; | |
| stroke-width: 0.5; | |
| shape-rendering: crispEdges; | |
| } | |
| .signed rect.bar.total { | |
| fill: rgb(77,77,77); | |
| } | |
| .signed rect.bar.positive { | |
| fill: rgb(26,150,65); | |
| } | |
| .signed rect.bar.negative { | |
| fill: rgb(215,25,28); | |
| } | |
| </style> | |
| <body> | |
| <form> | |
| <input type="radio" name="mode" id="mode-type" value="type" checked> | |
| <label for="mode-type">Changes by Type</label> | |
| <input type="radio" name="mode" id="mode-sign" value="sign" checked> | |
| <label for="mode-sign">Changes by Sign</label> | |
| <input type="radio" name="mode" id="mode-total" value="total" > | |
| <label for="mode-total">Year End Total</label> | |
| </form> | |
| <script src="http://d3js.org/d3.v3.min.js"></script> | |
| <script src="http://d3js.org/colorbrewer.v1.min.js"></script> | |
| <script src="waterfall.js"></script> | |
| </body> |
| Year | Begin | Investments | Benefits | Contributions | Other | Total | End | |
|---|---|---|---|---|---|---|---|---|
| 1994 | 3.44 | -0.643 | 0 | -0.117 | -1.855 | -2.615 | 0.825 | |
| 1995 | 0.825 | -1.436 | 2.255 | -0.188 | 0.5 | 1.131 | 1.956 | |
| 1996 | 1.956 | -1.304 | 0 | 0.082 | 1.079 | -0.143 | 1.813 | |
| 1997 | 1.813 | -2.836 | 1.762 | 0.117 | -0.71 | -1.667 | 0.146 | |
| 1998 | 0.146 | -2.832 | 0 | -0.059 | 0.282 | -2.609 | -2.463 | |
| 1999 | -2.463 | -4.812 | 5.611 | 0.292 | -0.818 | 0.273 | -2.19 | |
| 2000 | -2.19 | -5.417 | 0 | -0.115 | 2.276 | -3.256 | -5.446 | |
| 2001 | -5.446 | -1.894 | 4.553 | -0.292 | 0.944 | 3.311 | -2.135 | |
| 2002 | -2.135 | 5.527 | 0 | 0.017 | -0.122 | 5.422 | 3.287 | |
| 2003 | 3.287 | 1.965 | 0 | 0.544 | -0.566 | 1.943 | 5.23 | |
| 2004 | 5.23 | 4.719 | 0 | 0.088 | -2.084 | 2.723 | 7.953 | |
| 2005 | 7.953 | 4.068 | 0 | 1.605 | -0.43 | 5.243 | 13.196 | |
| 2006 | 13.196 | -0.264 | 0 | 0.508 | 0.254 | 0.498 | 13.694 | |
| 2007 | 13.694 | -4.14 | 0.36 | 0.496 | 2.135 | -1.149 | 12.545 | |
| 2008 | 12.545 | -1.232 | 0 | 0.496 | -0.286 | -1.022 | 11.523 | |
| 2009 | 11.523 | 10.321 | 0 | 0.496 | -0.694 | 10.123 | 21.646 | |
| 2010 | 21.646 | 1.161 | 0 | 0.797 | -0.705 | 1.253 | 22.899 | |
| 2011 | 22.899 | 1.958 | 0 | 0.826 | -1.621 | 1.163 | 24.062 | |
| 2012 | 24.062 | 2.208 | 0 | 1.258 | -1.427 | 2.039 | 26.101 | |
| 2013 | 26.101 | 2.045 | 0.36 | 1.911 | -1.481 | 2.835 | 28.936 | |
| 2014 | 28.936 | -1.095 | 0 | 1.157 | 2.64 | 2.702 | 31.638 |
| var margin = {top: 10, right: 0, bottom: 50, left: 40}, | |
| width = 900 - (margin.left + margin.right), | |
| width1 = width, | |
| width2 = width, | |
| height = 500 - 2 * (margin.top + margin.bottom), | |
| height1 = 300 - (margin.top + margin.bottom), | |
| height2 = height - height1, | |
| padding = 0.8; | |
| var speed = 1500; | |
| var reverseYAxis = true; | |
| var x1 = d3.scale.ordinal() | |
| .rangeBands([0, width1]); | |
| var x2 = d3.scale.ordinal(); | |
| var y1 = d3.scale.linear() | |
| .range([height1, 0]); | |
| var y2 = d3.scale.linear() | |
| .range([height2, 0]); | |
| if(reverseYAxis){ | |
| y1.range(y1.range().reverse()); | |
| y2.range(y2.range().reverse()); | |
| }; | |
| var x1Axis = d3.svg.axis() | |
| .scale(x1) | |
| .orient("bottom"); | |
| var y1Axis = d3.svg.axis() | |
| .scale(y1) | |
| .orient("left") | |
| .tickFormat(function(d) { return dollarFormatter(d); }); | |
| var x2Axis = d3.svg.axis() | |
| .scale(x2) | |
| .orient("bottom"); | |
| var y2Axis = d3.svg.axis() | |
| .scale(y2) | |
| .orient("left") | |
| .tickFormat(function(d) { return dollarFormatter(d); }); | |
| var svg = d3.select("body").append("svg") | |
| .attr("width", width + margin.left + margin.right) | |
| .attr("height", height + 2 * (margin.top + margin.bottom)); | |
| var chart = svg.append("g") | |
| .attr("transform", "translate(" + margin.left + "," + margin.top + ")") | |
| .attr("class", "signed"); | |
| var signFunc = function(value){ | |
| if((value >= 0 && !reverseYAxis) || | |
| (value < 0 && reverseYAxis)){ | |
| return "positive"; | |
| } else { | |
| return "negative"; | |
| } | |
| } | |
| var tipText = function(d){ | |
| return d.year + " " + d.type + ":<br/>" + dollarFormat(d.value) + "M"; | |
| } | |
| //var tooltips = d3.tip().html(tipText); | |
| // svg.call(tooltips); | |
| // Transform data (i.e., finding cumulative values and total) for easier charting | |
| var transformData = function(raw) { | |
| var cumulative = 0; | |
| var data = []; | |
| // Get list of change reasons | |
| var not_changes = ["Year", "Begin", "End"], | |
| changes = d3.keys(raw[0]).filter(function(d){ return not_changes.indexOf(d) < 0; }); | |
| // Add the initial value | |
| data.push({ | |
| year: raw[0].Year - 1 + "", | |
| type: "YearEnd", | |
| sign: "total", | |
| start: 0, | |
| end: +raw[0].Begin, | |
| value: +raw[0].Begin, | |
| }); | |
| cumulative = +raw[0].Begin; | |
| for (var i = 0; i < raw.length; i++) { | |
| // Add a bar for each of the different changes | |
| changes.forEach(function(c){ | |
| var d = {}; | |
| d.year = raw[i].Year, | |
| d.type = c; | |
| if(d.type != "Total"){ | |
| d.start = cumulative | |
| d.value = +raw[i][c] | |
| cumulative += d.value | |
| d.end = cumulative | |
| d.sign = signFunc(d.value) | |
| } else { | |
| d.start = +raw[i].Begin | |
| d.end = +raw[i].End | |
| d.value = d.end - d.start | |
| d.sign = signFunc(d.value) | |
| } | |
| data.push(d); | |
| }); | |
| // Add a bar for the year-end amount | |
| data.push({ | |
| year: raw[i].Year, | |
| type: "YearEnd", | |
| sign: 'total', | |
| start: 0, | |
| end: cumulative, | |
| value: cumulative | |
| }); | |
| } | |
| return data; | |
| }; | |
| d3.csv("texas_trs.csv", function(error, raw) { | |
| // parse the data | |
| var keys = d3.keys(raw[0]), | |
| amounts = keys.filter(function(k){ return k != "Year"; }), | |
| notChanges = ["Year", "Begin", "End"], | |
| changes = keys.filter(function(d){ return notChanges.indexOf(d) < 0; }), | |
| indivChanges = changes.filter(function(d){ return d != "Total"; }) | |
| raw.forEach(function(d){ | |
| amounts.forEach(function(k){ | |
| d[k] = +d[k]; | |
| }); | |
| }) | |
| // Transform data (i.e., finding cumulative values and total) for easier charting | |
| var data = transformData(raw); | |
| var nested = d3.nest() | |
| .key(function(d) {return d.type;}) | |
| .entries(data.filter(function(d) { return d.type != "YearEnd"; })); | |
| // Add a subchart for each reason for change | |
| width2 = (width + (margin.left + margin.right)) / changes.length - (margin.left + margin.right); | |
| // determine bounds of each chart | |
| var rangeByYear = raw.map(function(d) { | |
| var runTotal = [+d.Begin]; | |
| indivChanges.forEach(function(c, i) { | |
| runTotal.push(runTotal[i] + d[c]); | |
| }); | |
| return runTotal; | |
| }); | |
| var y1ext = d3.extent(rangeByYear.reduce(function(a, b) { | |
| return a.concat(b); | |
| })); | |
| y1max = d3.max(data, function(d){ return Math.max(d.start, d.end); }); | |
| y1min = d3.min(data, function(d){ return Math.min(d.start, d.end); }); | |
| var y2max = d3.max(raw, function(d) { | |
| return d3.max(changes.map(function(c){ | |
| return d[c]; | |
| })); | |
| }); | |
| var y2min = d3.min(raw, function(d) { | |
| return d3.min(changes.map(function(c){ | |
| return d[c]; | |
| })); | |
| }); | |
| var years = data.map(function(d){ return d.year; }), | |
| types = data.map(function(d){ return d.type; }); | |
| var typeColor = d3.scale.ordinal() | |
| .domain(types) | |
| .range(["rgb(77,77,77)"].concat(colorbrewer.Set2[8])); | |
| types.push(types.shift(1)); | |
| x1.domain(years); | |
| y1.domain([y1min, y1max]).nice(); | |
| x2.rangeBands([0, width2]) | |
| .domain(years.slice(1)); | |
| y2.domain([y2min, y2max]).nice(); | |
| var x1Type = d3.scale.ordinal() | |
| .rangeBands([0, x1.rangeBand()]) | |
| .domain(types.filter(function(t) { return t != "Total"; })); | |
| x2Axis.tickValues(x2.domain().filter(function(d, i) { return !(i % 2); })) | |
| var relEndWidth = 1.0, | |
| n = changes.length - 1, | |
| m = n + relEndWidth, | |
| barWidth = (x1.rangeBand() / (n + relEndWidth)); | |
| chart.append("g") | |
| .attr("class", "x axis") | |
| .attr("transform", "translate(0," + height1 + ")") | |
| .call(x1Axis); | |
| chart.append("g") | |
| .attr("class", "y axis") | |
| .call(y1Axis); | |
| chart.append("g") | |
| .append("line") | |
| .attr("stroke", "#222") | |
| .attr("stroke-width", 0.5) | |
| .attr("x1", 0) | |
| .attr("x2", width1) | |
| .attr("y1", y1(0)) | |
| .attr("y2", y1(0)); | |
| var mainHover = chart.selectAll("g.group") | |
| .data(data.filter(function(d) { return d.type != "Total"; })) | |
| .enter() | |
| .append("g") | |
| .attr("class", function(d){ return "group " + d.type + "-type " + d.sign; }) | |
| .attr("transform", function(d){ return "translate(" + x1(d.year) + ",0)"; }); | |
| mainHover.append("rect") // for capturing hover events | |
| .attr('width', x1.rangeBand()) | |
| .attr('height', height1) | |
| .attr('fill', 'none') | |
| //.attr('stroke', "#000") | |
| //.attr('stroke-width', 1) | |
| .attr('pointer-events', 'all') | |
| .on("mouseover", hoverMainOn) | |
| .on("mouseout", hoverOff); | |
| var mainTips = mainHover.selectAll("rect").append("g") | |
| var mainBars = mainHover.append("rect") | |
| .attr("class", function(d){ return "bar " + d.type + "-type " + d.sign; }) | |
| .attr("fill", function(d){ return typeColor(d.type); }) | |
| .attr("x", 0) | |
| .attr("width", function(d){ | |
| if(d.type == "YearEnd"){ | |
| return x1.rangeBand(); | |
| } else { | |
| return 0; | |
| }}) | |
| .attr("y", function(d){ return y1(d.start); }) | |
| .attr("height", 0) | |
| mainBars.transition() | |
| .duration(speed) | |
| .attr("y", function(d){ return Math.min(y1(d.start), y1(d.end)); }) | |
| .attr("height", function(d){ return Math.abs(y1(d.start) - y1(d.end)); }) | |
| mainBars.on("mouseover", hoverMainOn) | |
| .on("mouseout", hoverOff) | |
| mainBars.filter(function(d) { return d.type != "YearEnd" }).append("line") | |
| .attr("class", "connector") | |
| .attr("x1", function(d){ return x1(d.year) + x1Type(d.type) - (x1.rangeBand() - x1Type.rangeBand()) / 2; }) | |
| .attr("x2", function(d){ return x1(d.year) + x1Type(d.type) - (x1.rangeBand() + x1Type.rangeBand()) / 2; }) | |
| .attr("y1", function(d){ return y1(d.end); }) | |
| .attr("y2", function(d){ return y1(d.end); }); | |
| // Add small multiples - changes by type of change | |
| var subcharts = svg.selectAll("g.subchart") | |
| .data(nested); | |
| subcharts.enter().append("g") | |
| .attr("class", "subchart") | |
| .attr("transform", function(d, i){ | |
| return "translate(" + ((i + 1) * margin.left + i * (margin.right + width2)) + "," + | |
| (height1 + 2 * margin.top + margin.bottom) + ")"; | |
| }); | |
| // Add x-axes and rotate text | |
| subcharts.append("g") | |
| .attr("class", "x axis") | |
| .attr("transform", "translate(0," + height2 + ")") | |
| .call(x2Axis) | |
| .selectAll("text") | |
| .attr("y", 0) | |
| .attr("x", -9) | |
| .attr("dy", ".35em") | |
| .attr("transform", "rotate(270)") | |
| .style("text-anchor", "end"); | |
| // Add y-axes | |
| subcharts.append("g") | |
| .attr("class", "y axis") | |
| .call(y2Axis); | |
| // Add line at zero | |
| subcharts.append("g") | |
| .append("line") | |
| .attr("stroke", "#222") | |
| .attr("stroke-width", 0.5) | |
| .attr("x1", 0) | |
| .attr("x2", width2) | |
| .attr("y1", y2(0)) | |
| .attr("y2", y2(0)); | |
| // Add title for chart | |
| subcharts.append("text") | |
| .attr("y", 0) | |
| .attr("x", width2 / 2) | |
| .attr("text-anchor", "middle") | |
| .text(function(d) {return d.key;}) | |
| var subBars = subcharts.selectAll("g.bar") | |
| .data(function(d) {return d.values}); | |
| var subHover = subBars.enter() | |
| .append("g") | |
| .attr("class", function(d){ return "bar " + d.type + "-type " + d.sign; }) | |
| .attr("transform", function(d){ return "translate(" + x2(d.year) + ",0)"; }) | |
| subHover.append("rect") // for capturing hover events | |
| .attr('width', x2.rangeBand()) | |
| .attr('height', height2) | |
| .attr('fill', 'none') | |
| .attr('pointer-events', 'all') | |
| .on("mouseover", hoverSubOn) | |
| .on("mouseout", hoverOff) | |
| subHover.append("rect") | |
| .attr("fill", function(d){ return typeColor(d.type); }) | |
| .attr("x", 0) | |
| .attr("width", x2.rangeBand()) | |
| .attr("y", y2(0)) | |
| .attr("height", 0) | |
| .attr('pointer-events', 'none') | |
| .transition() | |
| .delay(speed * 3 / 2) | |
| .duration(speed) | |
| .attr("y", function(d){ return Math.min(y2(0), y2(d.value)); }) | |
| .attr("height", function(d){ return Math.abs(y2(d.end) - y2(d.start)); }); | |
| function hideChanges(delay){ | |
| mainHover // for capturing hover events | |
| .transition() | |
| .delay(delay) | |
| .duration(speed) | |
| .attr("transform", function(d){ return "translate(" + x1(d.year) + ",0)"; }) | |
| .selectAll("rect") | |
| .attr("width", x1.rangeBand()); | |
| mainBars.transition() | |
| .delay(delay) | |
| .duration(speed) | |
| .attr("width", function(d){ | |
| if(d.type == "YearEnd"){ | |
| return x1.rangeBand(); | |
| } else { | |
| return 0; | |
| }}); | |
| } | |
| function showChanges(delay){ | |
| mainHover.transition() | |
| .delay(delay) | |
| .duration(speed) | |
| .attr("transform", function(d){ return "translate(" + (x1(d.year) + x1Type(d.type) - | |
| (x1.rangeBand() - x1Type.rangeBand()) / 2) + ",0)"; }) | |
| .attr("width", x1Type.rangeBand()); | |
| mainBars.transition() | |
| .delay(delay) | |
| .duration(speed) | |
| //.attr("x", function(d){ return x1(d.year) + x1Type(d.type) - (x1.rangeBand() - x1Type.rangeBand()) / 2; }) | |
| .attr("width", x1Type.rangeBand()); | |
| } | |
| showChanges(speed * 3 / 2); | |
| function hoverMainOn(h) { | |
| mainBars | |
| .filter(function(d) { return (d.year != h.year) || (d.type != h.type); }) | |
| .attr("opacity", 0.4); | |
| subBars | |
| .filter(function(d){ return d.year != h.year; }) | |
| .attr("opacity", 0.4); | |
| //tooltips.show(h); | |
| } | |
| function hoverSubOn(h) { | |
| mainBars | |
| .filter(function(d) { return (d.year != h.year) || ((d.type != h.type) && (h.type != "Total")); }) | |
| .attr("opacity", 0.4); | |
| subBars | |
| .filter(function(d){ return d.year != h.year; }) | |
| .attr("opacity", 0.4); | |
| //tooltips.show(h); | |
| } | |
| function hoverOff(h) { | |
| mainBars.attr("opacity", 1.0); | |
| subBars.attr("opacity", 1.0); | |
| } | |
| d3.selectAll("input[name=mode]") | |
| .on("change", function() { | |
| console.log(this.value); | |
| if(this.value == "total"){ | |
| hideChanges(0); | |
| } else if(this.value == "sign") { | |
| showChanges(0); | |
| chart.classed("signed", true); | |
| subcharts.classed("signed", true); | |
| } else if(this.value == "type") { | |
| showChanges(0); | |
| chart.classed("signed", false); | |
| subcharts.classed("signed", false); | |
| } | |
| }); | |
| }); | |
| var dollarFormat = d3.format("$,.2f"); | |
| function dollarFormatter(n) { | |
| n = Math.round(n); | |
| var result = n; | |
| if (Math.abs(n) > 1000) { | |
| result = Math.round(n/1000) + 'K'; | |
| } | |
| return '$' + result; | |
| } |