Grouping in the DataTable

[API Developer]

Hey guys,

Do you know if it's possible to keep the domain the same if you want to group based off another variable entirely?

My data looks like this:

var data = google.visualization.arrayToDataTable([
      ['ID', 'Life Expectancy', 'Fertility Rate', 'Region',     'Population'],
      ['CAN',    80.66,              1.67,      'North America',  33739900],
      ['DEU',    79.84,              1.36,      'Europe',         81902307],
      ['DNK',    78.6,               1.84,      'Europe',         5523095],
      ['EGY',    72.73,              2.78,      'Middle East',    79716203],
      ['GBR',    80.05,              2,         'Europe',         61801570],
      ['IRN',    72.49,              1.7,       'Middle East',    73137148],
      ['IRQ',    68.09,              4.77,      'Middle East',    31090763],
      ['ISR',    81.55,              2.96,      'Middle East',    7485600],
      ['RUS',    68.6,               1.54,      'Europe',         141850000],
      ['USA',    78.09,              2.05,      'North America',  307007000]
    ]);

I want to do a group on Region, is this possible.

If not, is there anything I can do so I can keep a list of the ID's that would go under the region?

--My plan would be to make it so it shows in the tooltips

[asgallant]

You can group by any column you want.  What would you like the dataTable to look like after grouping?  Alternatively, what do you want to do with the grouped data?

[API Developer]

I was thinking about doing something like this:

function drawJoin() {
  var dt = google.visualization.arrayToDataTable([
    ['Department Number', 'First Name', 'Last Name', 'Sales'],
    [1,                      'john',      'doe',       10],
    [1,                      'jane',      'doe',       100],
    [3,                      'jill',     'jones',      50],
    [3,                      'jack',     'janes',      75],
    [5,                      'al',    'weisenheimer',  500]
  ]);

  // Group dt by column 0, and show column 3 aggregated by sum.
  var grouped_dt = google.visualization.data.group(
      dt, [0, 2],
    [ 
      {'column': 1, 'aggregation': function(ar) { return ar.toString() }, 'type': 'string'}, 
      {'column': 3, 'aggregation': google.visualization.data.sum, 'type': 'number'}
    ]);


  var table = new google.visualization.Table(document.getElementById('table'));
  table.draw(dt, null);

  var grouped_table = new google.visualization.Table(document.getElementById('grouped_table'));
  grouped_table.draw(grouped_dt, null);
}
​

Will this sort of thing work if I have tooltips/things with roles? Do I process them like all the other columns?

[asgallant]

Yes, grouping works with column roles.  If the role column is part of the group, you just need to use the column number.  If it is one of the columns being grouped, you need to add role: 'tooltip' to the column map, ie:

{column: 4, aggregation: function(ar) { return ar.join('\n') } , type: 'string' , role: 'tooltip'}, 

[API Developer]

That's great!

Are you sure the role: 'tooltip' works? I tried that and haven't been getting back the correct column when I do the group...

[asgallant]

Hmm...the Group function may not have been updated to handle column roles.  No problem - it just takes one extra step: after grouping the data, use the #setColumnProperty method to set the column's role:

// set the fifth column to the 'tooltip' role

groupedData.setColumnProperty(4, 'role', 'tooltip'); 

[API Developer]

Sounds good, thank ya!

--Should I do a bug request to get this resolved though?

[API Developer]

Hey asgallant,

Is there any way to get the information for what's currently being grouped on? I'd like to be able to not just add the tooltips together, but add the column information for what grouped it, so the user would get a better idea of what they're seeing.

On Wednesday, August 15, 2012 11:03:00 PM UTC-4, asgallant wrote:

[asgallant]

You can add any extra info you want.  The aggregation function takes an array of data and outputs a single data value, which can be anything (as long as it conforms to the data type specified), so if you want to add any extra data to the tooltip, go right ahead.

[API Developer]

Done and done :D