AppRISExVRU Loss Ratios by COB V2

Import Packages / Initialize

library(tidyverse)
library(ggplot2)
library(plotly)
# library(kableExtra)
library(DT)
library(data.table)
library(dygraphs)
library(latticeExtra)
library(stringr)
library(hrbrthemes)
library(patchwork)
library(tinytex)
library(dplyr)
library(Metrics)

Loss Ratios by Class of Business (COB)

This analysis will cover Total Loss Ratio (VRUInc / CATPrem + AOPPrem) as well as CAT Loss Ratios (VRUInc / CAT Prem).

Note: Data tables are interactive, so feel free to toggle with columns to sort in ascending/descending order, apply filters and/or ranges, etc.

DataFrame Set up

update data w/ mutate: add columns for Total Premium (TotalPremium = AOPPrem + CATPrem), Total Loss Ratios (TotPrem_LR = VRUInc / (AOPPrem + CATPrem)), CAT Loss Ratios (CATPrem_LR = VRUInc / CATPrem), Total Gain or Loss (Tot_Gain_Loss = (AOPPrem + CATPrem) - VRUInc), CAT Gain or Loss (CAT_Gain_Loss = CATPrem - VRUInc)

library(readxl)
VRU_Clean_Data_v2 <- read_excel("~/Desktop/DataAnalysis_AppRISE_Projects/AppRISE/VRUxAppRISE/Datasets/VRU_Clean_Data_v2.xlsx")

VRU_Clean_Data_v2 <- VRU_Clean_Data_v2 %>%
  dplyr::select(-c(Peril, PCS, Year)) %>%
  mutate(across('COB', str_replace, 'Condominiums', 'Condominium')) %>%
  mutate(across('COB', str_replace, 'BarsTavernsCocktailLoungesAndNightclubs', 'Bars')) %>%
  mutate(across('COB', str_replace, 'Healthcare_MedicalDental_InclSuppliers|Healthcare_MedicalDental_InclSuppliers_Service', 'Healthcare')) %>%
  mutate(across('COB', str_replace, 'Healthcare_Service', 'Healthcare')) %>%
  mutate(across('COB', str_replace, 'HotelMotels', 'HotelsMotels')) %>%
  mutate(across('COB', str_replace, 'GasStations_ConvenienceStores_CarWashes_AutoRepair', 'GasStations')) %>%
  mutate(across('COB', str_replace, 'lightmanufacturing', 'LightManufacturing')) %>%
  mutate(across('COB', str_replace, 'Restaurants', 'Restaurant')) %>%
  mutate(across('COB', str_replace, 'Churches_Nonprofit', 'Church')) %>%
  mutate(across('COB', str_replace, 'ApartmentsOverFourStories', 'Apartments')) %>%
  mutate(across('COB', str_replace, 'RetailWithRefrigerationEquipment', 'RetailwRefrigEquip'))

VRU_Clean_Data_v2 <- subset(VRU_Clean_Data_v2, !(COB == 'Pleasemakeaselection')) 

VRU_Clean_Data_v2 <- VRU_Clean_Data_v2 %>%
  transmute(Event, COB, AOPPrem, CATPrem, VRUInc, PrimaryState)

# update data w/ mutate: add columns for loss ratios
VRU_Clean_Data_v2 <- VRU_Clean_Data_v2 %>%
  mutate(TotalPremium = AOPPrem + CATPrem, TotPrem_LR = VRUInc / (AOPPrem + CATPrem), CATPrem_LR = VRUInc / CATPrem, Tot_Gain_Loss = (AOPPrem + CATPrem) - VRUInc, CAT_Gain_Loss = CATPrem - VRUInc)

# the prior line of code calculate 0-0 as 'NA', replace NA values with a 0.
VRU_Clean_Data_v2 <- replace(VRU_Clean_Data_v2, is.na(VRU_Clean_Data_v2), 0)

# kinda redundant... I just wanted a shorter name for the data tbh
LR_df <- VRU_Clean_Data_v2

# print df and verify proper adjustments were made
View(LR_df)

View Dataset: filter and export if you’d like

# print the table and add filter; this will allow us view data with a filter and gives us the ability to print the filtered table from the html file.
datatable(LR_df,
          rownames = FALSE, 
          filter = 'top',
          extensions = 'Buttons',
          options = list(
            dom = 'Bfrtip',
            buttons = c('copy', 'csv', 'excel', 'pdf', 'print')
          )) 

Loss Ratios

Overall Loss Ratios by COB

AggLR_COB <- LR_df %>%
  group_by(COB) %>%
  summarize(n    = n(),
            aggrTLR = sum(VRUInc)/sum(TotalPremium),
            aggrCLR = sum(VRUInc)/sum(CATPrem),
            net1 = sum(Tot_Gain_Loss),
            net2 = sum(CAT_Gain_Loss))

datatable(AggLR_COB,
          rownames = FALSE, 
          filter = 'top',
          extensions = 'Buttons',
          options = list(
            dom = 'Bfrtip',
            buttons = c('copy', 'csv', 'excel', 'pdf', 'print')),
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            'Table 2: ', htmltools::em('Aggregate Loss Ratios by COB')),
          colnames = c('Class of Business' = 1, '# of Occurences' = 2, 'Overall Total Loss Ratio' = 3, 'Overall CAT Loss Ratio' = 4, 'Total Gain(+) | Loss(-)' = 5, 'CAT Gain(+) | Loss(-)' = 6)) %>%
  formatPercentage(c('Overall Total Loss Ratio', 'Overall CAT Loss Ratio'), 3) %>%
  formatCurrency(c('Total Gain(+) | Loss(-)', 'CAT Gain(+) | Loss(-)'), currency = "", interval = 3, mark = ",")

