More nested functions, combining tables

More nested functions

We saw how nested functions interact with the program directory and did an exercise on when nested functions are necessary. See the lecture capture for details.

Dealing with multiple tables

So far, we’ve looked at manipulating and analyzing a single table of data. What if we want to combine data from multiple sources? For example, our municipalities spreadsheet has sheet containing just municipality names and land areas (in square kilometers). How can we combine these data with the rest of our municipality data to get population density information?

(In this case, there’s a simple potential solution: in Google Sheets, paste the column of areas into the municipalities table. For more complex cases, though, you’ll need to use the techniques described in this lecture.)

We started by writing code to get the area for a particular municipality:

Here’s the code we ended up with:

include tables
include gdrive-sheets

include shared-gdrive("cs111-2020.arr", "1imMXJxpNWFCUaawtzIJzPhbDuaLHtuDX")

ssid = "1jHvn5CPE6RkTTQRIXQbY5n5p4aiOH7fZsnwK2s6s6tc"
spreadsheet = load-spreadsheet(ssid)

ri-municipalities = load-table: name :: String, city :: Boolean,
  population-2000 :: Number, population-2010 :: Number
  # true because the sheet has a "header" row
  source: spreadsheet.sheet-by-name("municipalities", true)
end

ri-areas = load-table: name :: String, area :: Number
  source: spreadsheet.sheet-by-name("municipalities-area", true)
end

fun area-for-municipality(areas :: Table, name :: String) -> Number:
  filter-with(areas, lam(r): r["name"] == name end).row-n(0)["area"]
end

We’re doing a filter-with that should only ever return one row. We use the row-n method to get that one row, then we get its area.

We’ll continue this example next lecture.