Assignment 3

a. Load Data

Begin by downloading the raw dataset from NOAA to your computer. After signing in to Wrangler, click the “Import Data” button on the upper-right side of the home screen, and import the dataset you downloaded. You can choose the “Import and Wrangle” button to immediately start working. If you choose “Import” instead or come back to this later, you can go to the flows icon and create a new flow, selecting the dataset you have imported.

b. Cleanup & Remove Whitespace (5 pts)

First, get rid of the columns 10-22 that indicate the wind radii plus an extra one from the header format; we won’t be using them. Then, remove the whitespace at the beginning of each column. Use a replace task across all columns that uses Trifacta’s text matching language to replace one or more spaces at the start of the string with an empty string. Note that you must enclose patterns in backticks!

c. Extract and Fill Hurricane Information (15 pts)

Create new columns that extract patterns that match the hurricane identifying information. There are two columns that we are interested in; the hurricane identifier (e.g. AL011851) and the name (e.g. OSCAR). Then, fill in that information down for all empty cells to the next valid identifier or name. Finally, delete all of the original rows with hurricane information. There may be multiple ways of doing this, but here is one approach:

1. Create extract rules that extract only cells from column2 and column3, respectively, that match the type of information we want to create a new column.
2. Fill in the missing values with the last valid value.
3. Delete those rows that match the pattern used to extract the information in step 1.

Now, you will be able to do a bit of cleanup by changing the first column’s type to an actual date/time column, changing column4 (with the L, I, entries) to a string column (click the ABC icon).

d. Replacing Missing Placeholders (10 pts)

Replace the UNNAMED values in the column that holds the hurricane names with an empty string (indicating a missing value). Once you make the change to the name column, you will see the number of categories change from 289 to 288. Also change -99 in column 8 (corresponding to maximum wind speed) and -999 in column 9 (corresponding to minimum pressure) to an empty string (also indicating a missing value).

e. Creating a Timestamp (10 pts)

Now create a new timestamp that includes both the date and time information in a single column. Because the time is formatted as HHMM with no delimiter, you will need to put the delimiter there to allow Trifacta to parse the date correctly. After converting this column to HH:MM, you can then merge the date column with the time column to create a new column for the timestamp.

f. [CS 680 Only] Latitude and Longitude Cleanup (10 pts)

Update the columns that show latitude (values ending in N) and longitude (values ending in W or E) to convert them to the numeric values used by most mapping software. In that software, W is translated to a negative longitude value, and E is a positive value. For example, 70.3W becomes -70.3 and 12.2E becomes 12.2. N and S are similar for latitude, but the dataset contains no S values so all latitude values are positive, and this task should be easier than longitude. For longitude, there are many approaches, some using multiple auxiliary columns. However, you may also consider writing a formula (similar to Excel) to convert this column. See the documentation for information on how to write custom functions using the provided ones.

g. Name Columns (5 pts)

Name the columns appropriately and delete extra columns. You may also wish to move some of the columns around. You may consider doing this earlier, but because of new columns, you may find yourself renaming multiple times. Anyway, make sure you clean things up by the end. The columns should be named:

['identifier', 'name', 'datetime', 'record_id', 'name','status','latitude','longitude','max_wind','min_pressure']

and be in this order. You can reorder the columns through the Move step if necessary. Make sure to turn in the recipe (.wrangle file) when you’re done. You can also check that your solution is correct by running the job and downloading the resulting CSV and comparing it against this file (see the Details section above for more information).

a. Reading Data and Naming Columns (10 pts)

Read the data from hurdat2.txt using panda’s read_csv method and name the columns accordingly. If you do this assignment in the same directory as you did in Part 1, you should still have the hurdat2.txt file locally. Alternatively, you can run the following code to download it:

import os
from urllib.request import urlretrieve
url = "https://www.nhc.noaa.gov/data/hurdat/hurdat2-1851-2018-120319.txt"
local_fname = "hurdat2.txt"
if not os.path.exists("hurdat2.txt"):
    urlretrieve(url, local_fname)

If you try to read the data using just the filename, you will get a strange result where each row has a really long label. Remember that the format looks like:

AL011851,            UNNAMED,     14,
18510625, 0000,  , HU, 28.0N,  94.8W,  80, -999, -999, -999, -999, -999, -999, -999, -999, -999, -999, -999, -999, -999,
18510625, 0600,  , HU, 28.0N,  95.4W,  80, -999, -999, -999, -999, -999, -999, -999, -999, -999, -999, -999, -999, -999,

alternating between hurricane identification information and specific tracking points. So (a) there is no header specifying what the columns store, and (b) there are different numbers of fields in each row depending on whether the row is hurricane identifier information or tracking points. Read in eight columns (you may ignore the rest of the columns again) and name them as follows:

['date','time','record_id','status','latitude','longitude','max_wind','min_pressure']

By default, pandas will parse rows like " UNNAMED, HU" and keep the leading whitespace so you get values like " UNNAMED" and " HU". We do not want this extra whitespace. Luckily, there is a read_csv option to remove it (find it in the docs). The output should look like:

b. Extract and Fill Identifiers (15 pts)

Clearly, we still have an issue in that the rows that are hurricane identifiers are mixed in with rows that have tracking information. We want this information to be attached to each tracking point row. This involves four steps:

1. Extract out those rows with the identifier and name, and put them in new columns named named identifier and name.
2. Fill in this information for the tracking points
3. Delete the rows that just contain identifier information
4. Move the new columns to the front of the data frame.

The final data should look like:

c. Replace Missing Value Placeholders (10 pts)

We wish to replace hurricane names of UNNAMED, the max_wind values of -99, and the minimum pressure values of -999 with NaN (np.nan).

d. Create a timestamp (10 pts)

Right now, we have two columns for date and time. This makes it difficult to calculate the amount of time between two different hurricane tracking points. If we convert them to a timestamp, such calculations are easy. To do this, we can use pandas’ to_datetime method. This method can convert from strings to timestamps. In our case, if we concatenate the date and time columns, and feed the concatenated series to to_datetime, things should work. Add the new column named as datetime and remove the old date and time columns. Move this column to appear after the name column.

e. [CS 680 Only] Convert Latitude and Longitude (10 pts)

In the original data, latitudes are specified like 28.0N or 34.5S while longitudes are 94.8W or 4.5E. Most systems store such values as negative or positive numbers where S latitudes and W longitudes are stored with a negative sign. We will use pandas methods to convert these values to that format. In this case, we want to end up with a float type for these columns at the end. There are different ways to do the conversion, but one method is to use string indexing (.str[<index-or-slice>]) to extract out the last character (N,E,S,W), convert that to a boolean (use a comparison), and then convert the boolean to a number via .astype(int). True values become 1 and false values become 0, but we will need 1 and -1. Multiply the rest of the value (converted to a float) by the indicator (-1 or 1) to obtain the desired result.