Business Analytics Lab Worksheet 04

CME Group Foundation Business Analytics Lab

About

R can be used to make basic visual analytics, which can be helpful in understanding the data holistically. Additionally, R can help find correlations between variables and create scatter plots.

In this lab we will delve deeper into the marketing data set with R visual analytics and use Tableau to visualize the credit risk data set.

Setup

Make sure to download the folder titled ‘bsad_lab04’ 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, read in the creditrisk.csv file and make sure all the columns are captured and check the first couple of rows of the dataset “head(mydata)”

#mydata = read.csv(file="data/creditrisk.csv")
mydata = read.csv("data/states_purchase_orders.csv")
dept_name = mydata$DEPARTMENT.NAME

For CATEGORICAL variables it is useful to know the frequency of the different categories/levels of the variable. We can do that by using the table() function.

• Create a frequency table for the Loan Purpose and Job

# Command to create a frequency table: variable_table = table(variable) 
# variable_table
dept_table = table(dept_name) 
dept_table

Now, we will visualize some of the veriables to observe some patterns and differences.

First create a bar chart of the loan purpose frequency table and job frequency table Remember to extract the sales variable before calling it.

VARIABLES: Job and Loan Purpose

# Plot a bar plot using the " barplot(variable) " command where variable is the extracted variable
barplot(dept_table)

Next, create a histogram of loan purpose frequency table and job frequency table

# Plot a histogram using the " hist(variable) " command where variable is the extracted variable
hist(dept_table)

• What are some observable differences between the boxplot and the histogram? Please record your observations here.

Lastly, create a pie chart of Loan purpose frequency table. Plot only values greather than 10.

VARIABLE: Loan Purpose

# Plot a pie chart using:
# pie(variable)  (command to make a pie chart)
# To get only values greather than N from a frequency table use:
# new_table = my_table[ my_table > N ]  (command to select values greather than N)

new_table = dept_table[ dept_table > 2000 ]
pie(new_table)

Why is this visual not a good representation of the data? What makes the barplot a better representation?

---

Task 2

Lets explore the Illinois Tax dataset (READ the “state_tax_illinois.csv” file) and create a scatterplot of corporate tax vs. personal income tax.

The dataset start in 1994 but we want to tax revenue since 2000 for the first quarter of the year “Q1”

VARIABLES: personal_income_tax and corporate_income_tax

mydata = read.csv("data/state_tax_illinois.csv")
# To change the way that R shows very large or small values we can use the following command:
options(scipen = 9999)

# read the dataset extract the year variable and order the dataset by year in decreasing order using the command:

year = mydata$year

# mydata = mydata[ order(year, decreasing = TRUE) , ] (! Order the data in decreasing order)

mydata = mydata[ order(year,decreasing = TRUE), ]

# From the ordered dataset Extract personal_income_tax variable

us_tax = mydata$total_tax_US

# From the ordered dataset Extract corporate_income_tax variable

state = mydata$total_state

The previous task focused on visualizing one variable. A good way to visualize two variables would be a scatter plot or a correlation matrix.

# Plot personal_income_tax vs. corporate_income_tax 
# personal_income_tax will be on the x-axis
# corporate_income_tax will be on the y-axis

plot(X_VARIABLE,Y_VARIABLE)
plot(mydata)
plot(state,us_tax)

• Select only entries grather than year 1999
• After selecting entries greather than year 1999, select entries for quarter “Q1”

# To create a new_data filtering for an N NUMERIC value we can do the following
# new_data = mydata[ mydata$NUMERIC_VARIABLE > N , ]

new_data = mydata[ mydata$year<2000 , ]

# If we need to make another filter on a NON-NUMERIC we can do the following
# new_data = new_data[ new_data$CHAR_VARIABLE == "Q1", ]

new_data = new_data[ new_data$quarter == "Q4" , ]

head(new_data)

It might be easier to notice a trend if there was a line that fit through the points. So, lets repeat using the function ‘scatter.smooth’. This functions adds a smooth curve computed by loess, a method to locally smooth a curve.

us_tax = new_data$total_tax_US
state = new_data$total_state

scatter.smooth(us_tax,state)

Now, repeat below for total_tax_US vs. total_state, following the example that was just provided. Observe and note any trends in the scatter plots and potential relationships below. Do the same for all numeric variables.

# total_tax_US vs. total_state
scatter.smooth(new_data$motor_fuel_tax,new_data$personal_income_tax)

To quantify the data, its best to look at the correlation between two variables. What does this correlation explain?

cor(new_data[3:8])

Below, compute the correlation between every pair of variables (total_tax_US,total_state, personal_income_tax, corporate_income_tax, general_sales_tax, motor_fuel_tax ).

Make sure to extract the variables first and then compute the correlation.

# To make a correlation table of all the NUMERIC variables you can select some columns or a range of columns, with the following command:

# corr01 = mydata[ c(3,4) ] (The command c(3,4,...) inside the brackets selects a given column )
# cor(corr01) (will give you the correlation table for variables in columns 3 and 4)

# corr02 = mydata[3:5] ( the command [3:5] selects a range of columns from 3 to 5)
# cor(corr02)

---

Task 3 - TABLEAU

Follow the directions on the worksheet, download tableau academic on your personal computer or use one of the labs computers.

• Download Tablea academic here: https://www.tableau.com/academic/students

1. Refer to file ‘states_purchase_orders.csv’ in the data folder

2. Start Tableau and enter your LUC email if prompted.

3. Import the file into Tableau. Choose the Text File option when importing

4. Under the dimensions tab located on the left side of the screen.

DOUBLE click on the ‘Department.Name’, then DOUBLE click on ‘Number of Records’ variable located on the bottom left of the screen. Note the bar like view and breakdowns.

• What department ‘bin’ has the highest count? What is the count?
• From the upper right corner of the screen select other views (select one of the other views, talk about it and post an screenshot)

# Change the image for your screenshots and MAKE COMMENTS

5. Create a new sheet by clicking on the icon next to your current sheet.

Then DOUBLE click on ‘Number of Records’ variable then DOUBLE click on ‘Vendor.Zip’, then on the top left select the recommend view.

• What are the highlights on the map?
• Can you give an explanation of why there is a concentration on a particular region?

# Change the image for your screenshots and MAKE COMMENTS

---

Final Project Review

1. After reviewing your data and defined your problem, you need to prepare and analize your data.

mydata = read.csv("data/cps_progress_report.csv")
head(mydata)

Identify variables that are relevant to your problem (model). Here we are using attedance as the Y-variable or predicted variables. The other variables are independent variables that could influence the attendance rate.

Extract Variables

# Extracting Variables from the year 2011 to predict attendance for 2012
Y_attendance_2011 = mydata$Student.Attendance.2011...Percent
# In this model grades, environment and graduation are variables that affect attendance
X1_grades_2011 = mydata$Growth.All.Grades
X2_graduation_2011 = mydata$X5.Year.Cohort.Graduation.Rate.2011
X3_enviroment_2011 = mydata$Supportive.Environment

Clean and set the correct datatype

#### Dependent Variable  (Y) - Predicted
Y_attendance_2011 = sub("%","",Y_attendance_2011) # Remove % sign from the values
Y_attendance_2011 = as.numeric(Y_attendance_2011) # Changing from character to numeric
#### Independent Variables  (X1,X2,X3,... ) - Predictors
X1_grades_2011 = sub("%", "", X1_grades_2011)
X1_grades_2011 = as.numeric(X1_grades_2011)
X2_graduation_2011 = sub("%" , "" , X2_graduation_2011)
X2_graduation_2011 = as.numeric(X2_graduation_2011)
# Enviroment has different levels Weak, Strong, Very Strong ... hence its a categorical variable. We use the functions as.factor() to transform a character variable to factor
X3_enviroment_2011 = as.factor(X3_enviroment_2011)

Now start analyzing the relation between your independent variables (X1,X2,X3,…) and the dependent variable/predictor (Y).

scatter.smooth(Y_attendance_2011, X1_grades_2011)

scatter.smooth(Y_attendance_2011,X2_graduation_2011)

plot(X3_enviroment_2011,Y_attendance_2011)

Now that we look at the relation and interaction between that independent variables (x1,X2,X3,…) and dependent variable (y). We can build a predicted model.

## Build a model Y_Predicted ~ X1 + X2 + X3 ...
## How your independent variables (X1,X2,X3,...) interact with your dependent variable (Y_predicted) 
# For this case how grades, graduation and enviroment affect attendance of students at a CPS school in 2011 
# Linear Model Function - lm()
model_2011 = lm( Y_attendance_2011 ~ X1_grades_2011 + X2_graduation_2011 + X3_enviroment_2011)
summary(model_2011)

Call:
lm(formula = Y_attendance_2011 ~ X1_grades_2011 + X2_graduation_2011 + 
    X3_enviroment_2011)

Residuals:
     Min       1Q   Median       3Q      Max 
-18.8254  -4.0402  -0.4652   4.6193   9.6816 

Coefficients:
                                  Estimate Std. Error t value Pr(>|t|)    
(Intercept)                       54.08827    5.70941   9.474 5.47e-14 ***
X1_grades_2011                     0.20205    0.11039   1.830   0.0717 .  
X2_graduation_2011                 0.26619    0.05636   4.723 1.23e-05 ***
X3_enviroment_2011Not Enough Data  3.70131    2.81286   1.316   0.1927    
X3_enviroment_2011Strong           5.97264    2.52024   2.370   0.0207 *  
X3_enviroment_2011Very Strong     -0.61935    4.92062  -0.126   0.9002    
X3_enviroment_2011Weak            -3.61651    1.81628  -1.991   0.0505 .  
---
Signif. codes:  0 ‘***’ 0.001 ‘**’ 0.01 ‘*’ 0.05 ‘.’ 0.1 ‘ ’ 1

Residual standard error: 6.058 on 67 degrees of freedom
  (18 observations deleted due to missingness)
Multiple R-squared:  0.5717,    Adjusted R-squared:  0.5334 
F-statistic: 14.91 on 6 and 67 DF,  p-value: 9.599e-11

Notice the Adjusted R-squared value (coefficient of determination), that determines how close the data are to the fitted regression line. ( 0% indicates that the model explains none of the variability of the response data around its mean )