Using DC.js datatable to create a table with aggregated data by year

Question

I've been trying to use dc.js and crossfilter to both build charts and tables from a certain dataset.

So far building charts works fine, but I want to use the datatable functionality to build a small html table to summarize the data as follows:

|Year|TotalEmployees| 
|2015|555| 
|2016|666| 
|2017|777|

My dataset has around 20 000 rows, here's a sample of the data:

var data = [
{"Year":"2015","Category":"1","NbEmployee":"51"},
{"Year":"2015","Category":"2","NbEmployee":"31"},
{"Year":"2015","Category":"3","NbEmployee":"14"}
{"Year":"2016","Category":"1","NbEmployee":"51"},
{"Year":"2016","Category":"2","NbEmployee":"55"},
{"Year":"2016","Category":"3","NbEmployee":"65"},
{"Year":"2017","Category":"1","NbEmployee":"76"},
{"Year":"2017","Category":"2","NbEmployee":"98"},
];

So far this piece of code returns one row of result per row of data, and although it feels like it should be a simple manipulation, I can't figure out the right syntax to build a summarized table with one row per year:

var ndx = crossfilter(data);
var tableDim = ndx.dimension(function(d) {
    return d.Year;
});

var datatable = dc.dataTable("#dc-data-table");

datatable
    .dimension(tableDim)
    .group(function(d) {
        d.NbEmployee += d.NbEmployee;
        return d.Year;
        })
    .columns([
        function(d) {return d.Year;},
        function(d) {return d.NbEmployee;},
    ]);

I've tried countless times to apply the

.group().reduceSum()

functions to the dimension into a variable and then passing it to the .group() parameter, but I always end up with a compilation error, I'm pretty clueless right now.

The SQL translation of what I'm looking for is this:

SELECT 
    Year,
    NbEmp = SUM(NbEmploye)

FROM DB

GROUP BY
    Year

ORDER BY 
    Year

Thanks in advance for your help!

score 3 · Answer 1 · answered Sep 17 '17 at 07:39

3

The dataTable's group is not a group - yes, pretty confusing to use this method to mean something completely different from what it means in all the other charts. Here, it's a function, everywhere else it's a crossfilter object.

The dataTable is unique out of the dc.js charts in that it reads its data from the .dimension() object. This is because it displays the raw rows of data, rather than aggregated data, by default.

However, it can be used to display a group instead. This works because the only method it actually calls on the dimension is .top(), if you choose to display in descending order.

If you want to display in ascending order, you can use a fake group to produce an object which supports the .bottom() method.

answered Sep 17 '17 at 07:39

Gordon

19,811
4
36
74

Thanks for the information, however I chose to build my data Table with C# Razor since I have lots of different tables to build and DC.js is sadly not as user friendly for tables as I wish it were. – Frédéric Marcotte Piché Oct 10 '17 at 15:29
1

Sure, understood - dc.js dataTable is barely a proof of concept. When people want something nicer they often move onto DataTables.js or similar, hooking it into the dc events and crossfilter data. For anyone who comes across this later, see e.g. the answers for https://stackoverflow.com/questions/21596616/dc-js-data-table-using-jquery-data-table-plugin, which could be improved but basically the right idea. – Gordon Oct 10 '17 at 15:42
@AlokReddyKatanguri Have you tried using it on React.JS? – PrivateOmega May 14 '19 at 09:44
@PrivateOmega nopes I have used it standalone. For react better options are available on npm – Alok Reddy Kattangoori Jun 06 '19 at 10:37

Using DC.js datatable to create a table with aggregated data by year

1 Answers1