Using Google Chart/HighChart scatterplot

[nascif]

Hi Nicolas,

First of all, thanks for the awesome project, it is amazing how much functionality one can get out of it with very little setup. Well done!

I have a question about the Google Chart renderer. I noticed that I can double click on the chart to get a chart type selector, with a number of options in addition to the ones surfaced by default. I was able to use this method to convert a bar chart into a pie chart, for example.

But I can't use it to create a scatter plot. It complains about the data format, saying I need to supply two numeric columns - which I did, in all combinations I could thing of. Would you have any idea on why that could be happening?

It would be great if the scatter plot could be make a top level visualization (like the bar and line charts). The problem with this secondary selection method is that the pivottable will revert back to the primary chart type as soon as you make a change to any of the data role assignments.

Cheers, /Nascif

[nicolaskruchten]

Hmm, I wrote a reply but Github seems to have eaten it :(

Thanks for the kind words about this project!

I agree that a scatterplot might be a good addition to this library but I'm not clear on how to construct a pivot table whose output is compatible with the input to Google Charts. Would you put the x-axis on the columns and the y-axis on the rows and use a count aggregator? What would the aggregator signify?

Looking forward to your thoughts, Nicolas

[nascif]

Hi Nicolas,

Yes, I think that would be the most natural format. The challenge might be to convert this arrangement to two columns, which seems to be what Google Charts expects for a scatter. Could you use the chart type as a hint to change the mapping? I understand it might be trick given how you made an abstract interface between the pivottable and its renderers - which is really cool, btw.

As for the aggregator, scatters don't really use them for display (position or size). Two options come to mind:

1) Use them for color - say you are displaying stores by their coordinates (though of course a geo map would be better, just go with it for the sake of the example), the aggregated value could indicate the total sales of that particular store (a single color scale varying on saturation would be ideal). For aggregators that return categorical values (like listUnique, I believe) you could use the color to differentiate among them (say McDonald's orange vs Wendy's red).

2) Switch to bubble plot if there is an aggregator. Or always use the bubble plot, and treat the lack of aggregation (count) as a special case where all markers have the same size.

Cheers,   Nascif On Saturday, April 26, 2014 11:51 AM, Nicolas Kruchten notifications@github.com wrote:

Hmm, I wrote a reply but Github seems to have eaten it :( Thanks for the kind words about this project! I agree that a scatterplot might be a good addition to this library but I'm not clear on how to construct a pivot table whose output is compatible with the input to Google Charts. Would you put the x-axis on the columns and the y-axis on the rows and use a count aggregator? What would the aggregator signify? Looking forward to your thoughts, Nicolas — Reply to this email directly or view it on GitHub.

[nascif]

Thanks for the detailed example. I have a better understanding of the issue now - there is a transformation that always takes place, assuming the aggregation associated with a pivot table, and the renderer is called after that. That means there is a limitation on which visualizations can be used - even if the number of options is quite high.

Would it be feasible to have a flag to turn this behavior off, to allow for non-aggregated visualizations? Or to capture this behavior in a base AggregatedDataRenderer class, that extends a more generic DataRenderer? This would allow the use of the really cool infrastructure you put together to select variables to display other types of visualizations, that have to do aggregations their own way (or not at all).

We already talked by scatter plots and bubble plots. Another example: imagine that I want to compare distributions. Histograms and boxplots are ideal for that, but they require a completely different type of aggregation.

I noticed that you already thought about it and added support for binning through calculated columns. Problem with that is that they are only available at configuration time, so an end user can't bin on something that the programmer didn't make available. But it is nice that you can then turn around and use them in a group-by, very useful. 

Another example is showing relationships, like a network diagram, chord diagram or sankey. The treemap and the pivottable can show trees, but generic graphs are a different animal. :-) D3 has awesome examples of these.

If you decouple the aggregation from the variable selection and move it closer to the visualization itself, you would be moving in the direction of a generic data visualization framework.

Cheers, and thanks for your prompt reply,   Nascif

PS: Different topic: I would like to display values of a particular category as links, so that a user can click on them and launch an appropriate web page based on that value. This is a natural flow, I believe; you use the pivotable as a kind of data exploratory tool, and eventually you find something - a user name, a defect identifier, a city - that requires further investigation outside of the tool. I looked at the derived attributes feature and it seems to be the way to go - but could I use them to generate HTML fragments? I tried a similar path (pre-processing the values so that pivottable got a column of value) but the framework converted them to regular 'value' strings. Makes sense - you want to make sure that dirty data won't compromise your rendering - but what if I want to do it on purpose?

The idea is not only that I can see the aggregated values, but click on them to "drill down" to the detailed level behind them.

On Saturday, April 26, 2014 11:51 AM, Nicolas Kruchten notifications@github.com wrote:

Hmm, I wrote a reply but Github seems to have eaten it :( Thanks for the kind words about this project! I agree that a scatterplot might be a good addition to this library but I'm not clear on how to construct a pivot table whose output is compatible with the input to Google Charts. Would you put the x-axis on the columns and the y-axis on the rows and use a count aggregator? What would the aggregator signify? Looking forward to your thoughts, Nicolas — Reply to this email directly or view it on GitHub.

[bluechips23]

I think a lot of the questions asked here can be solved by a few little tweaks and some minor customized enhancements.

1) About the charts, one primary reason I chose not to use Google Charts is because I can't control them 100%. That's why I wrote my own HighCharts module. Enclosed is a sample here on how I am using HighCharts instead of Google charts. Using HighCharts, you can easily draw Scatterplots (or any of the mainstream charts) without tweaking much on pivot.js (except the part where you have to draw the HighChart instead of the Google charts).

function getChart(chartType, dataArray, metaData) {

    switch(chartType) {
        case "bar":
            return getBarChart(dataArray, metaData);
            break;

        case "column":
            return getColumnChart(dataArray, metaData);
            break;

        case "stackedcolumn":
            return getStackedColumnChart(dataArray, metaData);
            break;

        case "line":
            return getLineChart(dataArray, metaData);
            break;

        case "area":
            return getAreaChart(dataArray, metaData);
            break;
    }

}

function getBarChart(dataArray, metaData) {
    var chartTitle = metaData[0];
    var xAxisTitle = metaData[1];
    var yAxisTitle = metaData[2];

    var myOptions = {
        chart: {
            renderTo: 'container',
            type: 'bar'
        },
        title: {
            text: chartTitle
        },
        xAxis: {
            categories: []
        },
        yAxis: {
            title: {
                text: yAxisTitle
            }
        },
        plotOptions: {
            series: {
                shadow: true
            }
        },
        series:[]
    };

    var headers = new Array();

    headers = dataArray[0];

    console.log("GetCharts: dataArray.length: " + dataArray.length);

    for(var i = 1; i < headers.length; i++) {
        myOptions.xAxis.categories.push(headers[i]);
    }

    for(var i = 1; i < dataArray.length; i++) {
        var currentArray = new Array();
        currentArray = dataArray[i];
        var categoryName = currentArray[0];
        var categoryData = new Array();
        //console.log("CategoryName: " + categoryName + " for data: " + dataArray[i]);
        for(var j = 1; j < currentArray.length; j++) {
            categoryData.push(currentArray[j]);
            //console.log("Category Data: " + currentArray[j]);
        }

        myOptions.series.push({'name': categoryName, 'data': categoryData});
    }

    //console.log("Bar Chart Options: " + JSON.stringify(myOptions));

    return myOptions;
}

The following is the change you would need to make inside the gcharts_renders.js file instead of using the google charts:

          var chartContainerDiv = $("<div id='container'>");
      $('body').append(chartContainerDiv);
          var chartContainer = document.getElementById('container');

           \\I have no idea why I had to use an empty 
          \\element to add the charts to it, but this the only way I could draw the charts. Without this empty        
          \\element, my charts wouldn't draw. If you come up with a better solution to draw the highcharts,
          \\let me know.
          var emptyElement = $("<span>&nbsp;</span>");

          $('body').append(emptyElement);
          result = $("<div class='resultClass'>");
      var metaData = new Array();
      metaData[0] = title;
      metaData[1] = groupByTitle;
      metaData[2] = vAxisTitle;

      var chartResults = getChart(chartType, dataArray, metaData);
      //console.log("Chart Results in jChartRenders: " + JSON.stringify(chartResults));
      var chart = new Highcharts.Chart(chartResults);
      emptyElement.append(chartContainer);
      result.append(emptyElement);

      return result;

