In late September and early October, Hurricane Matthew caused devastation in the Caribbean, especially in Haiti, before continuing up the Florida coast. Using data from Weather Underground, we show how to map its trajectory and visualize its intensity and storm status.
1. Create a new spreadsheet with Geosheets enabled.
2. Import data from Weather Underground.
The page at https://www.wunderground.com/hurricane/atlantic/2016/Hurricane-Matthew has coordinates for the storm over a nearly two-week period along with classifications and wind speeds. Fortunately, the page has the data in a table, so we can easily import it into Google Sheets.
In cell B3, so that you have room to work, write this formula:
=IMPORTHTML("https://www.wunderground.com/hurricane/atlantic/2016/Hurricane-Matthew", "table", 1)
This is a Google Sheets built-in function that goes to the given and page and extracts the data it finds in the first table on the page (hence the use of "table" and "1" as arguments). In a moment, your spreadsheet should populate with the structured ata from that page.
3. Create a unified LatLng column to be used for mapping.
The imported data contains separate columns for Lat and Lng, but Geosheets uses a single column for the coordinates of each feature, so we need to merge these. In cell A3 type "LatLng" to designate that the column that will hold our coordinates. In cell A4, write this formula:
This is a simple Geosheets formula that takes separate latitude and longitude values and merges them into a single cell. Now copy the formula from cell A4 and paste it all the way down column A. You should have coordinates in column A for every row.
4. Designate the features as circles and assign a radius to each.
At this point, we have enough data to draw a map, but the coordinates will be better visualized as circles rather than teardrop markers. In cell I3, which should be the first empty column, add a column header called "Type". Type the value "circle" into cell I4 and then copy and paste it all the way down column I. This will tell Geosheets to draw each row as a circle instead of a marker.
Now in cell J3 add a column header called "Radius", which will be the radius in pixels of each circle. In cell J4 paste this formula:
=GEO_ASSIGN_RADIUS(F4:F58, 2, 10, 0)
This formula is telling Geosheets to look at all the values in column F, which are wind speeds, and assign a radius for each row based on how big the wind speed value is. All but the first argument are optional. The extra arguments we passed tell the function that the minimum radius to assign is 2 pixels, the maximum radius to assign is 10px, and to use 0 as the minimum wind speed value when interpolating. If you omit these extra arguments, Geosheets will choose sensible defaults for your, for example by taking the smallest and largest values in the column as the range to interpolate within.
5. Create a legend to assign colors.
The Weather Underground data gives us storm classifications for each row, e.g. Category 1, Category 2, etc. So we can color-code each feature based on these values. The easiest way to do that is to create a legend that maps each storm type to a color, and then write a formula that uses the legend to color-code each row.
Anywhere you have empty space in your document, perhaps below the data table in a cell like A61, simply create a two-column legend, where the left column contains the exact storm type values like "Tropical Storm" or "Category 1 Hurricane", and the right column contains a color name or hex code.
Our legend looks like this, transitioning from light yellow to dark red based on the intensity of the storm type:
Tropical Storm #FFfa00 Category 1 Hurricane #FFa000 Category 2 Hurricane #FF6400 Category 3 Hurricane #FF8c00 Category 4 Hurricane #FF3200 Category 5 Hurricane #FF0000 Post-Tropical Cyclone #FFfa00
Now we just need to write a formula to apply our legend to each row. In cell K3 add a column header called "Color". In cell K4 write this formula:
This formula says to look at all the values in column H, which contains the storm types, and fill in a color value for each row using the legend we defined in the range A61:B67. If it's easier to think about, you could also write the formula as =GEO_COLOR_FROM_LEGEND(H4, A61:B67) and then paste it into each cell in column H.
The function GEO_COLOR_FROM_LEGEND is just a convenience, you could easily use VLOOKUP to create a custom legend.
6. Draw the map using GEO_MAP.
We've now got a complete table with coordinates, attributes, and columns indicating how the features should be drawn. In cell A1 or wherever you have room, write this formula:
This tells Geosheets to create a map from our full data range, including the header row, but excluding the legend. We must also give our map a label so it can be referred to, so in this case we simply label it "matthew". In a few moments a url should populate in that cell that points to your finished map, which should look something like this:
You can view the full page map here: https://www.geosheets.com/map/s:nKbR1wJm/matthew.
The final spreadsheet should look like this:
You can view or copy the complete spreadsheet here: