Santosh Sewlal Santosh Sewlal - 8 months ago 51
Javascript Question

dc.js table on aggregated data

I've been trying to get my head around dc.js by duplicating the code from here,, with my own data.

{"grade": 8, "category": "Math", "rating": 4},
{"grade": 8, "category": "English", "rating": 3},
{"grade": 8, "category": "Math", "rating": 1},
{"grade": 8, "category": "Math", "rating": 3},
{"grade": 8, "category": "Science", "rating": 1},
{"grade": 9, "category": "Science", "rating": 2},
{"grade": 9, "category": "Science", "rating": 5},
{"grade": 9, "category": "English", "rating": 5}

with the result being a table like this:

8 English 3
8 Math 2.67
8 Science 1
9 English 5
9 Science 3.5

I've put this together

var chart = dc.dataTable("#test");

d3.json("test.json", function(error, experiments) {


var ndx = crossfilter(experiments),
exptDimension = ndx.dimension(function(d) {return +d.grade;}),
groupedDimension =
function (p, v) {
++p.number; += +v.rating;
p.category = v.category;
p.grade = v.grade;
p.avg = ( / p.number).toFixed(2);
return p;
function (p, v) {
--p.number; -= +v.rating;
p.category = v.category;
p.grade = v.grade;
p.avg = (p.number == 0) ? 0 : Math.round( / p.number).toFixed(2);
return p;
function () {
return {number: 0, total: 0, avg: 0, category:"", grade:"", }
rank = function (p) { return "rank" };

.columns([function (d) { console.log(d); return d.value.grade },
function (d) { return d.value.category },
function (d) { return d.value.avg }])
.sortBy(function (d) { return d.value.avg })

with this result:

9 English 4.00
8 Science 2.40

I feel like i'm missing something really simple. Does anybody have a suggestion on how to proceed?


Answer Source

Yeah, it is simple - or mostly simple. There's an implicit requirement that you want to group by both grade and category, whereas you were grouping by grade only. Your reduce functions wouldn't make a lot of sense that way, because

          p.category = v.category;

assumes there's only one category per grade. But if you ignore that, the answer was sort of "correct", in that the average rating for 9th graders is 4, and the average rating for 8th graders is 2.4.

To group by both grade and category, you can specify a dimension key with both values in it:

      exptDimension    = ndx.dimension(function(d) {return d.category+'/'+d.grade;}),

This constructs a string with both values in it. Some people just make an array key here, but I don't like that because it will do some implicit conversions to string, which is a bit slower.

So that was easy but it only orders by average rating:

9   English 5.00
9   Science 3.50
8   English 3.00
8   Math    2.67
8   Science 1.00

Another implicit requirement is that you want to order by grade in ascending order, and average rating in descending order. This is a bit tougher, because it means you need to specify a single key that encodes two opposite orders.

Here's one way to do it:

      .sortBy(function (d) { return (20-d.value.grade)*1000 + d.value.avg; })

Yeah. Wow. That's going to be fun to maintain, but I don't know a better way to do it. It creates a sort key where grade is highly negative-weighted, and avg is weakly positive-weighted, which when combined with d3.descending, will do what you want.

End result:

8   English 3.00
8   Math    2.67
8   Science 1.00
9   English 5.00
9   Science 3.50