Homework 4: Data Drama

Due: Wednesday, February 22, 2023 at 11:59PM ET.

Setup

• Create a file in Pyret called hw4-code.arr where you will write your Homework 4 solution.
• Create a file in your preferred word processor (Google Docs, Word, TextEdit, WordPad, etc) called hw4-written.pdf. This will hold your written work and graphs.
• Do not put your name anywhere in the file.
• Copy and paste the following starter code into hw4-code.arr:

Stencil Code and Resources

Make sure that your starter code has the line include data-source instead of import data-source as ds

Expand here for HW4 starter code

provide *
provide-types * 
include gdrive-sheets
include shared-gdrive("dcic-2021", "1wyQZj_L0qqV9Ekgr9au6RX2iqt2Ga8Ep")
include data-source

ALL-DATA = load-spreadsheet("1wWEI3nx64xkbpV9Ey06Ho_yvNQcWi8KpyQAtGlQmcfo")

CURRENCY-CONVERSION = load-table: from-c :: String, to-c :: String, conv-rate :: Number
​​source: ALL-DATA.sheet-by-name("currency", true)
end

# Uncomment for Part 2
#|
ART = load-table: id :: Number, cost :: Number, currency :: String, kind :: String
​​source: ALL-DATA.sheet-by-name("art", true)
end
|#

# Table and helper function for Part 4
titanic-raw-loader = 
load-spreadsheet("1ZqZWMY_p8rvv44_z7MaKJxLUI82oaOSkClwW057lr3Q")

TITANIC-RAW = load-table:
survived :: Number,
pclass :: Number,
raw-name :: String,
sex :: String,
age :: Number,
sib-sp :: Number,
par-chil :: Number,
fare :: Number
source: titanic-raw-loader.sheet-by-name("titanic", true)
end

fun first-name(full-name :: String) -> String:
​​doc: ```Extracts the first name from the given full name.
​​     The first name is defined as the part of the name that is between the first two spaces
​​     (removing parentheses if necessary)```
​​first-space = string-index-of(full-name, " ")
​​after-first = string-substring(full-name, first-space + 1, string-length(full-name))
​​# the second space is the first space of the substring after-first
​​second-space = string-index-of(after-first, " ")
​​# the first name will be before the space in after-first
​​between-spaces = string-substring(after-first, 0, second-space)
​​# get rid of first parenthesis if it exists
​​if string-char-at(between-spaces, 0) == "(":
​​  string-substring(between-spaces, 1, string-length(between-spaces))
​​else:
​​  between-spaces
​​end
where:
​​first-name("Mr. Wensley Z") is "Wensley"
​​first-name("Dr. Victor Frankenstein") is "Victor"
​​first-name("Ms. (Alice Potts) A. Bird") is "Alice"
end

Remember your resources!

• Pyret documentation
• How to create a plan
• CS0111 Table documentation (use this instead of the built-in Table documentation)
• Information on lambda expressions for Tables
• TA hours
• Edstem and the Homework 4 FAQ!
• Code clarity guide

Big Picture

This assignment has you practice a multi-stage data-analysis task. We're going to write functions that draw on data from two tables to perform computations. We'll also do multiple versions of a function, where each version adds more features, so you can see an example of building up solutions a bit at a time.

This assignment draws on all of the table lectures so far. For the planning tasks, we saw an example on 2/15, and you're also practicing it in lab 3. While we have covered everything that you need as of 2/15, the examples we do in class on 2/17 will give more practice with the concepts in this assignment.

The Assignment

Remember to use the CS0111 Table documentation (linked in Setup and Handin), not the official documentation. If your code uses constructs named sieve or extend (without functions), for example, you are using the wrong version and will not get credit.

The Marvel universe has superheroes all over the globe. As an international organization, Marvel has to deal with the complexities of exchange rates. In order to do this, the leaders at Marvel have to manipulate tables containing complicated data. In fact, they do this in other instances like evaluating potentially stolen art and investigating the Titanic. This homework will get you ready to join Marvel by using your table skills to uncover important information.

Important: as you start working on more complicated programming problems, we want you to practice planning out your tasks before you start coding. Taking the time to plan your approach to the programming tasks can potentially save hours of debugging time. We have given you some guidance on planning here. We also want to learn how you plan, so that we can help you and students like you plan more productively in the future. As you work through the assignment, submit your plans for the tasks that say "(Submit a plan for this task!)" to this Google form (we are not grading these harshly – we want to see that you did them, and we want to observe the approach you took).