Hanna Loss Ratios

Hanna: Overall Loss Ratios

hanna_events <- LR_df %>%
  filter(Event == 'Hanna') 

hanna_aggr_LR <- hanna_events %>%
  summarise(n    = n(),
            aggrTLR = sum(VRUInc)/sum(TotalPremium),
            #avgTLR = mean(TotPrem_LR),
            aggrCLR = sum(VRUInc)/sum(CATPrem),
            #avgCLR = mean(CATPrem_LR),
            net1 = sum(Tot_Gain_Loss),
            net2 = sum(CAT_Gain_Loss))

datatable(hanna_aggr_LR,
          rownames = FALSE,
          colnames = c('# of Occurences' = 1, 'Overall Total Loss Ratio' = 2, 'Overall CAT Loss Ratio' = 3, 'Total Gain(+) | Loss(-)' = 4, 'CAT Gain(+) | Loss(-)' = 5)) %>%
  formatPercentage(c('Overall Total Loss Ratio', 'Overall CAT Loss Ratio'), 3) %>%
  formatCurrency(c('Total Gain(+) | Loss(-)', 'CAT Gain(+) | Loss(-)'), currency = "", interval = 3, mark = ",")

# add caption: average Total and CAT Loss Ratios weren't too bad, however, in aggregate, you we see significant losses (19% & 27% vs. 99% and 135% respectively). Telling us there were few instances with significant losses.

Hanna: Overall Loss Ratios by COB

hanna_aggrLR_COB <- hanna_events %>%
  group_by(COB) %>%
  summarize(n    = n(),
            aggrTLR = sum(VRUInc)/sum(TotalPremium),
            #avgTLR = mean(TotPrem_LR),
            aggrCLR = sum(VRUInc)/sum(CATPrem),
            #avgCLR = mean(CATPrem_LR),
            net1 = sum(Tot_Gain_Loss),
            net2 = sum(CAT_Gain_Loss))

datatable(hanna_aggrLR_COB,
          rownames = FALSE, 
          filter = 'top',
          extensions = 'Buttons',
          options = list(
            dom = 'Bfrtip',
            buttons = c('copy', 'csv', 'excel', 'pdf', 'print')),
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            'Hanna: ', htmltools::em('Overall Loss Ratios by COB')),
          colnames = c('Class of Business' = 1, '# of Occurences' = 2, 'Overall Total Loss Ratio' = 3, 'Overall CAT Loss Ratio' = 4, 'Total Gain(+) | Loss(-)' = 5, 'CAT Gain(+) | Loss(-)' = 6)) %>%
  formatPercentage(c('Overall Total Loss Ratio', 'Overall CAT Loss Ratio'), 3) %>%
  formatCurrency(c('Total Gain(+) | Loss(-)', 'CAT Gain(+) | Loss(-)'), currency = "", interval = 3, mark = ",")

Hanna: Losses by COB (using Total Premium)

options(scipen = 10000)
ggplotly(hanna_events %>%
  #filter(COB == c('Education','RetailwRefrigEquip','Hospitality','Other','Church','Condominium','Apartments','Municipality'), ) %>%
  ggplot(aes(x = COB)) +
  geom_col(aes(y = TotalPremium, fill = COB)) +
  geom_col(aes(y = -VRUInc)) +
  #geom_text(aes(label = TotalPremium)) +
  labs(x = 'Event', y = 'Incurred Losses <--> Collected Premium', title = 'Loss by COB (Total Premium): Hanna') +
  theme(axis.text.x = element_text(angle = 45, vjust = 0.5), legend.position = "none")) 

#ggplotly(hanna_worst_TLR %>%
#  ggplot(aes(x = COB)) +
#  geom_col(aes(y = TotalPremium, fill = COB)) +
#  geom_col(aes(y = -VRUInc)) +
#  labs(x = 'Event', y = 'Incurred Losses <--> Collected Premium', title = 'High Loss COB: Hanna') +
#  theme(axis.text.x = element_text(angle = 45, vjust = 0.5)))

Hanna: Losses by COB (using CAT Premium)

options(scipen = 10000)
ggplotly(hanna_events %>%
  #filter(COB == c('Education','RetailwRefrigEquip','Hospitality','Other','Church','Condominium','Apartments','Municipality')) %>%
  ggplot(aes(x = COB)) +
  geom_col(aes(y = CATPrem, fill = COB)) +
  geom_col(aes(y = -VRUInc)) +
  labs(x = 'Event', y = 'Incurred Losses <--> Collected Premium', title = 'Loss by COB (CAT Premium): Hanna') +
  theme(axis.text.x = element_text(angle = 45, vjust = 0.5), legend.position = "none"))

#ggplotly(hanna_worst_TLR %>%
#  ggplot(aes(x = COB)) +
#  geom_col(aes(y = TotalPremium, fill = COB)) +
#  geom_col(aes(y = -VRUInc)) +
#  labs(x = 'Event', y = 'Incurred Losses <--> Collected Premium', title = 'High Loss COB: Hanna') +
#  theme(axis.text.x = element_text(angle = 45, vjust = 0.5)))

Hanna: Worst 8 COBs (by CAT Total Loss Ratios)

Hanna: Best 8 COBs (by Overall Total Loss Ratios)

Hanna: Best 8 COBs (by CAT Total Loss Ratios)

Laura Loss Ratios

Laura: Overall Loss Ratios

laura_events <- LR_df %>%
  filter(Event == 'Laura') 

laura_aggr_LR <- laura_events %>%
  summarise(n    = n(),
            aggrTLR = sum(VRUInc)/sum(TotalPremium),
            #avgTLR = mean(TotPrem_LR),
            aggrCLR = sum(VRUInc)/sum(CATPrem),
            #avgCLR = mean(CATPrem_LR),
            net1 = sum(Tot_Gain_Loss),
            net2 = sum(CAT_Gain_Loss))

datatable(laura_aggr_LR,
          rownames = FALSE,
          colnames = c('# of Occurences' = 1, 'Overall Total Loss Ratio' = 2, 'Overall CAT Loss Ratio' = 3, 'Total Gain(+) | Loss(-)' = 4, 'CAT Gain(+) | Loss(-)' = 5)) %>%
  formatPercentage(c('Overall Total Loss Ratio', 'Overall CAT Loss Ratio'), 3) %>%
  formatCurrency(c('Total Gain(+) | Loss(-)', 'CAT Gain(+) | Loss(-)'), currency = "", interval = 3, mark = ",")

Laura: Overall Loss Ratios by COB

laura_aggrLR_COB <- laura_events %>%
  group_by(COB) %>%
  summarize(n    = n(),
            aggrTLR = sum(VRUInc)/sum(TotalPremium),
            #avgTLR = mean(TotPrem_LR),
            aggrCLR = sum(VRUInc)/sum(CATPrem),
            #avgCLR = mean(CATPrem_LR),
            net1 = sum(Tot_Gain_Loss),
            net2 = sum(CAT_Gain_Loss))

datatable(laura_aggrLR_COB,
          rownames = FALSE, 
          filter = 'top',
          extensions = 'Buttons',
          options = list(
            dom = 'Bfrtip',
            buttons = c('copy', 'csv', 'excel', 'pdf', 'print')),
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            'Laura: ', htmltools::em('Overall Loss Ratios by COB')),
          colnames = c('Class of Business' = 1, '# of Occurences' = 2, 'Overall Total Loss Ratio' = 3, 'Overall CAT Loss Ratio' = 4, 'Total Gain(+) | Loss(-)' = 5, 'CAT Gain(+) | Loss(-)' = 6)) %>%
  formatPercentage(c('Overall Total Loss Ratio', 'Overall CAT Loss Ratio'), 3) %>%
  formatCurrency(c('Total Gain(+) | Loss(-)', 'CAT Gain(+) | Loss(-)'), currency = "", interval = 3, mark = ",")

Sally Loss Ratios

Sally: Overall Loss Ratios

sally_events <- LR_df %>%
  filter(Event == 'Sally') 

sally_aggr_LR <- sally_events %>%
  summarise(n    = n(),
            aggrTLR = sum(VRUInc)/sum(TotalPremium),
            #avgTLR = mean(TotPrem_LR),
            aggrCLR = sum(VRUInc)/sum(CATPrem),
            #avgCLR = mean(CATPrem_LR),
            net1 = sum(Tot_Gain_Loss),
            net2 = sum(CAT_Gain_Loss))

datatable(sally_aggr_LR,
          rownames = FALSE,
          colnames = c('# of Occurences' = 1, 'Overall Total Loss Ratio' = 2, 'Overall CAT Loss Ratio' = 3, 'Total Gain(+) | Loss(-)' = 4, 'CAT Gain(+) | Loss(-)' = 5)) %>%
  formatPercentage(c('Overall Total Loss Ratio', 'Overall CAT Loss Ratio'), 3) %>%
  formatCurrency(c('Total Gain(+) | Loss(-)', 'CAT Gain(+) | Loss(-)'), currency = "", interval = 3, mark = ",")

Sally: Overall Loss Ratios by COB

sally_aggrLR_COB <- sally_events %>%
  group_by(COB) %>%
  summarize(n    = n(),
            aggrTLR = sum(VRUInc)/sum(TotalPremium),
            #avgTLR = mean(TotPrem_LR),
            aggrCLR = sum(VRUInc)/sum(CATPrem),
            #avgCLR = mean(CATPrem_LR),
            net1 = sum(Tot_Gain_Loss),
            net2 = sum(CAT_Gain_Loss))

datatable(sally_aggrLR_COB,
          rownames = FALSE, 
          filter = 'top',
          extensions = 'Buttons',
          options = list(
            dom = 'Bfrtip',
            buttons = c('copy', 'csv', 'excel', 'pdf', 'print')),
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            'Sally: ', htmltools::em('Overall Loss Ratios by COB')),
          colnames = c('Class of Business' = 1, '# of Occurences' = 2, 'Overall Total Loss Ratio' = 3, 'Overall CAT Loss Ratio' = 4, 'Total Gain(+) | Loss(-)' = 5, 'CAT Gain(+) | Loss(-)' = 6)) %>%
  formatPercentage(c('Overall Total Loss Ratio', 'Overall CAT Loss Ratio'), 3) %>%
  formatCurrency(c('Total Gain(+) | Loss(-)', 'CAT Gain(+) | Loss(-)'), currency = "", interval = 3, mark = ",")

Delta Loss Ratios

Delta: Overall Loss Ratios

delta_events <- LR_df %>%
  filter(Event == 'Delta') 

delta_aggr_LR <- delta_events %>%
  summarise(n    = n(),
            aggrTLR = sum(VRUInc)/sum(TotalPremium),
            #avgTLR = mean(TotPrem_LR),
            aggrCLR = sum(VRUInc)/sum(CATPrem),
            #avgCLR = mean(CATPrem_LR),
            net1 = sum(Tot_Gain_Loss),
            net2 = sum(CAT_Gain_Loss))

datatable(delta_aggr_LR,
          rownames = FALSE,
          colnames = c('# of Occurences' = 1, 'Overall Total Loss Ratio' = 2, 'Overall CAT Loss Ratio' = 3, 'Total Gain(+) | Loss(-)' = 4, 'CAT Gain(+) | Loss(-)' = 5)) %>%
  formatPercentage(c('Overall Total Loss Ratio', 'Overall CAT Loss Ratio'), 3) %>%
  formatCurrency(c('Total Gain(+) | Loss(-)', 'CAT Gain(+) | Loss(-)'), currency = "", interval = 3, mark = ",")

Delta: Overall Loss Ratios by COB

delta_aggrLR_COB <- delta_events %>%
  group_by(COB) %>%
  summarize(n    = n(),
            aggrTLR = sum(VRUInc)/sum(TotalPremium),
            #avgTLR = mean(TotPrem_LR),
            aggrCLR = sum(VRUInc)/sum(CATPrem),
            #avgCLR = mean(CATPrem_LR),
            net1 = sum(Tot_Gain_Loss),
            net2 = sum(CAT_Gain_Loss))

datatable(delta_aggrLR_COB,
          rownames = FALSE, 
          filter = 'top',
          extensions = 'Buttons',
          options = list(
            dom = 'Bfrtip',
            buttons = c('copy', 'csv', 'excel', 'pdf', 'print')),
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            'Delta: ', htmltools::em('Overall Loss Ratios by COB')),
          colnames = c('Class of Business' = 1, '# of Occurences' = 2, 'Overall Total Loss Ratio' = 3, 'Overall CAT Loss Ratio' = 4, 'Total Gain(+) | Loss(-)' = 5, 'CAT Gain(+) | Loss(-)' = 6)) %>%
  formatPercentage(c('Overall Total Loss Ratio', 'Overall CAT Loss Ratio'), 3) %>%
  formatCurrency(c('Total Gain(+) | Loss(-)', 'CAT Gain(+) | Loss(-)'), currency = "", interval = 3, mark = ",")

Zeta Loss Ratios

Zeta: Overall Loss Ratios

zeta_events <- LR_df %>%
  filter(Event == 'Zeta') 

zeta_aggr_LR <- zeta_events %>%
  summarise(n    = n(),
            aggrTLR = sum(VRUInc)/sum(TotalPremium),
            #avgTLR = mean(TotPrem_LR),
            aggrCLR = sum(VRUInc)/sum(CATPrem),
            #avgCLR = mean(CATPrem_LR),
            net1 = sum(Tot_Gain_Loss),
            net2 = sum(CAT_Gain_Loss))

datatable(zeta_aggr_LR,
          rownames = FALSE,
          colnames = c('# of Occurences' = 1, 'Overall Total Loss Ratio' = 2, 'Overall CAT Loss Ratio' = 3, 'Total Gain(+) | Loss(-)' = 4, 'CAT Gain(+) | Loss(-)' = 5)) %>%
  formatPercentage(c('Overall Total Loss Ratio', 'Overall CAT Loss Ratio'), 3) %>%
  formatCurrency(c('Total Gain(+) | Loss(-)', 'CAT Gain(+) | Loss(-)'), currency = "", interval = 3, mark = ",")

Zeta: Overall Loss Ratios by COB

zeta_aggrLR_COB <- zeta_events %>%
  group_by(COB) %>%
  summarize(n    = n(),
            aggrTLR = sum(VRUInc)/sum(TotalPremium),
            #avgTLR = mean(TotPrem_LR),
            aggrCLR = sum(VRUInc)/sum(CATPrem),
            #avgCLR = mean(CATPrem_LR),
            net1 = sum(Tot_Gain_Loss),
            net2 = sum(CAT_Gain_Loss))

datatable(zeta_aggrLR_COB,
          rownames = FALSE, 
          filter = 'top',
          extensions = 'Buttons',
          options = list(
            dom = 'Bfrtip',
            buttons = c('copy', 'csv', 'excel', 'pdf', 'print')),
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            'Zeta: ', htmltools::em('Overall Loss Ratios by COB')),
          colnames = c('Class of Business' = 1, '# of Occurences' = 2, 'Overall Total Loss Ratio' = 3, 'Overall CAT Loss Ratio' = 4, 'Total Gain(+) | Loss(-)' = 5, 'CAT Gain(+) | Loss(-)' = 6)) %>%
  formatPercentage(c('Overall Total Loss Ratio', 'Overall CAT Loss Ratio'), 3) %>%
  formatCurrency(c('Total Gain(+) | Loss(-)', 'CAT Gain(+) | Loss(-)'), currency = "", interval = 3, mark = ",")

Ida Loss Ratios

Ida: Overall Loss Ratios

