Business Analytics Lab Worksheet 02

CME Group Foundation Business Analytics 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
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.
age_mean = mean(age)
age_mean

[1] 34.39765

#Calculate standard deviation of age below. Refer to Worksheet 1 for the correct command.
age_sd = sd(age)
age_sd

[1] 11.04513

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

[1] 73

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

[1] 18

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
age_lower = age_mean - (3) * age_sd
age_upper = age_mean + (3) * age_sd
age_upper

[1] 67.53302

age_lower

[1] 1.262269

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
iqr

75% 
 15 

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.

age[age>upperthreshold]

[1] 64 65 67 64 73 65 66 67 65

mydata[age>upperthreshold,]

age[age<lowerthreshold]

integer(0)

boxplot(age,horizontal = TRUE)

---

Task 2

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

mydata = read.csv(file = "data/creditriskorg.csv")
head(mydata)

#tail(mydata)

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.

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

#str(mydata)
summary(mydata)

          Loan.Purpose      Checking        Savings    Months.Customer
 Small Appliance:105    $-      :251    $-      : 62   Min.   : 5.0   
 New Car        :104    $216.00 :  2    $127.00 :  3   1st Qu.:13.0   
 Furniture      : 85    $271.00 :  2    $836.00 :  3   Median :19.0   
 Business       : 44    $296.00 :  2    $904.00 :  3   Mean   :22.9   
 Used Car       : 40    $305.00 :  2    $922.00 :  3   3rd Qu.:28.0   
 Education      : 23    $497.00 :  2    $104.00 :  2   Max.   :73.0   
 (Other)        : 24   (Other)  :164   (Other)  :349                  
 Months.Employed Gender   Marital.Status      Age        Housing   
 Min.   :  0.0   F:135   Divorced:156    Min.   :18.0   Other: 52  
 1st Qu.:  6.0   M:290   Married : 36    1st Qu.:26.0   Own  :292  
 Median : 20.0           Single  :233    Median :32.0   Rent : 81  
 Mean   : 31.9                           Mean   :34.4              
 3rd Qu.: 47.0                           3rd Qu.:41.0              
 Max.   :119.0                           Max.   :73.0              
                                                                   
     Years              Job      Credit.Risk
 Min.   :1.00   Management: 54   High:211   
 1st Qu.:2.00   Skilled   :271   Low :214   
 Median :3.00   Unemployed: 11              
 Mean   :2.84   Unskilled : 89              
 3rd Qu.:4.00                               
 Max.   :4.00                               
                                            

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?

checkings

  [1]    NA    NA    NA   638   963  2827    NA    NA  6509   966    NA    NA
 [13]   322    NA   396    NA   652   708   207   287    NA   101    NA    NA
 [25]    NA   141    NA  2484   237    NA   335  3565    NA 16647    NA    NA
 [37]    NA   940    NA    NA   218    NA 16935   664   150    NA   216    NA
 [49]    NA    NA   265  4256   870   162    NA    NA    NA   461    NA    NA
 [61]    NA   580    NA    NA    NA    NA   758   399   513    NA    NA   565
 [73]    NA    NA    NA   166  9783   674    NA 15328    NA   713    NA    NA
 [85]    NA    NA    NA   303   900    NA  1257    NA   273   522    NA    NA
 [97]    NA    NA   514   457  5133    NA   644   305  9621    NA    NA    NA
[109]    NA    NA  6851 13496   509    NA 19155    NA    NA   374    NA   828
[121]    NA   829    NA    NA   939    NA   889   876   893 12760    NA    NA
[133]   959    NA    NA    NA    NA   698    NA    NA    NA 12974    NA   317
[145]    NA    NA    NA   192    NA    NA    NA    NA    NA   942    NA  3329
[157]    NA    NA    NA    NA    NA    NA   339    NA    NA    NA   105    NA
[169]   216   113   109    NA    NA  8176    NA   468  7885    NA    NA    NA
[181]    NA    NA    NA    NA    NA    NA   734    NA    NA   172   644    NA
[193]   617    NA   586    NA    NA    NA    NA    NA   522   585  5588    NA
[205]   352    NA  2715   560   895   305    NA    NA    NA  8948    NA    NA
[217]    NA    NA    NA   483    NA    NA    NA   663   624    NA    NA   152
[229]    NA    NA   498    NA   156  1336    NA    NA    NA  2641    NA    NA
[241]    NA    NA    NA   887    NA    NA    NA    NA 18408   497    NA   946
[253]   986  8122    NA   778   645    NA   682 19812    NA    NA   859    NA
[265]    NA    NA    NA    NA    NA   795    NA    NA    NA    NA   852    NA
[277]    NA   425    NA    NA    NA 11072    NA   219  8060    NA    NA    NA
[289]    NA  1613   757    NA    NA   977   197    NA    NA    NA    NA    NA
[301]   256   296    NA    NA    NA   298    NA  8636    NA    NA 19766    NA
[313]    NA    NA    NA  4089    NA   271   949    NA   911    NA    NA    NA
[325]    NA   271    NA    NA    NA    NA  4802   177    NA    NA   996   705
[337]    NA    NA  5960    NA   759    NA   651   257   955    NA  8249    NA
[349]   956   382    NA   842  3111    NA    NA  2846   231    NA 17366    NA
[361]   332   242    NA   929    NA    NA    NA    NA    NA    NA    NA   646
[373]   538    NA    NA    NA    NA   135  2472    NA 10417   211 16630    NA
[385]   642    NA   296   898   478   315   122    NA    NA    NA   670   444
[397]  3880   819    NA    NA    NA    NA    NA    NA    NA    NA    NA   161
[409]    NA    NA   789   765    NA    NA   983    NA    NA   798    NA   193
[421]   497    NA    NA    NA    NA

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.

