Aggregate data before charting

[A Eady]

Hi there, 

I'm pretty new to the data visualization game. I'm wondering if the following is possible with dataTables/dataViews or if I should do it in PHP. 

I am building a charting application that charts a dataset using a Line Chart at 5 min intervals, however I would like to be able to show a monthly overview of sums and averages in a Column Chart form. 
Can I use my existing dataTable (5min intervals) to build a dataTable that aggregates the data into a daily average or sum?

IE. table 1 showing 5 min interval raw data

NB - times are really dateobjects

Time	temperature	rainfall
5	25	1
10	22	2
15	25	0
20	25	0
25	26	0
30	27	1
etc	etc	etc

table 2 showing aggregate data based on table 1

Day	temperature (avg)	rainfall (sum)
Mon	25	10
Tues	24	2
Wed	22	0
Thurs	26	0
Fri	26	0

I think I know how to do it in PHP, so I could build a new dataTable of this info that way, but I feel like is would be more efficient to use the dataTable I already have. 
I would appreciate any advice you might have. 

[Thomas Rybka]

You can totally do this with the group method.

Here's a jsfiddle: http://jsfiddle.net/kTWBe/2/

You want to group the data, and you'll want a function to effectively "floor" the datetimes to the nearest day (or whatever).

Assuming your dataTable looks something like this:

    var data = new google.visualization.DataTable();

    data.addColumn('datetime', 'Time');

    data.addColumn('number', 'Temperature');

    data.addColumn('number', 'Rainfall');

    data.addRows([

        [new Date(2013, 1, 2, 10, 0, 0), 25, 1],

        [new Date(2013, 1, 2, 10, 5, 0), 25, 2],

        [new Date(2013, 1, 2, 10, 10, 0), 27, 3],

        [new Date(2013, 1, 2, 10, 15, 0), 27, 4],

        ...

    ]);

   function floorDate(datetime) {

     var newDate = new Date(datetime);

     newDate.setHours(0);

     newDate.setMinutes(0);

     newDate.setSeconds(0);

     return newDate;

   }

   var newData = google.visualization.data.group(data, [{

        column: 0,

        modifier: floorDate,

        type: 'date'

    }], [{

        column: 1,

        label: 'Avg Temp',

        aggregation: google.visualization.data.avg,

        type: 'number'

    }, {

        column: 2,

        label: 'Rainfall',

        aggregation: google.visualization.data.sum,

        type: 'number'

    }]);

-Tom

--
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/groups/opt_out.

[Thomas Rybka]

Here are the docs for google.visualization.data.group:

https://developers.google.com/chart/interactive/docs/reference#google_visualization_data_group

[A Eady]

That's fantastic! So much easier than what I had in mind. 

Thank you!

To unsubscribe from this group and stop receiving emails from it, send an email to google-visualization-api+unsub...@googlegroups.com.