---
output:
  html_document: default
  pdf_document: default
---
# Chapter 7 - Examples - R Script

<br>
This file contains the relevant code used to generate the examples in chapter 7 of the CAS Data Quality Management in the P&C Insurance Sector Monograph.  The following files should be downloaded from the CAS website and stored in the same file directory as this script before compiling:

* wc_claims_problems.csv
* wc_claims_problems_5k.csv
* wc_claims_cleaned.csv

The first two datasets represent the original data with data quality issues, including duplicated records.  For some packages we use the smaller dataset with 5,000 claims to improve processing time.  The final dataset represents an improved dataset where the data quality issues have been largely remediated. 

<style>
  .main-container {
    max-width: 1800px !important;
  }
</style>


```{r setup, include=FALSE}
knitr::opts_chunk$set(echo = TRUE, cache = FALSE, message = FALSE, warning = FALSE)
options(max.print=500, width = 200)
library(dplyr)
```

### Example 7.1 (using RecordLinkage package in R)
This script uses the RecordLinkage package to identify potentially duplicate records in a dataset.  The script generates figure 11 and figure 12 from Chapter 7.

```{r, results = "hide"}
library(RecordLinkage)
```

```{r}
wcdata5k <- read.csv(file="wc_claims_problems_5k.csv")
wcdata_modified5k <- wcdata5k %>%
        select(-c("Claim_Number","Incd_Med_Loss"))
wcdata_identity5k <- wcdata5k %>%
        select("Claim_Number")
wcdata_identity5k <- as.numeric(unlist(wcdata_identity5k))
wcpairs5k <- compare.dedup(dataset = wcdata_modified5k, identity = wcdata_identity5k)
wcweightpairs5k <- epiWeights(wcpairs5k)

#To generate figure 11:
summary(wcweightpairs5k) 

#To generate figure 12:
getPairs(wcweightpairs5k,0.52,0.5) 
```

### Example 7.2 (using dlookr package in R)
The following script uses the dlookr package to generate a "Data Quality Diagnosis Report". The full report can only be outputted in an "HTML" or "PDF" format, and thus cannot be shown within this markdown file. To see the report, we recommend evaluating the script chunk outside of this file. 

One of the tables provided in the full report is the "Data Quality Overview" table, included in the Monograph as figure 13. An unformatted version of this table can be outputted directly in R markdown. The script chunks to generate both the full report and the unformatted version of figure 13 are provided below.

```{r, results = "hide"}
library(dlookr)
```

```{r, include = FALSE}
wcdata<-read.csv('wc_claims_problems.csv',
             colClasses = c('character',
                            'character',
                            'character',
                            'character',
                            'character',
                            'character',
                            'character',
                            'character',
                            'numeric'))
```

```{r}
#To generate figure 13:
wcdata %>%
        select(-"Claim_Number") %>%
        diagnose()
```
```{r, eval = FALSE}
#To generate the full report (run this code chunk outside of R markdown):
wcdata %>%
        select(-"Claim_Number") %>%
        diagnose_report(output_format = "html",
                   output_file = "cas_Monograph_dq.html")
```

### Example 7.3 (using VIM package in R)
The following script uses the VIM package to further analyze missing data. This package allows for visualization of missing values across multiple data fields. The below script is used to generate figures 14, 15 and 16 in the Monograph.

```{r, results = "hide"}
library(VIM)
```

```{r, include = FALSE}
wcdata<-read.csv('wc_claims_problems.csv',
             colClasses = c('character',
                            'character',
                            'character',
                            'character',
                            'character',
                            'character',
                            'character',
                            'character',
                            'numeric'))
```

```{r}
#To generate figure 14:
aggr(wcdata,
     props=TRUE,
     sort.vars=TRUE,
     only.miss=TRUE,
     combined=TRUE,
     numbers = TRUE,
     varheight=FALSE,
     col="red",
     cex.axis=.75,
     labels=c('Claim#','State','LossDate','ReptDate','ClmType','ClmStat','Body_Part','Inj_Type','Incd_Loss')
     ) 

#To generate figure 15:
spineMiss(wcdata[, c("Injury_Type", "Body_Part")],
          col="red") #generates figure 15

#To generate figure 16:
wcdata$Accident_Date<-as.Date(wcdata$Accident_Date)
wcdata$Accident_State<-as.factor(wcdata$Accident_State)
wcdata$Claim_Type<-as.factor(wcdata$Claim_Type)
wcdata$Body_Part<-as.factor(wcdata$Body_Part)
wcdata$Injury_Type<-as.factor(wcdata$Injury_Type)

wcdata %>%
  select(c("Accident_Date","Accident_State","Claim_Type","Body_Part","Injury_Type")) %>%
  parcoordMiss(highlight = 'Injury_Type', 
               alpha = 1.0,
               col = c("grey", "grey40","gold", "firebrick2","firebrick4","gold4"),
               labels=c("LossDate",'State','ClmTyp','Body_Part','Inj_Type'))
```

### Example 7.4 (using validate package in R)
The following script uses the validate package to demonstrate the application of data quality rules. This script is used to generate figure 17 in the Monograph.

```{r, results = "hide"}
library(validate)
```
```{r, include = FALSE}
library(lubridate)
```

```{r}
wcdata$Report_Date<-as.Date(wcdata$Report_Date)
validatereport<-check_that(wcdata,
               Incd_Med_Loss<1000000,
               Incd_Med_Loss>1,
               Report_Date-Accident_Date>=0,
               if(Injury_Type=='Concussion')  Body_Part=='Head'
               )

summary(validatereport)

#To generate figure 17:
barplot(validatereport,
        main='Validate on wc_claims',
        colors=c(fails='firebrick2',passes='grey',nNA='gold4'),
        xlab="Items",
        mgp=c(3,.6,0)) 
```

