Insurance case study on data manipulation

Summary: This case study is based on dummy dataset of an Insurance company who sells policies/products in Travel domain. We have been provided with Premium/Claims datsets for every individual policy no. or customer.

Insights derived: 1- Total sum Assured till date is highest for Delhi region. 2- Highest premium is paid in East zone and median premium almost remains same to approx 600 in every zone 3- Asia silver plan is very less famous amongst customer 4- Individual plans are most higly bought by customers and these plans provide higher sum insured. 5- Premium for Family plans are high compared to Individual plans 6- South zone has highest number of Claims with status as “NO”

Install required packages for data manipulation

library(dplyr)
library(stringr)
library(ggplot2)

Import Claims/Premium datasets

claimsData<-read.csv(file.choose(),na.strings = "")
head(claimsData)
premiumData<-read.csv(file.choose(),na.strings = "")
head(premiumData)

Merge premium and claims datasets

finalData <- claimsData %>%
  inner_join(premiumData, by = "POLICY_NO")
  head(finalData)

Create a subset of policy no., region and sub plan of the policy holders whose claim status = Yes

names(finalData)
subset1 <- finalData %>%
filter(Claim_Status == "Yes") %>%
select(POLICY_NO, REGION, Sub_Plan)%>%
head(subset1)

Create a subset of data for all the regions except Delhi with sumassured more than Rs. 5 lacs and claim status = Yes

subset2 <- finalData %>%
  filter(!str_detect(str_to_upper(REGION), "DELHI") &
  Sum_Assured > 500000 & Claim_Status == "Yes")
subset2

Calculate the sum for variable ‘Sum_Assured’ by ‘REGION’ variable.

Calculate total sum assured for each sub plan

subset4<-finalData%>% group_by(Sub_Plan)%>% summarise(sum=sum(Sum_Assured,na.rm=TRUE))
subset4

Box plot to show premium distribution zonewise

Relation between Sum assured and premium

ggplot(data = finalData,
       aes(
       x = finalData$Sum_Assured,
       y = finalData$Premium,
       color = Plan
       )) +
       geom_point(alpha = 1) +
       scale_x_log10() +
       ggtitle(label = "Premium v/s Sum Assured Planwise comparison") +
       xlab("Sum Assured") +
       ylab("Premium")+
  theme_minimal()

Premium v/s number of people relation

ggplot(data = finalData,
       aes(x = finalData$Premium, fill = Sub_Plan)) +
       scale_x_continuous(limits = c(0, 4000), breaks = seq(0, 4000, 1000)) +
       geom_histogram(binwidth = 50) +
       facet_grid(finalData$Plan ~ ., scales = "free_y") +
       ggtitle("Premium distribution planwise") +
       xlab("Premuim") +
       ylab("Number of people")+
  theme_update()

Claim Status count Zone wise

subset6<-finalData%>% group_by(Claim_Status,ZONE_NAME)%>%summarise(Total_Claims=n())
d<-ggplot(data=subset6,aes(x=Claim_Status,y=Total_Claims))+
  geom_col(aes(fill=ZONE_NAME),position="dodge")+
  ggtitle("Claim Status overview zone wise") +
       xlab("Claim Staus") +
       ylab("Number of people")+
  theme_linedraw()
d