Business Analytics Lab Worksheet 02

CME Group Foundation Business Analytics Lab

About

Today the lab focuses on data outliers, data preparation, and data modeling. This lab requires the use of Microsoft Excel, R, and ERDplus.

Setup

Make sure to download the folder titled ‘bsad_lab02’ zip folder and extract the folder to unzip it. Next, we must set this folder as the working directory. The way to do this is to open R Studio, go to ‘Session’, scroll down to ‘Set Working Directory’, and click ‘To Source File Location’. Now, follow the worksheet directions to complete the lab.

---

Task 1

First, we must calculate the mean, standard deviation, maximum, and minimum for the Age column using both R and Excel.

In R, we must read in the file again, extract the column and find the values that are asked for.

#Read File
mydata = read.csv(file="data/creditrisk.csv") 

#Name the extracted variable
age = mydata$Age 

#Calculate the average age below. Refer to Worksheet 1 for the correct command.

#Calculate standard deviation of age below. Refer to Worksheet 1 for the correct command. 

#Calculate the maximum of age below. The command to find the maximum is max(variable) where variable is the extracted variable.  

#Calculate the minimum of age below. The command to find the minimum is min(variable) where variable is the extracted variable.  

Next, compute the same values using Excel and compare results. Post a screenshot of this using the directions found in Lab 0. To screenshot, you can use the snipping tool and save the PNG image to the same folder all of the other data is.

Next, use the formula from class to detect any outliers. An outlier is value that “lies outside” most of the other values in a set of data. A common way to estimate the upper and lower threshold is to take the mean (+ or -) 3 * standard deviation. Try using this formula to find the upper and lower limit for age.

#Use the formula above to calculate the upper and lower threshold

A method to find the upper and lower thresholds discussed in introductory statistics courses involves finding the interquartile range. Follow along below to see how we first calculate the interquartile range..

quantile(age) 

  0%  25%  50%  75% 100% 
  18   26   32   41   73 

lowerq = quantile(age)[2] 
upperq = quantile(age)[4] 
iqr = upperq - lowerq

The formula below calculates the threshold. The threshold is the boundaries that determine if a value is an outlier. If the value falls above the upper threshold or below the lower threshold, it is an outlier.

Below is the upper threshold:

upperthreshold = (iqr * 1.5) + upperq 
upperthreshold

 75% 
63.5 

Below is the lower threshold:

lowerthreshold = lowerq - (iqr * 1.5)
lowerthreshold

25% 
3.5 

Are there any outliers? How many? It can also be useful to visualize the data using a box and whisker plot. The boxplot below supports the IQR we found of 15 and upper and lower threshold.

boxplot(age) 

---

Task 2

Next, we must calculate the mean for checking in Excel. Take a screenshot of this process and paste it here. How are the missing values are treated in the Excel calculation?

Now, read the file into R.

newdata = read.csv(file="data/creditriskorg.csv")
newdata 

We observe that the column names are shifted down below. So, we must make sure to use the command skip and set the header to true.

newdata = read.csv(file="data/creditriskorg.csv",
skip=1,header=TRUE,sep=",") 
newdata 

To calculate the mean for Checking in R, follow Worksheet 1. Extract the Checking column first and then find the average using the function built in R. What happens when we try to use the function?

checking = newdata$Checking 

To resolve the error, we must remove understand where it is coming from. There are missing values in the csv file, which is quite common as most datasets are not perfect. Additionally, there are commas within the excel spreadsheet, and R does not recognize that ‘1,234’ is equivalent to ‘1234’. Lastly, there are ‘$’ symbols throughout the file which is not a numerica symbol either.

The sub function replaces these symbols with something else. So, in order to remove the comma in the number “1,234”, we must substitute it with just an empty space.

As shown on the worksheet, type and copy the exact commands to find the mean with the NA values removed.

#substitute comma with blank in all of checking 

#substitute dollar sign with blank in all of checking 

#numeric convert

#mean with NA removed 

What are some other ways to clean this data in R? How about in Excel?

---

Task 3

Now, we will look at divvybike data. Go to https://www.divvybikes.com/system-data Download latest data (zip file) and go over the README file.

Open the file in Excel. Per the Data License Agreement who owns the data, and can the data be correlated with other sources?

Note the size of the file, the number of columns and of rows here.??? Identify the unique entities and fields.

Define a relational business logic for the column field ‘tripduration’.

Using www.erdplus.com draw a star schema using the following three tables:- A Fact table for Trip- A Dimension table for Station- A Dimension table for User