How to Shape Your Data

by amorrison@table... - Apr. 7th 2011

Unfortunately, data isn’t always in the format that Tableau likes it. That’s okay – we can fix that. The following is a step by step guide to reshaping your data so you can make the most of it in Tableau.

First, make sure there are no blank rows or columns within your data set. That’s usually pretty easy to fix manually but if there’s a systematic problem (like every fifth row is empty) you might prefer to use this nifty tool called Data Wrangler (more on this later) developed by the good people at the Stanford Visualization Group.

The data also needs to be in a data table format where each row contains only one piece of data. What on earth does that mean you ask? Well, it basically means that cross tabulations are bad.

Normalized Data (you want your data shaped this way):

Crosstab Data (you don't want it shaped like this):

While crosstabs make sense to people, it makes our software’s head hurt because each row contains three pieces of data -- in this case the company's net income in 2010, 2009 and 2008. Tableau feels much more comfortable when data is in a normalized format, where each row contains only one net income figure.

It can be a huge pain to do all the copy and pasting necessary to change data into this form. But the Data Wrangler can help with this too. Here’s a step by step guide using the cross-tab data from above:

Start by going to Data Wrangler and watching the brief intro video. Then paste your data into the data box as shown below.

Now promote the top row of data to be the column headers by highlighting it and selecting "promote" from the rows drop down.

Remember to click the "plus" button to confirm the change.

Now we'll reshape the data into a normalized format. Highlight the columns that have the three subject scores. Now "fold" them into one column by selecting the "Fold 2010, 2009, 2008 using header as a key" option from the left.

Delete the underscore that Data Wrangler added to your data. We don't want that. Select the text in one cell and then select "cut from fold."

All that remains is to export the data back to Excel and connect to Tableau:

Comments

You have done a great job with each unique point made on this topic. Thank you for your hard work. Margahayuland | Margahayuland Yurosie ide bagus

Submitted by quora30 on Thu, 05/23/2013 - 8:33am

In genuineness prosperous content and very helpful in sequence. I got it my elucidation from over here. I very much urge his/her workings with the constructive enlightening information. Thanks a lot………..
Illinois business appraisal

Submitted by Jason Hill on Tue, 05/21/2013 - 2:51pm

This is almost access type query ..Where we save or link the data.. Social Bookmarking

Submitted by fred on Tue, 05/21/2013 - 2:31pm

Submitted by Distro White Hat on Sun, 05/19/2013 - 9:24am

I have relay enjoyed this article.thanks so much I have also read it multiple times and plan on coming back for any other articles are bi a publish.
vietnam holidays | activities in vietnam

Submitted by kenguide on Fri, 05/17/2013 - 2:10am

Before you search the information in the internet, you need to think what your need for the holiday is. Do you want budget holiday? Or do you want holiday with high standard of accommodation? Per the information provided, you can then select and use the camping holiday smartly.

banc de swiss

Submitted by randsand on Fri, 05/17/2013 - 10:06am

read it multiple times and plan on coming back for any other articles are bi a publish. driversedguy.tumblr.com

Submitted by Richard Fonis on Fri, 05/17/2013 - 3:54am

in this case the company's net income in 2010, 2009 and 2008. Tableau feels much more comfortable when data is in a normalized format, things to do in Las Vegas

Submitted by Roger Toles on Sat, 05/18/2013 - 2:17am

They're different. My first plan of attack is the add on, if the data is too crazy I bring in the Wrangler. Margahayuland infoneosia malohong yadikhendriyanto

Submitted by Margahayuland on Thu, 05/16/2013 - 7:32am

I just found this blog and have high hopes for it to continue. Keep up the great work, its hard to find good one thanks!noa fonts

Submitted by monir on Sun, 05/05/2013 - 7:14am

I just found this blog and have high hopes for it to continue. Keep up the great work, its hard to find good one thanks!noa fonts

Submitted by monir on Sun, 05/05/2013 - 7:09am

"This is just the information I am finding everywhere. Thanks for your blog, I just subscribe your blog. This is a nice blog. " the Spook SEO gig

Submitted by ymous on Thu, 04/25/2013 - 3:30am

www.traveltrips.co Before you search the information in the internet, you need to think what your need for the holiday is. Do you want budget holiday? Or do you want holiday with high standard of accommodation? Per the information provided, you can then select and use the camping holiday smartly.

Submitted by reeti on Thu, 05/02/2013 - 11:14pm

Submitted by reeti on Thu, 05/02/2013 - 11:13pm

Thanks a lot for your job! You saved huge amount of time for me! I would be very grateful if you keep sharing such info with us Web Analytics Consulting

Submitted by Nati on Thu, 04/25/2013 - 12:06am

data shaping? That I guess i can always have that. Thank you so much for the guidelines and now I am looking forward into it now. PMP

Submitted by halena on Wed, 04/24/2013 - 12:54am

Thanks for taking this opportunity to discuss this, I feel fervently about Payment Service Provider and Excel - I like learning about this subject. If possible, as you gain information, please update this blog with more information. I have found it really useful.

Submitted by Jessw on Tue, 04/23/2013 - 4:44am

I will visit your blog every day because I like this page thanks. Submitted by Jessw on Tue, 04/23/2013 - 4:42am

I always had problems with table formating. Thank you a lot!

logo design

Submitted by designare logos on Mon, 04/22/2013 - 7:29pm

The reliable and experienced carriers are hired to perform shipping for TELS. For example, liquid and high-value cargos are transported only by regular carriers with whom the company has already cooperated before.

Submitted by Mike Walles on Mon, 04/22/2013 - 9:41am

The key link in the chain of transportation of cargoes is the consolidation of small lots more senders on a special ltl trucking company. Consolidation (consolidated) warehouse provides a starting point for regular transportation of cargoes on a given route. Time of delivery in this case varies depending on the term of the party assembly, route of transportation and other factors.

Submitted by James Daniels on Mon, 04/22/2013 - 9:40am

Thanks for taking this opportunity to discuss this, I feel fervently about rattan patio furniture and Excel - I like learning about this subject. If possible, as you gain information, please update this blog with more information. I have found it really useful.

Submitted by Jes on Mon, 04/22/2013 - 5:45am

love this website I hope it stays up for long, and prosperous.. u know wat I mean guys Download Full English Sub

Submitted by Download Full English Sub on Tue, 04/09/2013 - 7:54pm

I will visit to your blog every day because I like this page thanks. ingin cepat hamil

Submitted by tips cepat hamil on Tue, 04/09/2013 - 7:53pm

Thanks for taking this opportunity to discuss this, I feel fervently about TELS and I like learning about this subject. If possible, as you gain information, please update this blog with more information. I have found it really useful.

Submitted by jwoods on Tue, 04/09/2013 - 2:55am

I just wish to give an enormous thumbs up for the nice info you've got right here on this post.Thank You for the post That is genuinely helpful. Arcadia Garage Door Repair I must acknowledged you for the information you shared. I like the services that amazingly surprised me that this kind of work should be done in the proper way and also thanks for the information you shared.This looks absolutely perfect.I am very much overwhelmed by your thoughts for this particular story.

Submitted by WATSON on Sat, 02/09/2013 - 2:44am

Please read about an automated Excel unpivot utility, similar to data wrangler

http://www.spreadsheet1.com/unpivot-data.html

Advantages:

The conversion utility can accommodate any number of fixed columns to the left the data and a header row that is not immediately above the data.

Blank or zero cells could be excluded from the output database.

Column A in the database lists the row number of the input table. This information is useful when a pivot table is created from the new database table and rows should be sorted according the original sort order, for example when converting financial statements e.g. a multi-year balance sheet.

Arrays are used in order to minimize computing time by 'hitting' the Excel grid only twice.

The input parameters can be saved as a named template for future use on identical spreadsheets.

Submitted by Petros on Wed, 05/02/2012 - 6:10pm

Thanks for the tip Petros. That looks like a useful tool. Cheers, Lori

Submitted by lwilliams on Thu, 05/03/2012 - 7:55am

I need some excel help when submitting my excel data into Tableau. I'm not sure if this data is in the format that Tableau likes. My excel spreadsheet only has one piece of data in each row and each cell. It still isn't uploading correctly. Is it better to save my excel in a csv format?

Submitted by Randy on Mon, 08/01/2011 - 12:27pm

Data Wrangler is good, but it's not as intuitive as the reshape tool (especially if all you're doing is reshaping). For the moment, it's also row limited. You can record your transformations, then run them against a larger daatset using a local copy of python, but that's another step to do.

Submitted by Alex Kerin on Thu, 06/09/2011 - 1:09pm