Normalize (reformat) cross-tab data for Tableau wi

Tableau generally works best when input data is in "normalized" format, rather than cross-tab. This is also referred to as converting from "wide format" to "long format".

That is, converting from:

To:

Tableau provides a "reshaping tool" for Excel users, but if you don't have Excel, you're stuck.

So how can you get a spreadsheet into this format, without using Excel?

回答1:

Well, you can use this handy Google Sheets script I made.

/*
normalizeCrossTab: Converts crosstab format to normalized form. Given columns abcDE, the user puts the cursor somewhere in column D.
The result is a new sheet, NormalizedResult, like this:

a     b     c    Field Value
a1    b1    c1   D     D1
a1    b1    c1   E     E1
a2    b2    c2   D     D2
a2    b2    c2   E     E2
...

Author: 
Steve Bennett
stevage@gmail.com
@stevage1

Licence: Public Domain

*/

function start() {
  var html = HtmlService.createHtmlOutput(
    '<style>ol { padding-left: 1.5em; }</style>' + 
    '<script src="//code.jquery.com/jquery-1.11.2.min.js"></script>' +
    '<script>' + 
    'function allDone(msg) { ' +
    '  $("#normalizeBtn").hide();' +
    '  $("#datacols-output").html("<p>Your normalized data is in a sheet called NormalizedResult. If you run the normalization again, that sheet will be deleted and replaced.</p>");' +
    '};' +
    'function gotCols(cols) { ' + 
    '  $("#datacols-output").html(\'<p>These will be your dependent variables:</p><ul id="datacols"></ul>\'); ' + 
    '  $("#normalizeBtn").show();' +
    '  $.each(cols, function() {' + 
    '    $("#datacols").append($("<li>").text(this)); ' + 
    '  });' + 
    '  $("#datacols").after("<p>If they don\'t look right, move the cursor and press <i>Continue</i>.</p>"); ' + 
    '}' + 
    '</script>' + 
    '<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons.css">' +
    '<h2>Convert cross-tab</h2>'+
    '<p>This macro converts <i>cross-tab</i> data which has multiple dependent variables (eg, observations, sales figures) per row into a <i>normalized</i> format with one dependent variable per row.</p>' + 
    '<p>The name of each dependent variable becomes the value of a new column called <code>Field</code> and its value goes in a column called <code>Value</code>.</p>' +
    '<ol><li>Move <b>all independent variable columns to the left</b></li>' + 
    '    <li>Place the <b>cursor in the first dependent variable column</li></ol>'+
    '<p><button onClick="google.script.run.withSuccessHandler(gotCols).getDataColumns();">Continue</button></p>' + 
    '<p id="datacols-output"></p>' +
    '<p><button id="normalizeBtn" class="create" style="display:none;" onClick="google.script.run.withSuccessHandler(allDone).normalizeCrosstab(true);">Normalize</button></p>' + 
  '<br/><p><a target="_blank" href="http://kb.tableausoftware.com/articles/knowledgebase/denormalize-data">More information</a></p>')
      .setSandboxMode(HtmlService.SandboxMode.IFRAME)
      .setTitle('Normalize cross-tab')
      .setWidth(300);
  SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
      .showSidebar(html);
}

function onOpen() {
  var ss = SpreadsheetApp.getActive();
  var items = [
    {name: 'Normalize Crosstab', functionName: 'start'},
  ];
  ss.addMenu('Normalize', items);
}


function normalizeCrosstab(really) {
  if (!really) {
    return start();
  }
  var sheet = SpreadsheetApp.getActiveSheet(); 
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();
  var firstDataCol = SpreadsheetApp.getActiveRange().getColumn();
  var dataCols = values[0].slice(firstDataCol-1);

  var resultssheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("NormalizedResult");
  if (resultssheet != null) {
    SpreadsheetApp.getActive().deleteSheet(resultssheet);
  }
  var header = values[0].slice(0, firstDataCol - 1);

  var newRows = [];

  header.push("Field");
  header.push("Value");
  newRows.push(header);

  for (var i = 1; i <= numRows - 1; i++) {
    var row = values[i];
    for (var datacol = 0; datacol < dataCols.length; datacol ++) {
      newRow = row.slice(0, firstDataCol - 1); // copy repeating portion of each row
      newRow.push(values[0][firstDataCol - 1 + datacol]); // field name
      newRow.push(values[i][firstDataCol - 1 + datacol]); // field value
      newRows.push(newRow);
    }
  }
  var newSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet("NormalizedResult");
  var r = newSheet.getRange(1,1,newRows.length, header.length);
  r.setValues(newRows);
};

function getDataColumns() {  
  var sheet = SpreadsheetApp.getActiveSheet(); 
  var rows = sheet.getDataRange();
  var values = rows.getValues();
  var firstDataCol = SpreadsheetApp.getActiveRange().getColumn();
  var dataCols = values[0].slice(firstDataCol-1);
  return dataCols;

}

Full write-up with instructions on how to install.

回答2:

Newer versions of Tableau (9.0 and above) allow reshaping of data on import. It's as simple as selecting the columns you want to stack. Here's a useful tutorial.

In 2018, Tableau released Tableau Prep for reshaping and wrangling Data for analysis.

Some commercial tools useful for reshaping and cleaning data worth investigating are:

Trifacta
Paxata
Alteryx

Trifacta was created by some of the people who worked on the previous academic DataWrangler project. I've been told that Alteryx is good for some prep tasks for geospatial related data, and have met some enthusiastic Paxata users.

I have too little hands on experience with them to give more than a reference to their web sites, tending to use Python scripts instead to date.

If you want to "fold" columns that you want to merge into a single column. Tableau wrote a helpful tutorial here. Tableau 9 introduced some useful reshaping features for unpivoting tables and splitting columns.

回答3:

If you are comfortable with command lines and the Unix style of combining small tools using pipes, check out the open source csvkit toolsuite.

You can combine these utilities in many ways to get different effects, so the exact sequence of steps depends on your datasets (that's the point of the tools after all).

But for reshaping tasks, you could use csvcut to pull out columns of interest, csvgrep to pull out rows of interest and cvsstack to combine multiple csv files into one longer one, along with the -g and -n options to add a grouping field.

There are several other useful commands, and if you are familiar with Unix or linux, you can figure out what they do quickly just from the command names.