Data Visualization

Atul Nag

Associate Professor, KISS - DU

Data Visualization

• The first step in trying to interpret data is often to visualize it in some way.
• Data visualization can be as simple as creating a summary table, or it could require generating charts to help interpret, analyze, and learn from the data.
• Data visualization is very helpful for identifying data errors and for reducing the size of your data set by highlighting important relationships and trends.
• Data visualization is also important in conveying your analysis to others.
• Although business analytics is about making better decisions, in many cases, the ultimate decision-maker is not the person who analyzes the data.
• Therefore, the person analyzing the data has to make the analysis simple for others to understand.
• Proper data-visualization techniques greatly improve the ability of the decision-maker to interpret the analysis easily.

Overview of Data Visualization

• Decades of research studies in psychology and other fields show that the human mind can process visual images such as charts much faster than it can interpret rows of numbers.
• However, these same studies also show that the human mind has certain limitations in its ability to interpret visual images and that some images are better at conveying information than others.

Effective Design Techniques

• One of the most helpful ideas for creating effective tables and charts for data visualization is the idea of the data-ink ratio, first described by Edward R. Tufte in 2001 in his book - The Visual Display of Quantitative Information.
• The data-ink ratio measures the proportion of what Tufte terms “data-ink” to the total amount of ink used in a table or chart.

Case Study: Gossamer Industries

• Gossamer Industries, a firm that produces fine silk clothing products.
• Gossamer is interested in tracking the sales of one of its most popular items, a particular style of women’s scarf.
• The data used in this table and figure represent product sales by day.

Problems in the table & figure

• In the table, most of the grid lines serve no useful purpose.
• Likewise, in the figure, the horizontal lines in the chart also add little additional information.
• In both cases, most of these lines can be deleted without reducing the information conveyed.
• However, an important piece of information is missing from the figure: labels for axes.
• Axes should always be labelled in a chart unless both the meaning and unit of measure are obvious.

Modified table & figure

• Removing the unnecessary lines has increased the “white space,” making it easier to read both the table and the chart.
• The fundamental idea in creating effective tables and charts is to make them as simple as possible in conveying information to the reader.

Tables

This Photo by Unknown Author is licensed under CC BY-SA

When to use tables?

• The first decision in displaying data is whether a table or a chart will be more effective.
• In general, charts can often convey information faster and easier to readers, but in some cases a table is more appropriate.
• Tables should be used when the
1. Reader needs to refer to specific numerical values.
2. Reader needs to make precise comparisons between different values and not just relative comparisons.
3. Values being displayed have different units or very different magnitudes.

When to use tables?

• When the accounting department of Gossamer Industries is summarizing the company’s annual data for completion of its federal tax forms, the specific numbers corresponding to revenues and expenses are important and not just the relative values.

Preferred Solution

• The preferred solution is to combine the chart with the table into a single figure, as in Figure 3.6, to allow the reader to easily see the monthly changes in revenues and costs while also being able to refer to the exact numerical values.

Adding head count

• Now suppose that you wish to display data on revenues, costs, and headcount for each month.
• Costs and revenues are measured in dollars, but headcount is measured in the number of employees.
• Although all these values can be displayed on a line chart using multiple vertical axes, this is generally not recommended.
• Because the values have widely different magnitudes (costs and revenues are in the tens of thousands, whereas headcount is approximately 10 each month), it would be difficult to interpret changes on a single chart.

Table Design Principles

• In designing an effective table, keep in mind the data-ink ratio and avoid the use of unnecessary ink in tables.
• In general, this means that we should avoid using vertical lines in a table unless they are necessary for clarity.
• Horizontal lines are generally necessary only for separating column titles from data values or when indicating that a calculation has taken place.

Table Design Principles

• Most people find Design D, with the fewest grid lines, easiest to read.
• In this table, grid lines are used only to separate the column headings from the data and to indicate that a calculation has occurred to generate the Profits row and the Total column.

Table Design Principles

• In large tables, vertical lines or light shading can be useful to help the reader differentiate the columns and rows.

Crosstabulation

