Transforming a dataset with crossfilter

Question

I'm trying to create a dashboard using dc.js and I want to customize how a data table is visualized. So my data looks something like this

agegroup    gender  group   scores  total
  18-24       M      1       0.04    1
  45-54       F      2       2.23    13
  25-34       M      1       0.74    6
  25-34       M      2       1.47    8
  18-24       F      1       2.88    7
  35-44       F      2       3.98    14

When I initialize a data table, it'll look the same as my original csv. However what if I want

 agegroup    gender  group1.scores   group1.total  group2.scores  group2.total
      18-24       M      0.04              1           0.0               0
      18-24       F      2.88              1           0.0               0
      25-34       M      0.74              8           1.47              8
      25-34       F      0.0               0           0.0               0

Here is how I initalize and set up my data table

dataTable = dc.dataTable('#data-table');
var tableDim = ndx.dimension(function(d) {
          return d.gender;
        });
      dataTable
            .width(400)
            .height(800)
            .dimension(tableDim)
              .group(function(d){
                return  "Counts"
              })
              .size(20)
            .columns([
                function(d){
                  return d.gender;},
                function(d){
                  return d.agegroup;
                },
                function(d){
                  return d.group;
                },
                function(d){
                  return d.scores;
                },
                function(d){
                  return d.total;
                }, 
            ])
            .order(d3.ascending)
            .sortBy(function(d){
              return d.gender;
            });

I know that crossfilter allows you to filter and subset data quickly but I'm not sure how it'll function transforming datasets. Thanks!

Answer 1

So far, I was able to do this for now.

var tableDim = ndx.dimension(function (d) {
    return d.agegroup;
});

var dataTable = dc.dataTable("#someTable");
dataTable.width(300).height(800)
    .dimension(tableDim)
    .group(function (d) {
    return "Counts";
})
    .columns([
function (d) {
    return d.agegroup;
},
function (d) {
    return d.gender;
},
function (d) {
    if (d.group == 1) return d.scores;
    else return 0;
},
function (d) {
    if (d.group == 1) return d.total;
    else return 0;
},
function (d) {
    if (d.group == 2) return d.scores;
    else return 0;
},
function (d) {
    if (d.group == 2) return d.total;
    else return 0;
}]);

dc.renderAll();

Here is the JSFiddle working with the above code. Use this or make a new one next time when you are asking for such solutions on SO.

Remember, using dc.dataTable you may not be able to reduce the number of rows in the data set. If you really want to reduce the number of rows you may try group().reduce() methods and create new fields for group1.total, group1.scores etc..