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?