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/transmute : make premiums and incurred losses in thousands (i.e., 1000000/1000=1000, 1000/1000 = 1)

update data w/ mutate: add columns for Loss Ratios

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

LRatio_COB <- LR_df %>%
  group_by(COB) %>%
  summarize(n    = n(),
            x_bar1 = mean(TotPrem_LR),
            x_bar2 = mean(CATPrem_LR),
            sum1 = sum(Tot_Gain_Loss),
            sum2 = sum(CAT_Gain_Loss)) 

datatable(LRatio_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 1: ', htmltools::em('Average Loss Ratios by COB')),
          colnames = c('Class of Business' = 1, '# of Occurences' = 2, 'Average Total Loss Ratio' = 3, 'Average CAT Loss Ratio' = 4, 'Total Gain(+) | Loss(-)' = 5, 'CAT Gain(+) | Loss(-)' = 6)) %>%
  formatPercentage(c('Average Total Loss Ratio', 'Average CAT Loss Ratio'), 3) %>%
  formatCurrency(c('Total Gain(+) | Loss(-)', 'CAT Gain(+) | Loss(-)'), currency = "", interval = 3, mark = ",")

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_events <- subset(LR_df, Event == "Hanna")

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


hanna_avg_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_avg_LR,
          rownames = FALSE,
          colnames = c('# of Occurences' = 1, 'Average Total Loss Ratio' = 2, 'Average CAT Loss Ratio' = 3, 'Total Gain(+) | Loss(-)' = 4, 'CAT Gain(+) | Loss(-)' = 5)) %>%
  formatPercentage(c('Average Total Loss Ratio', 'Average CAT Loss Ratio'), 3) %>%
  formatCurrency(c('Total Gain(+) | Loss(-)', 'CAT Gain(+) | Loss(-)'), currency = "", interval = 3, mark = ",")

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: Average Loss Ratios by COB

hanna_avgLR_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_avgLR_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('Average Loss Ratios by COB')),
          colnames = c('Class of Business' = 1, '# of Occurences' = 2, 'Average Total Loss Ratio' = 3, 'Average CAT Loss Ratio' = 4, 'Total Gain(+) | Loss(-)' = 5, 'CAT Gain(+) | Loss(-)' = 6)) %>%
  formatPercentage(c('Average Total Loss Ratio', 'Average CAT Loss Ratio'), 3) %>%
  formatCurrency(c('Total Gain(+) | Loss(-)', 'CAT Gain(+) | Loss(-)'), currency = "", interval = 3, mark = ",")

Hanna: Aggregate 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('Aggregate Loss Ratios by COB')),
          colnames = c('Class of Business' = 1, '# of Occurences' = 2, 'Aggregate Total Loss Ratio' = 3, 'Aggregate CAT Loss Ratio' = 4, 'Total Gain(+) | Loss(-)' = 5, 'CAT Gain(+) | Loss(-)' = 6)) %>%
  formatPercentage(c('Aggregate Total Loss Ratio', 'Aggregate CAT Loss Ratio'), 3) %>%
  formatCurrency(c('Total Gain(+) | Loss(-)', 'CAT Gain(+) | Loss(-)'), currency = "", interval = 3, mark = ",")

Laura Loss Ratios

Laura: Average Loss Ratios

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


laura_avg_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_avg_LR,
          rownames = FALSE,
          colnames = c('# of Occurences' = 1, 'Average Total Loss Ratio' = 2, 'Average CAT Loss Ratio' = 3, 'Total Gain(+) | Loss(-)' = 4, 'CAT Gain(+) | Loss(-)' = 5)) %>%
  formatPercentage(c('Average Total Loss Ratio', 'Average CAT Loss Ratio'), 3) %>%
  formatCurrency(c('Total Gain(+) | Loss(-)', 'CAT Gain(+) | Loss(-)'), currency = "", interval = 3, mark = ",")

Laura: Aggregate Loss Ratios

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, 'Aggregate Total Loss Ratio' = 2, 'Aggregate CAT Loss Ratio' = 3, 'Total Gain(+) | Loss(-)' = 4, 'CAT Gain(+) | Loss(-)' = 5)) %>%
  formatPercentage(c('Aggregate Total Loss Ratio', 'Aggregate CAT Loss Ratio'), 3) %>%
  formatCurrency(c('Total Gain(+) | Loss(-)', 'CAT Gain(+) | Loss(-)'), currency = "", interval = 3, mark = ",")

Laura: Average Loss Ratios by COB

laura_avgLR_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_avgLR_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('Average Loss Ratios by COB')),
          colnames = c('Class of Business' = 1, '# of Occurences' = 2, 'Average Total Loss Ratio' = 3, 'Average CAT Loss Ratio' = 4, 'Total Gain(+) | Loss(-)' = 5, 'CAT Gain(+) | Loss(-)' = 6)) %>%
  formatPercentage(c('Average Total Loss Ratio', 'Average CAT Loss Ratio'), 3) %>%
  formatCurrency(c('Total Gain(+) | Loss(-)', 'CAT Gain(+) | Loss(-)'), currency = "", interval = 3, mark = ",")

Laura: Aggregate 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('Aggregate Loss Ratios by COB')),
          colnames = c('Class of Business' = 1, '# of Occurences' = 2, 'Aggregate Total Loss Ratio' = 3, 'Aggregate CAT Loss Ratio' = 4, 'Total Gain(+) | Loss(-)' = 5, 'CAT Gain(+) | Loss(-)' = 6)) %>%
  formatPercentage(c('Aggregate Total Loss Ratio', 'Aggregate CAT Loss Ratio'), 3) %>%
  formatCurrency(c('Total Gain(+) | Loss(-)', 'CAT Gain(+) | Loss(-)'), currency = "", interval = 3, mark = ",")

Sally Loss Ratios

Sally: Average Loss Ratios

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


sally_avg_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_avg_LR,
          rownames = FALSE,
          colnames = c('# of Occurences' = 1, 'Average Total Loss Ratio' = 2, 'Average CAT Loss Ratio' = 3, 'Total Gain(+) | Loss(-)' = 4, 'CAT Gain(+) | Loss(-)' = 5)) %>%
  formatPercentage(c('Average Total Loss Ratio', 'Average CAT Loss Ratio'), 3) %>%
  formatCurrency(c('Total Gain(+) | Loss(-)', 'CAT Gain(+) | Loss(-)'), currency = "", interval = 3, mark = ",")

Sally: Aggregate Loss Ratios

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, 'Aggregate Total Loss Ratio' = 2, 'Aggregate CAT Loss Ratio' = 3, 'Total Gain(+) | Loss(-)' = 4, 'CAT Gain(+) | Loss(-)' = 5)) %>%
  formatPercentage(c('Aggregate Total Loss Ratio', 'Aggregate CAT Loss Ratio'), 3) %>%
  formatCurrency(c('Total Gain(+) | Loss(-)', 'CAT Gain(+) | Loss(-)'), currency = "", interval = 3, mark = ",")

Sally: Average Loss Ratios by COB

sally_avgLR_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_avgLR_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('Average Loss Ratios by COB')),
          colnames = c('Class of Business' = 1, '# of Occurences' = 2, 'Average Total Loss Ratio' = 3, 'Average CAT Loss Ratio' = 4, 'Total Gain(+) | Loss(-)' = 5, 'CAT Gain(+) | Loss(-)' = 6)) %>%
  formatPercentage(c('Average Total Loss Ratio', 'Average CAT Loss Ratio'), 3) %>%
  formatCurrency(c('Total Gain(+) | Loss(-)', 'CAT Gain(+) | Loss(-)'), currency = "", interval = 3, mark = ",")

Sally: Aggregate 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('Aggregate Loss Ratios by COB')),
          colnames = c('Class of Business' = 1, '# of Occurences' = 2, 'Aggregate Total Loss Ratio' = 3, 'Aggregate CAT Loss Ratio' = 4, 'Total Gain(+) | Loss(-)' = 5, 'CAT Gain(+) | Loss(-)' = 6)) %>%
  formatPercentage(c('Aggregate Total Loss Ratio', 'Aggregate CAT Loss Ratio'), 3) %>%
  formatCurrency(c('Total Gain(+) | Loss(-)', 'CAT Gain(+) | Loss(-)'), currency = "", interval = 3, mark = ",")

Delta Loss Ratios

Delta: Average Loss Ratios

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


delta_avg_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_avg_LR,
          rownames = FALSE,
          colnames = c('# of Occurences' = 1, 'Average Total Loss Ratio' = 2, 'Average CAT Loss Ratio' = 3, 'Total Gain(+) | Loss(-)' = 4, 'CAT Gain(+) | Loss(-)' = 5)) %>%
  formatPercentage(c('Average Total Loss Ratio', 'Average CAT Loss Ratio'), 3) %>%
  formatCurrency(c('Total Gain(+) | Loss(-)', 'CAT Gain(+) | Loss(-)'), currency = "", interval = 3, mark = ",")

Delta: Aggregate Loss Ratios

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, 'Aggregate Total Loss Ratio' = 2, 'Aggregate CAT Loss Ratio' = 3, 'Total Gain(+) | Loss(-)' = 4, 'CAT Gain(+) | Loss(-)' = 5)) %>%
  formatPercentage(c('Aggregate Total Loss Ratio', 'Aggregate CAT Loss Ratio'), 3) %>%
  formatCurrency(c('Total Gain(+) | Loss(-)', 'CAT Gain(+) | Loss(-)'), currency = "", interval = 3, mark = ",")

Delta: Average Loss Ratios by COB

delta_avgLR_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_avgLR_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('Average Loss Ratios by COB')),
          colnames = c('Class of Business' = 1, '# of Occurences' = 2, 'Average Total Loss Ratio' = 3, 'Average CAT Loss Ratio' = 4, 'Total Gain(+) | Loss(-)' = 5, 'CAT Gain(+) | Loss(-)' = 6)) %>%
  formatPercentage(c('Average Total Loss Ratio', 'Average CAT Loss Ratio'), 3) %>%
  formatCurrency(c('Total Gain(+) | Loss(-)', 'CAT Gain(+) | Loss(-)'), currency = "", interval = 3, mark = ",")

Delta: Aggregate 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('Aggregate Loss Ratios by COB')),
          colnames = c('Class of Business' = 1, '# of Occurences' = 2, 'Aggregate Total Loss Ratio' = 3, 'Aggregate CAT Loss Ratio' = 4, 'Total Gain(+) | Loss(-)' = 5, 'CAT Gain(+) | Loss(-)' = 6)) %>%
  formatPercentage(c('Aggregate Total Loss Ratio', 'Aggregate CAT Loss Ratio'), 3) %>%
  formatCurrency(c('Total Gain(+) | Loss(-)', 'CAT Gain(+) | Loss(-)'), currency = "", interval = 3, mark = ",")

Zeta Loss Ratios

Zeta: Average Loss Ratios

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


zeta_avg_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_avg_LR,
          rownames = FALSE,
          colnames = c('# of Occurences' = 1, 'Average Total Loss Ratio' = 2, 'Average CAT Loss Ratio' = 3, 'Total Gain(+) | Loss(-)' = 4, 'CAT Gain(+) | Loss(-)' = 5)) %>%
  formatPercentage(c('Average Total Loss Ratio', 'Average CAT Loss Ratio'), 3) %>%
  formatCurrency(c('Total Gain(+) | Loss(-)', 'CAT Gain(+) | Loss(-)'), currency = "", interval = 3, mark = ",")

Zeta: Aggregate Loss Ratios

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, 'Aggregate Total Loss Ratio' = 2, 'Aggregate CAT Loss Ratio' = 3, 'Total Gain(+) | Loss(-)' = 4, 'CAT Gain(+) | Loss(-)' = 5)) %>%
  formatPercentage(c('Aggregate Total Loss Ratio', 'Aggregate CAT Loss Ratio'), 3) %>%
  formatCurrency(c('Total Gain(+) | Loss(-)', 'CAT Gain(+) | Loss(-)'), currency = "", interval = 3, mark = ",")