• A useful type of table for describing data of two variables is a crosstabulation, which provides a tabular summary of data for two variables.
• Data on the quality rating, meal price, and the usual wait time for a table during peak hours were collected for a sample of 300 Los Angeles area restaurants

Categorical

Quantitative

Crosstabulation

Pivot Tables

• A crosstabulation in Microsoft Excel is known as a PivotTable.
• We will first look at a simple example of how Excel’s PivotTable is used to create a crosstabulation of the Zagat’s restaurant data shown previously

Pivot Tables

• To create a PivotTable in Excel, we follow these steps:
• Step 1. Click the Insert tab on the Ribbon
• Step 2. Click PivotTable in the Tables group
• Step 3. When the Create PivotTable dialog box appears:
• Choose Select a table or range
• Enter A1:D301 in the Table/Range: box
• Select New Worksheet as the location for the PivotTable Report
• Click OK

​

Pivot Tables

• The following steps show how to use Excel’s PivotTable Field List to assign the Quality Rating field to the rows, the Meal Price ($) field to the columns, and the Restaurant field to the body of the PivotTable report.
• Step 4. In the PivotTable Fields task pane, go to Drag fields between areas below:
• Drag the Quality Rating field to the ROWS area
• Drag the Meal Price ($) field to the COLUMNS area
• Drag the Restaurant field to the VALUES area
• Step 5. Click on Sum of Restaurant in the VALUES area
• Step 6. Select Value Field Settings from the list of options
• Step 7. When the Value Field Settings dialog box appears:
• Under Summarize value field by, select Count
• Click OK

Pivot Tables

• To complete the PivotTable, we need to group the columns representing meal prices and place the row labels for a quality rating in the proper order:
• Step 8. Right-click in cell B4 or any other cell containing a meal price column label
• Step 9. Select Group from the list of options
• Step 10. When the Grouping dialog box appears:
• Enter 10 in the Starting at: box
• Enter 49 in the Ending at: box
• Enter 10 in the By: box
• Click OK
• Step 11. Right-click on “Excellent” in cell A5
• Step 12. Select Move and click Move “Excellent” to End

(This only works in the Desktop App so ignore this)

Pivot Tables

(This only works in the Desktop App so ignore this)

Pivot Tables: Frequency Distribution

• We can also use a PivotTable to create per cent frequency distributions, as shown in the following steps:
• Step 1. To invoke the PivotTable Fields task pane, select any cell in the pivot table
• Step 2. In the PivotTable Fields task pane, click the Count of Restaurant in the VALUES area
• Step 3. Select Value Field Settings . . . from the list of options
• Step 4. When the Value Field Settings dialog box appears, click the tab for ShowValues As
• Step 5. In the Show values as area, select % of Grand Total from the drop-down menu
• Click OK

Pivot Tables

• PivotTables in Excel are interactive, and they may be used to display statistics other than a simple count of items. As an illustration, we can easily modify the PivotTable to display summary information on wait times instead of meal prices.
• Step 1. To invoke the PivotTable Fields task pane, select any cell in the pivot table
• Step 2. In the PivotTable Fields task pane, click the Count of Restaurant field in the VALUES area
• Select Remove Field
• Step 3. Drag the Wait Time (min) to the VALUES area
• Step 4. Click on Sum of Wait Time (min) in the VALUES area
• Step 5. Select Value Field Settings… from the list of options
• Step 6. When the Value Field Settings dialog box appears:
• Under Summarize value field by, select Average
• Click Number Format
• In the Category: area, select Number
• Enter 1 for Decimal places:
• Click OK
• When the Value Field Settings dialog box reappears, click OK

Pivot Tables

• We can also examine only a portion of the data in a PivotTable using the Filter option in Excel.
• To Filter data in a PivotTable, click on the Filter Arrow next to Row Labels or Column Labels and then uncheck the values that you want to remove from the PivotTable.
• For example, we could click on the arrow next to Row Labels and then uncheck the Good value to examine only Very Good and Excellent restaurants.

Recommended PivotTables in Excel

