Extract SQL case statement from a rulelist
to_sql_case(rulelist, rhs_column_name = "RHS", output_colname = "output")A rulelist object
(string, default: "RHS") Name of the column in the rulelist to be used as RHS (WHEN some_rule THEN rhs) in the sql case statement
(string, default: "output") Name of the output column created by the SQL statement (used in case ... AS output_column)
(string invisibly) SQL case statement
As a side-effect, the SQL statement is cat to stdout. The output contains newline character.
rulelist, tidy, augment, predict, convert_rule_flavor
Other Auxiliary Rulelist Utility:
convert_rule_flavor()
model_c5 = C50::C5.0(Attrition ~., data = modeldata::attrition, rules = TRUE)
tidy(model_c5)
#> ---- Rulelist --------------------------------
#> ▶ Keys: trial_nbr
#> ▶ Number of distinct keys: 1
#> ▶ Number of rules: 24
#> ▶ Model type: C5
#> ▶ Estimation type: classification
#> ▶ Is validation data set: FALSE
#>
#>
#> rule_nbr trial_nbr LHS RHS support confidence lift
#> <int> <int> <chr> <fct> <int> <dbl> <dbl>
#> 1 1 1 ( JobLevel <= 1 ) & ( Mont… Yes 16 0.944 5.9
#> 2 2 1 ( EnvironmentSatisfaction … No 521 0.941 1.1
#> 3 3 1 ( DailyRate <= 722 ) & ( J… Yes 13 0.933 5.8
#> 4 4 1 ( JobRole == 'Research_Sci… No 195 0.924 1.1
#> 5 5 1 ( EnvironmentSatisfaction … Yes 9 0.909 5.6
#> 6 6 1 ( EnvironmentSatisfaction … Yes 9 0.909 5.6
#> 7 7 1 ( JobRole %in% c('Laborato… Yes 14 0.875 5.4
#> 8 8 1 ( JobRole == 'Laboratory_T… Yes 6 0.875 5.4
#> 9 9 1 ( Department == 'Sales' ) … Yes 13 0.867 5.4
#> 10 10 1 ( TotalWorkingYears > 2 ) No 1347 0.864 1
#> # ℹ 14 more rows
#> ----------------------------------------------
to_sql_case(tidy(model_c5))
#> CASE
#> WHEN ( JobLevel <= 1 ) AND ( MonthlyIncome <= 2468 ) AND ( OverTime = 'Yes' ) AND ( TotalWorkingYears > 2 ) AND ( YearsAtCompany <= 3 ) THEN 'Yes'
#> WHEN ( EnvironmentSatisfaction IN ('Medium', 'High', 'Very_High') ) AND ( JobInvolvement IN ('Medium', 'High', 'Very_High') ) AND ( OverTime = 'No' ) AND ( TrainingTimesLastYear > 1 ) AND ( WorkLifeBalance IN ('Better', 'Best') ) THEN 'No'
#> WHEN ( DailyRate <= 722 ) AND ( JobLevel <= 1 ) AND ( MonthlyIncome <= 2468 ) AND ( OverTime = 'Yes' ) AND ( TotalWorkingYears > 2 ) THEN 'Yes'
#> WHEN ( JobRole = 'Research_Scientist' ) AND ( OverTime = 'No' ) THEN 'No'
#> WHEN ( EnvironmentSatisfaction IN ('Low', 'Medium') ) AND ( MaritalStatus IN ('Divorced', 'Married') ) AND ( NumCompaniesWorked > 4 ) AND ( OverTime = 'Yes' ) AND ( PerformanceRating = 'Excellent' ) AND ( RelationshipSatisfaction IN ('Low', 'Medium', 'High') ) THEN 'Yes'
#> WHEN ( EnvironmentSatisfaction IN ('Low', 'Medium') ) AND ( Gender = 'Male' ) AND ( MaritalStatus IN ('Divorced', 'Married') ) AND ( NumCompaniesWorked > 4 ) AND ( OverTime = 'Yes' ) AND ( PerformanceRating = 'Excellent' ) THEN 'Yes'
#> WHEN ( JobRole IN ('Laboratory_Technician', 'Sales_Representative') ) AND ( MonthlyIncome <= 2657 ) AND ( TotalWorkingYears <= 2 ) AND ( WorkLifeBalance IN ('Bad', 'Good') ) THEN 'Yes'
#> WHEN ( JobRole = 'Laboratory_Technician' ) AND ( MaritalStatus = 'Single' ) AND ( MonthlyIncome > 2468 ) AND ( OverTime = 'Yes' ) AND ( TrainingTimesLastYear <= 2 ) THEN 'Yes'
#> WHEN ( Department = 'Sales' ) AND ( MaritalStatus = 'Single' ) AND ( OverTime = 'Yes' ) AND ( YearsSinceLastPromotion > 1 ) THEN 'Yes'
#> WHEN ( TotalWorkingYears > 2 ) THEN 'No'
#> WHEN ( Age <= 44 ) AND ( OverTime = 'No' ) AND ( TotalWorkingYears > 2 ) AND ( WorkLifeBalance = 'Bad' ) AND ( YearsWithCurrManager <= 0 ) THEN 'Yes'
#> WHEN ( EducationField = 'Life_Sciences' ) AND ( EnvironmentSatisfaction IN ('Low', 'Medium') ) AND ( JobInvolvement = 'Low' ) AND ( MonthlyIncome > 2468 ) AND ( OverTime = 'Yes' ) THEN 'Yes'
#> WHEN ( JobInvolvement = 'Low' ) AND ( OverTime = 'No' ) AND ( TotalWorkingYears <= 2 ) THEN 'Yes'
#> WHEN ( Department = 'Sales' ) AND ( JobInvolvement IN ('Low', 'Medium') ) AND ( JobSatisfaction IN ('Medium', 'High') ) AND ( MaritalStatus = 'Single' ) AND ( OverTime = 'Yes' ) AND ( YearsSinceLastPromotion <= 1 ) THEN 'Yes'
#> WHEN ( JobRole IN ('Laboratory_Technician', 'Sales_Representative') ) AND ( TotalWorkingYears <= 2 ) AND ( TrainingTimesLastYear <= 1 ) THEN 'Yes'
#> WHEN ( JobSatisfaction = 'High' ) AND ( OverTime = 'No' ) AND ( WorkLifeBalance = 'Bad' ) AND ( YearsSinceLastPromotion > 6 ) THEN 'Yes'
#> WHEN ( Department = 'Sales' ) AND ( JobInvolvement = 'Very_High' ) AND ( MaritalStatus = 'Single' ) AND ( OverTime = 'Yes' ) THEN 'Yes'
#> WHEN ( EnvironmentSatisfaction = 'Low' ) AND ( JobRole = 'Laboratory_Technician' ) AND ( TotalWorkingYears <= 2 ) THEN 'Yes'
#> WHEN ( JobRole = 'Human_Resources' ) AND ( TotalWorkingYears <= 2 ) THEN 'Yes'
#> WHEN ( OverTime = 'Yes' ) AND ( TotalWorkingYears <= 2 ) THEN 'Yes'
#> WHEN ( OverTime = 'No' ) AND ( TotalWorkingYears > 2 ) AND ( WorkLifeBalance = 'Bad' ) AND ( YearsWithCurrManager <= 0 ) THEN 'Yes'
#> WHEN ( Department = 'Sales' ) AND ( MaritalStatus = 'Single' ) AND ( OverTime = 'Yes' ) THEN 'Yes'
#> WHEN ( OverTime = 'No' ) AND ( WorkLifeBalance = 'Bad' ) AND ( YearsSinceLastPromotion > 6 ) THEN 'Yes'
#> WHEN ( EnvironmentSatisfaction IN ('Low', 'Medium') ) AND ( MonthlyRate > 16620 ) AND ( OverTime = 'Yes' ) THEN 'Yes'
#> ELSE NULL
#> END AS output