Visualising Twitter activity – a work in progress part VII: Getting started on visualising stuff

Now we’re getting somewhere! At the bottom of this post will be links to the previous parts of this odyssey.

I now have a spreadsheet with some data. How do I make that interesting to look at? First of all, I have to decide on a framework for graphing this. In part IV, I made a choice. I chose to save the data in a Google Spreadsheet. For two reasons. One is that I know something about how to work with it. As the previous parts have revealed, I dont know much. But I know enough. The other reason is, that Google has a chart API, that I should be able to use. That in itself is not a very good reason. I know how to work with HighCharts, so why not use that, instead of learning something new? Because Google promises, that the graphs will update live, as I fill in more data in the spreadsheet.

How cool is that?

Where to begin? I don’t think that Google actually provides any good examples. A lot of other people does – it just takes at bit of googling to find somthing that is helpful. I think this example is a good beginning. Go on, take a look.

OK, first of all it begins like this:

function drawChart() {
var query = new google.visualization.Query(‘http://docs.google.com/spreadsheet/tq?key=0AhCv9Xu_eRnSdHBELWZoZHRaQTl5VXdRa3JLTzByUlE&gid=1’);

That tells the chart software where to find the data. It does not look like the ordinary link to a spreadsheet. That looks a bit more like this:

https://docs.google.com/spreadsheets/d/14kCXRFEOxBMgbGDhwBP1Zy8yhZhC7ty54iBVlCovKxc/edit#gid=1

I’m not sure exactly what the difference is, but when I follow the URL in the script, my browser downloads a JSON-file. I think it contains the data from the spreadsheet. So I need to take the URL I already have for my spreadsheet, and format it like in the example.

https://docs.google.com/spreadsheet/tq?key=1kDIxZ5jBWYB2eiAjfsM_jRBr2mYWxudORECFApXBULE&gid=3

I’m almost certain that the gid=3 points to sheet number 3 in the spreadsheet. I’ve accessed the spreadsheet from the example, and copied the data to sheet no. 3 in my own sheet. If I change the long string of random letters in the example, nothing should happen.

It did’nt. The graph on that fiddle looks exactly the same. Now, lets try to the real data. That is in sheet number 1.
Lets also change the “query.setQuery(‘SELECT A, B, etc’)” to “query.setQuery(‘SELECT B, A’)”. That should collect the data from colums B and A in the sheet.
Try again. Yep. I’m now succesfully plotting the data.

It does not, however appears to update live…

It appears that Google does not live up to the promises. I’ll need HighCharts for that. If, that is, I can figure out how to get live data.
There is another problem. This is a very bad graph. It plots the timestamp of the tweet, against the coordinates from the tweet (remember that those were allmost always “None”?). Not that interesting. Maybe Google supports some way of counting? Quick test: Exchange ‘SELECT B, A’ with ‘SELECT COUNT(A), A)’. In theory, is that I should get the count of column A on the x-axis, and the timestamp on the y-axis.

Nope. Better take a look at the query language. Its some variant of SQL. The documentation can be found here. Note – the graph above is not the real thing. I might have to change the spreadsheet, and that will change the graph. Therefore it is an image of the graph. The code will be given below.

And now, before continuing, I have to do something about the laundry.

And now, the promised list of the previuos parts:

Part I: The idea, and the hardware
Part II: Getting my hands on tweets
Part III: Deciding what data to save (that is going to change over the next few days)
Part IV: Storing the data
Part V: Fixing some problems
Part VI: Problems that can wait

And the code for the graph:

Yep, its messy.