Zeta: Average Loss Ratios by COB

zeta_avgLR_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_avgLR_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('Average Loss Ratios by COB')),
          colnames = c('Class of Business' = 1, '# of Occurences' = 2, 'Average Total Loss Ratio' = 3, 'Average CAT Loss Ratio' = 4, 'Total Gain(+) | Loss(-)' = 5, 'CAT Gain(+) | Loss(-)' = 6)) %>%
  formatPercentage(c('Average Total Loss Ratio', 'Average CAT Loss Ratio'), 3) %>%
  formatCurrency(c('Total Gain(+) | Loss(-)', 'CAT Gain(+) | Loss(-)'), currency = "", interval = 3, mark = ",")

Zeta: Aggregate 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('Aggregate Loss Ratios by COB')),
          colnames = c('Class of Business' = 1, '# of Occurences' = 2, 'Aggregate Total Loss Ratio' = 3, 'Aggregate CAT Loss Ratio' = 4, 'Total Gain(+) | Loss(-)' = 5, 'CAT Gain(+) | Loss(-)' = 6)) %>%
  formatPercentage(c('Aggregate Total Loss Ratio', 'Aggregate CAT Loss Ratio'), 3) %>%
  formatCurrency(c('Total Gain(+) | Loss(-)', 'CAT Gain(+) | Loss(-)'), currency = "", interval = 3, mark = ",")

Ida Loss Ratios

Ida: Average Loss Ratios

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


ida_avg_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_avg_LR,
          rownames = FALSE,
          colnames = c('# of Occurences' = 1, 'Average Total Loss Ratio' = 2, 'Average CAT Loss Ratio' = 3, 'Total Gain(+) | Loss(-)' = 4, 'CAT Gain(+) | Loss(-)' = 5)) %>%
  formatPercentage(c('Average Total Loss Ratio', 'Average CAT Loss Ratio'), 3) %>%
  formatCurrency(c('Total Gain(+) | Loss(-)', 'CAT Gain(+) | Loss(-)'), currency = "", interval = 3, mark = ",")

Ida: Aggregate Loss Ratios

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, 'Aggregate Total Loss Ratio' = 2, 'Aggregate CAT Loss Ratio' = 3, 'Total Gain(+) | Loss(-)' = 4, 'CAT Gain(+) | Loss(-)' = 5)) %>%
  formatPercentage(c('Aggregate Total Loss Ratio', 'Aggregate CAT Loss Ratio'), 3) %>%
  formatCurrency(c('Total Gain(+) | Loss(-)', 'CAT Gain(+) | Loss(-)'), currency = "", interval = 3, mark = ",")

Ida: Average Loss Ratios by COB

ida_avgLR_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_avgLR_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('Average Loss Ratios by COB')),
          colnames = c('Class of Business' = 1, '# of Occurences' = 2, 'Average Total Loss Ratio' = 3, 'Average CAT Loss Ratio' = 4, 'Total Gain(+) | Loss(-)' = 5, 'CAT Gain(+) | Loss(-)' = 6)) %>%
  formatPercentage(c('Average Total Loss Ratio', 'Average CAT Loss Ratio'), 3) %>%
  formatCurrency(c('Total Gain(+) | Loss(-)', 'CAT Gain(+) | Loss(-)'), currency = "", interval = 3, mark = ",")

Ida: Aggregate 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('Aggregate Loss Ratios by COB')),
          colnames = c('Class of Business' = 1, '# of Occurences' = 2, 'Aggregate Total Loss Ratio' = 3, 'Aggregate CAT Loss Ratio' = 4, 'Total Gain(+) | Loss(-)' = 5, 'CAT Gain(+) | Loss(-)' = 6)) %>%
  formatPercentage(c('Aggregate Total Loss Ratio', 'Aggregate CAT Loss Ratio'), 3) %>%
  formatCurrency(c('Total Gain(+) | Loss(-)', 'CAT Gain(+) | Loss(-)'), currency = "", interval = 3, mark = ",")