ida_events <- LR_df %>%
  filter(Event == 'Ida') 

ida_aggr_LR <- ida_events %>%
  summarise(n    = n(),
            aggrTLR = sum(VRUInc)/sum(TotalPremium),
            #avgTLR = mean(TotPrem_LR),
            aggrCLR = sum(VRUInc)/sum(CATPrem),
            #avgCLR = mean(CATPrem_LR),
            net1 = sum(Tot_Gain_Loss),
            net2 = sum(CAT_Gain_Loss))

datatable(ida_aggr_LR,
          rownames = FALSE,
          colnames = c('# of Occurences' = 1, 'Overall Total Loss Ratio' = 2, 'Overall CAT Loss Ratio' = 3, 'Total Gain(+) | Loss(-)' = 4, 'CAT Gain(+) | Loss(-)' = 5)) %>%
  formatPercentage(c('Overall Total Loss Ratio', 'Overall CAT Loss Ratio'), 3) %>%
  formatCurrency(c('Total Gain(+) | Loss(-)', 'CAT Gain(+) | Loss(-)'), currency = "", interval = 3, mark = ",")

Ida: Overall Loss Ratios by COB

ida_aggrLR_COB <- ida_events %>%
  group_by(COB) %>%
  summarize(n    = n(),
            aggrTLR = sum(VRUInc)/sum(TotalPremium),
            #avgTLR = mean(TotPrem_LR),
            aggrCLR = sum(VRUInc)/sum(CATPrem),
            #avgCLR = mean(CATPrem_LR),
            net1 = sum(Tot_Gain_Loss),
            net2 = sum(CAT_Gain_Loss))

datatable(ida_aggrLR_COB,
          rownames = FALSE, 
          filter = 'top',
          extensions = 'Buttons',
          options = list(
            dom = 'Bfrtip',
            buttons = c('copy', 'csv', 'excel', 'pdf', 'print')),
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            'Ida: ', htmltools::em('Overall Loss Ratios by COB')),
          colnames = c('Class of Business' = 1, '# of Occurences' = 2, 'Overall Total Loss Ratio' = 3, 'Overall CAT Loss Ratio' = 4, 'Total Gain(+) | Loss(-)' = 5, 'CAT Gain(+) | Loss(-)' = 6)) %>%
  formatPercentage(c('Overall Total Loss Ratio', 'Overall CAT Loss Ratio'), 3) %>%
  formatCurrency(c('Total Gain(+) | Loss(-)', 'CAT Gain(+) | Loss(-)'), currency = "", interval = 3, mark = ",")

Ian Loss Ratios

Ian: Overall Loss Ratios

ian_events <- LR_df %>%
  filter(Event == 'Ian') 

ian_aggr_LR <- ian_events %>%
  summarise(n    = n(),
            aggrTLR = sum(VRUInc)/sum(TotalPremium),
            #avgTLR = mean(TotPrem_LR),
            aggrCLR = sum(VRUInc)/sum(CATPrem),
            #avgCLR = mean(CATPrem_LR),
            net1 = sum(Tot_Gain_Loss),
            net2 = sum(CAT_Gain_Loss))

datatable(ian_aggr_LR,
          rownames = FALSE,
          colnames = c('# of Occurences' = 1, 'Overall Total Loss Ratio' = 2, 'Overall CAT Loss Ratio' = 3, 'Total Gain(+) | Loss(-)' = 4, 'CAT Gain(+) | Loss(-)' = 5)) %>%
  formatPercentage(c('Overall Total Loss Ratio', 'Overall CAT Loss Ratio'), 3) %>%
  formatCurrency(c('Total Gain(+) | Loss(-)', 'CAT Gain(+) | Loss(-)'), currency = "", interval = 3, mark = ",")

Ian: Overall Loss Ratios by COB

ian_aggrLR_COB <- ian_events %>%
  group_by(COB) %>%
  summarize(n    = n(),
            aggrTLR = sum(VRUInc)/sum(TotalPremium),
            #avgTLR = mean(TotPrem_LR),
            aggrCLR = sum(VRUInc)/sum(CATPrem),
            #avgCLR = mean(CATPrem_LR),
            net1 = sum(Tot_Gain_Loss),
            net2 = sum(CAT_Gain_Loss))

datatable(ian_aggrLR_COB,
          rownames = FALSE, 
          filter = 'top',
          extensions = 'Buttons',
          options = list(
            dom = 'Bfrtip',
            buttons = c('copy', 'csv', 'excel', 'pdf', 'print')),
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            'Ian: ', htmltools::em('Overall Loss Ratios by COB')),
          colnames = c('Class of Business' = 1, '# of Occurences' = 2, 'Overall Total Loss Ratio' = 3, 'Overall CAT Loss Ratio' = 4, 'Total Gain(+) | Loss(-)' = 5, 'CAT Gain(+) | Loss(-)' = 6)) %>%
  formatPercentage(c('Overall Total Loss Ratio', 'Overall CAT Loss Ratio'), 3) %>%
  formatCurrency(c('Total Gain(+) | Loss(-)', 'CAT Gain(+) | Loss(-)'), currency = "", interval = 3, mark = ",")

Loss Ratio Comparisons

Loss Ratio Comparison by Event

tot_aggLR_byevent <- LR_df %>%
  group_by(Event) %>%
  summarize(n = n(),
            aggrTLR = sum(VRUInc)/sum(TotalPremium),
            aggrCLR = sum(VRUInc)/sum(CATPrem),
            net1 = sum(Tot_Gain_Loss),
            net2 = sum(CAT_Gain_Loss))

datatable(tot_aggLR_byevent,
          rownames = FALSE, 
          filter = 'top',
          extensions = 'Buttons',
          options = list(
            dom = 'Bfrtip',
            buttons = c('copy', 'csv', 'excel', 'pdf', 'print')),
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            'All Events: ', htmltools::em('Overall Loss Ratios')),
          colnames = c('Event' = 1, '# of Occurences' = 2, 'Overall Total Loss Ratio' = 3, 'Overall CAT Loss Ratio' = 4, 'Total Gain(+) | Loss(-)' = 5, 'CAT Gain(+) | Loss(-)' = 6)) %>%
  formatPercentage(c('Overall Total Loss Ratio', 'Overall CAT Loss Ratio'), 3) %>%
  formatCurrency(c('Total Gain(+) | Loss(-)', 'CAT Gain(+) | Loss(-)'), currency = "", interval = 3, mark = ",")

Loss Ratio Comparison by Event and COB

tot_aggLR_byeventCOB <- LR_df %>%
  group_by(Event, COB) %>%
  summarize(n = n(),
            aggrTLR = sum(VRUInc)/sum(TotalPremium),
            aggrCLR = sum(VRUInc)/sum(CATPrem),
            net1 = sum(Tot_Gain_Loss),
            net2 = sum(CAT_Gain_Loss))

datatable(tot_aggLR_byeventCOB,
          rownames = FALSE, 
          filter = 'top',
          extensions = 'Buttons',
          options = list(
            dom = 'Bfrtip',
            buttons = c('copy', 'csv', 'excel', 'pdf', 'print')),
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            'All Events: ', htmltools::em('Overall Loss Ratios')),
          colnames = c('Event' = 1, 'COB' = 2, '# of Occurences' = 3, 'Overall Total Loss Ratio' = 4, 'Overall CAT Loss Ratio' = 5, 'Total Gain(+) | Loss(-)' = 6, 'CAT Gain(+) | Loss(-)' = 7)) %>%
  formatPercentage(c('Overall Total Loss Ratio', 'Overall CAT Loss Ratio'), 3) %>%
  formatCurrency(c('Total Gain(+) | Loss(-)', 'CAT Gain(+) | Loss(-)'), currency = "", interval = 3, mark = ",")

Total Loss Ratio: Visualization of Losses for High Loss COBs (>150%)

Total Loss Ratios over 150% (by COB)

test_facetwrap <- LR_df %>%
  filter(TotPrem_LR > 150) %>%
  ggplot(aes(TotPrem_LR, fill = Event)) +
  geom_histogram() +
  #labs(title = "Possible Adverse Class Selection", subtitle = "COB performance over time", x = "", y = "VRU Loss") + 
  facet_wrap(~ COB) +
  theme_bw()

ggplotly(test_facetwrap)

This Total Loss Ratio facetwrap is to (1) identify the COBs with high loss ratios and (2) identify the events with loss ratios over 150%. Capturing this could lead us to further analysis of certain COBs. The resulting COBs and their respective performance will be visualized via premium collected vs loss incurred. It’s important to note that the resulting graphs will showcase performance for that COB, by event, as a whole.

COB: Apartments

ggplotly(LR_df %>%
  filter(COB == 'Apartments') %>%
  ggplot(aes(x = Event)) +
  geom_col(aes(y = TotalPremium, fill = Event)) +
  geom_col(aes(y = -VRUInc)) +
  labs(x = 'Event', y = 'Incurred Losses <--> Total Collected Premium', title = 'High Loss COB: Apartments') +
  theme(axis.text.x = element_text(angle = 45, vjust = 0.5))
)

COB: Church

ggplotly(LR_df %>%
  filter(COB == 'Church') %>%
  ggplot(aes(x = Event)) +
  geom_col(aes(y = TotalPremium, fill = Event)) +
  geom_col(aes(y = -VRUInc)) +
  labs(x = 'Event', y = 'Incurred Losses <--> Total Collected Premium', title = 'High Loss COB: Church') +
  theme(axis.text.x = element_text(angle = 45, vjust = 0.5))
)

COB: Condominium

ggplotly(LR_df %>%
  filter(COB == 'Condominium') %>%
  ggplot(aes(x = Event)) +
  geom_col(aes(y = TotalPremium, fill = Event)) +
  geom_col(aes(y = -VRUInc)) +
  labs(x = 'Event', y = 'Incurred Losses <--> Total Collected Premium', title = 'High Loss COB: Condominium') +
  theme(axis.text.x = element_text(angle = 45, vjust = 0.5))
)

COB: Education

ggplotly(LR_df %>%
  filter(COB == 'Education') %>%
  ggplot(aes(x = Event)) +
  geom_col(aes(y = TotalPremium, fill = Event)) +
  geom_col(aes(y = -VRUInc)) +
  labs(x = 'Event', y = 'Incurred Losses <--> Total Collected Premium', title = 'High Loss COB: Education') +
  theme(axis.text.x = element_text(angle = 45, vjust = 0.5))
)

COB: Entertainment

ggplotly(LR_df %>%
  filter(COB == 'Entertainment') %>%
  ggplot(aes(x = Event)) +
  geom_col(aes(y = TotalPremium, fill = Event)) +
  geom_col(aes(y = -VRUInc)) +
  labs(x = 'Event', y = 'Incurred Losses <--> Total Collected Premium', title = 'High Loss COB: Entertainment') +
  theme(axis.text.x = element_text(angle = 45, vjust = 0.5))
)

COB: Hotels Motels

ggplotly(LR_df %>%
  filter(COB == 'HotelsMotels') %>%
  ggplot(aes(x = Event)) +
  geom_col(aes(y = TotalPremium, fill = Event)) +
  geom_col(aes(y = -VRUInc)) +
  labs(x = 'Event', y = 'Incurred Losses <--> Total Collected Premium', title = 'High Loss COB: HotelsMotels') +
  theme(axis.text.x = element_text(angle = 45, vjust = 0.5))
)

COB: Office

ggplotly(LR_df %>%
  filter(COB == 'Office') %>%
  ggplot(aes(x = Event)) +
  geom_col(aes(y = TotalPremium, fill = Event)) +
  geom_col(aes(y = -VRUInc)) +
  labs(x = 'Event', y = 'Incurred Losses <--> Total Collected Premium', title = 'High Loss COB: Office') +
  theme(axis.text.x = element_text(angle = 45, vjust = 0.5))
)

COB: Restaurant

ggplotly(LR_df %>%
  filter(COB == 'Restaurant') %>%
  ggplot(aes(x = Event)) +
  geom_col(aes(y = TotalPremium, fill = Event)) +
  geom_col(aes(y = -VRUInc)) +
  labs(x = 'Event', y = 'Incurred Losses <--> Total Collected Premium', title = 'High Loss COB: Restaurant') +
  theme(axis.text.x = element_text(angle = 45, vjust = 0.5))
)

COB: Retail

ggplotly(LR_df %>%
  filter(COB == 'Retail') %>%
  ggplot(aes(x = Event)) +
  geom_col(aes(y = TotalPremium, fill = Event)) +
  geom_col(aes(y = -VRUInc)) +
  labs(x = 'Event', y = 'Incurred Losses <--> Total Collected Premium', title = 'High Loss COB: Retail') +
  theme(axis.text.x = element_text(angle = 45, vjust = 0.5))
)

COB: Retail With Refrigeration Equipment

ggplotly(LR_df %>%
  filter(COB == 'RetailwRefrigEquip') %>%
  ggplot(aes(x = Event)) +
  geom_col(aes(y = TotalPremium, fill = Event)) +
  geom_col(aes(y = -VRUInc)) +
  labs(x = 'Event', y = 'Incurred Losses <--> Total Collected Premium', title = 'High Loss COB: RetailwRefrigEquip') +
  theme(axis.text.x = element_text(angle = 45, vjust = 0.5))
)

COB: Service

ggplotly(LR_df %>%
  filter(COB == 'Service') %>%
  ggplot(aes(x = Event)) +
  geom_col(aes(y = TotalPremium, fill = Event)) +
  geom_col(aes(y = -VRUInc)) +
  labs(x = 'Event', y = 'Incurred Losses <--> Total Collected Premium', title = 'High Loss COB: Service') +
  theme(axis.text.x = element_text(angle = 45, vjust = 0.5))
)

CAT Loss Ratio: Visualization of Losses for High Loss COBs (>150%)

CAT Loss Ratios over 150% (by COB)

test_facetwrap <- LR_df %>%
  filter(CATPrem_LR > 150) %>%
  ggplot(aes(CATPrem_LR, fill = Event)) +
  geom_histogram() +
  #labs(title = "Possible Adverse Class Selection", subtitle = "COB performance over time", x = "", y = "VRU Loss") + 
  facet_wrap(~ COB) +
  theme_bw()

ggplotly(test_facetwrap)

This CAT Loss Ratio facetwrap is to (1) identify the COBs with high loss ratios and (2) identify the events with loss ratios over 150%. Capturing this could lead us to further analysis of certain COBs. The resulting COBs and their respective performance will be visualized via premium collected vs loss incurred. It’s important to note that the resulting graphs will showcase performance for that COB, by event, as a whole.

COB: Apartments

ggplotly(LR_df %>%
  filter(COB == 'Apartments') %>%
  ggplot(aes(x = Event)) +
  geom_col(aes(y = CATPrem, fill = Event)) +
  geom_col(aes(y = -VRUInc)) +
  labs(x = 'Event', y = 'Incurred Losses <--> Collected CAT Premium', title = 'High Loss COB: Apartments') +
  theme(axis.text.x = element_text(angle = 45, vjust = 0.5))
)

COB: Church

ggplotly(LR_df %>%
  filter(COB == 'Church') %>%
  ggplot(aes(x = Event)) +
  geom_col(aes(y = CATPrem, fill = Event)) +
  geom_col(aes(y = -VRUInc)) +
  labs(x = 'Event', y = 'Incurred Losses <--> Collected CAT Premium', title = 'High Loss COB: Church') +
  theme(axis.text.x = element_text(angle = 45, vjust = 0.5))
)

COB: Condominium

ggplotly(LR_df %>%
  filter(COB == 'Condominium') %>%
  ggplot(aes(x = Event)) +
  geom_col(aes(y = CATPrem, fill = Event)) +
  geom_col(aes(y = -VRUInc)) +
  labs(x = 'Event', y = 'Incurred Losses <--> Collected CAT Premium', title = 'High Loss COB: Condominium') +
  theme(axis.text.x = element_text(angle = 45, vjust = 0.5))
)

COB: Education

ggplotly(LR_df %>%
  filter(COB == 'Education') %>%
  ggplot(aes(x = Event)) +
  geom_col(aes(y = CATPrem, fill = Event)) +
  geom_col(aes(y = -VRUInc)) +
  labs(x = 'Event', y = 'Incurred Losses <--> Collected CAT Premium', title = 'High Loss COB: Education') +
  theme(axis.text.x = element_text(angle = 45, vjust = 0.5))
)

COB: Entertainment

ggplotly(LR_df %>%
  filter(COB == 'Entertainment') %>%
  ggplot(aes(x = Event)) +
  geom_col(aes(y = CATPrem, fill = Event)) +
  geom_col(aes(y = -VRUInc)) +
  labs(x = 'Event', y = 'Incurred Losses <--> Collected CAT Premium', title = 'High Loss COB: Entertainment') +
  theme(axis.text.x = element_text(angle = 45, vjust = 0.5))
)

COB: Gas Stations

ggplotly(LR_df %>%
  filter(COB == 'GasStations') %>%
  ggplot(aes(x = Event)) +
  geom_col(aes(y = CATPrem, fill = Event)) +
  geom_col(aes(y = -VRUInc)) +
  labs(x = 'Event', y = 'Incurred Losses <--> Collected CAT Premium', title = 'High Loss COB: Gas Stations') +
  theme(axis.text.x = element_text(angle = 45, vjust = 0.5))
)

COB: Healthcare

ggplotly(LR_df %>%
  filter(COB == 'Healthcare') %>%
  ggplot(aes(x = Event)) +
  geom_col(aes(y = CATPrem, fill = Event)) +
  geom_col(aes(y = -VRUInc)) +
  labs(x = 'Event', y = 'Incurred Losses <--> Collected CAT Premium', title = 'High Loss COB: Healthcare') +
  theme(axis.text.x = element_text(angle = 45, vjust = 0.5))
)

COB: Hotels Motels

ggplotly(LR_df %>%
  filter(COB == 'HotelsMotels') %>%
  ggplot(aes(x = Event)) +
  geom_col(aes(y = CATPrem, fill = Event)) +
  geom_col(aes(y = -VRUInc)) +
  labs(x = 'Event', y = 'Incurred Losses <--> Collected CAT Premium', title = 'High Loss COB: HotelsMotels') +
  theme(axis.text.x = element_text(angle = 45, vjust = 0.5))
)

COB: Office

ggplotly(LR_df %>%
  filter(COB == 'Office') %>%
  ggplot(aes(x = Event)) +
  geom_col(aes(y = CATPrem, fill = Event)) +
  geom_col(aes(y = -VRUInc)) +
  labs(x = 'Event', y = 'Incurred Losses <--> Collected CAT Premium', title = 'High Loss COB: Office') +
  theme(axis.text.x = element_text(angle = 45, vjust = 0.5))
)

COB: Restaurant

ggplotly(LR_df %>%
  filter(COB == 'Restaurant') %>%
  ggplot(aes(x = Event)) +
  geom_col(aes(y = CATPrem, fill = Event)) +
  geom_col(aes(y = -VRUInc)) +
  labs(x = 'Event', y = 'Incurred Losses <--> Collected CAT Premium', title = 'High Loss COB: Restaurant') +
  theme(axis.text.x = element_text(angle = 45, vjust = 0.5))
)

COB: Retail

ggplotly(LR_df %>%
  filter(COB == 'Retail') %>%
  ggplot(aes(x = Event)) +
  geom_col(aes(y = CATPrem, fill = Event)) +
  geom_col(aes(y = -VRUInc)) +
  labs(x = 'Event', y = 'Incurred Losses <--> Collected CAT Premium', title = 'High Loss COB: Retail') +
  theme(axis.text.x = element_text(angle = 45, vjust = 0.5))
)

COB: Retail With Refrigeration Equipment

ggplotly(LR_df %>%
  filter(COB == 'RetailwRefrigEquip') %>%
  ggplot(aes(x = Event)) +
  geom_col(aes(y = CATPrem, fill = Event)) +
  geom_col(aes(y = -VRUInc)) +
  labs(x = 'Event', y = 'Incurred Losses <--> Collected CAT Premium', title = 'High Loss COB: RetailwRefrigEquip') +
  theme(axis.text.x = element_text(angle = 45, vjust = 0.5))
)

COB: Service

ggplotly(LR_df %>%
  filter(COB == 'Service') %>%
  ggplot(aes(x = Event)) +
  geom_col(aes(y = CATPrem, fill = Event)) +
  geom_col(aes(y = -VRUInc)) +
  labs(x = 'Event', y = 'Incurred Losses <--> Collected CAT Premium', title = 'High Loss COB: Service') +
  theme(axis.text.x = element_text(angle = 45, vjust = 0.5))
)

COB: Wholesalers

ggplotly(LR_df %>%
  filter(COB == 'Wholesalers') %>%
  ggplot(aes(x = Event)) +
  geom_col(aes(y = CATPrem, fill = Event)) +
  geom_col(aes(y = -VRUInc)) +
  labs(x = 'Event', y = 'Incurred Losses <--> Collected CAT Premium', title = 'High Loss COB: Wholesalers') +
  theme(axis.text.x = element_text(angle = 45, vjust = 0.5))
)