Google Charts: How to replace data table elements in visualization, but not in original table (DataView approach not working)?

Question

I'm trying to perform a fairly basic procedure. I have a lengthy data table with large cell sizes to use with Google Visualization. Before creating the page I'd like to replace the large cells with codes and abbreviations to keep the file sizes and loading times down. I can handle that. However, when a cell or column value/name/label shows up in the visualization itself I'd like to see a longform version of the value (ex. 'Male' in original dataset -> 'M' in Google Visualization data table -> 'Male' in category filter dropdown, tooltip, etc.), and this has been unexpectedly problematic.

I've tried to replicate the issue (and my failed attempt to fix it) in the modified Google Playground example below. In this example I've changed the initial dataset to have 'M' and 'F' in the Gender column, and I still want the visualization to display 'Male' and 'Female' in the dropdown and the displayed table as it does in the original visualization.

My attempt to fix is labeled ATTEMPT TO FIX GENDER below; basically, I'm trying to create a DataView of the original table and replace the Gender column with a calculated column transforming the 'M's and 'F's into 'Male's and 'Female's...but I'm not sure if this is a wise approach, even if I could get it to work (which I can't). The point of this runaround is to avoid replacing every short value in the original table with a long one; I only want to replace table values as they are being displayed in the visualization. But I can't find another approach here or elsewhere, and I'm a bit new to this stuff so I don't think I can come up with one without some guidance. At least, not if the past several hours of failure are any indication.

Any advice or suggestions would be sorely appreciated. The original code example is here.

function drawVisualization() {
  // Prepare the data
  var data = new google.visualization.DataTable();
  data.addColumn('string', 'Name');
  data.addColumn('string', 'Gender');
  data.addColumn('number', 'Age');
  data.addColumn('number', 'Donuts Eaten');
  data.addRows([
    ['Michael' , 'M', 12, 5],
    ['Elisa', 'F', 20, 7],
    ['Robert', 'M', 7, 3],
    ['John', 'M', 54, 2],
     ['Jessica', 'F', 22, 6],
     ['Aaron', 'M', 3, 1],
     ['Margareth', 'F', 42, 8],
     ['Miranda', 'F', 33, 6]
  ]);

  *******//ATTEMPT TO FIX GENDER

  /*var sexfix = function (data, row) {
      var val = data.getValue(row, 1);
      switch (val) {
        case 'M':
          val = 'Male';
          break;
        case 'F':
          val = 'Female';
          break;
        default:
          alert('error');
          return 'Error!';
            }
      }

  var dview = new google.visualization.DataView(data);

  dview.setColumns([0,{calc: sexfix, type:'string'}, 2, 3]);

  ********/

  // Define a slider control for the Age column.
  var slider = new google.visualization.ControlWrapper({
    'controlType': 'NumberRangeFilter',
    'containerId': 'control1',
    'options': {
      'filterColumnLabel': 'Age',
    'ui': {'labelStacking': 'vertical'}
    }
  });

  // Define a category picker control for the Gender column
  var categoryPicker = new google.visualization.ControlWrapper({
    'controlType': 'CategoryFilter',
    'containerId': 'control2',
    'options': {
      'filterColumnLabel': 'Gender',
      'ui': {
      'labelStacking': 'vertical',
        'allowTyping': false,
        'allowMultiple': false
      }
    }
  });

  // Define a Pie chart
  var pie = new google.visualization.ChartWrapper({
    'chartType': 'PieChart',
    'containerId': 'chart1',
    'options': {
      'width': 300,
      'height': 300,
      'legend': 'none',
      'title': 'Donuts eaten per person',
      'chartArea': {'left': 15, 'top': 15, 'right': 0, 'bottom': 0},
      'pieSliceText': 'label'
    },
    // Instruct the piechart to use colums 0 (Name) and 3 (Donuts Eaten)
    // from the 'data' DataTable.
    'view': {'columns': [0, 3]}
  });

  // Define a table
  var table = new google.visualization.ChartWrapper({
    'chartType': 'Table',
    'containerId': 'chart2',
    'options': {
      'width': '300px'
    }
  });

  // Create a dashboard
  new google.visualization.Dashboard(document.getElementById('dashboard')).
      // Establish bindings, declaring the both the slider and the category
      // picker will drive both charts.
      bind([slider, categoryPicker], [pie, table]).
      // Draw the entire dashboard.
      draw(data);
}

EDIT: Solution below. In summary:

1) under dview.setColumns, include a label name as an option (e.g., "label: 'Gender'") so that controls and charts can refer to the calculated column by label;

2) draw the view, not the table (final line should be: "draw(dview)");

3) change the variable renaming in my sexfix code from general format "var = 'X'" to "return: {v: 'X' f: 'Xxxxxx'}"; and

4) either add the line "addFormattedValue: true" or use the "view:" option in the ControlWrappers and ChartWrappers to display formatted values.

asgallant asgallant · Accepted Answer · 2014-04-10T20:33:16

I suspect that you have two problems in your code. The first is that when you added the "Gender" column to the DataView, you left off the column label, which is needed because your gender filter has the filterColumnLabel option set instead of filterColumnIndex. Add the label in to fix:

{calc: sexfix, type:'string', label: 'Gender'}

The second problem is that you are drawing your Dashboard with the DataTable instead of the DataView:

new google.visualization.Dashboard(document.getElementById('dashboard')).
// Establish bindings, declaring the both the slider and the category
// picker will drive both charts.
bind([slider, categoryPicker], [pie, table]).
// Draw the entire dashboard.
draw(dview);

Google Charts: How to replace data table elements in visualization, but not in original table (DataView approach not working)?

1 Answers