Skip to main content

Day 8 - Adding columns

Skills: 1, 2​

Outcomes: 5​

Pre-reading: 4.1.4.3, 4.1.4.4​

Intro (15 mins)​

  • Let's say we were designing a fantasy game, and had a table of items that were near the current player. 
      items = table: item :: String, x-coordinate :: Number, y-coordinate :: Number
        row: "Sword of Dawn",           23,  -87
        row: "Healing Potion",         -45,   12
        row: "Dragon Shield",           78,  -56
        row: "Magic Staff",             -9,   64
        row: "Elixir of Strength",      51,  -33
        row: "Cloak of Invisibility",  -66,    5
        row: "Ring of Fire",            38,  -92
        row: "Boots of Swiftness",     -17,   49
        row: "Amulet of Protection",    82,  -74
        row: "Orb of Wisdom",          -29,  -21
      end
  • If we wanted to know the distance from the player to each item because, for example, if the distance were below a certain amount the player was able to pick the item up, we might want to add a new column, to create a table with four columns. We can do this using build-column.
  • First, we need a function that takes a row and calculates the value for the new column. But since the numbers we are producing are usually not exact, we need to use a different comparison operator for our tests, is-roughly 
    fun calc-distance(r :: Row) -> Number:
      doc: "does distance to origin from fields 'x-coordinate' and 'y-coordinate'"
      num-sqrt(num-sqr(r["x-coordinate"]) + num-sqr(r["y-coordinate"]))
    where:
      calc-distance(items.row-n(0)) is-roughly num-sqrt(num-sqr(23) + num-sqr(-87))
      calc-distance(items.row-n(3)) is-roughly num-sqrt(num-sqr(-9) + num-sqr(64))
    end
  • Now we can add a column to create a new table: 
    items-with-dist = build-column(items, "distance", calc-distance)
  • Let's say, instead, we wanted to move the player one unit of distance in the x direction. This would mean that the origin shifts by 1, and so the x-coordinate field in the table should have 1 subtracted from it, since items that were to the right are now closer, and the left ones (the negative coordinates) are now further.
  • We can do this by using transform-column, which takes a function that transforms a single column, here the value of the x-coordinate. So let's write one: 
    fun subtract-1(n :: Number) -> Number:
      doc: "subtracts 1 from input"
      n - 1
    where:
      subtract-1(10) is 9
      subtract-1(0) is -1
      subtract-1(-3.5) is -4.5
    end
  • Now we can construct a shifted table: 
    moved-items = transform-column(items, "x-coordinate", subtract-1)

Class Exercise (40 mins)​

  • Imagine all the items were pulled closer to the player by 10%. Create a new table that has new x & y coordinates for all the items.
  • Extract the name of the item that is closest to the player.
  • You want to "obfuscate" the list of items, displaying, rather than the name, a string that is a sequence of "X"s of the same length. i.e., "Sword of Dawn" becomes "XXXXXXXXXXXXX". Create a new table that is so transformed.
  • Get the dataset from https://data.boston.gov/dataset/employee-earnings-report, and load it via the CSV url.
  • Calculate the total earnings excluding the "DETAIL" column, since that is private compensation (police & fire employees hired for private events).
  • You might notice, in doing this, that the columns are not numbers, they are strings! We will talk more about data sanitization next time, but for now, you can use transform-column, using the helper function: 
    fun string-to-number-unsafe(s :: String) -> Number:
      doc: "Converts the given string to a number, returning 0 if not well formatted"
      string-to-number(string-replace(s, ",", "")).or-else(0)
    where:
      string-to-number-unsafe("1234") is 1234
      string-to-number-unsafe("-1.3") is -1.3
      string-to-number-unsafe("hello") is 0
    end
  • Now sort by your new total compensation to see the highest paid employees in the city. Sort the other way to see who is the lowest paid.

Wrap-up (5 mins)​

  • Transforming data is key to programming with tables. The original source of data may not have what you need in the right format, but we can use operations like build-column or transform-column to create new tables that do.