Ian Loss Ratios

Ian: Average Loss Ratios

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


ian_avg_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_avg_LR,
          rownames = FALSE,
          colnames = c('# of Occurences' = 1, 'Average Total Loss Ratio' = 2, 'Average CAT Loss Ratio' = 3, 'Total Gain(+) | Loss(-)' = 4, 'CAT Gain(+) | Loss(-)' = 5)) %>%
  formatPercentage(c('Average Total Loss Ratio', 'Average CAT Loss Ratio'), 3) %>%
  formatCurrency(c('Total Gain(+) | Loss(-)', 'CAT Gain(+) | Loss(-)'), currency = "", interval = 3, mark = ",")

Ian: Aggregate Loss Ratios

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, 'Aggregate Total Loss Ratio' = 2, 'Aggregate CAT Loss Ratio' = 3, 'Total Gain(+) | Loss(-)' = 4, 'CAT Gain(+) | Loss(-)' = 5)) %>%
  formatPercentage(c('Aggregate Total Loss Ratio', 'Aggregate CAT Loss Ratio'), 3) %>%
  formatCurrency(c('Total Gain(+) | Loss(-)', 'CAT Gain(+) | Loss(-)'), currency = "", interval = 3, mark = ",")

Ian: Average Loss Ratios by COB

ian_avgLR_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_avgLR_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('Average Loss Ratios by COB')),
          colnames = c('Class of Business' = 1, '# of Occurences' = 2, 'Average Total Loss Ratio' = 3, 'Average CAT Loss Ratio' = 4, 'Total Gain(+) | Loss(-)' = 5, 'CAT Gain(+) | Loss(-)' = 6)) %>%
  formatPercentage(c('Average Total Loss Ratio', 'Average CAT Loss Ratio'), 3) %>%
  formatCurrency(c('Total Gain(+) | Loss(-)', 'CAT Gain(+) | Loss(-)'), currency = "", interval = 3, mark = ",")

