Lab 3: Tables

You've been recruited by the PEZ company to do research on their competition. To do this research, the company gives you their research datasets for inspiration and analysis!

Setup

In this lab you’re going to be working with tables!

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," which is definitely worth a read after the lab.

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

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

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 for this lab (not the built-in Pyret documentation).

Part 1: Filtering

Let's use the powers of filtering to learn more from our candy data.

Suggestion: For each section, separate it from the next part by pasting this line below it.

#-----------------------------------

1.1: Sugar Rush

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.

Hint: Take a look at the filter-with function, and remember that functions can be passed as inputs to other functions. Call over a TA if you want help using this structure.

1.2: Bougie

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.

1.3: Chocolate

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).

1.4: Chocolate and caramel

Of the candies that have chocolate, what proportion also have caramel?

Write an expression in the definitions window that outputs this proportion in the interactions window.

1.5: Chocolate and nutty

Of the candies that have chocolate, what proportion are also nutty?

Write an expression in the definitions window that outputs this proportion in the interactions window.

CHECKPOINT: Call over a TA once you reach this point.

---

1.6: Chocolate and anything

What attribute is paired most frequently with chocolate?

To help answer this question, write a function that generalizes the expressions from 1.4 and 1.5. 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).

The function declaration should look something like this:

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

Look for commonalities across the code you wrote for caramel (1.4) and nutty (1.5).

Compare the results of your function on the inputs “fruity,” “nutty,” and “caramel.” You don’t have to write code for this comparison – just use your new function to compute all three proportions and compare them manually.

---

CHECKPOINT: Call over a TA once you reach this point.

---

Part 2: Building columns and analyzing them

2.1: New column

Build a column of Boolean values that indicates whether a candy is both fruity and hard, but not a pluribus. This should produce a new table with an added column. Write an expression in the definitions window that uses this new column to compute how many candies meet this condition.

(Pluribus means that there are multiple candies in a packet, ex: Skittles, M&M’s, and Smarties)

Hint: Take a look at the build-column function. Call over a TA if you want help using this.

2.2: Maximum

Of the candies for which this Boolean is true (fruity and hard, but not a pluribus), which has the highest winning percentage?

Hint: This requires the use of the order-by function, in addition to the row-n and filter-with functions.

An example of the row-n function to take the fifth row of a table:

table.row-n(4)

2.3: Mean

Of the candies for which this Boolean is true, what’s the average winning percentage?

Hint: This requires the use of the mean function.

---

CHECKPOINT: Call over a TA once you reach this point.

---

3: Scatterplot

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.

The tables documentation 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.

4: Comparing Attributes

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

The CEO at PEZ wants to understand the relative frequencies of the attributes in the table across the collection of candies studied. They want a function where they can give you names of two attributes (column names in the dataset), and you tell them which is more frequent (your function can return "same" if the two attributes are in the same number of candies). Design this function.

Discuss with your partner how you might go about testing this function. Write a comment describing your testing plan.

Now imagine that the analysts want to see the relative frequencies across all of the attributes. How would you want to report that information, and what would be needed to produce that report from the given dataset? You can think about the functions in the documentation, or you can think about reorganizing the data in the table to make it easier.

---

CHECKPOINT: Call over a TA once you reach this point.

---

5: Exploration (if you have time)

Now think about questions you have about this data, or come up with interesting questions to ask. Investigate (by writing and running expressions), and write a couple sentences in a comment about what you found.

Takeaways

This lab has mostly been about getting you comfortable working with tabular data and practicing some common operators on tables. It also gets you thinking about our course's focus on data: what patterns of manipulating data do we often use in computations? How does the organization of our data impact our ability to answer these questions?

Here, we see that filtering, ordering, and summarizing data are some of the key operations. So far we’ve only looked at these operations on tabular data, but these same building blocks will arise many times through this course. When you have a computation to perform around data, you should start by thinking through what combinations of filtering, sorting and summarizing will help you compute your answer.

Formula Cracked!

Great job! For all of your hardwork you've been rewarded with a lifetime supply of PEZ candy.