<p><div><div><div><div data-show-faces="false" data-height="21" data-width="100" data-layout="button_count" data-send="true" data-href="https://www.r-bloggers.com/faster-than-excel-painlessly-merge-data-into-actuarial-loss-development-triangles-with-r/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://datascienceplus.com/faster-than-excel-painlessly-merge-data-into-actuarial-loss-development-triangles-with-r/"> R Programming – DataScience+</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>The objective of this post is to create a method which easily combines loss runs, or listings of insurance claims, into triangles. Using only Excel, the common method is to create links between the excel files which must be updated manually for each new evaluation. This is prone to human error and is time-consuming. Using a script to merge the files first and then create a triangle saves time and is more consistent.</p>
<p>Example of the conventional linked Excel triangle:<br>
<a rel="nofollow" href="https://i1.wp.com/datascienceplus.com/wp-content/uploads/2017/12/labeled-triangle.png?ssl=1" target="_blank"><img sizes="(max-width: 490px) 100vw, 490px" src="https://i1.wp.com/datascienceplus.com/wp-content/uploads/2017/12/labeled-triangle-490x194.png?w=450&ssl=1" alt="" class="wp-image-10241" data-recalc-dims="1"></a></p>
<p>For accident year 2016, all the diagonal values need to be linked to a data file that was last evaluated in 2016. For 2015, the links go to the 2015 file, for 2014, the 2014 file, ect. This is time consuming.</p>
<p><a rel="nofollow" href="https://en.wikipedia.org/wiki/Chain-ladder_method" target="_blank">See Wikipedia for a definition of Loss Development Triangles and why they are useful.</a></p>
<h2>Methods</h2>
<p>I use the packages <code>plyr</code>, <code>dplyr</code>, <code>tidyr</code>, and <code>lubridate</code> to manipulate the data once it is loaded into R. The package <code>readxl</code> reads the excel files from the windows file directory. It is worth noting that all five of these packages are included in Hadley Wickham’s <code>tidyverse</code> package. The package <code>ChainLadder</code> has a variety of tools for actuarial reserve analysis.</p>
<pre>
library(dplyr)
library(readxl)
library(plyr)
library(tidyr)
library(ChainLadder)
library(lubridate)
</pre>
<h3>Step 1: Organize the Excel Files</h3>
<p>Copy all of the excel files into the same windows folder, inside the working directory of the R project. It is important to have *only* the files that are going to be loaded into R in this folder.</p>
<p>On my computer, my file structure is as follows:</p>
<p><code>C:/Users/Sam/Desktop/[R project working directory]/[folder with excel claims listing files to aggregate]/[file 1, file 2, file 3, etc]</code></p>
<p>Using the command <code>list.files()</code>, R automatically looks in the working directory and returns a vector of all the the file names which it sees. For example, R sees the data files in the current working directory:</p>
<pre>
wd_path = &quot;C:/Users/Sam/Desktop/projects/Excel Aggregate Loop/excel files&quot;
list.files(wd_path)
<em>## [1] &quot;claims listing 2011.xlsx&quot; &quot;claims listing 2012.xlsx&quot;
## [3] &quot;claims listing 2013.xlsx&quot; &quot;claims listing 2014.xlsx&quot;
## [5] &quot;claims listing 2015.xlsx&quot; &quot;claims listing 2016.xlsx&quot;
</em>
</pre>
<p>R can then loop through each of these files and perform any action. If there were 100 files, or 1000 files in this directory, this would still work.</p>
<pre>
file_names = list.files(wd_path)
file_paths = paste(wd_path, &quot;/&quot;, file_names, sep = &quot;&quot;)
head(read_excel(file_paths[4]) %>% select(-3,-4,-5))
<em>## # A tibble: 4 x 6
## name `license number` age `file year` `loss date` paid
## ## 1 jeff 3 23 2014 2011-01-01 400
## 2 sue 3 43 2014 2012-01-01 400
## 3 mark 2 55 2014 2013-01-01 200
## 4 sarah 1 100 2014 2014-01-01 500
</em>
</pre>
<p>In order to evaluate the age of the losses, we need to take into account when each loss was evaluated. This is accomplished by going into Excel and adding in a column for “file year”, which specifies the year of evaluation of the file. For instance, for the “claim listing 2013” file, all of the claims have a “2013” in the “file year” column.</p>
<h3>Step 2: Load the Data into R</h3>
<p>Initialize a data frame which will store the aggregated loss run data from each of the excel files. **The names of this data frame need to be the names of excel file columns which need to be aggregated.** For instance, these could be “reported”, “Paid Loss”, “Case Reserves”, or “Incurred Loss”. If the excel files have different names for the same quantities (ie, “Paid Loss” vs. “paid loss”), then they should be renamed within excel first.</p>
<pre>
merged_data = as_data_frame(matrix(nrow = 0, ncol = 3))
names(merged_data) = c(&quot;file year&quot;, &quot;loss date&quot;, &quot;paid&quot;)
</pre>
<p>Someone once said “if you need to use a ‘for’ loop in R, then you are doing something wrong”. Vectorized functions are faster and easier to implement. The function <code>my_extract</code> below takes in the file name of the excel file and returns a data frame with only the columns which are selected. </p>
<pre>
excel_file_extractor = function(cur_file_name){
  read_excel(cur_file_name[1], sheet = &quot;Sheet1&quot;, col_names = T) %>%
    select(`file year`, `file year`, `loss date`, paid) %>%
    rbind(merged_data)
}
</pre>
<p>Apply the function to all of the files in the folder that you created. Obviously, if you had 100 excel files this would still work just as effectively.</p>
<p>From the <code>plyr</code> package, <code>ldply</code> takes in a list and returns a data frame. The way to remember this is by the ordering of the letters (“list”-“data frame”-“ply”). For example, <code>ff</code> we wanted to read in a data frame and return a data frame, it would be <code>ddply</code>.</p>
<pre>
loss_run_data = ldply(file_paths, excel_file_extractor)
names(loss_run_data) = c(&quot;file_year&quot;, &quot;loss_date&quot;, &quot;paid losses&quot;)
</pre>
<p>The data now has only the columns what we selected, despite the fact that the loss run files had different columns in each of the files. </p>
<pre>
glimpse(loss_run_data)
<em>## Variables: 3
## $ file_year 2011, 2012, 2012, 2013, 2013, 2013, 2014, 2014, 20...
## $ loss_date 2011-01-01, 2011-01-01, 2012-01-01, 2011-01-01, 2...
## $ paid losses 100, 200, 300, 300, 350, 100, 400, 400, 200, 500, ...
</em>
</pre>
<pre>
head(loss_run_data)
<em>## file_year loss_date paid losses
## 1 2011 2011-01-01 100
## 2 2012 2011-01-01 200
## 3 2012 2012-01-01 300
## 4 2013 2011-01-01 300
## 5 2013 2012-01-01 350
## 6 2013 2013-01-01 100
</em>
</pre>
<h3>Step 3: Create Development Triangles</h3>
<p>Finally, once we have the loss run combined, we just need to create a triangle. This is made easy by the `as.triangle` function from the `ChainLadder` package.</p>
<p>The only manual labor required in excel was to go into each file and create the `file year` column, which was just the year of evaluation of each loss run file.</p>
<pre>
loss_run_data  = loss_run_data %>% 
  mutate(accident_year = as.numeric(year(ymd(loss_date))),
         maturity_in_months = (file_year - accident_year)*12)

merged_triangle = as.triangle(loss_run_data, 
                              dev = &quot;maturity_in_months&quot;, 
                              origin = &quot;accident_year&quot;, 
                              value = &quot;paid losses&quot;)

merged_triangle
</pre>
<p><a rel="nofollow" href="https://i0.wp.com/datascienceplus.com/wp-content/uploads/2017/12/tri_snip.png?ssl=1" target="_blank"><img sizes="(max-width: 340px) 100vw, 340px" src="https://i0.wp.com/datascienceplus.com/wp-content/uploads/2017/12/tri_snip.png?resize=340%2C148&ssl=1" alt="" width="340" class="wp-image-10238" data-recalc-dims="1" height="148"></a></p>
<p>Within the package `ChainLadder` is a plot function, which shows the development of losses by accident year. Because these are arbitrary amounts, the plot is not realistic.</p>
<pre>
plot(merged_triangle, 
     main = &quot;Paid Losses vs Maturity by Accident Year&quot;,
     xlab = &quot;Maturity in Months&quot;, 
     ylab = &quot;Paid Losses&quot;)
</pre>
<p><a rel="nofollow" href="https://i1.wp.com/datascienceplus.com/wp-content/uploads/2017/12/loss_dev_plot.png?ssl=1" target="_blank"><img sizes="(max-width: 490px) 100vw, 490px" src="https://i2.wp.com/datascienceplus.com/wp-content/uploads/2017/12/loss_dev_plot-490x308.png?w=450&ssl=1" alt="" class="wp-image-10232" data-recalc-dims="1"></a></p>
<h2>Conclusion</h2>
<p>When it comes to aggregating excel files, R can be faster and more consistent than linking together each of the excel files, and once this script is set in place, making modifications to the data can be done easily by editing the `exel_file_extractor` function.</p>
<p>Download the data and Rmd files for this article from <a rel="nofollow" href="https://github.com/sdcastillo/Loss-Development-Triangles" target="_blank">my github account</a></p>
<ul></ul>
<p><strong>
<p>Related Post</p>
<p></p></strong>
<ol>
<li><a rel="nofollow" href="https://datascienceplus.com/the-mcomp-package/" title="Permanent Link toThe Mcomp Package" target="_blank">The Mcomp Package</a></li>
<li><a rel="nofollow" href="https://datascienceplus.com/building-a-hacker-news-scraper-with-8-lines-of-r-code-using-rvest-library/" title="Permanent Link toBuilding a Hacker News scraper with 8 lines of R code using rvest library" target="_blank">Building a Hacker News scraper with 8 lines of R code using rvest library</a></li>
<li><a rel="nofollow" href="https://datascienceplus.com/building-a-telecom-dictionary-scraping-web-using-rvest-in-r/" title="Permanent Link toBuilding a Telecom Dictionary scraping web using rvest in R" target="_blank">Building a Telecom Dictionary scraping web using rvest in R</a></li>
<li><a rel="nofollow" href="https://datascienceplus.com/scraping-javascript-rendered-web-content-using-r/" title="Permanent Link toScraping Javascript-rendered web content using R" target="_blank">Scraping Javascript-rendered web content using R</a></li>
<li><a rel="nofollow" href="https://datascienceplus.com/analysing-cryptocurrency-market-in-r/" title="Permanent Link toAnalysing Cryptocurrency Market in R" target="_blank">Analysing Cryptocurrency Market in R</a></li>
</ol>
	<p>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="https://datascienceplus.com/faster-than-excel-painlessly-merge-data-into-actuarial-loss-development-triangles-with-r/"> R Programming – DataScience+</a></strong>.</div>
<hr>
<a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers.com</a> offers <strong><a rel="nofollow" href="https://feedburner.google.com/fb/a/mailverify?uri=RBloggers">daily e-mail updates</a></strong> about <a rel="nofollow" href="https://www.r-project.org/" title="The R Project for Statistical Computing">R</a> news and <a rel="nofollow" href="https://www.r-bloggers.com/search/tutorial" title="R tutorials">tutorials</a> on topics such as: <a rel="nofollow" href="https://www.r-bloggers.com/search/data%20science" title="Data science">Data science</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Big%20Data" title="Big Data">Big Data, <a rel="nofollow" href="https://www.r-users.com/" title="R jobs">R jobs</a>, visualization (<a rel="nofollow" href="https://www.r-bloggers.com/search/ggplot2" title="ggplot and ggplot2 tutorials">ggplot2</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/boxplot" title="Boxplots using lattice and ggplot2 tutorials">Boxplots</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/map" title="Maps and gis">maps</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/animation" title="Animation in R">animation</a>), programming (<a rel="nofollow" href="https://www.r-bloggers.com/search/RStudio" title="RStudio IDE for R">RStudio</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/sweave" title="Sweave and literate programming">Sweave</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/LaTeX" title="LaTeX in R">LaTeX</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/SQL" title="SQL and databases">SQL</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/eclipse" title="Eclipse IDE for R">Eclipse</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/git" title="git and github, Version Control System">git</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/hadoop" title="Large data in R using Hadoop">hadoop</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Web+Scraping" title="Web Scraping of google, facebook, yahoo, twitter and more using R">Web Scraping</a>) statistics (<a rel="nofollow" href="https://www.r-bloggers.com/search/regression" title="Regressions and ANOVA analysis tutorials">regression</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/PCA" title="principal component analysis tutorial">PCA</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/time+series" title="Time series">time series</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/trading" title="finance trading">trading</a>) and more...
<img width="1" alt="" src="http://feeds.feedburner.com/~r/RBloggers/~4/ZwbpD3u4JO0" height="1"></a></div>