Unit 9Extending Tables
Unit Overview

Students learn to extend tables with new columns, computed from a previous table’s data.

Product Outcomes:
Length: 105 minutes
Glossary:
• extend: A table query that creates a table with new column(s), whose entries are computed with an expression

• predictor: a function which, given a value from one data set, tries to predict a related value in a different data set

• r-squared: A value used to measure how well a predictor function performs

Materials:
Preparation:
 Types Functions Number +, -, *, /, num-sqrt, num-sqr, mean, median, mode, modes String n/a Image draw-plot, draw-plots Boolean ==, <>, <, >, <=, >= Series function-plot, scatter-plot, bar-chart, pie-chart, freq-bar-chart, histogram Table extract, select, order, sieve, extend

Introduction

Overview

Learning Objectives

Evidence Statements

Product Outcomes

Materials

Preparation

Introduction (Time 5 minutes)

• A while back, we found that a nation’s total GDP explained about 30% of the variation in median life expectancy. In other words, our predictor wasn’t that great. That’s not a surprise: more populous countries will usually have a higher GDP! We’re just comparing big countries to small ones, not rich countries to poor ones. Do you think that the GDP per person - which is a measure of individual wealth - would be a better indicator?

Turn to Page 36 and take two minutes to write down what you think.

• We have our countries table, but that only tells us the total GDP. We need to find a way to add a new column to the table, which is calculated based on other columns. By the end of this lesson, you’ll know how to do just that.

Extending Tables

Overview

Learning Objectives

Evidence Statements

Product Outcomes

Materials

Preparation

Extending Tables (Time 10 minutes)

• Turn to Page 37 in your workbook, and complete the exercise.

In this activity, students manually compute entries to new columns, based on values in a table’s columns that area already filled in.

• In this workbook exercise, you’ve done something very different from what we’ve learned so far: you computed a new column based on existing data in the table. In the sock exercise, this means creating a new column called price-per-sock, and filling in a new value for each row, based on the price and socks values in that row. Creating a new table with an additional column is called extending a table.

Extend in Pyret

Overview

Learning Objectives

Evidence Statements

Product Outcomes

Materials

Preparation

Extend in Pyret (Time 15 minutes)

• Open the Unit 9 Starter File, Save a Copy, and Run the program.

• What does basketball evaluate to?

• What does basketball-extended evaluate to?

• extend produces a table with a new column, containing data that is computed by an expression over each row. It is similar sieve in that it applies this expression to each row, but is different because it CREATES data instead of removing it.

• Let’s examine the code used to create the basketball-extended table:

• The extend keyword comes first, since we might want to use our new columns for sieving, sorting or selecting later.

• It is followed by the table being extended, in this case basketball.

• Then, the using keyword, in the same place as the sieve commands This tells Pyret which columns we will use to extend the table.

• Next are the names of the columns being used, followed by a colon :

• The expression on the next line is very special:

We give our new column the name total, followed by a colon :, then an expression to compute the value of each entry in the column. This expression is the sum of a player’s points in each game.s

• Finally, the query ends like all other table queries; with the end keyword.

We encourage you to question the class individually on what they think is the purpose of each component of the extend query. Ask "what do the names after the using keyword are", etc.

• In your Definitions Area, follow the directions in the code for the first exercise, to extend the stores table in Pyret. You should create a column called price-per-sock.

This could also be a group activity, where you are live coding and prompting the class to help you write the correct definition.

• Suppose we want to find out which menu items have the highest amount of sodium per gram. Use the sodium and serving-size columns to write an extend query that will answer this question.

Body Building

Overview

Learning Objectives

Evidence Statements

Product Outcomes

Materials

Preparation

Body Building (Time 15 minutes)

• Now let’s get some practice using extend queries with our Table Plans! Turn to Page 38, and read the word problem carefully.
• On a sheet of scrap paper, sketch a sample Start and End Table to make sure you have a clear picture of what you need to do.

• Now start your table plan: do you need to add any new columns to the table? Yes! We need to add a protein-per-gram column, which is computed using the protein and serving-size columns. Write the extend query to do this.

• Do we need to get rid of any rows? Yes! We want to keep only the rows that have a protein-per-gram higher than .12. Write the sieve query to do this.

• Do the rows need to be any particular order? It’s not clear from the word problem, so we can choose: do we skip this step, or should we order them so your aunt can immediately see which food is best?

• Do we have any unecessary columns? Yes! All we care about are the name, calories, and protein-per-gram columns. Write the select query to do this.

• Once you’re done with your Table Plan, program your queries in the Definitions Area, under "4. Body Building".

Debrief and review with the class.

• On page Page 45, write down what an order query is for.

Have some student volunteer what they wrote.

Term-Length

Overview

Learning Objectives

Evidence Statements

Product Outcomes

Materials

Preparation

Term-Length (Time 15 minutes)

• How common is it for a president to serve two terms? We’d like to see a histogram comparing 1-term vs. 2-term presidents. The length of a term is defined by $year_{ended} - year_{started}$.
• Turn to page Page 39, and read the word problem carefully.

• Complete the Table Plan to create the table you’ll need.

• Extract the columns you’ll need to create the histogram

• Define the histogram series, and then plot the histogram. If you’ve forgotten how to make a historgram series, a plot, or how to draw the plot, don’t forget to refer back to your contracts page!

Economic Indicators

Overview

Learning Objectives

Evidence Statements

Product Outcomes

Materials

Preparation

Economic Indicators (Time 30 minutes)

• Economics data scientists use something called a gdp-per-capita to compare countries’ productivity. This is calculated by dividing a country’s GDP by population. This estimates how productive the average citizen of a particular country is:

Have students calculate this by hand for one or two countries in the countries table.

• The United Nations has hired our class to come up with a report on Africa’s economy. They want us to investigate whether population size or per-capita GDP is the more significant predictor for median life expectancy.
• Turn to page Page 40, and read the word problem carefully.

• Complete the Table Plan to create the table you’ll need.

• Extract the columns you’ll need to create the scatterplots and predictor functions for both relationships: "population v. life expectancy" and "per-capita-GDP v. life expectancy".

• Define the two scatterplot series, and then plot them both.

• Define your predictor functions and r-squared values. Plot your predictor functions on top of your scatterplots, and use the r-squared values to determine which predictor better explains median life expectancy.

• The UN also wants following information:

• A bar chart of the gdp-per-capita for every country in Africa.

• The mean and median gdp-per-capita values for countries in Africa.

Closing

Overview

Learning Objectives

Evidence Statements

Product Outcomes

Materials

Preparation

Closing (Time 15 minutes)

• Open the Extend Syntax Errors file, and see if you can fix all the bugs you find. Once you’re done, uncomment each query by removing the hash sign (#) and click Run.

• Take a few minutes and record your findings on Page 36. Do your findings match your hypothesis? What new questions does this raise?

• You now have all the tools you need to complete your analysis for your final project! Open your Project File and Report, and finish the remaining questions. Be sure to explain why you chose to remove any rows with sieve or add any columns with extend, and to add the r-squared value for any correlations you find through linear regression.