This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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, |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE PROCEDURE [dbo].[PlotDistribution] | |
AS | |
BEGIN | |
SET NOCOUNT ON; | |
EXECUTE sp_execute_external_script | |
@language = N'R', | |
@script = N' | |
library("reshape2") | |
library("ggplot2") |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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] |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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'' |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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), |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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", |
OlderNewer