Class summary: Working With Tables

Copyright (c) 2017 Kathi Fisler

1 Loading Tables from Google Sheets

Go back to the gradebook from last class:

This is a smaller gradebook that is good for experimenting with and testing programs. A realistic gradebook might be maintained in a spreadsheet. Here’s an example of how to load a gradebook from a Google Sheet:

The load-spreadsheet function is used to tell Pyret that you want to use data from a Google Sheet. The string passed as an argument is the Google Document ID (the sequence of numbers and letters in the document URL).

One more step is needed to get the google sheet data loaded into a table. For that, we use the load-table command. The first line names the columns that will be brought in from the sheet. The second line (source) says to load the sheet named "Sheet1" from imported-grades. The true says that the sheet has a header row (that will not be loaded).

2 Computing Course Grades

Let’s extend the gradebook with exam averages and letter grades based on the averages, then sort the gradebook by letter grades (in ascending order). Our grading scheme will award "A" for grades from 88 to 100, "B" for grades at least 76 but below 88, "C" for grades at least 60 but below 76, and "NC" otherwise.

Before writing code, it helps to list out the tasks that a problem requires. This will help you decide which functions to write. What are the tasks in this problem, and what ordering dependencies are there?

• Compute average of exams

• Add averages to table

• Convert averages to letter grades

• Add letter grades to table

• Sort the gradebook by letter grades

We did the first two tasks last class, so let’s focus on the third one. Here’s a version of the code

Some of you originally wrote this function to take a number as input:

Part of the homework will ask you to reflect on the strengths and weaknesses of each version.

Now, we want to use grade-to-letter2 to extend the gradebook. Following what we did last class, you might try:

But this requires gradebook to have a column named "avg" (which it doesn’t), so we really need to write:

(we wrote exam-avg last class).

3 Pizza Tables

For our other example, we converted our pizza program from last class to use a table of toppings. Here’s such a table:

Now, rewrite the pizza-cost-with-tip function to extract toppings prices from this table. This program will need the following tasks:

• Extract the price of a topping

• Add the topping price to the base pizza price

• Add the tip to the price

Let’s focus on the first task. For the moment, assume that the topping we want to add is "onion". How might we write that computation?

This code uses filter-by to locate the row for "onion". To get the value in the "price" cell for that row, we need to extract the onion-row from the table (which we do with row-n(0)), then lookup the price column from the row (with ["price"] as we have done before).

Before row-n, we are writing a period. Pyret has operators for getting information from tables. Given a table (the result of filter-by), we use the period to tell Pyret that we want to use an extraction operator.

3.1 Getting the price of any topping

This code is great for getting the price of onions, but what about pepperoni? The expression for that would look like:

Notice that this is almost exactly the same code as before. In the spirit of not writing the same computation multiple times, we should create a function to combine the common computation (as we have done before). But here, the common computation includes defining a function. How do we handle that???

We handle it as we have done before! We make a function that takes the changing content as a parameter, putting the common code inside the function:

Here we introduced the parameter want-topping for the name/string for the topping. We also renamed the common function to has-topping instead of having it continue to refer to a specific ingredient in its name.

How does this work? Think back to substitution: if we call topping-price("onion"), then want-topping is replaced with "onion", even within the inner function. This gets us back the original code we wrote for onions. Similar for pepperoni.

3.2 What about other toppings?

What happens if we ask for topping-price("egg")? Pyret will give an error about not having row-n. The problem is that the filter-by in this case returns an empty table (which by definition has no rows). So we need to extend this code to check whether there are rows in the table before we try to get a row out.

We’ll do this by running the filter, then asking whether the table has any rows before trying to get one. This uses a new operator on tables called length:

What should we return if the topping isn’t found? We can’t return a string like "No Such Topping" because this function is supposed to return a number. What we can do, however, is use a Pyret construct called raise to report that there’s a problem with the input that prevents us from returning a reasonable answer:

When you run this new version, the "No such topping" message shows up in a box that looks like what Pyret uses to report errors. Pyret is not returning this string. It is reporting a problem, using the "No such topping" string as an error message (that you specified in your program).