checkings[1:6]

[1]  $-          $-          $-          $638.00     $963.00     $2,827.00 
168 Levels:  $-     $1,257.00   $1,336.00   $1,613.00  ...  $996.00 

clean = checkings[1:10]
#substitute comma with blank in all of checking 
clean = sub(",","",clean)
#substitute dollar sign with blank in all of checking
clean = sub("\\$","",clean)
class(clean)

[1] "character"

#numeric convert
clean = as.numeric(clean)

NAs introduced by coercion

class(clean)

[1] "numeric"

#mean with NA removed 
clean

 [1]   NA   NA   NA  638  963 2827   NA   NA 6509  966

#substitute comma with blank in all of checking 
checkings = sub(",","",checkings)
#substitute dollar sign with blank in all of checking
checkings = sub("\\$","",checkings)
#numeric convert
checkings = as.numeric(checkings)

NAs introduced by coercion

#mean with NA removed 
checkings

  [1]    NA    NA    NA   638   963  2827    NA    NA  6509   966    NA    NA
 [13]   322    NA   396    NA   652   708   207   287    NA   101    NA    NA
 [25]    NA   141    NA  2484   237    NA   335  3565    NA 16647    NA    NA
 [37]    NA   940    NA    NA   218    NA 16935   664   150    NA   216    NA
 [49]    NA    NA   265  4256   870   162    NA    NA    NA   461    NA    NA
 [61]    NA   580    NA    NA    NA    NA   758   399   513    NA    NA   565
 [73]    NA    NA    NA   166  9783   674    NA 15328    NA   713    NA    NA
 [85]    NA    NA    NA   303   900    NA  1257    NA   273   522    NA    NA
 [97]    NA    NA   514   457  5133    NA   644   305  9621    NA    NA    NA
[109]    NA    NA  6851 13496   509    NA 19155    NA    NA   374    NA   828
[121]    NA   829    NA    NA   939    NA   889   876   893 12760    NA    NA
[133]   959    NA    NA    NA    NA   698    NA    NA    NA 12974    NA   317
[145]    NA    NA    NA   192    NA    NA    NA    NA    NA   942    NA  3329
[157]    NA    NA    NA    NA    NA    NA   339    NA    NA    NA   105    NA
[169]   216   113   109    NA    NA  8176    NA   468  7885    NA    NA    NA
[181]    NA    NA    NA    NA    NA    NA   734    NA    NA   172   644    NA
[193]   617    NA   586    NA    NA    NA    NA    NA   522   585  5588    NA
[205]   352    NA  2715   560   895   305    NA    NA    NA  8948    NA    NA
[217]    NA    NA    NA   483    NA    NA    NA   663   624    NA    NA   152
[229]    NA    NA   498    NA   156  1336    NA    NA    NA  2641    NA    NA
[241]    NA    NA    NA   887    NA    NA    NA    NA 18408   497    NA   946
[253]   986  8122    NA   778   645    NA   682 19812    NA    NA   859    NA
[265]    NA    NA    NA    NA    NA   795    NA    NA    NA    NA   852    NA
[277]    NA   425    NA    NA    NA 11072    NA   219  8060    NA    NA    NA
[289]    NA  1613   757    NA    NA   977   197    NA    NA    NA    NA    NA
[301]   256   296    NA    NA    NA   298    NA  8636    NA    NA 19766    NA
[313]    NA    NA    NA  4089    NA   271   949    NA   911    NA    NA    NA
[325]    NA   271    NA    NA    NA    NA  4802   177    NA    NA   996   705
[337]    NA    NA  5960    NA   759    NA   651   257   955    NA  8249    NA
[349]   956   382    NA   842  3111    NA    NA  2846   231    NA 17366    NA
[361]   332   242    NA   929    NA    NA    NA    NA    NA    NA    NA   646
[373]   538    NA    NA    NA    NA   135  2472    NA 10417   211 16630    NA
[385]   642    NA   296   898   478   315   122    NA    NA    NA   670   444
[397]  3880   819    NA    NA    NA    NA    NA    NA    NA    NA    NA   161
[409]    NA    NA   789   765    NA    NA   983    NA    NA   798    NA   193
[421]   497    NA    NA    NA    NA

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

mean(checkings,na.rm = TRUE)

[1] 2559.805

sum(checkings,na.rm = TRUE)/length(checkings)

[1] 1048.014

---

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.

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

Using https://erdplus.com/#/standalone draw a star schema using the following three tables:

• A Fact table for Trip
• A Dimension table for Community Area
• A Dimension table for Rider