Re: DataView that swaps a DataTable's columns and rows - something to do with setColumn() and the 'domain' and 'data' roles I think...?

[asgallant]

You need to pivot your data, so you end up with one data series for each country.  There isn't any support for pivots in the API, but I wrote a hack that shows you how to do a pivot manually: http://jsfiddle.net/asgallant/HkjDe/

On Thursday, November 15, 2012 1:45:18 PM UTC-5, Iain wrote:

Hi, hoping to get some help with DataViews.  My ultimate goal is to draw Pie Charts and Column Charts form one DataTable - my understanding is that I can create different DataViews from that DataTable to support this.

I've created a DataTable with columns 'KGs', 'Year', 'Country', and 'Material'.  I was able to create a pie chart that aggregated each country's KG's by year using the following code:

var by_year = google.visualization.data.group(dt, [1], [{'column': 0, 'aggregation': google.visualization.data.sum, 'type': 'number'}]);

var chart = new google.visualization.PieChart(document.getElementById('chart_year'));

chart.draw(by_year, options);

I then wanted to create a Column Chart with KGs on the y-axis, Year on the x-axis and Country in the Legend.  I grouped the data as follows:

var by_year_country = google.visualization.data.group(dt, [1,2], [{'column': 0, 'aggregation': google.visualization.data.sum, 'type': 'number'}]);

Then I tried to tell the API what was data and what was grouping with the following code, which produced a Column Chart but one in which the x-axis repeats the year for every country, and the legend has just one entry with no label:

var view = new google.visualization.DataView(by_year_country);

view.setColumns([

{sourceColumn:0, type:'string', role:'domain', label:'Year'},

{sourceColumn:1, type:'string', role:'domain', label:'Country'},

{sourceColumn:2, type:'number', role:'data'}

]);

chart.draw(view, options);

Can anyone provide any advice on how this is supposed to be implemented, or the appropriate terminology upon which to seek an answer?

Many thanks in advance,

Iain

[Iain]

Nice - I've written an implementation of that that works - now I just have to figure out how to automate it as I won't know what the data will be - some form of loop through the distinct values of the given column I expect.

Any experience of the performance of this pivot function for real data sets?

Thanks for your help, really appreciated.

Iain

[Iain]

Darn, I thought the automation would be simple, but I have an weird error I can't figure out.  The code below, in which the for loop automates what is then commented out, doesn't work.  If I bring the comment into play to replace the automation however, I can go on to group the data to complete the pivot and draw a table or chart fine.  But as far as I can see, the 'columns' array from either implementation is identical to the other.  Any ideas?

var columns = [0];
var columnNames = data.getDistinctValues(2);
for (var i=0; i<columnNames.length; i++) {
      name = columnNames[i];
      columns.push({type:'number', label: name, calc: function (data, row) {return (data.getValue(row, 1) == name) ? data.getValue(row, 2) : null;}});
}
/*
var columns = [0,
      {type: 'number', label: 'Arg', calc: function (data, row) {return (data.getValue(row, 1) == 'Arg') ? data.getValue(row, 2) : null;}},
      {type: 'number', label: 'Bol', calc: function (data, row) {return (data.getValue(row, 1) == 'Bol') ? data.getValue(row, 2) : null;}},
      {type: 'number', label: 'Chi', calc: function (data, row) {return (data.getValue(row, 1) == 'Chi') ? data.getValue(row, 2) : null;}}
]
*/
var view = new google.visualization.DataView(year_country_grouping);
view.setColumns(columns);

Thanks again,
Iain

[asgallant]

At a guess, I'd say the problem is this line:

var columnNames = data.getDistinctValues(2);

You're getting the values from column 2, but comparing them to column 1 in the loop.  I think you meant to have this:

var columnNames = data.getDistinctValues(1);

[Iain]

Hi, very good of you have a look and reply.

The variable data is the DataTable, and when I create the view below, you'll see I actually create it from another view of that DataTable called 'year_country_group'.  In the DataTable, the countries column is column 2 - in the year_country_group view, the countries column is column 1.

Thanks,

Iain

[asgallant]

Ahh, I see the problem now.  You have a closure issue in play: by the time the view is calculated, the "name" variable will contain the name of the last entry, thus you will never get it to match anything else.  Try this instead:

for (var i = 0; i < columnNames.length; i++) {

    name = columnNames[i];
    columns.push({
        type:'number',
        label: name,

        calc: (function (myName) {
            return function (data, row) {
                return (data.getValue(row, 1) == myName) ? data.getValue(row, 2) : null;
            }
        })(name)
    });
}

This locks the value of "name" to the "myName" variable inside the closure so that when the returned function is executed, it has a unique value of "myName".

[Iain]

Awesome!  I was thinking it was probably down to an issue with my javascript more than my use of the API.

Thank you very much - I really appreciate your help!  I'd suggest perhaps I will return the favour sometime but honesty I imagine your skills will always surpass mine!  Thanks though.

Iain

[asgallant]

You're welcome.