Excel also can recommend PivotTables for your data set.

Charts

Charts (or graphs) are visual methods for displaying data.

Scatter Charts

• A scatter chart is a graphical presentation of the relationship between two quantitative variables.
• consider the advertising/sales relationship for an electronics store in San Francisco.

Scatter Chart

• Step 1. Select cells B2:C11
• Step 2. Click the Insert tab in the Ribbon
• Step 3. Click the Insert Scatter (X,Y) or Bubble Chart button in the Charts group
• Step 4. When the list of scatter chart subtypes appears, click the Scatter button
• Step 5. Click the Design tab under the Chart Tools Ribbon
• Step 6. Click Add Chart Element in the Chart Layouts group
• Select Chart Title, and click Above Chart
• Click on the text box above the chart, and replace the text with Scatter Chart for the San Francisco Electronics Store

Scatter Chart

• Step 7. Click Add Chart Element in the Chart Layouts group
• Select Axis Title, and click Primary Horizontal
• Click on the text box under the horizontal axis, and replace “Axis Title” with Number of Commercials
• Step 8. Click Add Chart Element in the Chart Layouts group
• Select Axis Title, and click Primary Vertical
• Click on the text box next to the vertical axis, and replace “Axis Title” with Sales ($100s)
• Step 9. Right-click on one of the horizontal grid lines in the body of the chart, and
• click Delete
• Step 10. Right-click on one of the vertical grid lines in the body of the chart, and
• click Delete

Scatter Chart: Trendline

• A trendline is a line that provides an approximation of the relationship between the variables. To add a linear trendline using Excel, we use the following steps:
• Step 1. Right-click on one of the data points in the scatter chart and select
• Add Trendline…
• Step 2. When the Format Trendline task pane appears, select Linear under Trendline Options

Scatter Charts

Charts

• The Chart Buttons in Excel allow users to quickly modify and format charts.
• Three buttons appear next to a chart whenever you click on a chart to make it active.
• Clicking on the Chart Elements button brings up a list of check boxes to quickly add and remove axes, axis titles, chart titles, data labels, trendlines, and more.
• Clicking on the Chart Styles button allows the user to quickly choose from many preformatted styles to change the look of the chart.
• Clicking on the Chart Filter button allows the user to select the data to be included in the chart.
• The Chart Filter button is very useful for performing additional data analysis.

(This is for the Desktop Application)

Recommended Charts

• Similar to the ability to recommend PivotTables, Excel has the ability to recommend charts for a given data set.
• Step 1. Select cells B2:C11
• Step 2: Click the Insert tab in the Ribbon
• Step 3: Click the Recommended Charts button in the Charts group
• Step 4: When the Insert Chart dialog box appears, select the Scatter option
• Click OK

Line Charts

• Line charts are similar to scatter charts, but a line connects the points in the chart.
• Line charts are very useful for time series data collected over a period of time (minutes, hours, days, years, etc.).
• Kirkland Industries sells air compressors to manufacturing companies.

Scatter Charts vs Line Charts

Line Charts

• To create the line chart in Excel, we follow these steps:
• Step 1. Select cells A2:B13
• Step 2. Click the Insert tab on the Ribbon
• Step 3. Click the Insert Line Chart button in the Charts group
• Step 4. When the list of line chart subtypes appears, click the Line with Markers button under 2-D Line (This creates a line chart for sales with a basic layout and minimum formatting)
• Step 5. Select the line chart that was just created to reveal the Chart Buttons

Line Charts

• Step 6. Click the Chart Elements button
• Select the check boxes for Axes, Axis Titles, and Chart Title
• Deselect the check box for Gridlines
• Click on the text box next to the vertical axis, and replace “Axis Title” with Sales ($100s)
• Click on the text box next to the horizontal axis and replace “Axis Title” with Month
• Click on the text box above the chart, and replace “Sales ($100s)” with Line Chart for Monthly Sales Data

Line Charts

• Line charts can also be used to graph multiple lines.
• Suppose we want to break out Kirkland’s sales data by region (North and South).

Line Charts: Sparklines

