SQL-Server-R-Services-Samples/FraudDetection/R/02-data-preprocessing.R at master · seasmith/SQL-Server-R-Services-Samples · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
####################################################################################################
## Compute context
####################################################################################################
connection_string <- "Driver=SQL Server;
Server=[Server name];
Database=[Database name];
UID=[User name];
PWD=[Password]"
sql_share_directory <- paste("c:\\AllShare\\", Sys.getenv("USERNAME"), sep = "")
dir.create(sql_share_directory, recursive = TRUE)
sql <- RxInSqlServer(connectionString = connection_string,
                     shareDir = sql_share_directory)
local <- RxLocalSeq()
####################################################################################################
## Account level partition: create trainFlag and append it as the last column of tagged_fraud table
####################################################################################################
account_level_partition <- function(in_table, out_table, key, nfRate, frdRate) {
  data <- rxImport(in_table)

  # Split and sample the data by key: all trans with the same key will stay in the same sample population.
  # A trainFlag column is added: trainFlag =1 for train set, trainFlag = 0 for test set

  splitDataByKey<-function(dataset, key, NFrate, Frate= NFrate)
  {
    Facct = unique(dataset[dataset$Label > 0,][key])
    set.seed(23)
    Frand = runif(dim(Facct)[1])
    Facct$trainFlag = (Frand <= Frate)

    NFacct = unique(dataset[dataset$Label == 0,][key])
    NFrand = runif(dim(NFacct)[1])
    NFacct$trainFlag = (NFrand <= NFrate)

    data.set = merge(dataset, rbind(Facct,NFacct), by= key, all.x =TRUE, sort=FALSE)
    return (data.set)

  }

  data <- splitDataByKey(data, key, nfRate, frdRate)
  data_file_path <- file.path(tempdir(), "data.csv")
  write.csv(x = data,
            file = data_file_path,
            row.names = FALSE)
  data_text <- RxTextData(file = data_file_path)
  rxDataStep(inData = data_text,
             outFile = out_table,
             overwrite = TRUE)
}
rxSetComputeContext(sql)
tagged_fraud_table <- RxSqlServerData(table = "tagged_fraud",
                                      connectionString = connection_string)
partition_fraud_table <- RxSqlServerData(table = "partition_fraud",
                                         connectionString = connection_string)
rxExec(account_level_partition,
       in_table = tagged_fraud_table,
       out_table = partition_fraud_table,
       key = 'accountID',
       nfRate = 0.7,
       frdRate = 0.7)
####################################################################################################
## Create train and test tables and clean them respectively
####################################################################################################
clean_table <- function(in_table, out_table) {
  data <- rxImport(in_table)
  data$trainFlag <- NULL
  data[is.na(data)] <- 0
  data[data == "\"\"\"\"\"\"\"\""] <- 0
  data <- subset(data, transactionAmount > 0)
  date_time <- paste0(data$transactionDate, data$transactionTime)
  error_flag <- is.na(strptime(date_time, "%Y%m%d%H%M%S"))
  data <- data[!error_flag, ]
  data_file_path <- file.path(tempdir(), "data.csv")
  write.csv(x = data,
            file = data_file_path,
            row.names = FALSE)
  data_text <- RxTextData(file = data_file_path)
  rxDataStep(inData = data_text,
             outFile = out_table,
             overwrite = TRUE)
}
rxSetComputeContext(sql)
partition_fraud_table <- RxSqlServerData(table = "partition_fraud",
                                         connectionString = connection_string)
filter_table <- RxSqlServerData(sqlQuery = "select * from partition_fraud where trainFlag = 'TRUE'",
                                connectionString = connection_string)
clean_training_table <- RxSqlServerData(table = "clean_training",
                                        connectionString = connection_string)
rxExec(clean_table,
       in_table = filter_table,
       out_table = clean_training_table)
filter_table <- RxSqlServerData(sqlQuery = "select * from partition_fraud where trainFlag = 'FALSE'",
                                connectionString = connection_string)
clean_testing_table <- RxSqlServerData(table = "clean_testing",
                                       connectionString = connection_string)
rxExec(clean_table,
       in_table = filter_table,
       out_table = clean_testing_table)
####################################################################################################
## Cleanup
####################################################################################################
rm(list = ls())