ramnov

CREATE PROCEDURE [dbo].[LoadData]  
AS  
BEGIN  
DROP TABLE IF EXISTS [dbo].[LoanStats]
CREATE TABLE [dbo].[LoanStats](
	[id] [int] NULL,
	[member_id] [int] NULL,
	[loan_amnt] [int] NULL,
	[funded_amnt] [int] NULL,
	[funded_amnt_inv] [int] NULL,

ramnov

--DROP unnecessary column desc 
ALTER TABLE [dbo].[LoanStats] DROP COLUMN [desc]

--Remove % from int_rate and convert its type to float 
UPDATE [dbo].[LoanStats] SET [int_rate] = REPLACE([int_rate], '%', '')
ALTER TABLE [dbo].[LoanStats] ALTER COLUMN [int_rate] float

--Remove % from revol_util and convert its type to float
UPDATE [dbo].[LoanStats] SET [revol_util] = REPLACE([revol_util], '%', '')
ALTER TABLE [dbo].[LoanStats] ALTER COLUMN [revol_util] float

ramnov

CREATE PROCEDURE [dbo].[PlotDistribution]  
AS  
BEGIN  
SET NOCOUNT ON;  
EXECUTE sp_execute_external_script 
@language = N'R',
@script = N'  
library("reshape2")
library("ggplot2")

ramnov

CREATE PROCEDURE [dbo].[SplitLoans]  
AS  
BEGIN  
  SET NOCOUNT ON;  
  -- 75% Training data 
  DROP TABLE IF EXISTS [dbo].[LoanStatsTrain]
  SELECT * INTO [dbo].[LoanStatsTrain] FROM (SELECT * FROM [dbo].[LoanStats] WHERE (ABS(CAST((BINARY_CHECKSUM(id, NEWID())) as int)) % 100) < 75)a
  -- 25% Test data
  DROP TABLE IF EXISTS [dbo].[LoanStatsTest]
  SELECT * INTO [dbo].[LoanStatsTest] FROM (SELECT * FROM [dbo].[LoanStats] WHERE [id] NOT IN (SELECT [id] FROM [dbo].[LoanStatsTrain]))a

ramnov

CREATE PROCEDURE [dbo].[BuildModel]  
AS  
BEGIN  
  DECLARE @inquery nvarchar(max) = N'SELECT * FROM [dbo].[LoanStatsTrain]'  
  
  DROP TABLE IF EXISTS [dbo].[models]
  CREATE TABLE [dbo].[models]([model] [varbinary](max) NOT NULL)

  INSERT INTO [dbo].[models]
  EXEC sp_execute_external_script 

ramnov

CREATE PROCEDURE [dbo].[ScoreLoans] 
AS  
BEGIN  
  DECLARE @inquery nvarchar(max) = N'SELECT * FROM [dbo].[LoanStatsTest]'  
  DECLARE @model varbinary(max) = (SELECT TOP 1 model FROM models)
  
  DROP TABLE IF EXISTS [dbo].[LoanStatsPredictions]
  CREATE TABLE [dbo].[LoanStatsPredictions]([is_bad_Pred] [float] NULL, [id] [int] NULL) 

  INSERT INTO [dbo].[LoanStatsPredictions]   

ramnov

CREATE PROCEDURE [dbo].[PlotROCCurve]  
AS  
BEGIN  
  EXEC sp_execute_external_script
  @language = N'R',
  @script = N'  
  suppressMessages(library("ROCR"))
  
  # create output directory
  mainDir <- ''C:\\temp\\plots''  

ramnov

install.packages("devtools")
install.packages("dplyr")
library("devtools")
library("dplyr")
install_github("RevolutionAnalytics/dplyrXdf")
airline_demo_xdf_file <- file.path(rxGetOption("sampleDataDir"), "AirlineDemoSmall.xdf")
airline <- RxXdfData(airline_demo_xdf_file)
smry <- airline %>% group_by(DayOfWeek) %>%
  summarise(DayOfWeekMeans=mean(CRSDepTime),
            DayOfWeekStdDev=sd(CRSDepTime),

ramnov

airline_demo_xdf_file <- file.path(rxGetOption("sampleDataDir"), "AirlineDemoSmall.xdf")
airline_demo_xdf <- RxXdfData(airline_demo_xdf_file)
airline_demo_data <- rxImport(airline_demo_xdf)
head(airline_demo_data)

ramnov

airline_demo_xdf_file <- file.path(rxGetOption("sampleDataDir"), "AirlineDemoSmall.xdf")

# Find min/max value of a particular column CRSDepTime in airlineXdfData
single_column_minmax <- rxDataStep(airline_demo_xdf_file, transformFunc=function(varlst) {
  rng <- range(varlst$CRSDepTime, na.rm = TRUE)
  .min <<- min(rng[1], .min)
  .max <<- max(rng[2], .max)
  NULL
},
transformVars="CRSDepTime",