2) I had the same exact requirement about implementing hyperlinks within the data type. This is more of a specialized tweak. The following code snippets are the changes I did in the pivot.js to implement that:

if(colKey[j].indexOf("http") > -1) {
            urlTxt = $('<a/>', {
                        name: 'link',
                        href: colKey[j],
                        text: colKey[j],
                        target: '_blank'
                    });     
            th = $("<th class='pvtColLabel'>").attr("colspan", x);
            th.append(urlTxt);

You also need to do the same for the row keys as well:

        if (x !== -1) {
          if(txt.indexOf("http") > -1) {
            urlTxt = $('<a/>', {
                        name: 'link',
                        href: txt,
                        text: txt,
                        target: '_blank'
                    });
            th = $("<td class='pvtRowLabel'>").attr("rowspan", x);
            th.append(urlTxt);
          } else {
            th = $("<td class='pvtRowLabel'>").text(txt).attr("rowspan", x);
          }
          if (parseInt(j) === rowAttrs.length - 1 && colAttrs.length !== 0) {
            th.attr("colspan", 2);
          }
          tr.append(th);    
        }

Note: The code in the most recent release of pivot.js may have changed as I haven't updated it since last few releases.

Hope this helps.

[nicolaskruchten]

Thanks for your comments, both!

I'll see what I can do about implementing support for scatter/bubble charts with Google charts along the lines above, which make a fair bit of sense.

Your thoughts about a more generalized visualization system are interesting, and I've also thought about how to extend this library in that direction, but I think I would likely create a new project to do this rather than making such big modifications to a system whose primary virtue is its simplicity :)

Regarding drill-through, there is already an issue open for this here #33

Regarding HighCharts, @bluechips23 would you be willing to contribute your renderer plugins to this project under an MIT license?

[bluechips23]

I would be happy to!

Thanks,

Sudip

Sent from my iPhone. Please excuse the brevity, spelling and punctuation.

On Apr 26, 2014, at 2:00 PM, Nicolas Kruchten notifications@github.com wrote:

Thanks for your comments, both!

I'll see what I can do about implementing support for scatter/bubble charts with Google charts along the lines above, which make a fair bit of sense.

Your thoughts about a more generalized visualization system are interesting, and I've also thought about how to extend this library in that direction, but I think I would likely create a new project to do this rather than making such big modifications to a system whose primary virtue is its simplicity :)

Regarding drill-through, there is already an issue open for this here #33

Regarding HighCharts, @bluechips23 would you be willing to contribute your renderer plugins to this project under an MIT license?

— Reply to this email directly or view it on GitHub.

[nascif]

Awesome, thanks both! Will look forward for the new features!

Regarding the drill-through (and having not read the issue yet) one idea is to create a variable value renderer, along the lines of the aggregated item. It could introduce a new column, or perhaps simply decorate an existing one. The configuration would take a variable name and a function that takes a value of that variable and returns an appropriate URL based on that value. At rendering time (thinking of tables here) that decorator would be called along the lines described by Sudip.

Thanks, Nascif

(sent from my phone)

On Apr 26, 2014, at 2:16 PM, Sudipan Mishra notifications@github.com wrote:

I would be happy to!

Thanks,

Sudip

Sent from my iPhone. Please excuse the brevity, spelling and punctuation.

On Apr 26, 2014, at 2:00 PM, Nicolas Kruchten notifications@github.com wrote:

Thanks for your comments, both!

I'll see what I can do about implementing support for scatter/bubble charts with Google charts along the lines above, which make a fair bit of sense.

Your thoughts about a more generalized visualization system are interesting, and I've also thought about how to extend this library in that direction, but I think I would likely create a new project to do this rather than making such big modifications to a system whose primary virtue is its simplicity :)

Regarding drill-through, there is already an issue open for this here #33

Regarding HighCharts, @bluechips23 would you be willing to contribute your renderer plugins to this project under an MIT license?

— Reply to this email directly or view it on GitHub. — Reply to this email directly or view it on GitHub.

[bluechips23]

Hi Nicolas,

Here's the link to my HighCharts plugin - getCharts.js

I did not write the plugin as a .coffee file, but hopefully it can be used as a standalone javascript plugin file.

If HighCharts plugin is used instead of Google charts, then the following piece of code needs to be added (replacing the Google Charts code) in gchart_renders.js file:

var chartContainerDiv = $("<div id='container'>");
      $('body').append(chartContainerDiv);
      var chartContainer = document.getElementById('container');
      var emptyElement = $("<span>&nbsp;</span>");
      $('body').append(emptyElement);
      result = $("<div class='resultClass'>");
      var metaData = new Array();
      metaData[0] = title;
      metaData[1] = groupByTitle;
      metaData[2] = vAxisTitle;

      var chartResults = getChart(chartType, dataArray, metaData);
      var chart = new Highcharts.Chart(chartResults);
      emptyElement.append(chartContainer);
      result.append(emptyElement);

      return result;

If there are any additional issues or questions, please let me know.

[raaffaaeell]

Hi bluechips23, thanks for the effort, I'm trying to get this to work but what would I need to replace exactly in the gcharts_renderers file ?

Cheers

[bluechips23]

raaflaaeell,

Note - you will need to add highcharts js libraries as well into your header. After that,

Replace this code in gchart_renders.js file below:

dataTable = google.visualization.arrayToDataTable(dataArray);
      result = $("<div style='width: 100%; height: 100%;'>");
      wrapper = new google.visualization.ChartWrapper({
        dataTable: dataTable,
        chartType: chartType,
        options: options
      });
      wrapper.draw(result[0]);
      result.bind("dblclick", function() {
        var editor;
        editor = new google.visualization.ChartEditor();
        google.visualization.events.addListener(editor, 'ok', function() {
          return editor.getChartWrapper().draw(result[0]);
        });
        return editor.openDialog(wrapper);
      });
      return result;

with this code:

var chartContainerDiv = $("<div id='container'>");
      $('body').append(chartContainerDiv);
      var chartContainer = document.getElementById('container');
      var emptyElement = $("<span>&nbsp;</span>");
      $('body').append(emptyElement);
      result = $("<div class='resultClass'>");
      var metaData = new Array();
      metaData[0] = title;
      metaData[1] = groupByTitle;
      metaData[2] = vAxisTitle;

      var chartResults = getChart(chartType, dataArray, metaData);
      var chart = new Highcharts.Chart(chartResults);
      emptyElement.append(chartContainer);
      result.append(emptyElement);

      return result;

Also, in the same gChart_renders.js file, replace this code below:

$.pivotUtilities.gchart_renderers = {
    "Line Chart": makeGoogleChart("LineChart"),
    "Bar Chart": makeGoogleChart("ColumnChart"),
    "Stacked Bar Chart": makeGoogleChart("ColumnChart", {
      isStacked: true
    }),
    "Area Chart": makeGoogleChart("AreaChart", {
      isStacked: true
    })
  };

with this code here:

$.pivotUtilities.gchart_renderers = {
    "Column Chart": makeGoogleChart("column"),
    "Stacked Column Chart": makeGoogleChart("stackedcolumn"),
    "Bar Chart": makeGoogleChart("bar"),
    "Line Chart": makeGoogleChart("line"),
    "Area Chart": makeGoogleChart("area", {
      isStacked: true
    }),
    "Pie Chart": makeGoogleChart("pie"),
    "Scatter Plot" : makeGoogleChart("scatter"),
    "Bubble Chart" : makeGoogleChart("bubbles")
  };

[raaffaaeell]

bluechips23, sorry for the late response, I was in a hurry, but in the 10 minutes that I tested it didn't worked unfortunally. I'll see if I can test it a bit more late tonight and maybe point the errors.

Cheers and thanks :smile:

[raaffaaeell]

Hey bluechips23, I got it to work today, thank you very much for the support.

[bluechips23]

Raaffaaeell, glad you could get it to work. Just curious - what changes did you need to make for it to work and what were your earlier errors?

[raaffaaeell]

When I selected the block of code to replace, by mistake I selected one more line haha. I only discovered this when I copied another original and followed the steps, after this, it worked, then I compared the two files.

Cheers, thanks for the help

[mattscotty]

This is great, thanks for this. Would be great to see it added as a default option.

[nicolaskruchten]

I ended up building a Scatter Chart in both the Google Chart and C3 renderers. Feel free to check it out here: http://nicolas.kruchten.com/pivottable/examples/rcsvs.html