Respondents' Average Ratings

Data Transformation using Excel and Tableau

Data transformation is fun! More so when you’re doing it using tools like Microsoft Excel and Tableau. While programming languages like Python and R facilitate this very easily, it’s not everybody’s cup of tea. Excel and Tableau are relatively easier to understand.

Problem Statement

A client shared an Excel spreadsheet consisting of responses to a survey. The survey consisted of some 40+ Insurance providers, each evaluated on 5 factors: Responsiveness, Technology, Product, Partnership, In-Force Client Servicing. The data was organized as shown in the picture below, and for all practical purposes, is organized sub-optimally.

Input data organized by level-1 header consisting of Provider, level-2 header consisting of Factor
Input data organized by level-1 header consisting of Provider, level-2 header consisting of Factor

The client wanted to visualize/summarize the performance of each Provider based on all the responses captured. Secondly, he wanted to churn out as many charts as one possibly could using this data, which necessitated some data transformation.

Challenges

As mentioned earlier, this layout of the data is sub-optimal. The Provider names are along the rows and suffixed with a number, one for each factor that the Provider is evaluated on. To be able to use this data to churn charts, it needs to be in a flat-file format, where there is just one level of columns. Essentially, this means bringing Provider names from rows to columns.

Data Transformation

Step 1: In Excel, strip out the numbers at the end of the Provider name. A simple combination of LEFT and LEN functions in Excel does the trick for this step.

Step 2: Now, concatenate the texts for Providers and Factors into a single piece of text, separated by a hyphen (-). Ampersands (&) come in handy for quick tasks like this.

Level-1 and Level-2 column headers are combined into on single-level header
Level-1 and Level-2 column headers are combined into on single-level header

Step 3: Import this data in Tableau.

Step 4: In the Data Source tab in Tableau, select all the columns (except From) and Unpivot this data.

Data Transformation using Pivoted Columns as Field and Field Value
Pivoted Columns as Field and Field Value

Step 5: Create Calculated Fields for Provider and Factor in Tableau.

Calculated Field for Provider
Calculated Field for Provider
Calculated Field for Factor
Calculated Field for Factor

Results

The resultant flat file is now usable for subsequent analyses using Tableau.

Flat File in Tableau
Flat File in Tableau

A few sample visualizations from this flat file are shown below.

Providers' Average Ratings
Providers’ Average Ratings
Respondents' Average Ratings
Respondents’ Average Ratings

You can find more such samples of data visualization on my Tableau Public portfolio. For more such case studies, check out the blog archives.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

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

Google photo

You are commenting using your Google 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 )

Connecting to %s