Experimenting with Google Charts and Graphs API.

[lardconcepts]

Not that there's anything wrong with the default graph images, but I wondered about letting Google do the work, and for the future when I've got more data, easily selecting and zooming ranges.

I see that someone on another forum was discussing Cumulus with Google Charts, but the demo he links to now appears to be using the very polished and lovely Highcharts, which is free for personal use. Perfect! But... it's another API to learn and you need to manipulate the data before you feed it to the API.

I want simples, dammit! So, how simple is this:

• Convert dayfileheader.txt to a csv and save it as dayfileheader.csv on your weather website (or use mine, see below).
• Add dayfile.txt to the upload table in Cumulus, and tell it to save it on the server as dayfile.csv
• Open a new Google Spreadsheet. Now (using my data here) copy and paste the first line into A1, and the second into A2 (corrected now!) - (here's what mine looks like).

  Code: Select all

  =ImportData("http://weather.talking-news.info/dayfileheader.csv")
  =ImportData("http://weather.talking-news.info/dayfile.csv")

• Now just INSERT>GRAPH, use ranges Sheet1!A:A, Sheet1!P:P, pick a suitable graph, then copy the "embed" code.
• Paste in Webpage, et voila; for example: http://weather.talking-news.info/ggraphs/index.html

And that... is... it! From now on, your chart and Google spreadsheet will reflect your dayfile.
SOME users suggest there might be an update delay of up to an hour when the source file changes; for the dayfile, this won't matter too much.

Now, of course it might be that you want a fancy dashboard to switch between charts, or to run a motion chart over a time period, in which case you can get a bit more advanced with https://developers.google.com/chart/ using your same source data spreadsheet.

If you don't see the type of chart you want, don't forget that Google also has a whole other static image chart API; perhaps you fancy a radar chart to show wind distribution. If you're going to use the static image charts as opposed to the interactive javascript charts, then you might want to try the chart wizard.

Hope this helps someone - I can see a lot of potential. I just need to learn how to use it beyond basic charts, now!

[nitrx]

Looks nice but did you manipulite the dayfileheader ? Al fields are put in cel A1, and the dayfile.csv should be put in A2 ??

[lardconcepts]

Looks nice but did you manipulite the dayfileheader ? Al fields are put in cel A1, and the dayfile.csv should be put in A2 ??

Oops, no, I cocked up! You're right, it's A1 and A2 - well spotted, corrected above now. Thanks!

[nitrx]

Arrgh doesn't work I can't change the separators I've a comma (, ) for decimals and datenotation is dd-mm-yyyy csv sepeator is a semicolumn (;) this can't be changed in googlespreadsheets as far as I know

[lardconcepts]

Arrgh doesn't work I can't change the separators I've a comma (, ) for decimals and datenotation is dd-mm-yyyy csv sepeator is a semicolumn (;) this can't be changed in googlespreadsheets as far as I know

Ah, so your dayfile.txt is being generated based on your system's local? That hadn't occurred to me. There's got to be a way of overcoming that.

Can you link to your dayfile or paste a bit here? That's my Friday Challenge!

[nitrx]

Arrgh doesn't work I can't change the separators I've a comma (, ) for decimals and datenotation is dd-mm-yyyy csv sepeator is a semicolumn (;) this can't be changed in googlespreadsheets as far as I know

Ah, so your dayfile.txt is being generated based on your system's local? That hadn't occurred to me. There's got to be a way of overcoming that.

Can you link to your dayfile or paste a bit here? That's my Friday Challenge!

Yes this is always a problem my dayfile is over here http://www.apeldoorn.tk/weer/dayfile.txt

Thanks in advance

[lardconcepts]

Yes this is always a problem my dayfile is over here http://www.apeldoorn.tk/weer/dayfile.txt

Challenge accepted! I'll get back to you later...

[nitrx]

Yes this is always a problem my dayfile is over here http://www.apeldoorn.tk/weer/dayfile.txt

Challenge accepted! I'll get back to you later...

FYI I found this http://support.google.com/fusiontables/ ... wer=171181 I can import the dayfile.csv from my local disk and alter the seperator unfotunally this cannot be done with remote files, it seems you don't have to rename dayfile.txt to dayfile.csv ..

[lardconcepts]

FYI I found this http://support.google.com/fusiontables/ ... wer=171181 I can import the dayfile.csv from my local disk and alter the seperator unfotunally this cannot be done with remote files, it seems you don't have to rename dayfile.txt to dayfile.csv ..

Yes, you can also do the same with Google Spreadsheets. But as you mentioned, no good here!

So, I think I have solved the challenge! It's not pretty, maybe some expert with Google Spreadsheets can do it far better, but this works:
https://docs.google.com/spreadsheet/ccc ... Kemc#gid=3

And here's your data in a live graph - http://weather.talking-news.info/ggraphs/nitrxdemo.html - it's nice that you have lots of data to test with!
Click the time ranges on the top left, or select a range from the slider below.

Just note that for me it takes about 7 or 8 seconds to display the graph on the webpage because of all the data.
If you wanted, you could probably put some sort of ajaxy "spinner" there or a note to wait a moment or something.

So, in the first sheet, I put in A1:

Code: Select all

=ImportData("http://www.apeldoorn.tk/weer/dayfile.txt")

Then I made a new sheet. In A1 I put:

Code: Select all

=ArrayFormula(JOIN(",",'Raw import'!A1:V1))

This joins the "incorrect" splits back together again.

In B1, I swap make your decimal comma into a decimal point:

Code: Select all

=SUBSTITUTE(A1,",",".")

In C1, I change your semicolon separator into a comma.

Code: Select all

=SUBSTITUTE(B1,";",",")

With both of these, copy the cell, select the whole column and paste, so it grows with the data.

Now we have comma separated values with dotted decimals, I make a third sheet and in A1 I put the header row:

Code: Select all

=ImportData("http://weather.talking-news.info/dayfileheader.csv")

And in A2 I import the "fixed" column from the previous sheet, and split it on the comma.

Code: Select all

=arrayformula(split('Merged data'!C:C,","))

The arrayformulas mean that the data will expand as needed in both rows and columns.

And that's it - unless the Google Docs docs are wrong, when your dayfile updates, the Google spreadsheet will update the import, the split and merge will update, and the chart will update. Everything depends on each other and updates automatically (or should, according to Google!). So no need to touch the spreadsheet again.

Feel free to copy this to your own google docs account so you can play around.

Of course, this is only one basic graph using one type of data, but I think we've overcome the first hurdle now!

PS - I'm not as Google Docs expert, I'm just learning as I go. I make no guarantees!

[nitrx]

PS - I'm not as Google Docs expert, I'm just learning as I go. I make no guarantees!

Great job I never had figured this out , now I can make an inteface for differnt graphs, I'll try to do this with the month logs too,

Thank you very much great job my first samplegraph http://apeldoorn.tk/weer/googlegraph.html

[lardconcepts]

Thank you very much great job my first samplegraph http://apeldoorn.tk/weer/googlegraph.html

My pleasure - now I need to get my head into the advanced options and make some REALLY smart looking graphs

[beteljuice]

I don't know which 'route' you took to modify nitrx style data, but it seems to work. I didn't check early years data (they used to have a different line ending which can stuff parsers !)

Not totally sure exactly what format you need for the google graphs, but here is a beteljuice (php) take on things. In the google data call it should refer to eg. csv.php

csv.php

Code: Select all

<?php
/*
   modify dayfile.txt for google graphs format
   beteljuice April 2012
   
   create a cache (csv) file no more than once every 6 hrs (don't know or care when you update your dayfile.txt)
   
*/

$dayfile = "http://www.apeldoorn.tk/weer/dayfile.txt"; // path or full url to dayfile

$then = time() - (6 * 60 * 60); // 6 hours ago

if(!file_exists("dayfile.csv") || (file_exists("dayfile.csv") && filemtime("dayfile.csv") < $then)) {
     $raw = file($dayfile);
     if($raw){
        $output = "";
        // cheat to see if . is dec delimiter
        $dec_check = substr($raw[0], 10, 4); // get a chunk which includes temp
        if (strpos($dec_check, ".") !==FALSE ){ // must be .
            $delim = 0;
        } else {
            $delim = 1; // dec delimiter needs replacing (assuming ",")
        }
        $date_check = substr($raw[0], 2, 1); // get date seperator
        // check date seperator is /
        if ($date_check == "/"){ // must be /
            $datelim = 0;
        } else {
            $datelim = 1; // needs replacing
        }
        // find field seperator
        $field_check = substr($raw[0], 8, 1);
        if ($field_check == ","){ // not a problem
            $fieldlim = 0;
        } else {
            $fieldlim = 1; // needs replacing
        }
        foreach($raw as $key) { // step through the raw data
            $key = rtrim($key); // get rid of last data field line returns prior to 1st Nov 2010
            if($delim){
                $key = str_replace(",", ".", $key); // make sure dec seperator is .
            }
            if($fieldlim){
                $key = str_replace($field_check, ",", $key); // make sure field seperator is ,
            }
            if($datelim){ // php4 work around
                $date_check = "/" .$date_check. "/";
                $key = preg_replace('.$date_check.', "/", $key, 2); // make sure date seperator is /
            }
            $output .= $key."\n";
        } // end foreach $raw

        // save the cache file
        $fh = fopen("dayfile.csv", "w");
        fwrite($fh, $output);
        fclose($fh);
    } // END if $raw
} // a valid dayfile.csv should exist ! (unless perhaps you are running in safe mode)
readfile("dayfile.csv");
?>

[nitrx]

I think there must be something wrong with the spreadsheet the cells aren't calculated at the end when a new day was added, I updated the dayfile.txt on my server but after an hour the graph stayed stuck on the 5th of april last cells in the speadsheet seems to be out of range ?

BTW, Beteljuice I see the dayfile.csv is created on your server nice job maybe it can be a solution...

[nitrx]

create a cache (csv) file no more than once every 6 hrs (don't know or care when you update your dayfile.txt)

*/

$dayfile = "http://www.apeldoorn.tk/weer/dayfile.txt"; // path or full url to dayfile

$then = time() - (6 * 60 * 60); // 6 hours ago

As I don't almost know nothing about php can this time-interval be increased something like

Code: Select all

$then = time() - (0 * 0 * 0); // 0 seconds ago

I don't understand this formula ...

Second how does the script know to access the dayfile.txt without a command to process ??

[lardconcepts]

I think there must be something wrong with the spreadsheet the cells aren't calculated at the end when a new day was added, I updated the dayfile.txt on my server but after an hour the graph stayed stuck on the 5th of april last cells in the speadsheet seems to be out of range ?

That is really weird; I found the culprit - in "merged data", the last row had "skipped" one, so that the formulas were:
=ArrayFormula(JOIN(",",'Raw import'!A838:V838))
=ArrayFormula(JOIN(",",'Raw import'!A839:V839))
=ArrayFormula(JOIN(",",'Raw import'!A841:V841))

Skipping
=ArrayFormula(JOIN(",",'Raw import'!A840:V840))

So it was looking for tomorrow's data, which wasn't there yet of course.

I've re-aligned it, not sure how that happened unless a row was deleted or I messed up.
I might try doing one from scratch, see if I can duplicate, and then post in Google Help if it does it again.

Anyway, you're fixed for now - fingers crossed for tomorrow!

PS - are you uploading it automatically through Cumulus? No need to edit anything manually, let Cumulus do the upload

I don't know which 'route' you took to modify nitrx style data, but it seems to work.

Click back a page. All is explained in this post

A php script is OK if you want to avoid Google Docs, but with my way, there's no need for any server side processing, and you get your graphs data in the right format.

Not saying your way is bad, wrong, or anything, but I don't see an immediate advantage.

That said, we've yet to see if "my way" is actually reliable - time will tell