Advertisements

Steve Bennett blogs

…about maps, open data, Git, and other tech.

Normalize cross-tabs for Tableau: a free Google Sheets tool


Problem

You want to do some visualisation magic in Tableau, but your spreadsheet looks like this:

All those green columns are dependent variables: independent observations about one location defined by the white columns.

Tableau would be so much happier if your spreadsheet looked like this:

This is called “normalizing” the “cross-tab” format, or converting from “wide format” to “long format”, or “UNPIVOT“. Tableau provides an Excel plugin for reshaping data. Unfortunately, if you don’t use Excel, you’re stuck. It’s kind of weird.

Solution

Anyway, I’ve made a Google Sheets script “Normalize cross-tab” that will do it for you.

As the instructions say, to use it, you:

  1. Reorganise your data so that all the independent variable columns are to the right of all the dependent ones; then
  2. Place the cursor somewhere in the first (leftmost) independent variable column.

It then creates a new sheet, “NormalizedResult”, and puts the result there.

How to use

It’s surprisingly clumsy to share Google Scripts, at least until the new “Add-ons” feature is mature. Here’s the best I can do for you:

1. Copy the script to the clipboard

Go to https://raw.githubusercontent.com/stevage/normalize-crosstab/master/normalizeCrossTab.gs, select all the text, and copy to the clipboard.

2. Upload your spreadsheet to Google Sheets

Upload your Excel spreadsheet into Google Sheets, if it’s not there already.

3. Tools > Script Editor…

4. Click “Spreadsheet”

 

 

 

 

 

 

 

5. Paste

In the window labelled “Code.gs”, select all the text and paste over it the script from the clipboard.

6. Save.

You need to give this script “project” a name. It doesn’t matter.

7. Select the “start” function.

8. Click Run

Click Continue and accept the authorisation request.

9. Follow the instructions of the script

Now, switch windows to your Google Sheet, and you’ll see the sidebar.

10. Download your normalised spreadsheet

On the NormalizedResult page, choose File > Download as…

Screenshot 2015-01-06 20.53.36

 

 

 

 

If you want to convert several spreadsheets, you can save yourself pain by loading them all into the same workbook. Just remember that the script will always save its output to NormalizedOutput.

Advertisements

6 responses to “Normalize cross-tabs for Tableau: a free Google Sheets tool

  1. Josh Smith June 2, 2016 at 2:24 am

    This works perfectly, thank you!

  2. cassidyh October 10, 2016 at 10:39 pm

    Tried this but my normalized sheet was just blank, save for the “field” and “result” headers. Any idea what I did wrong?

  3. alexmohseni December 23, 2016 at 6:51 am

    Great script. I noticed that if the dependent variables are of DATE type, the script doesn’t work, but by switching dates to TEXT, it works. Thanks!

  4. Sara Goodwin January 20, 2017 at 4:53 pm

    This saved me so much time with my Tableau group project. Thanks so much!

  5. Thom February 8, 2017 at 10:58 am

    is there a fix for the DATE Type issue? I can convert it to text but then it is text and not a date which creates further problems.

  6. Pingback: Eine Tabelle in Google Tabellen entpivotieren – Jacob Fricke

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: