Practice Problems: Construct/Transform Tables (Pyret)

Solutions are found at the bottom of the page!

Problem 1: Student Grade Table

Create a table to store student information and perform basic operations on it.

Part A: Create the Table Create a table called students-table with the following data:

• Columns: name (String), age (Number), grade (String), gpa (Number)
• Rows:
  • "Alice", 18, "A", 3.8
  • "Bob", 17, "B", 3.2
  • "Charlie", 18, "A", 3.9
  • "Diana", 16, "C", 2.8

Part B: Extract Data Write expressions to:

1. Get Alice's GPA
2. Get all student names
3. Get the second student's complete record

Template:

# Part A: Create table
students-table =

# Part B: Extract data
alice-gpa = 
all-names = 
second-student =

---

Problem 2: Sales Data Transformation

Given a table of sales data, create new columns and filter the data.

Given Data:

sales-table = table: product :: String, quantity :: Number, unit-price :: Number
  row: "Laptop", 2, 999.99
  row: "Mouse", 10, 25.50
  row: "Keyboard", 5, 75.00
  row: "Monitor", 1, 299.99
end

Tasks:

1. Add a total-revenue column (quantity × unit-price)
2. Add a category column: "High" if unit-price ≥ 100, "Low" otherwise
3. Filter to show only items with quantity > 2
4. Transform unit-price column to apply a 10% discount

Requirements:

• Use build-column for adding new columns
• Use filter-with for filtering rows
• Use transform-column for modifying existing columns
• Use lam expressions appropriately

Template:

sales-table = table: product :: String, quantity :: Number, unit-price :: Number
  row: "Laptop", 2, 999.99
  row: "Mouse", 10, 25.50
  row: "Keyboard", 5, 75.00
  row: "Monitor", 1, 299.99
end

# Task 1: Add total-revenue column
sales-with-revenue =

# Task 2: Add category column  
sales-with-category =

# Task 3: Filter for quantity > 2
high-quantity-sales =

# Task 4: Apply 10% discount to unit-price
discounted-sales =

---

Problem 3: Weather Data Analysis

Process weather data to add calculated fields and perform filtering.

Setup Data:

weather-table = table: city :: String, temp-fahrenheit :: Number, humidity :: Number, wind-speed :: Number
  row: "Boston", 72, 65, 5
  row: "New York", 75, 70, 8
  row: "Chicago", 68, 60, 12
  row: "Miami", 85, 80, 3
  row: "Seattle", 65, 75, 7
end

Tasks:

1. Add a temp-celsius column (convert from Fahrenheit: C = (F - 32) × 5/9)
2. Add a comfort-level column: "Comfortable" if temperature is 68-78F and humidity < 70%, "Uncomfortable" otherwise
3. Add a wind-category column: "Calm" if wind-speed < 5, "Breezy" if 5-10, "Windy" if > 10
4. Filter to show only comfortable cities
5. Extract the cities with calm winds

Template:

weather-table = table: city :: String, temp-fahrenheit :: Number, humidity :: Number, wind-speed :: Number
  row: "Boston", 72, 65, 5
  row: "New York", 75, 70, 8
  row: "Chicago", 68, 60, 12
  row: "Miami", 85, 80, 3
  row: "Seattle", 65, 75, 7
end

# Task 1: Add temp-celsius column
weather-with-celsius =

# Task 2: Add comfort-level column
weather-with-comfort =

# Task 3: Add wind-category column
weather-with-wind-category =

# Task 4: Filter for comfortable cities
comfortable-cities =

# Task 5: Extract cities with calm winds
calm-wind-cities =

---

Solutions

Problem 1:

# Part A: Create table
students-table = table: name :: String, age :: Number, grade :: String, gpa :: Number
  row: "Alice", 18, "A", 3.8
  row: "Bob", 17, "B", 3.2
  row: "Charlie", 18, "A", 3.9
  row: "Diana", 16, "C", 2.8
end

# Part B: Extract data
alice-gpa = students-table.row-n(0)["gpa"]
all-names = students-table.get-column("name")
second-student = students-table.row-n(1)

Problem 2:

sales-table = table: product :: String, quantity :: Number, unit-price :: Number
  row: "Laptop", 2, 999.99
  row: "Mouse", 10, 25.50
  row: "Keyboard", 5, 75.00
  row: "Monitor", 1, 299.99
end

# Task 1: Add total-revenue column
sales-with-revenue = build-column(sales-table, "total-revenue",
  lam(row): row["quantity"] * row["unit-price"] end)

# Task 2: Add category column  
sales-with-category = build-column(sales-with-revenue, "category",
  lam(row): if row["unit-price"] >= 100: "High" else: "Low" end end)

# Task 3: Filter for quantity > 2
high-quantity-sales = filter-with(sales-with-category,
  lam(row): row["quantity"] > 2 end)

# Task 4: Apply 10% discount to unit-price
discounted-sales = transform-column(sales-table, "unit-price",
  lam(price): price * 0.9 end)

Problem 3:

weather-table = table: city :: String, temp-fahrenheit :: Number, humidity :: Number, wind-speed :: Number
  row: "Boston", 72, 65, 5
  row: "New York", 75, 70, 8
  row: "Chicago", 68, 60, 12
  row: "Miami", 85, 80, 3
  row: "Seattle", 65, 75, 7
end

# Task 1: Add temp-celsius column
weather-with-celsius = build-column(weather-table, "temp-celsius",
  lam(row): ((row["temp-fahrenheit"] - 32) * 5) / 9 end)

# Task 2: Add comfort-level column
weather-with-comfort = build-column(weather-with-celsius, "comfort-level",
  lam(row): 
    temp = row["temp-fahrenheit"]
    humidity = row["humidity"]
    if (temp >= 68) and (temp <= 78) and (humidity < 70):
      "Comfortable"
    else:
      "Uncomfortable"
    end
  end)

# Task 3: Add wind-category column
weather-with-wind-category = build-column(weather-with-comfort, "wind-category",
  lam(row):
    wind = row["wind-speed"]
    if wind < 5:
      "Calm"
    else if wind <= 10:
      "Breezy"
    else:
      "Windy"
    end
  end)

# Task 4: Filter for comfortable cities
comfortable-cities = filter-with(weather-with-wind-category,
  lam(row): row["comfort-level"] == "Comfortable" end)

# Task 5: Extract cities with calm winds
calm-wind-weather = filter-with(weather-with-wind-category,
  lam(row): row["wind-category"] == "Calm" end)
calm-wind-cities = calm-wind-weather.get-column("city")