Assignment 3

a. Load Data

Begin by downloading the raw dataset 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. Convert Medium (10 pts)

The Medium column is written with both capitalized words and lowercase words. Standardize this by converting this to all uppercase. Also strip any leading or trailing whitespace. You can verify the need for the whitespace removal by finding the number of unique values for the column before and after this change. Replace the original Medium column with your cleaned column.

c. Fix Problematic Accession Years (10 pts)

There are only a few problematic accession year, and Trifacta should allow you to see these by clicking the red bar in the column. Based on these values, create a rule to extract only the year, making sure to remove the original column and rename the new column AccessionYear.

d. Fix Object End Dates (15 pts)

There are certain years where the Object End Date is before the Object Begin Date. These fields should correspond with the entries in the Object Date field. In some cases, the end date is 0 which may be a placeholder for either an unknown or missing value or the same year. In other cases, it seems like something went wrong with the translation specifically with dates that begin in 664 B.C. (-664). We will try to fix the second case, and set the others to null values to indicate that there is some issue with the object end date. To accomplish this, craft a way to extract the end date from the Object Date column but only when we see a dash (either - or –) and the “B.C.” ending since we know that is where things are problematic. Now, coalesce the new column with the old column, making sure that the new column takes precedence. Finally, fill those remaining values where the Object End Date is less than Object Begin Date with null. Finally, make sure to remove the original column and rename the cleaned column as Object End Date.

e. Save Recipe

Save your recipe for this part as “a3-part1.wrangle” by clicking the recipe view, and then the gear icon and selecting “Download Recipe as wrangle”. You can also run your recipe on the dataset and download the resulting csv file to compare your results with those from Part 3.

a. Transform Tags (15 pts)

Create a new recipe that extracts the Object ID and Tags columns so that we have a new table from Object ID to each individual tag. To create a new recipe, go back to the flow, and create a new recipe (either off of the original data or the recipe from Part 1). Then select the Object ID and Tags columns and create a new table or delete all the other columns. Now, we want to split each string in the Tags column into an array of values based on the | delimiter. Note that this is a special character is both Wrangler’s language and regular expressions and therefor should be escaped with a backslash. Finally, create a new row for each tag, removing any rows that have no tags. Turn in this recipe as “a3-part2a.wrangle”.

b. [CSCI 680 Only] Transform Artists (20 pts)

Create a third recipe that extracts artist information and creates a table with the artist information split into rows for each of the objects in the collection dataset. For this part, we are interested in four columns: Artist Role, Artist Display Name, Artist Begin Date, and Artist End Date. Like Part a, we can split the individual columns into arrays. However, in contrast to Part a, we need to keep the correspondences between the extracted elements. In other words, the first element in the Artist Role column should be paired with the first element in the Artist Display Name column. The final table should retain Object ID. Here, the ARRAYZIP function (search for transformation) will be useful. In addition, we can extract elements to columns, but then we need to split the arrays back into individual columns again and rename them appropriately. Turn in this recipe as “a3-part2b.wrangle”.

Hints

• Use the magnifying glass icon to search for particular functions
• You can unnest arrays into columns, but will need to use array indexing to extract out each column of elements (e.g. [0]); you can do multiple of these extractions in one step, though.

a. Load Data

Use panda’s read_csv method to load the data. Be aware that you may improve type inference if you are able to set low_memory=False. Store the loaded data frame into a variable for use throughout the assignment.

b. Convert Medium (10 pts)

The Medium column is written with both capitalized words and lowercase words. Standardize this by converting this to all uppercase. Also strip any leading or trailing whitespace. You can verify the need for the whitespace removal by finding the number of unique values for the column before and after this change. Replace the original Medium column with your cleaned column.

c. Fix Problematic Accession Years (10 pts)

There are only a few problematic accession year, and Trifacta should allow you to see these by clicking the red bar in the column. Based on these values, create a rule to extract only the year, making sure to remove the original column and rename the new column AccessionYear.

d. Fix Object End Dates (15 pts)

There are certain years where the Object End Date is before the Object Begin Date. Examine these by writing a boolean query. These fields should correspond with the entries in the Object Date field. In some cases, the end date is 0 which may be a placeholder for either an unknown or missing value or the same year. In other cases, it seems like something went wrong with the translation specifically with dates that begin in 664 B.C. (-664). We will try to fix the second case, and set the others to null values to indicate that there is some issue with the object end date. To accomplish this, craft a way to extract the end date from the Object Date column but only when we see a dash (either - or –) and the “B.C.” ending since we know that is where things are problematic. (Consider a capturing regular expression for this.) Now, replace the values in the Object End Date column with such dates. Finally, fill the remaining dates where the end date is before the begin date with NaN values to indicate we don’t know that value.

a. Transform Tags (15 pts)

Create a new dataframe with one row per tag and the corresponding Object ID. Here, we can split the Tags column using pandas string methods, and then the explode method to move each of those entries to a new row. Make sure that the Object ID column is still there and correct after running explode.

b. [CSCI 680 Only] Transform Artists (20 pts)

Create a third dataframe that contains artist information split into rows for each of the objects in the collection dataset. This requires less manual effort than in Wrangler because we can loop over the columns. Like Part a, we can split the individual columns into lists. However, pandas will not allow you to explode multiple columns at once; it will allow you to create a new data frame from all of the individual exploded columns. Again, we are interested in four columns: Artist Role, Artist Display Name, Artist Begin Date, and Artist End Date, and need to keep the correspondences between the extracted elements. In other words, the first element in the Artist Role column should be paired with the first element in the Artist Display Name column. The final table should retain Object ID.

Hints

• Think about whether you can use loops or comprehensions to make your code more robust.