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:
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?
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:
> 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:
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?
On Thursday, November 15, 2012 8:25:52 PM UTC, asgallant wrote:
> 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:
>> 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:
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;}});
On Friday, November 16, 2012 12:07:13 PM UTC, Iain wrote:
> 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
> On Thursday, November 15, 2012 8:25:52 PM UTC, asgallant wrote:
>> 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:
>>> 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:
On Friday, November 16, 2012 9:28:02 AM UTC-5, Iain wrote:
> 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?
> On Friday, November 16, 2012 12:07:13 PM UTC, Iain wrote:
>> 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
>> On Thursday, November 15, 2012 8:25:52 PM UTC, asgallant wrote:
>>> 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:
>>>> 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:
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.
On Friday, November 16, 2012 4:56:00 PM UTC, asgallant wrote:
> 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);
> On Friday, November 16, 2012 9:28:02 AM UTC-5, Iain wrote:
>> 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?
>> On Friday, November 16, 2012 12:07:13 PM UTC, Iain wrote:
>>> 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
>>> On Thursday, November 15, 2012 8:25:52 PM UTC, asgallant wrote:
>>>> 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:
>>>>> 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:
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".
On Friday, November 16, 2012 12:32:07 PM UTC-5, Iain wrote:
> 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
> On Friday, November 16, 2012 4:56:00 PM UTC, asgallant wrote:
>> 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);
>> On Friday, November 16, 2012 9:28:02 AM UTC-5, Iain wrote:
>>> 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?
>>> On Friday, November 16, 2012 12:07:13 PM UTC, Iain wrote:
>>>> 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
>>>> On Thursday, November 15, 2012 8:25:52 PM UTC, asgallant wrote:
>>>>> 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:
>>>>>> 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:
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.
On Friday, November 16, 2012 6:27:00 PM UTC, asgallant wrote:
> 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:
> 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".
> On Friday, November 16, 2012 12:32:07 PM UTC-5, Iain wrote:
>> 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
>> On Friday, November 16, 2012 4:56:00 PM UTC, asgallant wrote:
>>> 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);
>>> On Friday, November 16, 2012 9:28:02 AM UTC-5, Iain wrote:
>>>> 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?
>>>> On Friday, November 16, 2012 12:07:13 PM UTC, Iain wrote:
>>>>> 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
>>>>> On Thursday, November 15, 2012 8:25:52 PM UTC, asgallant wrote:
>>>>>> 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:
>>>>>>> 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:
On Monday, November 19, 2012 5:27:12 AM UTC-5, Iain wrote:
> 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
> On Friday, November 16, 2012 6:27:00 PM UTC, asgallant wrote:
>> 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:
>> 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".
>> On Friday, November 16, 2012 12:32:07 PM UTC-5, Iain wrote:
>>> 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
>>> On Friday, November 16, 2012 4:56:00 PM UTC, asgallant wrote:
>>>> 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);
>>>> On Friday, November 16, 2012 9:28:02 AM UTC-5, Iain wrote:
>>>>> 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?
>>>>> On Friday, November 16, 2012 12:07:13 PM UTC, Iain wrote:
>>>>>> 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
>>>>>> On Thursday, November 15, 2012 8:25:52 PM UTC, asgallant wrote:
>>>>>>> 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:
>>>>>>>> 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:
>>>>>>>> Can anyone provide any advice on how this is supposed to be >>>>>>>> implemented, or the appropriate terminology upon which to seek an answer?
