Setting multiple DataTable columns to "certainty"

581 views
Skip to first unread message

Ken Burkhalter

unread,
Dec 6, 2014, 4:33:00 PM12/6/14
to google-visua...@googlegroups.com
Y'all have been such an enormous help in getting me up to speed rapidly in my new quest to learn and use javaScript that I'm hoping you can do it again.   [:-)}

In Google Charts, I am trying to replicate this chart appearance whose software I am trying to replace for tighter integration into a web site.

Note that the data after the "current time" is shown in dashed lines.

I am outputting a csv file (with more temps than shown in the image above) that looks like this ...

My challenge is to set the Type and Role attributes on columns 2,4,6,8,10 to reflect the nature of those columns as 'certainty' data, not temp values, as I want to make the data point entries later than the 'current time' dashed by using the "certainty" role: attribute.

I have spent a day trying to find documentation/examples oh how to do this correctly, but all the examples use manually created data tables where the column definitions can be set up before the Table is populated.

My Table is already read in and I must thus create the proper definitions "after the fact"

My code is (full code page attached) ...
         . . .
            var dataView = new google.visualization.DataView(data);
dataView.setColumns([2, 4, 6, 8, 10 {
type: "boolean",
role: "certainty"
}]);
                . . . 


Without the "dataView 4 lines of code everything works, except I get this error...

   "All series on a given axis must be of the same data type"


With the dataView code lines I get a...
   
   " Uncaught SyntaxError: Unexpected token { "

Can't get rid of the unexpected token error, and ALSO wondering if I am doing this right anyway?!  That is, is there an even easier way to change the data appearance after a specific x-axis value (ie, later times) that doesn't require all the 'certainty' column data to be inserted into the data package?



Thanks for any help you can suggest.
  [:-)}

Chart.html

Ken Burkhalter

unread,
Dec 7, 2014, 1:14:52 PM12/7/14
to google-visua...@googlegroups.com
OK

Solved the various errors but not getting the chart results desired.

Despite the use of  data.setColumnProperty(2, 'role', 'certainty');  for col 2 (and 4,6,8,10) and a correct array and  DataTable, none of the chart lines change from certain to uncertain mode.  On the chart below (output from the code shown below it) the lines should all change at ~14 hours where the input data changes, but it doesn't ...










Here is the data array contents around the change...


And here is the page code...

<html>
<head>
<script src="https://www.google.com/jsapi"> </script>
<script src="http://code.jquery.com/jquery-1.10.1.min.js"> </script>
<script src="http://jquery-csv.googlecode.com/git/src/jquery.csv.js"></script>


<script>
// Load the Visualization API from Google and set Listener
google.load("visualization", "1", {packages:["corechart"]});
google.setOnLoadCallback(drawChart);


// this has to be a global function
function drawChart() {
   // grab the CSV
   $.get("temps.csv", function(csvString) {
 // transform the CSV string into a 2-dimensional array
 var arrayData = $.csv.toArrays(csvString, {onParseValue: $.csv.hooks.castToScalar});
 alert (arrayData[0]+ '\n' +  arrayData[1]);
 //alert(arrayData.join('\n'));
 // this new DataTable object holds all the data
 // Define the 'certainty' columns
 
 var data = new google.visualization.arrayToDataTable(arrayData);
 data.setColumnProperty(2, 'role', 'certainty');
 data.setColumnProperty(4, 'role', 'certainty');
 data.setColumnProperty(6, 'role', 'certainty');
 data.setColumnProperty(8, 'role', 'certainty');
 data.setColumnProperty(10,'role', 'certainty');


 //Create a DataView from the data_table
 //Set the first column of the dataview to format as a number, and return the other columns as is.
 //dataView.setColumns([{calc: function(data, row) { return data.getFormattedValue(row, 0); }, type:'number'}, 1, 2, 3, 4, 5]);
 
 // set chart options
 var start = -20;
 var vTicks = [];
 while (start <= 100) {
 vTicks.push(start);
 start+=10;
 }


 var options = {
 fontSize: 8,
 legend: {position: 'right'},
 colors: ['green', 'blue', 'red', 'green', 'yellow', 'gray'],
 series: {
 0: { lineDashStyle: [2, 2] }
 },
 hAxis: {
 title: 'Time',
 gridlines: {
 count: 25,
 },
 minorGridlines: {
 count: 3
 },


 },
 vAxis: {
 title: 'Temperature',
 gridlines: {
 count: 12,
 },
 minorGridlines: {
 count: 4
 },
 ticks: vTicks
 }
 };
 var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
 chart.draw(data,options);
 });
}
 
  </script>
  </head>
 
  <body>
    <div id="chart_div" style="width: 800px; height: 300px;"></div>
  </body>