Ian: Aggregate 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('Aggregate Loss Ratios by COB')),
          colnames = c('Class of Business' = 1, '# of Occurences' = 2, 'Aggregate Total Loss Ratio' = 3, 'Aggregate CAT Loss Ratio' = 4, 'Total Gain(+) | Loss(-)' = 5, 'CAT Gain(+) | Loss(-)' = 6)) %>%
  formatPercentage(c('Aggregate Total Loss Ratio', 'Aggregate 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('Aggregate Loss Ratios')),
          colnames = c('Event' = 1, '# of Occurences' = 2, 'Aggregate Total Loss Ratio' = 3, 'Aggregate CAT Loss Ratio' = 4, 'Total Gain(+) | Loss(-)' = 5, 'CAT Gain(+) | Loss(-)' = 6)) %>%
  formatPercentage(c('Aggregate Total Loss Ratio', 'Aggregate 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('Aggregate Loss Ratios')),
          colnames = c('Event' = 1, 'COB' = 2, '# of Occurences' = 3, 'Aggregate Total Loss Ratio' = 4, 'Aggregate CAT Loss Ratio' = 5, 'Total Gain(+) | Loss(-)' = 6, 'CAT Gain(+) | Loss(-)' = 7)) %>%
  formatPercentage(c('Aggregate Total Loss Ratio', 'Aggregate CAT Loss Ratio'), 3) %>%
  formatCurrency(c('Total Gain(+) | Loss(-)', 'CAT Gain(+) | Loss(-)'), currency = "", interval = 3, mark = ",")

Total Loss Ratio Density Plot (by Event)

eventdensity <- LR_df %>%
 # group_by(COB) %>%
  filter(TotPrem_LR > 1) %>%
  ggplot(aes(x = TotPrem_LR, group = Event, fill = Event)) +
  geom_density(adjust = 2.5, alpha = .3)
eventdensity
Density plot for funsies

Density plot for funsies 

#ggplotly(eventdensity)

Total Loss Ratios by COB: Highlighting COBs with over 150% Loss

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()
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.

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. 

graph1 <- LR_df %>%
  #group_by(Event, COB) %>%
  filter(COB == 'Apartments', TotPrem_LR > 0) %>%
  ggplot(aes(x = TotPrem_LR, fill = Event)) +
  geom_histogram()

graph1

Visualization of Losses for High Loss COBs

COB: Apartments

LR_df %>%
  filter(COB == 'Apartments') %>%
  mutate(Event = fct_relevel(Event, 'Hanna', 'Laura', 'Sally', 'Delta', 'Zeta', 'Ida', 'Ian')) %>%
  ggplot(aes(x = Event)) +
  geom_col(aes(y = TotalPremium, fill = Event)) +
  geom_col(aes(y = -VRUInc)) +
  labs(x = 'Event', y = 'Incurred Losses <--> Collected Premium', title = 'High Loss COB: Apartments') +
  theme(axis.text.x = element_text(angle = 45, vjust = 0.5))

COB: Church

LR_df %>%
  filter(COB == 'Church') %>%
  mutate(Event = fct_relevel(Event, 'Hanna', 'Laura', 'Sally', 'Delta', 'Zeta', 'Ida', 'Ian')) %>%
  ggplot(aes(x = Event)) +
  geom_col(aes(y = TotalPremium, fill = Event)) +
  geom_col(aes(y = -VRUInc)) +
  labs(x = 'Event', y = 'Incurred Losses <--> Collected Premium', title = 'High Loss COB: Church') +
  theme(axis.text.x = element_text(angle = 45, vjust = 0.5))

COB: Condominium

LR_df %>%
  filter(COB == 'Condominium') %>%
  mutate(Event = fct_relevel(Event, 'Hanna', 'Laura', 'Sally', 'Delta', 'Zeta', 'Ida', 'Ian')) %>%
  ggplot(aes(x = Event)) +
  geom_col(aes(y = TotalPremium, fill = Event)) +
  geom_col(aes(y = -VRUInc)) +
  labs(x = 'Event', y = 'Incurred Losses <--> Collected Premium', title = 'High Loss COB: Condominium') +
  theme(axis.text.x = element_text(angle = 45, vjust = 0.5))

COB: Education

LR_df %>%
  filter(COB == 'Education') %>%
  mutate(Event = fct_relevel(Event, 'Hanna', 'Laura', 'Sally', 'Delta', 'Zeta', 'Ida', 'Ian')) %>%
  ggplot(aes(x = Event)) +
  geom_col(aes(y = TotalPremium, fill = Event)) +
  geom_col(aes(y = -VRUInc)) +
  labs(x = 'Event', y = 'Incurred Losses <--> Collected Premium', title = 'High Loss COB: Education') +
  theme(axis.text.x = element_text(angle = 45, vjust = 0.5))

COB: Entertainment

LR_df %>%
  filter(COB == 'Entertainment') %>%
  mutate(Event = fct_relevel(Event, 'Hanna', 'Laura', 'Sally', 'Delta', 'Zeta', 'Ida', 'Ian')) %>%
  ggplot(aes(x = Event)) +
  geom_col(aes(y = TotalPremium, fill = Event)) +
  geom_col(aes(y = -VRUInc)) +
  labs(x = 'Event', y = 'Incurred Losses <--> Collected Premium', title = 'High Loss COB: Entertainment') +
  theme(axis.text.x = element_text(angle = 45, vjust = 0.5))

COB: Hotels Motels

LR_df %>%
  filter(COB == 'HotelsMotels') %>%
  mutate(Event = fct_relevel(Event, 'Hanna', 'Laura', 'Sally', 'Delta', 'Zeta', 'Ida', 'Ian')) %>%
  ggplot(aes(x = Event)) +
  geom_col(aes(y = TotalPremium, fill = Event)) +
  geom_col(aes(y = -VRUInc)) +
  labs(x = 'Event', y = 'Incurred Losses <--> Collected Premium', title = 'High Loss COB: HotelsMotels') +
  theme(axis.text.x = element_text(angle = 45, vjust = 0.5))

COB: Office

LR_df %>%
  filter(COB == 'Office') %>%
  mutate(Event = fct_relevel(Event, 'Hanna', 'Laura', 'Sally', 'Delta', 'Zeta', 'Ida', 'Ian')) %>%
  ggplot(aes(x = Event)) +
  geom_col(aes(y = TotalPremium, fill = Event)) +
  geom_col(aes(y = -VRUInc)) +
  labs(x = 'Event', y = 'Incurred Losses <--> Collected Premium', title = 'High Loss COB: Office') +
  theme(axis.text.x = element_text(angle = 45, vjust = 0.5))

COB: Restaurant

LR_df %>%
  filter(COB == 'Restaurant') %>%
  mutate(Event = fct_relevel(Event, 'Hanna', 'Laura', 'Sally', 'Delta', 'Zeta', 'Ida', 'Ian')) %>%
  ggplot(aes(x = Event)) +
  geom_col(aes(y = TotalPremium, fill = Event)) +
  geom_col(aes(y = -VRUInc)) +
  labs(x = 'Event', y = 'Incurred Losses <--> Collected Premium', title = 'High Loss COB: Restaurant') +
  theme(axis.text.x = element_text(angle = 45, vjust = 0.5))

COB: Retail

LR_df %>%
  filter(COB == 'Retail') %>%
  mutate(Event = fct_relevel(Event, 'Hanna', 'Laura', 'Sally', 'Delta', 'Zeta', 'Ida', 'Ian')) %>%
  ggplot(aes(x = Event)) +
  geom_col(aes(y = TotalPremium, fill = Event)) +
  geom_col(aes(y = -VRUInc)) +
  labs(x = 'Event', y = 'Incurred Losses <--> Collected Premium', title = 'High Loss COB: Retail') +
  theme(axis.text.x = element_text(angle = 45, vjust = 0.5))

COB: Retail With Refrigeration Equipment

LR_df %>%
  filter(COB == 'RetailwRefrigEquip') %>%
  mutate(Event = fct_relevel(Event, 'Hanna', 'Laura', 'Sally', 'Delta', 'Zeta', 'Ida', 'Ian')) %>%
  ggplot(aes(x = Event)) +
  geom_col(aes(y = TotalPremium, fill = Event)) +
  geom_col(aes(y = -VRUInc)) +
  labs(x = 'Event', y = 'Incurred Losses <--> Collected Premium', title = 'High Loss COB: RetailwRefrigEquip') +
  theme(axis.text.x = element_text(angle = 45, vjust = 0.5))

COB: Service

LR_df %>%
  filter(COB == 'Service') %>%
  mutate(Event = fct_relevel(Event, 'Hanna', 'Laura', 'Sally', 'Delta', 'Zeta', 'Ida', 'Ian')) %>%
  ggplot(aes(x = Event)) +
  geom_col(aes(y = TotalPremium, fill = Event)) +
  geom_col(aes(y = -VRUInc)) +
  labs(x = 'Event', y = 'Incurred Losses <--> Collected Premium', title = 'High Loss COB: Service') +
  theme(axis.text.x = element_text(angle = 45, vjust = 0.5))

CAT Loss Ratios by COB: Highlighting COBs with over 150% Loss

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()

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.

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.

Fun graph :)

funsies1 <- LR_df %>%
  mutate(RankOrder = rank(TotalPremium))

funsies2 <- funsies1 %>%
  #filter(PrimaryState == c('TX','VA','AL','SC','CA','CT','FL','GA','HI','IL','LA','MA','MI','MS','NC','NJ','NV','NY')) %>%
  ggplot(aes(x = reorder(PrimaryState, RankOrder))) +
  geom_col(aes(y = TotalPremium), fill = "#5d8402") +
  geom_col(aes(y = -VRUInc)) +
  geom_text(aes(y = 10000, label = PrimaryState)) +
  coord_polar() 
funsies2