Week 3

MGSC 3010 Intro to Business Analytics

4
Week 3 Learning Objectives

 Charts
 Trendlines of Time Series Data
 Pivots Chart
 Pivots Table

5
6
Agenda

 Charts
 Bar Chart
 Line Chart
 Scatter Plot

7
Why Charts?
Average Monthly Precipitation (Inches)

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
ASHEVILLE, NC 4.06 3.83 4.59 3.50 4.41 4.38 3.87 4.30 3.72 3.17 3.82 3.39
BAKERSFIELD, CA 1.18 1.21 1.41 0.45 0.24 0.12 0.00 0.08 0.15 0.30 0.59 0.76
BATON ROUGE, LA 6.19 5.10 5.07 5.56 5.34 5.33 5.96 5.86 4.84 3.81 4.76 5.26
BILLINGS, MT 0.81 0.57 1.12 1.74 2.48 1.89 1.28 0.85 1.34 1.26 0.75 0.67
DAYTONA BEACH, FL 3.13 2.74 3.84 2.54 3.26 5.69 5.17 6.09 6.61 4.48 3.03 2.71
EUGENE, OR 7.65 6.35 5.80 3.66 2.66 1.53 0.64 0.99 1.54 3.35 8.44 8.29
HONOLULU,HI 2.73 2.35 1.89 1.11 0.78 0.43 0.50 0.46 0.74 2.18 2.26 2.85
ST. LOUIS, MO 2.14 2.28 3.60 3.69 4.11 3.76 3.90 2.98 2.96 2.76 3.71 2.86
TUCSON, AZ 0.99 0.88 0.81 0.28 0.24 0.24 2.07 2.30 1.45 1.21 0.67 1.03

VS.

8
Charts in Excel
 Bar, line, column, pie, and etc.
 Menu > Insert

 A general way to draw a chart.

 Select data (or not)
 Click a chart type.
 Edit some details (Select Data)

9
Bar Chart
Open the Excel file Visualization_Template.
Let’s draw a bar graph for Asheville, NC
• Select A3:M4.
• Click Insert > Column Chart > 2-D Column

10
Bar Chart
 Let’s plot more states side by side.

Legend

Axis titles

 When to use bar charts?

 Compare values

11
Line Chart
• What if we want to visualize the trend?
• Draw a line graph for Baton Rouge, LA

12
Time Series Data
 A series of data points indexed in time order is called
“Time Series” data.
• Example: The monthly precipitation data

13
Time Series Data
 A series of data points indexed in time order is called
“Time Series” data.
• Example: The monthly precipitation data

14
Categorical and Numerical Values
 Categorical values:
 Mathematical operations do not make sense
 Year 2000, year 2001, …, year 2022
 Jan, Feb, Mar,…
 High, Medium, Low
 Countries, cities, states…
 Numerical values
 Mathematical operations make sense

 Categorical values, think about bar and line graphs

 Numerical values, think about scatter plot

15
Scatter Plot
 Let’s treat January and February as 𝑥 , 𝑦 .
 When to use scatter plot?
 Plot the relationship between two series of numerical values.

16
Takeaways

 Chart types
 Bar, Line, Scatter plot
 Trendline

 It is important to understand when to use what

 Bar: great for comparing values
 Line: great for visualizing trends
 Scatter: great for plotting numerical values on both axis

17
One-dimensional table
Two-dimensional table

18
Agenda

 Pivot tables

19
Why Pivot Tables?
 Pivot table aggregates data from a more extensive
source table in a meaningful way.

 A pivot table is an interactive table for quickly

summarizing large amounts of source data.

 You can filter its rows and columns to see different

summaries of the source data.

 You can further modify the pivot table.

20
Creating Pivot Tables
 Consider a dataset that contains order information of
different products in different countries.
• Column names: Order ID, Product, Category, Amount,
Date and Country.

21
How to Create a Pivot Table?

• Click any single cell inside

the dataset.

• On the Insert tab, in the

Tables group, click
PivotTable.

22
How to Create a Pivot Table?
• On the appeared window, first make sure the selected range is
correct.

23
How to Create a Pivot Table?
• Check, “existing worksheet”, click on any cell in the existing
worksheet as the location, and then press OK.

24
How to Create a Pivot Table?

Each column name is

known as a “field.”

Drag the Drag the field

field you you want to see
want to as rows or
use for columns in your
filtering. pivot table.

Drag the numerical filed you

want to know its summary
statistics.

25
Before Create a Pivot Table…

Visualize it in your mind

i.e. know how your report will look like.

26
Example 1: 1-D Table
 Let’s create a report that shows total dollar value of
different products. Values
Rows

Product Total dollar value

Apple

Orange

27
Example 1: 1-D Table
 Let’s create a report that shows total dollar value of
different products.

28
Filter Area
 What if we are interested in a particular country?
 We use a categorical field for filtering.

