Using Google's Visualization API, I use google.visualization.data.group to create sub-tables based on my raw data. My raw data uses the trick of {v:"US", f:"United States"} to display something other than the value, but when I use the aggregation function, the formatting is eliminated leaving only the "US" portion.
Is there any way to retain the original formatting, or an easy way to add it back on DataTables created using the group aggregation?
Sample data:
[2010, {v:"MA", f:"Morocco"}, {v:"002", f:"Africa"}, {v:"002", f:"Northern Africa"}, 21.12724],
[2010, {v:"AW", f:"Aruba"}, {v:"019", f:"Americas "}, {v:"019", f:"Caribbean"}, 0.98],
[2010, {v:"AF", f:"Afghanistan"}, {v:"142", f:"Asia"}, {v:"142", f:"Southern Asia"}, 0.9861],
[2010, {v:"AO", f:"Angola"}, {v:"002", f:"Africa"}, {v:"002", f:"Middle Africa"}, 5.11774],
Aggregation Function:
var countryData = google.visualization.data.group(
rawData,
[0, 1],
[{'column': 4, 'aggregation': google.visualization.data.sum, 'type': 'number'}]
);
Edit:
On further thought, it's probably impossible to group with format since there is no guarantee that the format for each value will be consistent. With that in mind, it's probably better (or only possible) to write a function that will add the formats to each column of my data. So the question becomes, "How the heck do I do that?"
I would really rather not create my raw data as unformatted values alone, and then additional tables for looking up the formats for each value. That would require an additional 2 tables (one for regions which is 28 rows, one for countries which is over 240 rows), and then creating two functions to look through each value in the grouped table (which will have 30+ years of data, meaning thousands of lines) adding the values.
That seems like a really complex solution.
Is there some way to do this with modifier functions? Can I write a function to return each value in the table as a {v: "US", f: "United States"} formatted object? Or is there an easy way to write a column formatter that will look up the appropriate value in my original table and adopt that format? Which would cause the least headaches both for me (who has to write it), and for the users (who have to load it)?
EDIT 2:
It looks like I should be able to create a formatter for the new table using something like this:
function (dt, row) {
return {
v: (dt.getValue(row, 1) / 1000000),
f: (dt.getValue(row, 1) / 1000000) + 'M'
}
}
But the issue becomes that since I am not dealing with number formats, I would have to create some sort of lookup table that will take the value, look it up in a lookup table, and then return the appropriate format. It also looks like I may have to loop through the whole table, row by row, which is thousands of lines.
I can't imagine that there isn't an easy way to do this without some brute force looping and assigning values.
EDIT 3:
So I tried something tricky. Rather than setting each row as a value/format, I created the value/format portion as a string, and then after grouping used eval() to evaluate the objects. This worked great. Here is the data:
[2010, "{v: 'MA', f: 'Morocco'}", 21.13],
[2010, "{v: 'AW', f: 'Aruba'}", 0.98],
[2010, "{v: 'AF', f: 'Afghanistan'}", 0.99],
[2010, "{v: 'AO', f: 'Angola'}", 5.12],
Here is the new code:
var countryCount = countryData.getColumnRange(0).count;
for (var i = 0; i <= countryCount; i++) {
countryData.setValue(i, 1, eval('(' + countryData.getValue(i,1) + ')'));
};
The issue is that when I output this to a Google DataTable, it shows {v: 'AE', f: 'United Arab Emirates'} despite the fact that checking the result with eval properly gives me:
>>> eval('(' + countryData.getValue(i,1) + ')')
Object v="AE" f="United Arab Emirates"
So what am I doing wrong here?
I just ran into this issue myself. I decided to use a modifier to change the value to the formatted value using the original dataTable to find the formatted values. This isn't terribly efficient, but it works and computers are fast.
First create a lookup function:
function getFormatForValue(dataTable, column, value) {
// we need to spin through column in the dataTable looking
// for the matching value and then return the formatted value
var rowcount = dataTable.getNumberOfRows();
for (var i=0; i<rowcount; i++) {
if (dataTable.getValue(i, column) === value) {
// we found it, this will look much better
return dataTable.getFormattedValue(i, column);
}
}
// better than nothing
return value;
}
Then call that in a modifier, changing your original group call:
var countryData = google.visualization.data.group(
rawData,
[
{
'column': 0,
'modifier': function(value) { return getFormatForValue(rawData, 0, value); },
'type': 'string'
},
{
'column': 1,
'modifier': function(value) { return getFormatForValue(rawData, 1, value); },
'type': 'string'
}
],
[{'column': 4, 'aggregation': google.visualization.data.sum, 'type': 'number'}]
);
Update: It seems that you need the value and the formatted value preserved. In my case of displaying pie charts, I don't care to preserve the original value. I guess this won't work for you, but I'll leave this answer here for others that may have a simpler case like mine.
I spent a few more minutes on this and here is an alternative that will copy the formatted value while keeping the original cell value.
Create a copy function that uses the lookup function:
function copyFormattedValues(oldDataTable, oldColumn, newDataTable, newColumn) {
var rowcount = newDataTable.getNumberOfRows();
for (var i=0; i<rowcount; i++) {
var value = newDataTable.getValue(i, newColumn);
var formatted = getFormatForValue(oldDataTable, oldColumn, value);
newDataTable.setFormattedValue(i, newColumn, formatted);
}
}
Then in your case, call it once for each column you want to copy.
copyFormattedValues(rawData, 0, countryData, 0);
copyFormattedValues(rawData, 1, countryData, 1);
Your source and destination columns are the same but in some cases they may be different.
Of course, ideally all of this would have just happened automatically.