Pivot table data transform
HTML
<script src="http://mbostock.github.com/d3/d3.js"></script>
<script src="//cdnjs.cloudflare.com/ajax/libs/underscore.js/1.3.3/underscore-min.js"></script>
CSS
table{
border-collapse: collapse;
border: 2px black solid
}
td{
border: 1px black solid;
padding: 10px;
font-size: 12px
}
JavaScript
var label = ['P', 'R', 'T', 'C', 'V'],
product = ['P1', 'P1', 'P1', 'P2'],
region = ['R1', 'R1', 'R2', 'R2'],
time = ['T1', 'T2', 'T1', 'T2'],
cost = [10, 20, 3, 4],
volume = [3, 12, 1, 40],
dataset = [product, region, time, cost, volume],
nestKeys = ['P', 'R'],
measure = 'C',
aggregateFunctions = {sum: d3.sum, max: d3.max, min: d3.min, median: d3.median, mean: d3.min},
aggregateFunction = 'sum';
// Flat data
//----------------------------
var flatTable = {};
flatTable.values = d3.transpose(dataset);
flatTable.colHeaders = label;
flatTable.rowHeaders = null;
var flatMatrix = [flatTable.colHeaders].concat(flatTable.values);
console.log('Flat', JSON.stringify(flatTable));
// Aggregated data
//----------------------------
var flatTableObject = flatTable.values.map(function (d, i) {
var result = {}
d.forEach(function (d2, i2) {
result[label[i2]] = d2
});
return result;
});
var groupedData = groupBy(flatTableObject, nestKeys);
var groupedTable = {};
groupedTable.values = d3.merge(d3.keys(groupedData).map(function (d, i) {
return d3.keys(groupedData[d]).map(function (d2, i2) {
var value = d3.values(groupedData[d])[i2];
return [d, d2, value];
});
}));
groupedTable.colHeaders = nestKeys.concat(measure);
groupedTable.rowHeaders = null;
var groupedMatrix = [groupedTable.colHeaders].concat(groupedTable.values);
console.log('Grouped', JSON.stringify(groupedTable));
function groupBy(data, nestKeys) {
var dataNest = d3.nest();
nestKeys.forEach(function (d, i) {
dataNest.key(function (d2) {
return d2[d];
});
});
dataNest.rollup(function (d, i, j) {
return aggregateFunctions[aggregateFunction](d, function (d) {
return d[measure];
});
});
return dataNest.map(data);
}
// Pivoted data
//----------------------------
var uniqueRowHeaderNames =...