Search code examples
dc.jscrossfilter

dc.js create dataTable with min max avg values for three columns

Attempting to create a d3/dc/xfilter dataTable with the min, max and average values for 3 of the columns in the sample data. Been struggling for hours but unable to understand how to integrate the reduceAdd, reduceRemove, reduceInitial functions into the dataTable to create the three necessary rows.

Desired output will look something like this:

------------------------------------------
|  Value   |  Cars  |  Bikes  |  Trucks  |
------------------------------------------
|    Min   |   125  |   310   |    189   |
------------------------------------------
|    Max   |   230  |   445   |    290   |
------------------------------------------
|    Avg   |   178  |   385   |    245   |
------------------------------------------

Also cannot see how to add the first (label) column. I know reduceInitial can return an array (e.g. ['min', 'max', 'avg']) but how to reference the labels from it?

var myCSV = [	
{"shift":"1","date":"01/01/2016/08/00/00","car":"178","truck":"255","bike":"317","moto":"237"},
{"shift":"2","date":"01/01/2016/17/00/00","car":"125","truck":"189","bike":"445","moto":"273"},
{"shift":"3","date":"02/01/2016/08/00/00","car":"140","truck":"219","bike":"328","moto":"412"},
{"shift":"4","date":"02/01/2016/17/00/00","car":"222","truck":"290","bike":"432","moto":"378"},
{"shift":"5","date":"03/01/2016/08/00/00","car":"200","truck":"250","bike":"420","moto":"319"},
{"shift":"6","date":"03/01/2016/17/00/00","car":"230","truck":"220","bike":"310","moto":"413"},
{"shift":"7","date":"04/01/2016/08/00/00","car":"155","truck":"177","bike":"377","moto":"180"},
{"shift":"8","date":"04/01/2016/17/00/00","car":"179","truck":"203","bike":"405","moto":"222"},
{"shift":"9","date":"05/01/2016/08/00/00","car":"208","truck":"185","bike":"360","moto":"195"},
{"shift":"10","date":"05/01/2016/17/00/00","car":"150","truck":"290","bike":"315","moto":"280"},
{"shift":"11","date":"06/01/2016/08/00/00","car":"200","truck":"220","bike":"350","moto":"205"},
{"shift":"12","date":"06/01/2016/17/00/00","car":"230","truck":"170","bike":"390","moto":"400"},
];


dataTable = dc.dataTable('#dataTable');
lc1 = dc.lineChart("#line1");
lc2 = dc.lineChart("#line2");
lc3 = dc.lineChart("#line3");

var dateFormat = d3.time.format("%d/%m/%Y/%H/%M/%S");

myCSV.forEach(function (d) {
	d.date = dateFormat.parse(d.date);
});

myCSV.forEach(function (d) {
	d['car'] = +d['car'];
	d['bike'] = +d['bike'];
	d['moto'] = +d['moto'];
});

//console.log(myCSV);

var facts = crossfilter(myCSV);
var dateDim = facts.dimension(function (d) {return d.date});

var carDim = facts.dimension(function (d) {return d['car']});
var dgCar = dateDim.group().reduceSum(function (d) {return d['car']});

var bikeDim = facts.dimension(function (d) {return d['bike']});
var dgBike = dateDim.group().reduceSum(function (d) {return d['bike']});

var motoDim = facts.dimension(function (d) {return d['moto']});
var dgMoto = dateDim.group().reduceSum(function (d) {return d['moto']});

var minDate = new Date ("2016-01-01T08:00:00.000Z");
var maxDate = new Date ("2016-01-03T17:00:00.000Z");	

var maxY = d3.max(myCSV, function(d) {return d['car']});

function reduceAdd(i,d){ return i+1; }
function reduceRemove(i,d){return i-1; }
function reduceInitial(){ return ['min','max','avg'];}


dataTable
  .width(jsTablWidth)
  .height(400)
  .dimension(dateDim)
  .group( function(d){return '';} )
  .columns([
    {
      label: 'Value',
      format: function(d) { return dateGroup1.reduce(reduceAdd,reduceRemove,reduceInital); }
    },
    {
      label: tSel1.replace(/_/g, " "),
      format: function(d) { return //avg cars ; }
    },
    {
      label: tSel2.replace(/_/g, " "),
      format: function(d) { return //avg bikes ; }
    },
    {
      label: tSel3.replace(/_/g, " "),
      format: function(d) { return //avg moto; }
    }
  ]);


dc.renderAll();
dc.redrawAll();
svg{height:280px;}
<script src="http://cdnjs.cloudflare.com/ajax/libs/d3/3.3.3/d3.min.js"></script>
<script src="http://cdnjs.cloudflare.com/ajax/libs/crossfilter/1.3.1/crossfilter.min.js"></script>
<script src="http://dc-js.github.io/dc.js/js/dc.js"></script>
<link href="http://dc-js.github.io/dc.js/css/dc.css" rel="stylesheet"/>

<svg id="dataTable"></svg>
<svg id="line1"></svg>
<svg id="line2"></svg>
<svg id="line3"></svg>

Solution

  • Okay, hope you're okay with transposing the table across the diagonal, putting the modes of transportation as rows instead of columns. This solution is already pretty wacky without figuring that part out.

    sample output

    There's really no way to calculate of the min and max except to keep track of all the values. So we're going to use the reductions from the complex reductions example. These actually don't reduce at all, but maintain a sorted array of the filtered rows.

    We need a unique key in order to keep the sorted array (so that we remove the correct row. Luckily you have that in the shift field.

    So here are those functions, or rather functions that generate reducers given a unique key accessor.

      function groupArrayAdd(keyfn) {
      var bisect = d3.bisector(keyfn);
      return function(elements, item) {
          var pos = bisect.right(elements, keyfn(item));
          elements.splice(pos, 0, item);
          return elements;
      };
  }
  function groupArrayRemove(keyfn) {
      var bisect = d3.bisector(keyfn);
      return function(elements, item) {
          var pos = bisect.left(elements, keyfn(item));
          if(keyfn(elements[pos])===keyfn(item))
              elements.splice(pos, 1);
          return elements;
      };
  }
  function groupArrayInit() {
      return [];
  }

    Since these keep references to the entire rows, we only need one group; we'll use more specific accessors when we calculate the metrics below.

    Here we want crossfilter.groupAll, which reduces everything to one bin. This is because the rows are not partitioned by any key; every row contributes to all modes of transport:

    var filteredRows = facts.groupAll().reduce(
  groupArrayAdd(dc.pluck('shift')),
  groupArrayRemove(dc.pluck('shift')),
  groupArrayInit
);

    Now comes the most absurd part. We're going to create the fakest dimension object you ever saw. The important thing is that it's an object with a .bottom() method which dynamically calculates each of the rows:

    var fakeDim = {
  bottom: function() {
    return [
      {key: 'Car', value: filteredRows.value(), acc: dc.pluck('car')},
      {key: 'Truck', value: filteredRows.value(), acc: dc.pluck('car')},
      {key: 'Bike', value: filteredRows.value(), acc: dc.pluck('bike')},
      {key: 'Moto', value: filteredRows.value(), acc: dc.pluck('moto')}
    ];
  }
};

    Except, wait, that doesn't look like it's doing any calculation at all, just fetching values? And what's that weird acc?

    Well we're producing exactly the source data that we need to produce the table rows, and we'll use the format accessors below to actually calculate everything. We'll use the key for the "label column", we'll keep the raw rows in the value member; and we'll supply an accessor acc for computing the metrics.

    The data table definition looks like this:

    dataTable
  .width(400)
  .height(400)
  .dimension(fakeDim)
  .group( function(d){return '';} )
  .columns([
    {
      label: 'Value',
      format: function(d) { 
        return d.key;
      }
    },
    {
      label: 'Min',
      format: function(d) {
        return d3.min(d.value, d.acc);
      }
    },
    {
      label: 'Max',
      format: function(d) {
        return d3.max(d.value, d.acc);
      }
    },
    {
      label: 'Avg',
      format: function(d) {
        return d3.mean(d.value, d.acc);
      }
    }
  ]);

    Here's where all the metrics are finally calculated. We'll have all the rows available, and we have an accessor for each table row. d3-array has handy functions for computing the min, max, and average of an array. Boom, done.

    I threw a stacked chart into this fiddle for testing. (I know stacking these values probably makes no sense, it just helps to be able to filter.)

    http://jsfiddle.net/gordonwoodhull/g4xqvgvL/21/

    Rotating the dataTable

    The extra bounty on this reminded me that I never solved the table transposition problem, so I thought I'd take a look, because it's fun. I still think the bounty should go to @SergGr, but here is a solution to transpose the table, based on the categories, the dimension, and the column accessors/formatters.

    First, we're going to need the list of categories, so let's structure the categories and field names a little better:

    var categories = {  
  Car: 'car',
  Truck: 'truck',
  Bike: 'bike',
  Moto: 'moto'
};

    Now the fake dimension can be simplified, because it's generated from this category map:

    function fake_dimension(cats) {
  return {
    bottom: function() {
      return Object.keys(cats).map(function(k) {
        return {
          key: k,
          value: filteredRows.value(),
          acc: dc.pluck(cats[k])
        };
      });
    }
  };
}
var fakeDim = fake_dimension(categories);

    We need to pull the column definitions out of the chart definition, because we're going to transform them:

    var columns = [
  {
    label: 'Value',
    format: function(d) { 
      return d.key;
    }
  },
  {
    label: 'Min',
    format: function(d) {
      return d3.min(d.value, d.acc);
    }
  },
  {
    label: 'Max',
    format: function(d) {
      return d3.max(d.value, d.acc);
    }
  },
  {
    label: 'Avg',
    format: function(d) {
      return d3.mean(d.value, d.acc);
    }
  }
];

    Finally, we can write the transposition function:

    function transpose_datatable(cats, dim, cols) {
  var cols2 = d3.map(cols, function(col) { // 1
    return col.label;
  });
  return {
    dim: { // 2
      bottom: function() {
        var dall = d3.map(dim.bottom(Infinity), function(row) { // 3
          return row.key;
        });
        return cols.slice(1).map(function(col) { // 4
          var row = {
            label: col.label
          };
          Object.keys(cats).forEach(function(k) {
            row[k] = dall.get(k);
          });
          return row;
        });
      }
    },
    cols: [ // 5
      {
        label: cols[0].label,
        format: function(d) {
          return d.label;
        }
      }
    ].concat(Object.keys(cats).map(function(k) { // 6
      return {
        label: k,
        format: function(d) {
          return cols2.get(d.label).format(d[k]);
        }
      }
    }))
  };
}

var transposed = transpose_datatable(categories, fakeDim, columns)
    1. First, we're going to need a map of the original columns to their definitions, because these will become the rows. We can use a d3.map here, which acts like a well-behaved JavaScript object.
    2. We're going to create a new fake dimension, and a new array of column definitions. The fake dimension only has a .bottom() method, just like the one above.
    3. The definition of .bottom() will need all the original data, indexed by key (category name). So we'll throw that into a d3.map object as well.
    4. Now we can build the fake dimension data. The first column was just the titles (which will now be column headers), so we'll skip that. The data for the row will be the new title (former column label), and a field for each category. The fields are populated with the rows from the original dimension.
    5. The new column definitions need to replace the label, column, and the rest are generated from the category names.
    6. The label for each column is now the category name, and .format() calls the original column's format, fetching the data using the category name.

    New screenshot:

    screenshot with rotated datatable