Form Requirements – G1.5 Runs

G1.5 Runs

Disclosure 5 of the G-1 General Standards requires the modeler to provide information related to changes in the hurricane model from the previously-accepted hurricane model to the initial submission of this year. Color-coded maps by county and support files are to be provided that reflect changes in:

  • Percentage difference in average annual zero deductible statewide hurricane loss costs based on 2012 and 2017 CatFund data for each hurricane model component change
  • Percentage difference in average annual zero deductible statewide hurricane loss costs based on 2012 and 2017 CatFund data for all hurricane model components change

The following table is a summary of the changes per component under evaluation between the previously-accepted hurricane model and the model currently set for submission:

Changes under evaluation Swaths (Hurdat) Roughness (ZIP centroid) Vulnerability ILM Code Demand Surge Swaths (Hurdat) Roughness (ZIP centroid) Vulnerability ILM Code Demand Surge
Hurdat v6.2 v6.2 v6.2 v6.2 none v6.3 v6.2 v6.2 v6.2 none
Roughness v6.2 v6.2 v6.2 v6.2 none v6.2 v6.3 v6.2 v6.2 none
Hurdat + Roughness v6.2 v6.2 v6.2 v6.2 none v6.3 v6.3 v6.2 v6.2 none
Year of the Hurricane (PR only) v6.2 v6.2 v6.2 v6.2 none v6.2 v6.2 v6.2 v6.3 none
All model changes (including YH impact) v6.2 v6.2 v6.2 v6.2 none v6.3 v6.3 v6.2 v6.3 none

In summary, for November 2018 submission, there is a total of twelve (12) ILM runs:

  • Two (2) baseline runs, one for each CatFund dataset and v6.2 model inputs
  • Five (5) runs with each change under evaluation (highlighted in the right side of the table) with 2012 CatFund data
  • Five (5) runs with each change under evaluation (highlighted in the right side of the table) with 2017 CatFund data

Required files

Several important files are required in order to generate the support files and the color-coded maps

1.- ExcelLoader java program to read the ILM outputs and generate the final form. There are several versions required in order to generate the output required for the different support files. The files are located at:

  • /home/mitch-a/dmis-projects/fphlm/proc/2017_standards_v6.3/code/ExcelLoader/A4/2018_A4-A7FormsExcelLoader_A4A
    • This version of the program is built with coverage files for 2012 CatFund data
  • /home/mitch-a/dmis-projects/fphlm/proc/2017_standards_v6.3/code/ExcelLoader/A4/2018_A4-A7FormsExcelLoader_A4B
    • This version of the program is built with coverage files for 2017 CatFund data
  • /home/mitch-a/dmis-projects/fphlm/proc/2017_standards_v6.3/code/ExcelLoader/G1.5/
    • The four (4) versions of the program found in this folder have the coverage files broken down in LR and MHR components for 2012 and 2017 CatFund data

2.- Python notebooks to import data from ILM runs and generate the required files for color-coded maps. These files are located at:

  • /home/mitch-a/dmis-projects/fphlm/versions/v6.3/2017_ROA/2017FormG1.5/2017FormG1.5_avg_loss_cost.ipynb
  • /home/mitch-a/dmis-projects/fphlm/versions/v6.3/2017_ROA/2017FormG1.5/2017FormG1.5_avg_loss_cost_perchange.ipynb

3.- Support file templates. These files are the template for the different support files required. These files are located at:

  • /home/mitch-a/dmis-projects/fphlm/versions/v6.3/2017_ROA/2017FormG1.5/2017FormG1.5_supportFile_template.xls
  • /home/mitch-a/dmis-projects/fphlm/versions/v6.3/2017_ROA/2017FormG1.5/2017FormG1.5_supportFile_PR_MHR_LR_template.xls

4.- File 2017FormA5.xlsx. This file is the template of the form. It is located at:

  • /home/mitch-a/dmis-projects/fphlm/versions/v6.3/2017_ROA/

Processing

WSC runs

To be updated by Yudong

ILM runs

To be updated by Tianyi

Post-processing

In order to create the files required for the support files, you have to simulate a post-processing run as if it were for Form A5. The zero deductible log outputs of the ExcelLoader program will be the inputs to the support files.

  • Create a folder to hold the intermediate files that will result from this process. Named 20181102 in this example

Steps to create the files with the results required to create the support files

1.- Create a folder to hold the intermediate files for a specific change under evaluation (v6.3_hurdat_ilm_6.2_2012CatFund, v6.3_roughness_ilm_6.2_2012CatFund, and so on…)

2.- Within the recently created folder create another folder to contain the files and folders required to generate the required outputs. Named gen_excel_file in this example

