Risk Analysis in the CARTO Builder

• Degree of Difficulty: ***

• Goal: what US counties have higher risk for insuring railroad companies.

• Features Highlighted:

  • Widgets: Category, Formula and Time Series.
  • Analysis: Intersect, Outliers & Cluster analysis.

• Datasests needed:

  • Railroad accidents (dot_rail_safety_data): download it from the builder-demo CARTO account and import it into CARTO from your local machine.
  • US counties (cb_2013_us_county_500k): search and connect via Data Library.

Instructions

1. Import and create a map

Import dot_rail_safety_data csv file into your dataset dashboard.

• Importing files into CARTO is easy! A wide diversity of geodata is supported in CARTO during the importing.

You can import directly to your Map dashboard as a .carto file and skip this step.

Dataset view

Click on CREATE MAP.

• Main menu/sections of the Builder.

Rename the map name and dataset to "Risk Analysis Demo" and "Railroad Accidents", respectivally.

• You should have a dashboard like this:

2. Layers and styles

Ordering of the layers in the Builder

• Basemap

• Layers

The different options layers have

5 options: DATA, ANALYSES, STYLE, POP-UPS and LEGENDS.

• DATA:

  • Switch to VALUES to SQL
  • map view to dataset view.

• STYLE:

  • FILL: click on the marker size, select BY VALUE, set total_damage as the variable and choose a color.
  • Change the STROKE to 0.
  • Switch to VALUES to CARTOCSS. The CartoCSS command line allows more advanced users to layer style in a more precise way.
  • turbo-carto

3. Add widgets

Back to the main menu, select WIDGETS

• ADD WIDGET:
  • Railroad Companies Category Widget: select Category, choose railroad, and click on CONTINUE. In order to rename the widget, come back to the list of widgets and double click on the name and rename it as "Railroad Companies".

• Total Damage Formula Widget: select Formula, choose total_damage, and click on CONTINUE and set OPERATION to sum and add $ as PREFIX. In order to rename the widget, come back to the list of widgets and double click on the name and rename it as "Total damage".

• Date Time Series Widget: select Time Series, choose date, and click on CONTINUE. In order to rename the widget.

4. Add US counties layer, start the analysis

Back to the main menu, select LAYERS, then ADD

If you have imported the map as a .carto file please skip this step.

• Click on DATA LIBRARY, type "counties" on the SEARCH bar, select the cb_2013_us_county_500k dataset and finally, click on ADD LAYER.
• Rename the new layer to "US Counties".

Click on "US Counties" layer, ANALYSES, ADD ANALYSIS

• Select Intersect second layer:

  • Select "Railroad accidents" as TARGET LAYER and SUM(total_damage) as OPERATION. Apply.
  • When the analysis is done, an explanatory window will pop up. Click on DONE.

• First, using the map show the viewer the results of the analysis: only the counties overlapping with data points are showed. Secondly, go to the dataset view to show the new column created with the previous analysis, sum_total_damage.

• Sum Total Damage Histogram Widget: from the same DATA section, check the Add as a widget box of the sum_total_damage field and EDIT. This will create a new histogram widget. Set the buckets to 7 and rename it as "Sum Total Damage".

• Use the autostyling and removing the visibility of the "Railroad accidents" layer. Remove auto style again. Go back to the main menu.

5. Continue the analysis, get outliers and clusters

Click on ADD ANALYSIS just below "US Counties"

• Select Detect outliers and clusters:
  • Select sum_total_damage as TARGET COLUMN and leave the rest of parameters with the default values. Apply.
  • Again, when the analysis is done, an explanatory window will pop up. Click on DONE.

• We are going to add a last widget, Sum Total Damage Histogram Widget: click on WIDGETS, ADD, Category and select quads. CONTINUE. Rename it as "Groups".

• Filter by HH and HL counties. Those are counties with high value of total damage surrounded by counties with also high values, and counties with high value of total damage surrounded by counties with low values. Click on Auto style to better distinguish them. Remove the auto style.

6. Share and export your results

Back to the main menu, click on the three dots on the "US Counties" layer

• Select Export data, choose CSV.
• Open (with Excel or another similar software) the csv file you just download US_Counties.csv. Collapse the_geom column. You should have 39 counties/rows, containing only HH and HL values.

Publishing

• Below the map title it should show PRIVATE, ADD PEOPLE and Map not published yet. Let's change that.
  • First, click on PRIVATE, and again. Select Link.
  • Secondly, click on SHARE (at the bottom of the main menu). Click on PUBLISH, and then DONE.
  • Get the link and past it into your browser.

The dashboard should show your "Railroad accidents" as green dots with sizes depending on the total damage. In addition, all the groups of counties will be displayed. This is because the filters and auto styling you did, it is not applied. Finally, you will have four widgets but in different order.

• Back to the main menu, click on "US Counties" layer. Go to the STYLE tab. Style the layer with a FILL BY VALUE. Select quads as variable and choose a couple of colors that can be easily distinguished.
• Click again on SHARE and now in UPDATE.
• Now if you go back to your browser tab where you have pasted the link nothing has changed. But if you refresh the page, voilá! The colors have modified.