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 directions to complete the lab.

---

Task 1

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

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

#Read File
scoring = read.csv(file = "data/scoring.csv")
#Name the extracted variable
seniority = scoring$Seniority
time = scoring$Time
age = scoring$Age
expenses = scoring$Expenses
income = scoring$Income
assets = scoring$Assets
debt = scoring$Debt

#Calculate the averages below. Refer to Worksheet 1 for the correct command.
Seniority_mean = mean(seniority)
Time_mean = mean(time)
Age_mean = mean(age)
Expenses_mean = mean(expenses)
Income_mean = mean(income)
Assets_mean = mean(assets)
Debt_mean = mean(Debt)

Seniority_mean
Time_mean
Age_mean
Expenses_mean
Income_mean
Assets_mean
Debt_mean

#Calculate standard deviations below. Refer to Worksheet 1 for the correct command. 
Seniority_sd = sd(seniority)
Time_sd = sd(time)
Age_sd = sd(age)
Expenses_sd = sd(expenses)
Income_sd = sd(income)
Assets_sd = sd(assets)
Debt_sd = sd(debt)

Seniority_sd
Time_sd
Age_sd
Expenses_sd
Income_sd
Assets_sd
Debt_sd

#Calculate the maximum of age below. The command to find the maximum is max(variable) where variable is the extracted variable.  
Seniority_max = max(seniority)
Time_max = max(time)
Age_max = max(age)
Expenses_max = max(expenses)
Income_max = max(income)
Assets_max = max(assets)
Debt_max = max(debt)

Seniority_max
Time_max
Age_max
Expenses_max
Income_max
Assets_max
Debt_max

#Calculate the minimum of age below. The command to find the minimum is min(variable) where variable is the extracted variable.  
Seniority__min = min(seniority)
Time_min = min(time)
Age_min = min(age)
Expenses_min = min(expenses)
Income_min = min(income)
Assets_min = min(assets)
Debt_min = min(debt)

Seniority__min
Time_min
Age_min
Expenses_min
Income_min
Assets_min
Debt_min

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.

#Upper Threshold
Seniority_upper = Seniority_mean + (3) * Seniority_sd
Time_upper = Time_mean + (3) * Time_sd
Age_upper = Age_mean + (3) * Age_sd
Expenses_upper = Expenses_mean +(3) * Expenses_sd
Income_upper = Income_mean + (3) * Income_sd
Assets_upper = Assets_mean + (3) * Assets_sd
Debt_upper = Debt_mean + (3) * Debt_sd

Seniority_lower = Seniority_mean - (3) * Seniority_sd
Time_lower = Time_mean - (3) * Time_sd
Age_lower = Age_mean - (3) * Age_sd
Expenses_lower = Expenses_mean - (3) * Expenses_sd
Income_lower = Income_mean - (3) * Income_sd
Assets_lower = Assets_mean - (3) * Assets_sd
Debt_lower = Debt_mean - (3) * Debt_sd

Seniority_upper
Time_upper
Age_upper
Expenses_upper
Income_upper
Assets_upper
Debt_upper

Seniority_lower
Time_lower
Age_lower
Expenses_lower
Income_lower
Assets_lower
Debt_lower

#Find the quantile data for seniority
quantile(seniority)
lowerq = quantile(seniority)[2]
upperq = quantile(seniority)[4]

iqr = upperq - lowerq
iqr

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

Below is the lower threshold:

lowerthreshold = lowerq - (iqr * 1.5)
lowerthreshold

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.

150 Outliers

seniority[seniority>upperthreshold]
seniority[seniority<lowerthreshold]

boxplot(seniority,horizontal = TRUE)

---

Task 2

Next, we must read the ‘creditriskorg.csv’ file into R. This is the original dataset and contains missing values.

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

head(creditriskorig)

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.

creditriskorg = read.csv("data/creditriskorg.csv",skip = 1)
head(creditriskorg)

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?

savings = creditriskorg$Savings
savings

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.

clean = savings
#substitute comma with blank in all of checking 
clean = sub(",","",clean)

#substitute dollar sign with blank in all of checking 
clean = sub("\\$","",clean)

#numeric convert
clean = as.numeric(clean)

#mean with NA removed 
clean

What are some other ways to clean this data in R? How about in Excel? You can represent each unknown symbol as 0 if that is there value. In excel, you’d have to type in specific values.

---

Task 3

Now, we will look at Chicago taxi data. Go and explore the interactive dashboard and read the description of the data.

Chicago Taxi Dashboard: https://data.cityofchicago.org/Transportation/Taxi-Trips-Dashboard/spcw-brbq

Chicago Taxi Data Description: http://digital.cityofchicago.org/index.php/chicago-taxi-data-released/

Open in RStudio the csv file is located in the data folder, note the size of the file, the number of columns and of rows here. Use the functions learned in lab00 and lab01 to describe the data, identify unique entities, fields and summarize.

taxidata = read.csv(file = "data/taxi_trips_sample.csv")
Trip_Seconds = taxidata$Trip.Seconds
Trip_Seconds_max = max(Trip_Seconds)
Trip_Seconds_min = min(Trip_Seconds)
Trip_Seconds_mean = mean(Trip_Seconds)
Trip_Seconds_sd = sd(Trip_Seconds)

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

Star Schema: