---
# System prepended metadata

title: 'Lab 3: Tables'

---

# Lab 3: Tables
It’s time to do our first multi-step data analysis!

[FiveThirtyEight](https://en.wikipedia.org/wiki/FiveThirtyEight) conducted a survey in which tens of thousands of people were asked to choose between two candies; from the survey’s responses, they computed the winning percentage of each candy. Then, they compiled a data-set with Number attributes like the winning percentage, relative price, and sugar percentage, and also Boolean attributes such as chocolate, fruity, caramel, and hard. This data has been published in a FiveThirtyEight article called [“The Ultimate Halloween Candy Power Ranking,"](https://fivethirtyeight.com/features/the-ultimate-halloween-candy-power-ranking/) which is definitely worth a read after the lab.

In this lab, you’ll be looking at the **relationships between these columns**.

## Setup
First, include this code at the top of your program:

```python
provide *
provide-types *

include tables
include shared-gdrive("dcic-2021", "1wyQZj_L0qqV9Ekgr9au6RX2iqt2Ga8Ep")
include gdrive-sheets

# spreadsheet id from Google Sheets
ssid = "1XzeWZToT-lqPFVpp-RZLimdoGdGaTcVQZ_8VbDbAkOc"
data-sheet = load-spreadsheet(ssid)
candy-data = 
  load-table: name, chocolate, fruity, caramel, nutty, nougat, crisped-rice, 
    hard, bar, pluribus, sugar-percent, price-percent, win-percent
    source: data-sheet.sheet-by-name("candy-data", true)
  end
```

This code loads a Pyret table from a Google Sheet. Press the “Run” button in the top right corner to process the code in the definitions window. Then type candy-data in the interactions window to see the data as a table.

You’ll want to refer to the [CS111 Pyret Tables Documentation](https://hackmd.io/@cs111/table) for this lab (not the built-in Pyret documentation).

Suggestion: Use a comment-line as shown below to separate the code for each part. This can help you navigate your code file.

```
#-----------------------------------
```

## Part 1: Basic Table Filtering
Let’s use the powers of filtering to learn more from our candy data. Recall the `filter-with` table operation from class: it takes a function from `Row` to `Boolean` as an input, and uses that function to determine which rows to put in the output table.

**Task**: We want to know which candies have the most sugar. Write an expression in the definitions window that produces a table containing only the candies where `sugar-percent` is greater than 0.75.

**Task**: Now that we know how to satisfy our sweet tooth, write an expression in the definitions window that produces a table containing only the candies where `price-percent` is greater than 0.90.

**Task**: How many of the candies have chocolate? Write an expression in the definitions window that outputs this number in the interactions window. You can get the length of a table by writing `<table>.length()` where `<table>` is the name of a table (or an expression that evaluates to a table).


## Part 2: Combining multiple table operations

Now that you’ve practiced basic table operations, let’s practice computations that involve multiple table operations (or multiple uses of the same table operations). This is also a good opportunity to practice planning programs.

### Practice with Planning

The author of the article linked above was interested in combinations of ingredients. Let’s say we want to figure out what proportion (percentage) of the candies with chocolate also have caramel. Before we try to write the code, let’s step back and *plan* what we need to do.

We’ve seen planning (and to-do lists) in lecture. These next tasks are practice with that.

Task: Discuss with your partner what computations you will need for this problem: will you need to filter tables? build columns? Compute lengths of tables? Sketch out a to-do list on paper.

Now, let’s practice with the blocks-based planning tool that Kathi showed in lecture.

Task: Click on [this link](https://snap.berkeley.edu/snap/snap.html#present:Username=kfisler&ProjectName=table-plans-lecture) to start the tool. Drag in blocks from the left side that correspond to your todo list and put them in order. Create an account on Snap if you want it to store your files, otherwise use “export program” to save them to your computer.

Note that Snap does **NOT** save files automatically. Be sure to save while you are working.

*Make sure you know how to export a plan to xml before you leave (using “export program” under the file menu), since you will need that for homework.*

<details class="part" data-startline="78" data-endline="82" data-position="4710" data-size="266" open=""><summary>Once you’ve brought in some blocks, check here for solutions</summary>
<p data-position="4783" data-size="0"><span data-position="4783" data-size="133">There are two solutions here to show some variation on how you might do this. One is above the box with the comment and one is below.</span></p>
<p data-position="4918" data-size="0"><img src="https://i.imgur.com/3gYtJEd.png" alt="Sample block plans" class="" data-position="4918" data-size="54" loading="lazy"></p>
</details>

## CHECKPOINT: Call over the teacher once you reach this point.

**Task**: Using your plan as a guide, write an expression (not a function) to compute the proportion of chocolate candies that also have caramel.

**Task**: Now check for the proportion of chocolate candies that are nutty. Feel free to copy and adapt code that you’ve already written. How much did you have to change?

Expecting that we’ll want to compare several proportions of chocolate candies, let’s write a function that generalizes the expressions from the previous two tasks.

This function will take in a `String` representing the name of the ingredient being paired with chocolate and a `Table` that is only candies that have the chocolate attribute. This function returns a number (which is the proportion of candies that have the given ingredient out of the candies that have chocolate).

**Task**: Write a function `candy-with-attr` that takes a table of candies with chocolate and a string naming an ingredient. It returns the proportion of chocolate candies that also have the given ingredient. Figure out how to reuse your work on the previous two problems to help with this one. The function declaration should look like this:

```python
candy-with-attr(table :: Table, attr :: String)-> Number:
```

Experiment with your function in the interactions window (to make sure you know how to use this function).

## CHECKPOINT: Call over the teacher once you reach this point.

## Part 3: Adding columns for analysis

For our next analysis, let’s figure out the highest winning percentage of a candy that is both fruity and hard, but not a pluribus (pluribus means that there are multiple candies in a packet, e.g. Skittles, M&M’s, and Smarties)

**Task**: Develop a plan for this program in the blocks-planning tool (use the same area, just put your plan further down in the same blocks file. Consider the various table operations we’ve seen, including `filter-with`, `length`, `build-column`, `order-by`, and `row-n`. *Hint*: if you need a piece of information that isn’t already in the table, considering computing a new column.

**Task**: Extend the candy table with a `Boolean` column that indicates whether a candy is both fruity and hard, but not a pluribus.

**Task**: Finish executing your plan, ending with an expression that produces the highest winning percentage candy that is fruity, hard, and not a pluribus.

**Task**: Write another expression to compute the average winning percentage among these fruity-hard-single candies (use the `mean` function in our tables documentation).

## CHECKPOINT: Call over the teacher once you reach this point.

## Part 4: Scatterplots and pie charts

What’s the relationship between sugar and winning percentage? Do these two attributes seem correlated? One way to gain intuition on this is to create a scatterplot that puts one attribute on each axis.

**Task**: The tables [documentation](https://hackmd.io/@cs111/table) includes a scatter-plot function. Look at the documentation and try to figure out how to use it to generate a scatterplot of sugar versus winning percentage (it does not matter which variable goes on each axis). Write an expression in the definitions window that creates the scatterplot. In a comment, summarize the relationship in a sentence or two.

**Task**: Now, go back to the proportions of chocolate questions from Part 1. Using the `pie-chart` function in the tables documentation, generate a chart that shows the proportions of other ingredients in candies with chocolate.

## Part 5: Comparing Attributes

***This is a discussion and planning question – you are NOT being asked to write code to do this analysis. In a comment, write a few sentences to summarize your ideas.***

Imagine that we want to understand the relative frequencies of the attributes in the column names. Specifically, we want to be able to choose two attributes (column names) and find out which is more frequent within the entire table.

**Task**: Using the block-planning tool to develop a plan for this function. **Do not write the function, just plan it.**

**Task**: Discuss with your partner how you might go about testing this function. Write a comment describing your testing plan (what tables would you make, what rows would you need, etc).

**Task**: Download the XML file from the blocks tool. Upload it and your code file for this lab to the Lab 3 area on Google Classroom (I am not grading them, but want to make sure you know how to retrieve and upload these kinds of files before the homework).

## CHECKPOINT: Call over the teacher once you reach this point.
