Transcribed Text
Why SHOULD we use PivotTables?
1. Summarizes data in a way that is easy to understand and it also allows for easy charting.
2. Helps find trends or relationships in datasets … still we must remember that old statistical
saying the correlation does not imply causation. The same goes here, just because we may see a
trend between various data, it doesn’t necessarily mean that one data point is causing the trend in
another.
Data is not information until it is acted upon to give it meaning. Aggregation is one method of
making data into information. We use PivotTable “reports” to show aggregated large datasets in
ways that are easily understood. Aggregation includes counting, summing, maximizing,
minimizing, averaging, and grouping – basically, analyzing numerical data. Specifically,
PivotTables allow us to:
Look at large amounts of data in many different ways.
Aggregate numerical data
Drill down data to get further “into the weeds”. Also, data can be filtered to concentrate
on specific areas of the dataset.
Change rows to columns or columns to rows (or "pivot") to check for different
possibilities and trends.
Chart the data … and show large datasets in ways that are easy to understand.
Pivot Table Labs
Scenario:
You have recently been hired to work as a data analyst for the Marple, Magnum & Clouseau
Security Agency (MMCSA). MMCSA has decided to open offices in Oklahoma. They are
analyzing the data set they obtained to determine the best locations for one main office and
several branch offices. They are looking for high crime areas, but want to limit their locations to
at least “small cities” in size unless the town has either a military base and or a university
present. Also, they would prefer locations with major interstate highways.
Based on statistics from MMCSA offices in other states, if the following high crime rates are
present (in order of precedence), MMCSA will have a better chance of “turning a profit”.
1) Violent Crime
2) Murder
3) Forcible Rape
4) Aggregated Assault
5) Arson
6) Burglary
7) Larceny – Theft
8) Property Crime
9) Motor Vehicle Theft
Exercise 1:
You need to look at each of the following and based on the data, choose which location would
serve as the best location for the main office. You are also to choose two other locations and
one alternate for branch offices.
Exercise 2:
Based on total crime, which are the most “dangerous” cities/towns in Oklahoma? Do these
locations seem to have similarities?
Exercise 3:
The new manager of MMCSA is moving to Oklahoma. He has a daughter that will be attending
a public college or university in Oklahoma. The new manager is extremely paranoid when it
comes to his family. Which university do you think he would choose to send his daughter to?
Exercise 4:
Determine if the cities with higher crime rates may be caused by multiple factors. Which
factors seem to have the most significance when it comes to the higher crime rate?
Exercise 5:
Does the size of the city / town (population) seem to have any significance in crime rate?
These solutions may offer step-by-step problem-solving explanations or good writing examples that include modern styles of formatting and construction
of bibliographies out of text citations and references. Students may use these solutions for personal skill-building and practice.
Unethical use is strictly forbidden.