(This is for the Desktop Application)

Bar Charts and Column Charts

• Bar charts and column charts provide a graphical summary of categorical data.
• Bar charts use horizontal bars to display the magnitude of the quantitative variable.
• Column charts use vertical bars to display the magnitude of the quantitative variable.
• Consider a regional supervisor who wants to examine the number of accounts being handled by each manager.

Bar Charts and Column Charts

• Step 1. Select cells A2:B9
• Step 2. Click the Insert tab on the Ribbon
• Step 3. Click the Insert Column or Bar Chart button in the Charts group
• Step 4. When the list of bar chart subtypes appears:
• Click the Clustered Bar button in the 2-D Bar section
• Step 5. Select the bar chart that was just created to reveal the Chart Buttons

Bar Charts and Column Charts

• Step 6. Click the Chart Elements button
• Select the check boxes for Axes, Axis Titles, and Chart Title
• Deselect the check box for Gridlines
• Click on the text box next to the horixontal axis, and replace “Axis Title” with Accounts Managed
• Click on the text box next to the vertical axis, and replace “Axis Title” with Manager
• Click on the text box above the chart, and replace “Chart Title” with Bar Chart of Accounts Managed

Bar and Column Charts

• We can make this bar chart even easier to read by ordering the results by the number of accounts managed.
• We can do this with the following steps:
• Step 1. Select cells A1:B9
• Step 2. Right-click any of the cells A1:B9
• Select Sort
• Click Custom Sort
• Step 3. When the Sort dialog box appears:
• Make sure that the check box for My data has headers is checked
• Select Accounts Managed in the Sort by box under Column
• Select Smallest to Largest under Order
• Click OK

Bar and Column Charts

• In the completed bar chart in Excel, we can easily compare the relative number of accounts managed for all managers.
• However, note that it is difficult to interpret from the bar chart exactly how many accounts are assigned to each manager.
• If this information is necessary, these data are better presented as a table or by adding data labels to
• the bar chart, which is created in Excel using the following steps:
• Step 1. Select the chart to reveal the Chart Buttons
• Step 2. Click the Chart Elements button
• Select the check box for Data Labels
• This adds labels of the number of accounts managed to the end of each bar so that the reader can easily look up exact values displayed in the bar chart.

Pie Charts

• Pie charts are another common form of chart used to compare categorical data.
• However, many experts argue that pie charts are inferior to bar charts for comparing data.
• Research has shown that people find it very difficult to perceive differences in areas.
• Making visual comparisons is much easier in the bar chart than in the pie chart (particularly when using a limited number of colours for differentiation).
• Therefore, we recommend against using pie charts in most situations and suggest instead using bar charts for comparing categorical data.

Bubble Charts

• A bubble chart is a graphical means of visualizing three variables in a two-dimensional graph and is therefore sometimes a preferred alternative to a 3-D graph.
• Suppose that we want to compare the number of billionaires in various countries.
• Provides a sample of six countries, showing, for each country, the number of billionaires per 10 million residents, the per capita income, and the total number of billionaires.

Bubble Charts

• Step 1. Select cells B2:D7
• Step 2. Click the Insert tab on the Ribbon
• Step 3. In the Charts group, click Insert Scatter (X,Y) or Bubble Chart In the Bubble subgroup, click Bubble
• Step 4. Select the chart that was just created to reveal the Chart Buttons

Bubble Charts

• Step 5. Click the Chart Elements button
• Select the check boxes for Axes, Axis Titles, Chart Title and Data Labels. Deselect the check box for Gridlines.
• Click on the text box under the horizontal axis, and replace “Axis Title” with Billionaires per 10 Million Residents
• Click on the text box next to the vertical axis, and replace “Axis Title” with Per Capita Income
• Click on the text box above the chart, and replace “Chart Title” with Billionaires by Country

Bubble Charts

• Step 6. Double-click on one of the Data Labels in the chart (e.g., the “$54,600” next to the largest bubble in the chart) to reveal the Format Data Labels task pane
• Step 7. In the Format Data Labels task pane, click the Label Options icon and open the Label Options area
• Under Label Contains, select Value from Cells and click the Select Range… button
• When the Data Label Range dialog box opens, select cells A2:A8 in the Worksheet
• Click OK
• Step 8. In the Format Data Labels task pane, deselect Y Value under Label Contains, and select Right under Label Position

Heat Maps

• A heat map is a two-dimensional graphical representation of data that uses different shades of colour to indicate magnitude.
• Figure shows a heat map indicating the magnitude of changes for a metric called same-store sales, which are commonly used in the retail industryto measure trends in sales.
• The cells shaded red in indicate declining same store sales for the month, and cells shaded blue indicate increasing same-store sales for the month.
• Column N contains sparklines for the same-store sales data.

​

​

Heat Maps

• Can be created in Excel
• Step 1. Select cells B2:M17
• Step 2. Click the Home tab on the Ribbon
• Step 3. Click Conditional Formatting in the Styles group
• Select Color Scales and click on Blue–White–Red Color Scale

Heat Map: Sparklines

• To add the sparklines in column N, we use the following steps:
• Step 4. Select cell N2
• Step 5. Click the Insert tab on the Ribbon
• Step 6. Click Line in the Sparklines group
• Step 7. When the Create Sparklines dialog box appears:
• Enter B2:M2 in the Data Range: box
• Enter N2 in the Location Range: box
• Click OK
• Step 8. Copy cell N2 to N3:N17

Heat Map: Inference

Heat Map: Sparklines

• Because heat maps depend strongly on the use of colour to convey information, one must be careful to make sure that the colours can be easily differentiated and that they do not become overwhelming.
• To avoid problems with interpreting differences in colour, we can add sparklines as shown in column N.
• The sparklines clearly show the overall trend (increasing or decreasing) for each location.
• However, we cannot gauge differences in the magnitudes of increases and decreases among locations using sparklines.
• The combination of a heat map and sparklines here is a particularly effective way to show both trend and magnitude.

Additional Charts for Multiple Variables

Stacked Column Chart

• To create the stacked-column chart shown in the previous figure, we use the following steps:
• Step 1. Select cells A2:C14
• Step 2. Click the Insert tab on the Ribbon
• Step 3. In the Charts group, click the Insert Column or Bar Chart button Select Stacked Column under 2-D Column

Clustered Column chart

• An alternative chart for these same data is called a clustered-column (or clustered-bar) chart.
• It is created in Excel following the same steps but selecting Clustered Column under the 2-D Column in Step 3.
• Clustered-column and clustered-bar charts are often superior to stacked-column and stacked-bar charts for comparing quantitative variables, but they can become cluttered for more than a few quantitative variables per category.

Additional Charts for Multiple Variables

Stacked/Clustered Column Charts

• However, when comparing many quantitative variables, using multiple charts can often be superior even if each chart must be made smaller.
• Stacked-column and stacked bar charts should be used only when comparing a few quantitative variables and when there are large differences in the relative values of the quantitative variables within the category.

Treemap

• A treemap is useful for visualizing hierarchical data along multiple dimensions.
• An investor can use the treemap to quickly get an idea of the performance of individual companies relative to other companies in their market sector as well as the performance of entire market sectors relative to other sectors.

Treemap

• Excel allows the user to create treemap charts.
• The step-by-step directions below explain how to create a treemap in Excel for the top-100 global companies based on 2014 market value using data in the file Global100.
• In this file we provide the continent where the company is headquartered in column A, the country headquarters in column B, the name of the company in column C, and the market value in column D.
• For the treemap to display properly in Excel, the data should be sorted by column A, “Continent,” which is the highest level of the hierarchy.

Treemap

• Step 1. Select cells A1: D101
• Step 2. Click Insert on the Ribbon
• Click on the Insert Hierarchy Chart button in the Charts group
• Select Treemap from the drop-down menu
• Step 3. When the treemap chart appears, right-click on the treemap portion of the chart
• Select Format Data Series… in the pop-up menu
• When the Format Data Series task pane opens, select Banner

Treemap