motion chart using csv

[Maliki Yacouba]

Hi
I went through the "Running and testing CSVDataSourceServlet" of Using External Data Source Section.
My problem is that I can not make a motionChart or any other chart with the "csv_example.csv", except OrgChart.
Can someone help me how I can use a csv file to do a motion chat.

thanks
maliky

[ChartMan]

Hi

 

Can you be more specific in what exactly is failing for you. The first thing to look is the data format the CSV file is in, the motion chart expects columns with a certain format (listed in the motion chart doc page).

If this still doesn't work please send us some more details.

 

ChartMan

[Maliki Yacouba]

Hi CharMan,
Yes I did make sure that the motionchart column requirements are met. actually, I created a motion chart with the same data through my google account using the spreadsheet.

Here is the tutorial I followed:
http://code.google.com/apis/visualization/documentation/dev/dsl_csv.html
Here are the changes I did in "all_examples_html" file:
1) I make sure I loaded the motionchart package :
google.load('visualization', '1', {'packages':['table,motionchart,columnchart,piechart, barchart,orgchart']});

2) I save the file named "csv_myfile_csv" in myWeApp of the tomtcat directory

3) in the file "all_examples.html" I changed the file "csv_example.csv" by "csv_myfile_csv" and the query became:
query = new google.visualization.Query('csv?url=http://localhost:8080/myWebApp/csv_myfile.csv');

Then I tried to visualize MotionChart
var chart = new google.visualization.MotionChart(document.getElementById('csv_div'));

I get the following message.
"CSV Data Source
An organization chart. The data is taken from the csv data source.
First and second columns must be entity and time."

but when I change to BarChart as:
var chart = new google.visualization.BarChart(document.getElementById('csv_div'));
I will have my variable name appear as legend alongside empty chart area.

and When I change to ColumnCart as:
var chart = new google.visualization.ColumnChart(document.getElementById('csv_div'));

I will have my entity names listed on the x-axis but no chart appear.

Thanks for the help.
Maliky

----- Original Message -----
From: ChartMan <char...@google.com>
Date: Saturday, July 4, 2009 1:51 pm
Subject: [visualization-api] Re: motion chart using csv

> Hi
>
> Can you be more specific in what exactly is failing for you. The
> first thing
> to look is the data format the CSV file is in, the motion chart

> expectscolumns with a certain format (listed in the motion chart

[ChartMan]

can you copy and send the query response coming back from your server.

[Maliki Yacouba]

Hi

The only strange message I am getting is "First and second columns must be entity and time."
But that requirement is met because I used the same data to build motionchart on Google spreadsheet. Also when a visualize a Table instead of a MotionChart I get the table without problem.

Here is the first few rows of the data of the file.
OTERM1,GOID1,Year,GOID1,GOID2,GOTERM2
extracellular space,5615,2009,5615,7154,cell communication
metabolism,8152,2009,8152,5941,unlocalized protein complex

One more question. Is it possible to make the bubble of the motionchart clickable - I mean when the user click on a particular bubble it goes to next level of information

thanks

[ChartMan]

From looking into your data I see that you have the following columns

OTERM1, GOID1, Year, GOID2,...

Which means that the first two columns of your data are not entity and time but entity and values. You should move the year column to be the second in your data or use a google.visualization.DataView to reorder your columns.

As for bubble clicks - the motion chart state is quite a complex object with a lot of information in it. On every user interaction with the chart this object is changed and you can view its content from the advanced menu of the chart or get it using chart.getState(). You can inspect how this object changes and act accordingly.

ChartMan

[Maliki Yacouba]

Hi,
I am sorry. that was not column order I intended to share with you. As you have suggested I made sure the firs two column of my data are entity and time (e.g OTERM1, YEAR, GOID1, GOID2). But the motionchart still did show and continue to tell me "The First and Second Column must me entity and time".

here is the part of the tutorial that I think is not working well.

// Handle the csv data source query response
function handleCsvDsResponse(response) {
if (response.isError()) {
alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
return;
}

var data = response.getDataTable();

var chart = new google.visualization.MotionChart(document.getElementById('csv_div'));

chart.draw(data, {width: 600, height: 150, is3D: true});
}

Please not that this is part of the all_example.html file in http://code.google.com/apis/visualization/documentation/dev/dsl_csv.html

Thank for your help.

Maliky


----- Original Message -----
From: ChartMan <char...@google.com>
Date: Tuesday, July 7, 2009 1:24 am
Subject: [visualization-api] Re: motion chart using csv

> From looking into your data I see that you have the following

> columnsOTERM1,GOID1, Year, GOID2,...

> Which means that the first two columns of your data are not entity
> and time
> but entity and values. You should move the year column to be the
> second in
> your data or use a google.visualization.DataView to reorder your
> columns.
> As for bubble clicks - the motion chart state is quite a complex
> object with
> a lot of information in it. On every user interaction with the
> chart this
> object is changed and you can view its content from the advanced
> menu of the
> chart or get it using chart.getState(). You can inspect how this

> objectchanges and act accordingly.

[ChartMan]

Hi

Each visualization supports a certain data format. A map shows locations, a motion chart requires entities, time and then values and so on.
I assume that you are using data that is not in the correct format for the the visualization you are using.
Please send the csv example you are using and the code you are using to send the request and draw the visualization.

ChartMan

[Maliki Yacouba]

Hi chartMan,

to test whether my data was compatible, I used a set of data Google used to demonstrate the motionchart (state, year, unployement, houseprice etc). the data was initially in excel format, I just save it in csv file.
I am sending you the codes and the data attachment. I can visualize the table, and the orgchart, but not motionchart


I hope you can find the issue.

thank you,

maliky

> http://localhost:8080/myWebApp/csv_myfile.csv'<http://localhost:8080/myWebApp/csv_myfile.csv%27>> );

[ChartMan]

Please check the following url when you run the example server
http://localhost:8080/myWebApp/csv?url=http://localhost:8080/myWebApp/csv_training.csv&tq=limit
3
You should be getting a response for the first 3 lines of you data source:

google.visualization.Query.setResponse({version:'0.6',status:'warning',warnings:[{reason:'data_truncated',message:'Retrieved
data was truncated',detailed_message:'Data has been truncated due to
userrequest (LIMIT in
query)'}],sig:'1951736017',table:{cols:[{id:'Col1',label:'State',type:'string',pattern:''},{id:'Col2',label:'Year',type:'number',pattern:''},{id:'Col3',label:'HousePrice',type:'number',pattern:''},{id:'Col4',label:'Unemployement',type:'number',pattern:''},{id:'Col5',label:'Region',type:'number',pattern:''},{id:'Col6',label:'Population',type:'number',pattern:''},{id:'Col7',label:'SubPrimeLoan',type:'number',pattern:''}],rows:[{c:[{v:'Alabama'},{v:2000.0},{v:203.6},{v:4.5},,{v:4452.0},{v:0.122}]},{c:[{v:'Alaska'},{v:2000.0},{v:169.7},{v:6.7},,{v:628.0},{v:0.046}]},{c:[{v:'Arizona'},{v:2000.0},{v:207.2},{v:4.0},,{v:5167.0},{v:0.131}]}]}});

I removed the 3 rows limit (tq=limit 3) and use the response to creaet
a motionchart.

ChartMan

[ChartMan]

Please check the following url when you run the example server http://localhost:8080/myWebApp/csv?url=http://localhost:8080/myWebApp/csv_training.csv&tq=limit 3
You should be getting a response for the first 3 lines of you data source:

google.visualization.Query.setResponse({version:'0.6',status:'warning',warnings:[{reason:'data_truncated',message:'Retrieved data was truncated',detailed_message:'Data has been truncated due to userrequest (LIMIT in query)'}],sig:'1951736017',table:{cols:[{id:'Col1',label:'State',type:'string',pattern:''},{id:'Col2',label:'Year',type:'number',pattern:''},{id:'Col3',label:'HousePrice',type:'number',pattern:''},{id:'Col4',label:'Unemployement',type:'number',pattern:''},{id:'Col5',label:'Region',type:'number',pattern:''},{id:'Col6',label:'Population',type:'number',pattern:''},{id:'Col7',label:'SubPrimeLoan',type:'number',pattern:''}],rows:[{c:[{v:'Alabama'},{v:2000.0},{v:203.6},{v:4.5},,{v:4452.0},{v:0.122}]},{c:[{v:'Alaska'},{v:2000.0},{v:169.7},{v:6.7},,{v:628.0},{v:0.046}]},{c:[{v:'Arizona'},{v:2000.0},{v:207.2},{v:4.0},,{v:5167.0},{v:0.131}]}]}});

I  removed the 3 rows limit (tq=limit 3) and use the response to create a motionchart.

[Maliki Yacouba]

Hi ChartMan,
Here is my query response. I find out that your response starts cols:id='Col1' while my response start with cols:id=Col0'
My question is how to fix this since I am not hand coding the table, rather I am getting the table from the csv file.


my query response: google.visualization.Query.setResponse({version:'0.6',status:'warning',warnings:[{reason:'data_truncated',message:'Retrieved data was truncated',detailed_message:'Data has been truncated due to userrequest (LIMIT in query)'}],sig:'941796702',table:{cols:[{id:'Col0',label:'State',type:'string',pattern:''},{id:'Col1',label:'Year',type:'string',pattern:''},{id:'Col2',label:'HousePrice',type:'string',pattern:''},{id:'Col3',label:'Unemployement',type:'string',pattern:''},{id:'Col4',label:'Region',type:'string',pattern:''},{id:'Col5',label:'Population',type:'string',pattern:''},{id:'Col6',label:'SubPrimeLoan',type:'string',pattern:''}],rows:[{c:[{v:'Alabama'},{v:'2000'},{v:'203.6'},{v:'4.5'},{v:'South'},{v:'4452'},{v:'0.122'}]},{c:[{v:'Alaska'},{v:'2000'},{v:'169.7'},{v:'6.7'},{v:'West'},{v:'628'},{v:'0.046'}]},{c:[{v:'Arizona'},{v:'2000'},{v:'207.2'},{v:'4'},{v:'Southwest'},{v:'5167'},{v:'0.131'}]}]}});

Hopefully this is answer to my problem.
Thank you for your help
Maliky.

----- Original Message -----
From: ChartMan <char...@google.com>
Date: Thursday, July 9, 2009 1:26 am
Subject: [visualization-api] Re: motion chart using csv

> Please check the following url when you run the example server
> http://localhost:8080/myWebApp/csv?url=http://localhost:8080/myWebApp/csv_training.csv&tq=limit

> 3<http://localhost:8080/myWebApp/csv?url=http://localhost:8080/myWebApp/csv_training.csv&tq=limit%203>

[Maliki Yacouba]

Hi chartMan,

Here is my query response. Compare to your response, my response start with Col0 and also all my labels are defaulted to string.
so my question is how can I set the col label to a desire type?

Thank you,
Malik.


google.visualization.Query.setResponse({version:'0.6',status:'warning',warnings:[{reason:'data_truncated',message:'Retrieved data was truncated',detailed_message:'Data has been truncated due to userrequest (LIMIT in query)'}],sig:'1261978498',table:{cols:[{id:'Col0',label:'State2',type:'string',pattern:''},{id:'Col1',label:'Year',type:'string',pattern:''},{id:'Col2',label:'HousePrice',type:'string',pattern:''},{id:'Col3',label:'Unemployement',type:'string',pattern:''},{id:'Col4',label:'Region',type:'string',pattern:''},{id:'Col5',label:'Population',type:'string',pattern:''},{id:'Col6',label:'SubPrimeLoan',type:'string',pattern:''}],rows:[{c:[{v:'Alabama'},{v:'2000'},{v:'203.6'},{v:'4.5'},{v:'South'},{v:'4452'},{v:'0.122'}]},{c:[{v:'Alaska'},{v:'2000'},{v:'169.7'},{v:'6.7'},{v:'West'},{v:'628'},{v:'0.046'}]},{c:[{v:'Arizona'},{v:'2000'},{v:'207.2'},{v:'4'},{v:'Southwest'},{v:'5167'},{v:'0.131'}]}]}});

[ChartMan]

So I was able to track down the problem.

You are using the CsvDataSourceServlet in the java open source library. The servlet is an example servlet that demonstrated how to become a CSV based data source.
Since this is an example the code is very simple. Therefore, the servlet does not take care of identifying the data structure of the CSV and outputs a table where all columns are strings.

If you wish to create your own real CSV data source, take the example servlet and add code that creates a list of ColumnDescriptions for your csv file. Now with the list in hand you can call the utility code in CsvDataSourceHelper to create a data table based on the csv reader and the list of column descriptions.

Let me know if this makes sense and if it works for you.

In addition I will update our documentation so that this is made clear.

ChartMan

[Maliki Yacouba]

Hi ChartMan
I was about to send you a new e-mail because I thought you didn't want to respond me anymore about this problem.

The way you describe the problem makes sense to me. The only problem is that I am not an experienced programmer - and I guess that is why we love google API: simplicity and convenience. However, I will try.

Also I think this an opportunity for you to ugrade the CsvDataSourceServlet to incorporate column description. Can it be on request?

Thank you for you help,
Maliky.

----- Original Message -----
From: ChartMan <char...@google.com>
Date: Thursday, July 16, 2009 8:01 am
Subject: [visualization-api] Re: motion chart using csv

> So I was able to track down the problem.
>
> You are using the CsvDataSourceServlet in the java open source
> library. The
> servlet is an example servlet that demonstrated how to become a
> CSV based
> data source.
> Since this is an example the code is very simple. Therefore, the

> servletdoes not take care of identifying the data structure of the

[ChartMan]

The code you need to add in DataSourceServlet is quite simple.
Something like :

  DataTable dataTable = null;
List<ColumnDescription> columnDescriptions = Lists.newArrayList();
columnDescriptions.add(new ColumnDescription("i1", ValueType.NUMBER, null));
columnDescriptions.add(new ColumnDescription("i2", ValueType.TEXT, null));
columnDescriptions.add(new ColumnDescription("i3", ValueType.DATE, null));
....
    try {
dataTable = CsvDataSourceHelper.read(reader, columnDescriptions, false);
    } catch (IOException e) {
      log.error("Couldn't read from url inside CSVDataSource.read(): " + url);
      throw new DataSourceException(ReasonType.INVALID_REQUEST,
"Couldn't read from url: " + url);
    }
    return dataTable;

Adding this to our example is an option but I am not sure it will
happen in the near future.

ChartMan

[Kev M-Wallace]

Hi ChartMan

I would like to remove this response from the Google response. 

"Retrieved data was truncated. Data has been truncated due to userrequest (LIMIT in query)"

Is there any way to suppress the result message and just use the results.