### Example 7.5 (Imputation of Missing Values)
The following script demonstrates the imputation of missing values as discussed in Example 7.5 of the Monograph.  We use the nnet package to fit a multinomial log-linear model via a neural network to carry out classification mode imputation. This script is used to generate figure 18 in the Monograph.

```{r, results = "hide"}
library(nnet)
```

```{r, include = FALSE}
wcdata<-read.csv('wc_claims_problems.csv',
             colClasses = c('character',
                            'character',
                            'character',
                            'character',
                            'character',
                            'character',
                            'character',
                            'character',
                            'numeric'))
```

```{r}
#There is no mode function in Base R so we define one:
mode <- function(x){
        ux <- unique(x)
        ux[which.max(tabulate(match(x,ux)))]
}

missing<-is.na(wcdata$Body_Part)|is.na(wcdata$Injury_Type!='NA')
wcdata_nomiss<-wcdata[!missing,]
wcdata_miss<-wcdata[missing,]

#Body Part mode imputation
wcdata_miss$Mode_BP_Imp <- mode(wcdata_nomiss$Body_Part)

#Fitting a classification model to impute the missing values
bp_model <- multinom(Body_Part ~ Injury_Type+Claim_Type+Incd_Med_Loss, data = wcdata_nomiss)
wcdata_miss$Classifaction_BP_Imp <- predict(bp_model, wcdata_miss)
wcdata_miss_mod <- wcdata_miss%>%
        select(-c("Accident_Date","Report_Date","Claim_Status"))
wcdata_miss_mod[1:10,]
```

### Example 7.6 (Anomaly Scoring on Non-missing Data)
The following script demonstrates the imputation of missing values as discussed in Example 7.6 of the Monograph.  We use the nnet package to fit a multinomial log-linear model via a neural network to generate the probabilities of anomalies. This script is used to generate figure 19 in the Monograph.

```{r, include = FALSE}
options(scipen=999)
```

```{r, results = "hide"}
library(nnet)
```

```{r}
missing<-is.na(wcdata$Body_Part)|is.na(wcdata$Injury_Type!='NA')

wcdata_nomiss<-wcdata[!missing,]
wcdata_miss<-wcdata[missing,]

bp_model <- multinom(Body_Part ~ Injury_Type+Claim_Type+Incd_Med_Loss, data = wcdata_nomiss)

summary(bp_model)

preds<-data.frame(fitted(bp_model))

wcdata_nomiss$Probability_Body_Part<-ifelse(wcdata_nomiss$Body_Part=='Back',preds$Back,
                    ifelse(wcdata_nomiss$Body_Part=='Hand',preds$Hand,
                      ifelse(wcdata_nomiss$Body_Part=='Head',preds$Head,preds$Wrist)))

anomalies<-wcdata_nomiss[wcdata_nomiss$Probability_Body_Part<.025,]
anomalies_mod <- anomalies%>%
        select(-c("Accident_Date","Report_Date","Claim_Status"))
anomalies_mod[1:10,]
```

### Example 7.7 (Impact of Improving Data Quality on an Actuarial Analysis)
The following script demonstrates the impact of improving data quality on Actuarial Analyses, as detailed in Example 7.7.  Specifically this involves comparing the fit of glm models to the original dataset versus the datasets with improved data quality.  This script is used to generate figures 20 and figure 21 in the Monograph.   

```{r}
wcdata__orig<-read.csv('wc_claims_problems.csv')
wcdata_cleaned<-read.csv('wc_claims_cleaned.csv')

wcdata__orig$Report_Lag<-difftime(wcdata__orig$Report_Date ,wcdata__orig$Accident_Date , units = c("days"))
wcdata_cleaned$Report_Lag<-difftime(wcdata_cleaned$Report_Date ,wcdata_cleaned$Accident_Date , units = c("days"))

WeightedGini <- function(solution, weights, submission){
   df = data.frame(solution, weights, submission)
   n <- nrow(df)
   df <- df[order(df$submission, decreasing = TRUE),]
   df$random = cumsum(df$weights/sum(df$weights))
   df$cumPosFound <- cumsum(df$solution * df$weights)
   df$Lorentz <- df$cumPosFound / df$cumPosFound[n]
   sum(df$Lorentz[-1]*df$random[-n]) - sum(df$Lorentz[-n]*df$random[-1]) }

NormGini <- function(solution, weights, submission) {
   WeightedGini(solution, weights, submission) / WeightedGini(solution, weights, solution)
                }

glm_orig<-glm(Incd_Med_Loss~Accident_State+Report_Lag+Claim_Type+Claim_Status+Body_Part+Injury_Type,
            data=wcdata__orig,
            family=Gamma(link='log'))
glm_cleaned<-glm(Incd_Med_Loss~Accident_State+Report_Lag+Claim_Type+Claim_Status+Body_Part+Injury_Type,
            data=wcdata_cleaned,
            family=Gamma(link='log'))

weights_orig<-rep(1,nrow(wcdata__orig))
weights_cleaned<-rep(1,nrow(wcdata_cleaned))

summary(glm_orig)
summary(glm_cleaned)

NormGini(wcdata__orig$Incd_Med_Loss,weights_orig,predict(glm_orig,wcdata__orig,type='response'))
NormGini(wcdata_cleaned$Incd_Med_Loss,weights_cleaned,predict(glm_cleaned,wcdata_cleaned,type='response'))

#To generate figure 20:
plot(glm_orig) 
#To generate figure 21:
plot(glm_cleaned)
```