3.- Within gen_excel_file create the following folders:

  • log
  • v6.2_aal
  • v6.3_aal

4.- Within v6.2_aal and v6.3_aal create the following directory structure:

  • Zero_Deductible/Combined/results/
  • Specific_Deductible/Combined/results/
    • You can skip the creation of Specific_Deductible folder since we only care about Zero_Deductible. In that case, only create Zero_Deductible and create a symlink named Specific_Deductible pointing to Zero_Deductible

5.- For each v6.2_aal and v6.3_aal folders, copy or symlink the file final_all.txt from the combined folder of the versions’ corresponding runs.

  • v6.2_aal folder will always have files from the baseline run that correspond to each CatFund dataset
  • v6.3_aal folder will always have files from the run with the change under evaluation that corresponds to each CatFund dataset
  • As an example, for 2018 November submission and hurdat change with 2012CatFund data, the structure would be as follows:
    • For v6.2_aal:
      • Zero_Deductible: /home/mitch-a/dmis-projects/fphlm/proc/2017_standards_v6.3/forms/g1-5/20181011_run/20181011_ilmv6.2_AllModelChanges_2012CatFund/combined/results/final_all.txt
    • For v6.3_aal:
      • Zero_Deductible: /home/mitch-a/dmis-projects/fphlm/proc/2017_standards_v6.3/forms/g1-5/20181011_run/20181011_ilmv6.3_hurdat_2012CatFund/combined/results/final_all.txt

6.- Copy A5 form template and the corresponding ExcelLoader program into the gen_excel_file directory

  • When creating files for 2012 CatFund data use ExcelLoader_A4A
  • When creating files for 2017 CatFund data use ExcelLoader_A4B
  • There will be occasions where the Commercial_Residential portion of the support file needs to be broken down in LR and MHR components, in which case you need to use the corresponding ExcelLoader located at:
    • /home/mitch-a/dmis-projects/fphlm/proc/2017_standards_v6.3/code/ExcelLoader/G1.5/

7.- The resulting directory structure and files of gen_excel_file folder should be something similar to this:

  • v6.2_aal/Zero_Deductible/Combined/results/final_all.txt
  • v6.2_aal/Specific_Deductible/Combined/results/final_all.txt
  • v6.3_aal/Zero_Deductible/Combined/results/final_all.txt
  • v6.3_aal/Specific_Deductible/Combined/results/final_all.txt
  • log
  • ExcelLoader_you_version.jar file
  • 2017FormA5.xlsx

8.- Run the Java program in the command line as follows:

  • Syntax: java -jar ExcelLoader_your_version.jar -a5 AllOptions A5_template.xlsx path_to_aal_6.2_folder/ path_to_aal_6.3_folder/ output_file.xlsx path_log_folder/
  • Example: java -jar ExcelLoader_FormsA4-7-FPHLM_v5.0.jar -a5 AllOptions 2017FormA5.xlsx v6.2_aal/ v6.3_aal/ 20181008_FormA5.xlsx log/

9.- If the program runs without any issues, the log/logs_zero_deductible folder should have all the results required for the support file

10.- Repeat the steps for all changes under evaluation

Steps to create the support files

1.- Copy the corresponding support file template to the post-processing root directory, 20181102/change_under_evaluation

2.- Open the template with Microsoft Excel

3.- Go to gen_excel_file folder, then logs_zero_deductible

4.- Notice how the support file template has two sets of columns in each of the sheets. The columns on the left will hold data from the files within the logs_older_set folder. The columns on the right will hold data from the files within the logs_newer_set folder.

5.- Also notice the sheet names, each one of them will be filled with the data from the respective log files. For example, Owners_Frame sheet will have data from zipcode_losscosts_Owners-Frame.csv. As mentioned in the previous step, the left side contains data from logs_older_set and the right side contains data from logs_newer_set

6.- To add the data into the sheets follow the following steps:

  • Open the log file
  • Copy the values from County, ZipcodeCoverage, and LossCosts columns and paste into county, zipcodeexposure, and wt_tot columns, respectively
  • In the first cell of form_construction column insert the sheet name and double click in the autofill tool (bottom right corner) to fill the rest of the column’s cells with the same value
  • Add a zero (0) in the first cell of deductible column. Double click the autofill tool to fill the rest of the columns with zero
  • For columns region and coastal_inland, open a previous submission support file and use Excel’s VLOOKUP function to get the corresponding value based on the county name
  • You only need to do this process for the left side, then copy values from all columns except wt_tot to the right side. Values for wt_tot in the right side comes from the LossCosts column in the log file in logs_newer_set

7.- Repeat step 6 for all construction types

  • Please note that the number of rows between different construction types varies, so you cannot copy and paste the rows between different sheets

8.- Repeat the steps for all changes under evaluation

Steps to create the required files for the color-coded maps

1.- Use Anaconda Navigator or an existing Jupyter Notebook or Jupyter Lab environment and open:

  • /home/mitch-a/dmis-projects/fphlm/versions/v6.3/2017_ROA/2017FormG1.5/2017FormG1.5_avg_loss_cost.ipynb

The program reads the final_all.txt file and generates the average loss cost at the county level based on the losses and units per county

  • Change the paths to point to the correct final_all.txt file
  • Change the path of the output file. Ideally, you will save the output file in the folder with the intermediate files for the change under evaluation
  • Run all the cells
    • As a precaution, you can comment the last cell and test the output first before saving to a file
    • If everything looks ok, proceed to save the output .csv file
  • The format of the output .csv file is as follows:
    • County, Units in County, Loss Cost per County, Avg Loss Cost per Unit in County

You will need to perform this process only once for the baseline run and once for each change under evaluation. As an example, you can check the baseline for 2018 November submission in the following paths:

  • 2012 CatFund baseline is located at: /home/mitch-a/dmis-projects/fphlm/proc/2017_standards_v6.3/forms/g1-5/post-processing/v6.2_all_model_changes_ilm6.2_2012CatFund/20181022_avg_loss_cost_per_unit_county.csv
  • 2017 CatFund baseline is located at: /home/mitch-a/dmis-projects/fphlm/proc/2017_standards_v6.3/forms/g1-5/post-processing/v6.2_all_model_changes_ilm6.2_2017CatFund/20181023_avg_loss_cost_per_unit_county.csv

After generating all the required outputs, you should have twelve (12) .csv files with the aforementioned output

2.- Use Anaconda Navigator or an existing Jupyter Notebook or Jupyter Lab environment and open:

  • /home/mitch-a/dmis-projects/fphlm/versions/v6.3/2017_ROA/2017FormG1.5/2017FormG1.5_avg_loss_cost_perchange.ipynb

This program reads the outputs of the previous program, calculates the percentage change, and outputs a .csv file ready to be used to create the map

  • Change the paths to point to the corresponding baseline and change under evaluation .csv files generated in the previous step
    • You would set the path to the baseline file just once and change only the path of the losses for the change under evaluation
  • Change the path of the output file. Ideally, you will save the output file in the folder with the intermediate files for the change under evaluation
  • Run all the cells
    • As a precaution, you can comment the last cell and test the output first before saving to a file
    • If everything looks ok, proceed to save the output .csv file
  • The format of the output .csv file is as follows:
    • County, Percentage

Steps to create the maps

The maps for Form G1.5 detail percentage change at the county level. The shapefile used to generate this map is located in:

  • /home/mitch-a/dmis-projects/fphlm/proc/2017_standards_v6.3/data/base_maps/fl_2011_counties/cntbnd_jul11.shp

There is also an ArcMap Layer file (.lyr) from the last and current submission that contains the quantities and color ramp used in the map. Using this file expedites the map generation process. This file is located at:

  • /home/mitch-a/dmis-projects/fphlm/versions/v6.3/2017_ROA/2017FormG1.5/2017G1.5_color_ramp.lyr

1.- Open ArcMap and create a new empty map

  • In the left panel, right click on Layers and then Add Data...
  • Select the aforementioned shapefile. The right panel should now display the map of Florida with county boundaries
  • Repeat the process to Add Data..., but this time select the .csv files generated in the previous step
  • In the left panel:
    • Right click on the map layer (cntbnd_jul11 or the name of the loaded shapefile) > Properties... > Join & Relates > Add...
    • In the popup, on field 1. Choose the field in this layer that the join will be based on: select NAME or the attribute in the shapefile that correspond to the county name
    • Once selected, field 3. Choose the field in the table to base the join on: select County if it is not automatically selected
    • Click Ok. You should now see an entry under the Joins panel at the left
    • Go to the Symbology tab, then click on Quantities
    • Click on Import... then open the file selection dialog through the folder icon and load the aforementioned .lyr file
    • Click OK
    • In the Import Symbology Matching Dialog, select Percentage as the Value Field, then click OK
    • You should see now a customized color ramp, ranges, and labels for each range
    • Click Apply then OK
  • If everything went smoothly the map should be colored now according to the loaded color ramp.
  • Now change the view to Layout View. View > Layout View
  • If the map is not centered in the document, click on the Full Extent tool in the toolbar. The button is represented by a globe
  • Insert Title and Legends
    • For the title: Click on Insert > Title and type the desired title. Properties of the title can be changed through a double click over the title in the document. Set the appropriate format
    • For the legend: Click on Insert > Legend. Hit Next for all steps. You should see a legend over the document now
      • Position the legend at the bottom left of the document
      • Resize accordingly
      • To change the caption of the legend, select the label under the map shapefile layer (cntbnd_jul11 or the name of the loaded shapefile), click to enter edit mode and delete the text if any. Then select and click over the shapefile layer to enter edit mode and change the caption to Percentage
    • For Min and Max legend: Click on Insert > Text
      • Change text to: Min: Value at ZIP code XXXXX Max: Value at Zip code XXXXX
      • To get these values, right click on the data source layer > Open, then sort by Percentage by right-clicking over the column header and selecting either Sort Ascending or Sort Descending
      • Place the legend at the bottom right of the document
  • Place an * over the counties with Min and Max percentage change.
  • Save the current document as an ArcMap document (.mxd). File > Save as...
  • Export the current document as a PNG file. File > Export Map... Set resolution to 600 dpi

Once the first map is finished and the document has been saved as an ArcMap document, the process can be repeated for the remaining changes under evaluation maps using the same document and changing the data source, join, title, and legends accordingly.

Save each map as a separate ArcMap document.

Results

1.- Send prototype of form to the verifier.
2.- Verifier sends prototype of form to team leader.
3.- Team leader sends prototype of form to expert.

Notes

  • The post-processing environments for the results submitted in November 2018 are located at:
    • /home/mitch-a/dmis-projects/fphlm/proc/2017_standards_v6.3/forms/g1-5/post-processing/
  • The outputs generated for November 2018 submission can be found at:
    • /home/mitch-a/dmis-projects/fphlm/proc/2017_standards_v6.3/forms/g1-5/outputs/
  • The data underlying the maps for November 2018 submission can be found at:
    • /home/mitch-a/dmis-projects/fphlm/proc/2017_standards_v6.3/forms/g1-5/outputs/map_data/
  • There will be occasions that support files will be required to have the Commercial_Residential component separated into LR and MHR components, if that is the case:
    • If the exposure data changes, you need to request new separated coverage files for LR and MHR components and create new versions of the ExcelLoader program. If the data doesn’t change, you can use the ones mentioned in section 1.1
      • The coverage files contain the aggregated exposures at zip code level
        • It is a .csv file without header with the following format:
          • zipcode,LMs,LMapp,LMc,LMale,Total_Coverage
      • Instructions to build a new version of the program are located at: /home/mitch-a/dmis-projects/fphlm/proc/2017_standards_v6.3/code/ExcelLoader/README
    • You need to run the ExcelLoader program separately for LR and MHR component, specifically you need to:
      • Create a new folder with v6.2_aal, v6.3_aal, log folders, Form A5 template file, and the corresponding ExcelLoader program
      • This time, instead of copying or linking final_all.txt file, you need to copy or link lr_aal.txt or mhr_aal.txt as final_all.txt and then run the ExcelLoader program for each of the components.
      • The file log/logs_zero_deductible/{logs_older_set,logs_newer_set}/zipcode_losscosts_CommercialResidential.csv will give you the values required for the wt_tot column of the given component
  • Additionally, there will be occasions that maps and support files will be required for runs with DemandSurge. If that is the case:
    • Files from the ILM run for Form A4A and A4B can be used as runs with 2012 and 2017 CatFund data, respectively
    • For November 2018 submission, an additional ILM run using 2017 CatFund data was performed, but with v6.2 model inputs, since there wasn’t any ILM run with v6.2 model inputs, 2017 CatFund data, and DemandSurge enabled
  • All the files and configurations in this document are specific to this example run and submission. The processor should update parameters as required by the submission and/or version.
  • Regarding the map generation process, if there is a change in the ranges and colors, you need to set them manually. The provided .lyr file is just a convenience that will work if everything remains the same.
  • The outputs of the Python notebooks used in Section 1.3.3 to generate the files for color-coded maps are specific to the data in the shapefile used in this submission. You need to take appropriate measures based on the shapefile you will work on.
    • You need to be aware of the format of the output in order for the Join operation to be successful. Ideally, a join operation through a zip code attribute would be less prone to error, unfortunately, the shapefile of the base map didn’t include that information, so the join operation had to be performed over the column with the county name
    • As a rule of thumb, you need to generate the .csv or Excel file with a column that will serve to join the data with the shapefile, either by zip code or county name
  • The results and maps showing the impact of each individual model change that were submitted in November 2018 did not contain demand surge; however, the result and map showing the impact of all model changes combined that were submitted did contain demand surge for agreement with Form A-5’s results.
Download PDF
Sidebar