</html>


As far as I can tell I have faithfully replicated several examples I discovered, but obviously I have something wrong.

Hopefully someone's sharp eyes will catch the error.
 [:-)}


Ken Burkhalter

unread,
Dec 7, 2014, 4:39:46 PM12/7/14
to google-visua...@googlegroups.com
Thought I might have discovered the problem, when I realized I had not set the 'type' property, so edited all the column setColumnProperty statements like this column 10 example...

   data.setColumnProperty(10,'role', 'certainty');

   data.setColumnProperty(10,'type', 'boolean');

but it made no difference, and I'm running out of ideas!  :-(

Ken Burkhalter

unread,
Dec 8, 2014, 9:36:25 AM12/8/14
to google-visua...@googlegroups.com
I think I figured out the issue here, and that was I needed to add a Listener to delay the attempts to modify column properties until the visualization.arrayToDataTable effort was completed.

I thus modified my code (yellow highlight) as follows but now am getting an All series on a given axis must be of the same data type×  error that I can't seem to resolve.

I think I'm getting close, but no cigar yet.  :-(

Help.

<html>
<head>
<script src="https://www.google.com/jsapi"> </script>
<script src="http://code.jquery.com/jquery-1.10.1.min.js"> </script>
<script src="http://jquery-csv.googlecode.com/git/src/jquery.csv.js"></script>


<script>
// Load the Visualization API from Google and set Listener
google.load("visualization", "1", {packages:["corechart"]});
google.setOnLoadCallback(drawChart);


// this has to be a global function
function drawChart() {
   // grab the CSV
   $.get("temps.csv", function(csvString) {
 // transform the CSV string into a 2-dimensional array

 var chartTbl = $.csv.toArrays(csvString, {onParseValue: $.csv.hooks.castToScalar});
 alert (chartTbl[0]+ '\n' +  chartTbl[1]);
 //alert(chartTbl.join('\n'));

 // this new DataTable object holds all the data
 
 var data = new google.visualization.arrayToDataTable(chartTbl);



 // Define the 'certainty' columns

 google.visualization.events.addListener(data, 'ready', function(event) {

 data.setColumnProperty(2, 'role', 'certainty');
 data.setColumnProperty(4, 'role', 'certainty');
 data.setColumnProperty(6, 'role', 'certainty');
 data.setColumnProperty(8, 'role', 'certainty');

 data.setColumnProperty(8, 'type', 'boolean');

 data.setColumnProperty(10,'role', 'certainty');
 data.setColumnProperty(10,'type', 'boolean');

 });

Sergey Grabkovsky

unread,
Dec 8, 2014, 9:57:38 AM12/8/14
to google-visua...@googlegroups.com
Hi Ken,

You had some pretty fundamental issues in the code that you attached in your initial post. The one that drew my eye is your .setColumns call. With the way you're calling it, it seems like you expect it to set the type: 'boolean', and role: 'certainty' on all of the columns, which is not what it does. .setColumns changes the columns of the chart to a subset (plus calculated columns). So when you wrote .setColumns([2, 4, 6, 8]), that means that those columns would be the only ones in the data table. You actually don't need to use a DataView or the ready event at all. In fact, here is an example of the chart you want: http://jsfiddle.net/stjxwsju/

I think one of the issues you may be seeing is that the jQuery CSV parser doesn't detect boolean values, which is completely reasonable. You might have to override it, and use a custom converter. Perhaps something like this:
var arrayData = $.csv.toArrays(csvString, {onParseValue: function(value) {
  if (value === 'false') {
    return false;
  } else if (value === 'true') { 
    return true;
  } else {
    return $.csv.hooks.castToScalar(value);
  }
}});

Hopefully that will work. If you're still having issues, please post back with your full HTML (and preferably an example CSV as well [in text, not a screenshot]).


--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-visualizati...@googlegroups.com.
To post to this group, send email to google-visua...@googlegroups.com.
Visit this group at http://groups.google.com/group/google-visualization-api.
For more options, visit https://groups.google.com/d/optout.

Ken Burkhalter

unread,
Dec 8, 2014, 5:16:24 PM12/8/14
to google-visua...@googlegroups.com


On Monday, December 8, 2014 9:57:38 AM UTC-5, Sergey wrote:
Hi Ken,

You had some pretty fundamental issues in the code that you attached in your initial post. The one that drew my eye is your .setColumns call. With the way you're calling it, it seems like you expect it to set the type: 'boolean', and role: 'certainty' on all of the columns, which is not what it does. .setColumns changes the columns of the chart to a subset (plus calculated columns). So when you wrote .setColumns([2, 4, 6, 8]), that means that those columns would be the only ones in the data table. You actually don't need to use a DataView or the ready event at all. In fact, here is an example of the chart you want: http://jsfiddle.net/stjxwsju/

I think one of the issues you may be seeing is that the jQuery CSV parser doesn't detect boolean values, which is completely reasonable. You might have to override it, and use a custom converter. 

Sergey -

I can't even begin to express my gratitude at your remarkable, and non-judgement (at my struggling efforts) responses!  You are a gem amongst gems!

I modified my code substantially to attempt to incorporate your suggestions, plus some additional thoughts.

This is my current code....

<html>
<head>
<script src="https://www.google.com/jsapi"> </script>
<script src="http://code.jquery.com/jquery-1.10.1.min.js"> </script>
<script src="http://jquery-csv.googlecode.com/git/src/jquery.csv.js"></script>


<script>
// Load the Visualization API from Google and set Listener
google.load("visualization", "1", {packages:["corechart"]});
google.setOnLoadCallback(drawChart);



 $.get("temps.csv", function(csvString) {

 // transform the CSV string into a 2-dimensional array

 dataArray = $.csv.toArrays(csvString, {onParseValue: $.csv.hooks.castToScalar});
 //alert (dataArray[0]+ '\n' +  dataArray[1]);
 //alert(chartTbl.join('\n'));
});


function drawChart() {
 data = [
 // columns: Time,Dwn,Cert,Mid,Cert,Out,Cert,Frez,Cert,Frig,Cert
    {label: 'Time', type: 'number'},  //col 0
    {label: 'Dwn', type: 'number'},   //col 1
 {label: 'Cert', type: 'boolean'}, //col 2
    {label: 'Mid', type: 'number'},   //col 3
 {label: 'Cert', type: 'boolean'}, //col 4
    {label: 'Out', type: 'number'},   //col 5
    {label: 'Cert', type: 'boolean'}, //col 6
    {label: 'Frez', type: 'number'},  //col 7
    {label: 'Cert', type: 'boolean'}, //col 8
    {label: 'Frig', type: 'number'},  //col 9
    {label: 'Cert', type: 'boolean'}, //col 10
 ];
 //alert(dataArray);
 //alert(data);
 data = google.visualization.arrayToDataTable(dataArray, true);  //true means the first row has data.


 var csv = google.visualization.dataTableToCsv(data);
    alert(csv);
 // Define the columns...
 data.setColumnProperty(0, 'role', 'domain');
 data.setColumnProperty(2, 'role', 'certainty');

 data.setColumnProperty(4, 'role', 'certainty');
 data.setColumnProperty(6, 'role', 'certainty');
 data.setColumnProperty(8, 'role', 'certainty');

 data.setColumnProperty(10,'role', 'certainty');
// });

 
 // set chart options
 var start = -20;
 var vTicks = [];
 while (start <= 100) {
 vTicks.push(start);
 start+=10;
 }



 var d = new Date()
 var days = ['Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday'];
 var months = ['January','February','March','April','May','June','July','August','September','October','November','December'];
 var day = days[d.getDay()];
 var month = months[d.getMonth()];

 
 var options = {
 fontSize: 8,
 legend: {position: 'right'},

 // columns: Dwn, Mid, Out, Frez, Frig
 //legend: 'true',
 title: day,
 series: {
 //  n:  {color: '#1DC70E',lineWidth:4,lineDashStyle: [2, 2] },
 0: { color: '#E30805' }, //Dwn
 1: { color: '#F78F07' }, //Mid
 2: { color: '#1DC70E' }, //Out
 3: { color: '#0A5EFA' }, //Frez
 4: { color: '#44B8F2' }  //Frig

  },
 hAxis: {
 title: 'Time',
 gridlines: {
 count: 25,
 },
 minorGridlines: {
 count: 3
 },


 },
 vAxis: {
 title: 'Temperature',
 gridlines: {
 count: 12,
 },
 minorGridlines: {
 count: 4
 },
 ticks: vTicks
 }
 };
 var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
 chart.draw(data,options);
};
 
  </script>

  </head>
 
  <body>
    <div id="chart_div" style="width: 800px; height: 300px;"></div>
  </body>
</html>

I am no longer getting any JavaScript Console errors and am getting everything with the charts I desire EXCEPT still not getting the 'certainty' results expressed on the graph. 

NOTE: I am not getting the labels either that I thought I set in the drawChart definitions.

I seem to kinda be getting the csv data OK, as this is a partial dump of the dataTableToCsv(data) reconstruction of what is feeding the chart...























I am puzzled though, why the alert dump seems to be discontinuous.

It sure doesn't look right!

Anyway, much closer than ever before.

Hopefully a few "fine tuning" efforts will put the icing on the cake.
  [:-)}






Sergey Grabkovsky

unread,
Dec 8, 2014, 5:24:39 PM12/8/14
to google-visua...@googlegroups.com
Hi Ken,

It's really difficult to read your code. Please attach the HTML and CSV file, or at least a sample of the CSV file, so that I can copy/paste them into my own environment.

As for the discontinuity in your alert, it appears that Chrome truncates the text somewhere in the middle (look at the line for value 5.71).

Ken Burkhalter

unread,
Dec 9, 2014, 1:38:50 PM12/9/14
to google-visua...@googlegroups.com


On Monday, December 8, 2014 5:24:39 PM UTC-5, Sergey wrote:
Hi Ken,

It's really difficult to read your code. Please attach the HTML and CSV file, or at least a sample of the CSV file, so that I can copy/paste them into my own environment.

As for the discontinuity in your alert, it appears that Chrome truncates the text somewhere in the middle (look at the line for value 5.71).

Sergey - My apologies.  I misunderstood your prior request for "attached" code to mean "insert it inline with the post, and not an image"

The code is attached this time!  :-)

Along with the csv file I have been using for testing.  That should make any testing you do identical to my environment, which may make a difference because as far as I can determine I have the same code as you posted in the jsfiddle area but I am not getting any indication of the "uncertainty" dashed lined that should be showing after 14:00 hours on the chart.

This is my current output chart...


Note that, for testing purposes, in my code I have inserted in lines 19 an alert message to show the contents of the CSV table originally constructed by csv.toArrays, and then in lines 32-33 a reverse transformation from dataTable back to CSV to verify the integrity of the original translation.  It appears that the true/false contents are correctly being handled, so I did not implement your suggested custom converter for the jQuery CSV parser.

Hopefully with this exchange we can put the final nail in the coffin, and solve the last part of the mystery.
   [:-)}

Thank you.

Chart.html
temps.csv

Sergey Grabkovsky

unread,
Dec 9, 2014, 2:08:01 PM12/9/14
to google-visua...@googlegroups.com
Hi Ken,

It appears that my initial diagnosis was correct, and $.csv.hooks.castToScalar doesn't properly cast boolean values. However, the solution I proposed wouldn't have worked either, since it csv.toArrays appear to ignore the value entirely when you return false. At this point, you basically have three options:
1. Explicitly iterate over your chartTbl 2D array and replace the string 'false' and 'true' with their boolean counterparts
for (var i = 0; i < chartTbl.length; i++) {
var row = chartTbl[i];
for (var j = 0; j < row.length; j++) {
if (row[j] === 'false') {
row[j] = false;
} else if (row[j] === 'true') {
row[j] = true;
}
}
}
2. Figure out how to get castToScalar to return false without it being ignored.
3. Use our google.visualization.Query.

I attached solutions 1 and 3 for you. Personally, I'd go with solution #3.

--
Chart-casting.html
Chart-query.html

Ken Burkhalter

unread,
Dec 9, 2014, 3:17:28 PM12/9/14
to google-visua...@googlegroups.com
Sergey-

It works perfectly.  I went with your 3rd alternative using the Query as that feels a better approach to me too.

I cannot thank you enough for all your efforts.  I don't think I could have ever solved this on my own for a very long time.

You have given me a solid foundation to now build on, and I am very excited to now begin to put it to good use.

Thanks again!
   [:-)}

Ken Burkhalter

unread,
Dec 9, 2014, 4:46:38 PM12/9/14
to google-visua...@googlegroups.com
Sergey -

I quick question. With the latest code I am getting these javaScript errors.

I assume they are just advisory and nothing to worry about, as everything seems to be working fine...



Thanks

Sergey Grabkovsky

unread,
Dec 9, 2014, 4:50:44 PM12/9/14
to google-visua...@googlegroups.com
Sorry, about that. I'd left some debugging code in. Please change the version of visualization (google.load('visualization', '0.1', ...)) back to either "1", "1.1", or "1.0".

--

Ken Burkhalter

unread,
Dec 9, 2014, 5:02:58 PM12/9/14
to google-visua...@googlegroups.com
Thanks.  That does it!
  [:-)}
You received this message because you are subscribed to a topic in the Google Groups "Google Visualization API" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-visualization-api/1B-RwqbnE40/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-visualizati...@googlegroups.com.

To post to this group, send email to google-visua...@googlegroups.com.
Visit this group at http://groups.google.com/group/google-visualization-api.
For more options, visit https://groups.google.com/d/optout.

-- 
-ken burkhalter
Reply all
Reply to author
Forward
0 new messages