Part 1: Prices Under Exchange Rates

Learning Goals:

• Practice planning programs
• Combine data across two tables to answer a question

Companies that do business internationally need to be able to quote prices in different currencies, following a table of exchange rates. This is the kind of situation where one would have two separate tables: one with the prices of items, and another with the exchange rates per country. For this problem, we'll be computing currency conversions for an online art store in which artists and clients may be in different countries.

The store has two tables, that are both in your starter code. One is for artwork, called ART: it tracks the unique id, cost and base currency for each piece, as well as what kind of artwork it is. It has some errors in the data, which we will clean up in the next part. It is commented out – ignore it for now.

table: id :: Number, cost :: Number, currency :: String, kind :: String

The other table is for currency conversion, called CURRENCY-CONVERSION. Each row holds the conversion rate (the multiplicative factor) to convert from the first currency (from-c) to the second (to-c).

table: from-c :: String, to-c :: String, conv-rate :: Number

For example, if a row of the Table looked like:

["EUR" "TND" 3.32]

we would convert from Euros (EUR) to Tunisian Dinars (TND) by multiplying the price in Euros by 3.32.

We will use these tables to develop several programs for selling art across currencies.

How do I see what the Tables in this assignment look like?

Unlike in Homework 3, these tables are not typed directly into Pyret, but rather pulled in from a Google Sheet. To see what these Tables look like, feel free to type the name of the Table in the definitions window and hit "Run", or hit "Run" first and then type the name in the interactions window and hit return.

For these tasks, you can use our CURRENCY-CONVERSION Table when writing tests.

Task 1: (Submit a plan for this task!) Write a function called exchange-price-1, which takes in as input a Table that has the same columns as CURRENCY-CONVERSION, a starting currency (a String), a desired currency (a String), and a price (a Number), and returns a Number of the price converted from the starting currency to the desired currency. For this exercise, you can assume that a row with the starting currency in the "from-c" column and the desired currency in the "to-c" column exists exactly once in the table. For now, do not worry about handling the case of converting a currency to itself.

How do the stated assumptions affect how you should write tests?

For this and every homework problem that has text like "you can assume that a row appears exactly once in the table," your test cases should obey this assumption. That is, for this problem, do not include tests like exchange-price-1(CURRENCY-CONVERSION, "JPY", "EUR"), since this test violates the assumption (there are no rows with "JPY" in the "from-c" column and "EUR" in the "to-c" column in this Table).

How do I write tests for decimal numbers?

Remember that you can use mathematical expressions in tests, such as 0.25 is 1/4.

Pyret also has an is-roughly, but it will be much easier to use the mathematical expression instead.

Task 2: (Submit a plan for this task!) Sometimes, we cannot make the assumption that we make in Task 1. For this task, assume that input currency conversion table may not list the conversion from A to B, but it may list the conversion from B to A. Write a function called exchange-price-2, which has the same inputs and output as exchange-price-1, but uses the direct conversion (as in Task 1) or the inverse conversion if necessary. (You compute the inverse of a number N using 1 / N, where / is division).

Details:

• You should also account for the case where the starting currency and the desired currency are the same, even if the Table does not contain the relevant row. (Hint: what would the conversion rate be in this case?)
• For cases where the starting currency and desired currency are different, you can assume that a row with one currency in the "to-c" and the other currency in the "from-c" column appears in the Table. If there are multiple matching rows, you can assume that they are consistent with each other (that is, if you have a Row with A in to-c and B in from-c, and a row with B in to-c and A in from-c, no matter if you choose to do the direct conversion or the inverse conversion, you will get approximately the same answer.)

Task 3: (Submit a plan for this task!) Create a function called plot-exchange-rates which takes in a Table that has the same columns as CURRENCY-CONVERSION and a starting currency (a String) and produces a bar chart of conversion rates from the given currency to all the other available currencies, that is, currencies for which there exists either a direct or an inverse conversion (or both) from the given currency.

Questions to ask yourself when making a plan

• How do you make sure that you only include the available currencies in your answer?
• Sometimes, the other currency will be in the "from-c" column, and other times, it will be in the "to-c" column. How can you make sure that the bar chart includes both of these cases?
• How do you create a plot that uses the inverse conversion if the direct conversion is not available?

Part 2: Data cleaning

This section deals with the ART Table. Most of the section is exploratory (you are meant to write short pieces of code and write down what you notice, but not turn in code), with one coding task that you turn in.

In the 2/13 lecture, we learned that there are multiple approaches to cleaning data: using sanitizers, manually cleaning up the source spreadsheet, filtering out rows that we recognize as malformed, and writing functions to clean up columns. One of your jobs in these tasks will be to determine which of these approaches makes the most sense for different kinds of messy data.

Task 4: Uncomment the block below the line that says # Uncomment for Part 2 by removing the surrounding #| and |#, and try to run the code. You will encounter one error. Using what you learned in class, change the load-table block of code so that this error no longer happens (Note: column A corresponds to the "id" column). Even though the error text will hint towards editing the Google sheet itself, you can and should resolve this in code. In a one-sentence comment below this change, in your own words, explain what the error was and why it happened.

Now, examine the ART Table in the Pyret window. For the data to be considered "clean," we would want:

• Every id in the "id" column to be a unique number
• Every price in the "cost" column to be between 100 and 99,999 (inclusive)
• Every value in the "currency" column to be a valid global currency, represented as a three-letter capitalized
• Every cell in the "kind" column to contain one of three values: "visual," "sculpture," or "other."

The next few tasks ask you to reflect on what it would mean to clean the existing data to conform to these expectations.

As you complete tasks 5-7 remember that, aside from scrolling through the columns to examine data, you can also graph the data or even sort it.

Task 5: One of the values in the "id" column appears twice, violating one of the requirements above. In a short comment that starts with "Part 2, Task 5:", name this value, and explain how to find it efficiently ("efficiently" here means faster than reading through all of the values in the column).

Task 6: In a short comment that starts with "Part 2, Task 6:", explain how the values in the "cost" column are not clean, using 1-2 specific examples from the data. Which of the approaches listed in the blue box at the top of this part would be your first choice for cleaning up the data, and why? Which would be your second choice, and what is an upside and a downside of this second choice?

Task 7: In a short comment that starts with "Part 2, Task 7:", explain how the values in the "currency" column are not clean, using 2-4 specific examples from the data. Which of the approaches listed in the blue box at the top of this part would be your first choice for cleaning up the data, and why? Which would be your second choice, and what is an upside and a downside of this second choice?

Task 8: Examine the "kind" column. We consider "visual" art to be art that has "paint" or "photo" in the "kind" description, and "sculpture" to be art that has "marble", "ceramic," or "wood" in the "kind" description (all case-insensitive). Everything else would be considered "other." Write a function called clean-kind that takes in a String (which you can assume will look like any of the values in the pre-cleaning "kind" column) and produces either "visual", "sculpture", or "other", based on the criteria named in this task. To test this task, use specific values from the given data.

Note: this task does not ask you to use transform-column to clean the ART Table, but you can (and should) certainly try out what it looks like to use clean-kind as an input to transform-column for this purpose.

Part 3: Art sales

Learning Goals:

• Practice writing functions that take tables as inputs
• Practice writing helper functions for use with tables
• Practice testing tables

Task 9: Since the given ART Table has data errors and is pretty large (and therefore unwieldy to use in test cases), come up with two small example Tables with the same columns as ART. Make sure that the Tables you create obey the criteria for what it means for the data to be clean, as given in Part 2. For the currency column, you can limit yourself to the following currencies (so that they match the CURRENCY-CONVERSION Table): CAD, CHF, CNY, EUR, JPY, MXN, USD, ZAR. You may end up revising these tables as you work through and test the following tasks – that's okay, but make the tables now as a starting point!

Task 10: (Submit a plan for this task!) Create a function called get-art-price that takes in an art ID (Number), a currency (String), a Table that has the same columns as CURRENCY-CONVERSION and a Table that has the same columns as ART and returns a Number for the price of the artwork with the given id, converted to the input currency. You can assume that exactly one row with the given id exist in the ART-like input table. If a direct or inverse conversion doesn't exist (as described in Task 2), this function should raise("unable to convert price").

Task 11: (Submit a plan for this task!) You want to find the priciest art in the Table, but you realize that this is hard to do when all of the artworks are in different currencies (for example, an artwork that costs 33,000 JPY is actually worth less than an artwork that costs 330 EUR, when they are converted to the same currency). You realize that you need to convert all of the artwork to the same currency in order to do this analysis. Create a function called priciest-art that takes in a target currency (String), a Table that has the same columns as CURRENCY-CONVERSION, and a Table that has the same columns as ART and returns the artwork ID (Number) that costs the most once all artworks have been converted to the target currency. You can assume that all conversions to the given currency exist (either as direct or inverse conversions) in the input currency conversion table (*note: this means that you will have to examine CURRENCY-CONVERSION to find such currencies when you are testing your function in order to obey this assumption).

Part 4: A Titanic Dataset

Learning Goals:

• Think about how table designs and code can embody assumptions (possibly limited) about people
• Practice cleaning up raw data for reliable processing

There are a few different versions of databases of passengers who sailed on the Titanic. (There is some ambiguity in this term, because the Titanic made a few stops even on its maiden voyage.) One such database is in your starter code, called TITANIC-RAW.

We're going to do an analysis of this dataset, using an assumption about names. We want to determine:

• The seven most popular first names of male passengers,
• The seven most popular first names of female passengers,

Relative to the dataset, we define the relevant concepts as follows:

• A man has sex field “male”, woman has it as “female” (we acknowledge that this binary excludes people. We return to this issue later in the exercise)
• A “first name” is the part of raw-name between the first and second spaces (excluding any parentheses that appear). For example, "Dr. Victor Frankenstein"'s first name would be "Victor," and "Ms. (Laura Jeanne) Reese Witherspoon"'s first name would be "Laura.""

We give you a helper function, first-name, which takes in a full name as a String and outputs the first name according to the criterion above.

Task 12: (Submit a plan for this task!) Using table functions and the first-name helper function, determine the seven most popular male and female names according to our first name definition. You do not have to print them out directly in the interactions window – in fact, the easiest way to find these names will likely be to do some computations on the raw data and then find an appropriate function to summarize some data in the resulting tables (hint: where in the Table documentation is "summarizing" discussed?) Don't overcomplicate this – for reference, our solution uses 5 lines of code.

In your hw4-written.pdf file:

• Write the 7 most common male first names in descending order
• Write the 7 most common female first names in descending order

Task 13: Does anything surprise you about your findings? Expand TITANIC-RAW in your Pyret window and observe how names were recorded in the dataset. In your hw4-written.pdf file, describe any observations you have made in this exploration. Before you started this task, did you believe that the definition of first name was reasonable (if no, briefly state why not). Did this change after your exploration?

Task 14: Read the following two short articles and include your answers to the questions below in hw4-written.pdf

• Falsehoods Programmers Believe About Names.
• Gender Storage in Databases

Questions:

• Discuss what assumptions were made about names in the Titanic dataset that we gave you.
• Do the articles help explain what might have led to any of the interesting observations you made about the resulting data?

Check Block (Autograder Compatibility)

Hit "Run", then paste this check block into the interactions window. All the tests should pass: if they don't, this means you have a typo in a function header and the autograder won't run on your code!

​​check "functions exist and have correct inputs": 
​​  CLEAN-ART = table: id, cost, currency, kind
​​    row: 1, 1, "USD", "other"
​​  end
​​  is-number(exchange-price-1(CURRENCY-CONVERSION, "USD", "EUR", 1)) is true
​​  is-number(exchange-price-2(CURRENCY-CONVERSION, "USD", "EUR", 1)) is true
​​  is-string(clean-kind("")) is true
​​  is-number(get-art-price(1, "USD", CURRENCY-CONVERSION, CLEAN-ART)) is true
​​  is-number(priciest-art("USD", CURRENCY-CONVERSION, CLEAN-ART)) is true
​​end

Handin

• Download your code file and make sure it is called hw4-code.arr.
• Your written answers should be clearly marked with corresponding task numbers. Download or save your written file and make sure it is a PDF called hw4-written.pdf. If your written file is not a PDF, we might not be able to grade it.
• Hand in hw4-code.arr and hw4-written.pdf on Gradescope.
• Make sure you have submitted plans for tasks 1, 2, 3, 10, 11, and 12 to this Google form.

Theme Song

#thatPOWER by will.i.am ft. Justin Bieber

---

Brown University CSCI 0111 (Spring 2023)