29
Summary Statistics
 In Values we see the summary statistics.
 Note that the default option in Values is to Sum.
 We can also see average, count, max…

30
Example 2: 2-D Table
 Instead of filtering the country, we can also list out the
summary statistics per product per country.
Values Rows Columns

France U.S …

Apple ? Statistics ?... ?

Orange ?
…

31
Example 2: 2-D Table
 Instead of filtering the country, we can also list out
the summary statistics per product per country.

32
Takeaways
 Why pivot tables?
 Summarize/aggregate a more extensive source table.

 How to create a pivot table from the source table?

 What do you want in the row/column or both?
 What summary statistics do you want to see?
 Any filters?

 How to modify a pivot table?

 Column Labels -> Label filters/Value filters

33
Modify the pivot tables
Pivot charts

34
Agenda

 Modify the pivot tables

 Pivot Charts
 Bar, Line
 Stacked column

 3D plots
 Revisit on Randy’s 2-way table

35
Modify the Pivot Table
 We can also modify the report table by “slicing” it.
 That is we can filter on the value of the report and get a
sub-table.

36
Modify the Pivot Table
 To only shows the countries in which the total dollar
amount of products is greater than $200,000?
 Note this rule/condition on “Value Filters”, is applied on
the Grand Total.

37
Modify the Pivot Table
 We can modify the report using “Label Filters.”

 If we use a field “Date” in the rows or columns, we can also

use “Date Filters”.
38
Pivot bar charts
Pivot line charts

39
What is a Pivot Chart?

 A pivot chart is the visual representation of a pivot

table in Excel. Pivot charts and pivot tables are
connected with each other.
 Same idea with Charts, but related to pivot tables.

 Let’s do some explorations on the data.

40
Exercise 1: Dollar Amount of Order Data
 Monthly total dollar amount of order data

 What if we break it down by products?

41
Exercise 1: Dollar Amount of Order Data
 What if we break it down by products?

Peaks and valleys? Missing values? Granularity of data?

42
Exercise 1: Dollar Amount of Order Data
• Among Australia, Canada, and France, which country that has
the fastest-declining monthly trend?
• Illustrates the monthly dollar amount trend with a linear
trend line.

43
Exercise 2: Most Profitable Product Type
 Total dollar amount per country and category (bar).

44
Exercise 2: Most Profitable Product Type
 What is the most profitable product type in each country?

45
Exercise 2: Most Profitable Product Type
 What is the market share of the most profitable type in
each country?
Chart type: 100% Stacked bar

46
Exercise 2: Most Profitable Product Type
 To show the percentages on the columns
 Values quadrant -> Value Field Settings -> Show Values As

Chart type: 100% Stacked bar

47
Takeaways

 How to modify a pivot table?

 Column/Row Labels -> Label filters/Value filters

 Pivot Charts
 Bar, lines same as what we learned before
 Two new chart types
 Stacked bar: great for investigating proportions, ratios, shares
 3D plot: Surface plot and contour plot. Fun!

48
Revisit Randy’s 2-way Table: 3D Plot
 Let’s create a 3D plot to visualize the 2-way table.

Contour Plot
3D Surface Plot
Series9

Series8
6000
Series7 5000
4000
Series6
3000

Series5 2000
1000
Series4 Series1 0
1
Series3 2
3
Series3 4
Series5 5
6
Series2 Series7 7
8
9
Series9 10
Series1 11
1 2 3 4 5 6 7 8 9 10 11
0-1000 1000-2000 2000-3000 3000-4000 4000-5000 5000-6000
0-2000 2000-4000 4000-6000

49
Appendix

 Label Filters

What does these mean?

50
Test 1
If key in “Ban?a” in the “Label Filters” for Row labels. We get nothing left. Banana
row is not selected. Because there are 2 characters “an” in between “Ban” and “a”,
instead of 1, which is what “?” specified (single character).

51
Test 2
If
If key
key in
in “Ban*a” in
in the
the “Label Filters”
Filters” for
for Row
Row labels.
labels. Banana
Banana row
row is
is selected.
selected.
Because “*” can be used to specify any number of characters, which in this case
is
is two
two for
for “an” between “Ban” and “a”.

52
48
Test 3
I created another two product types called “Coconuts” and ”Corn”. Let’s see why
using “*” can be very convenient.

53
Test 3
Now, I want to select both Carrots and Coconuts at the same time but not Corn.
We can use a regular expression: “C*s”. This will give you both Carrots and
Coconuts but NOT Corn. Because, ”C*s” represents any word/pattern that starts
with C end with s, but doesn’t care what’s in between. If there are products called
“Celeries”, “Crops”, they will be selected as well.

54
Summary
The use of “?” and ”*” falls under a big topic in coding called “regular
expressions.” Google for Regular expression, you can get this in Wikipedia.

Having said this, different programming languages (R, python, Java, and etc.)
might differ a little on the conventional syntax for regular expressions.

55