4.2.1.1 Loading Data Tables🔗

The first step to working with an outside data source is to load it into your programming and analysis environment. Which source you use depends on the programming environment that you are using for Pyret:

• If you are using CPO, you can load tables from Google Sheets (if you want to load a CSV, you first need to import it into Google Sheets)

• If you are using VSCode, you can load tables directly from CSV files

Both use the same Pyret operation (load-table), but in slightly different ways.

Google Sheets and CSV files treat the types of data in cells differently, so there are also differences in how we manage the types of Pyret columns after loading. Columns like "Num Tickets" that appear to contain both numbers and strings highlight the differences. We discuss these nuances in separate sections for each kind of source file.

include gdrive-sheets

ssid = "1Ks4ll5_8wyYK1zyXMm_21KORhagSMZ59dcr7i3qY6T4"
event-data =
  load-table: name, email, tickcount, discount, delivery, zip
    source: load-spreadsheet(ssid).sheet-by-name("Orig Data", true)
  end

4.2.1.2 Dealing with Missing Entries🔗

When we create tables manually in Pyret, we have to provide a value for each cell – there’s no way to "skip" a cell. When we create tables in a spreadsheet program (such as Excel, Google Sheets, or something similar), it is possible to leave cells completely empty. What happens when we load a table with empty cells into Pyret?

The original data file has blanks in the discount column. After we load it into Pyret, we see something interesting in that column (though what it is will differ depending on whether you’re reading from Google Sheets or CSV files).

• If you are using Google Sheets and CPO, load the table as follows:

  event-data =
    load-table: name, email, tickcount, discount, delivery
      source: load-spreadsheet(ssid).sheet-by-name("Data", true)
    end

  event-data will be the following table:

  

  Note that those cells that had discount codes in them now have an odd-looking notation like some("student"), while some of the cells that were empty contain none, but none isn’t a string. What’s going on?

  Pyret supports a special type of data called option. As the name suggests, option is for data that may or may not be present. none is the value that stands for "the data are missing". If a datum are present, it appears wrapped in some.

  Look also at the last two rows (for Zander and Shweta) – they also appear empty when seen in Google Sheets, but Pyret has loaded them as strings of spaces (e.g., some(" ")). What does that mean? It means that those cells weren’t actually empty in the Google Sheet, but instead contained several spaces.

  Do Now!

  Look at the discount value for Ernie’s row: it reads some("none"). What does this mean? How is this different from none (as in Sam’s row)?

• If you are using CSV files and VSCode, load the table as follows:

  url = "https://raw.githubusercontent.com/data-centric-computing/dcic-public/main/materials/datasets/events-f25.csv"
  
  event-data =
    load-table: name, email, tickcount, discount, delivery, zip
      source: csv-table-url(url, default-options)
    end

  event-data will be the following table:

  

  Note that cells that had no data have either empty strings ("") or strings with spaces (" "). What caused the difference? In the cells where the string has spaces, the cell in the original CSV appeared to be empty, but it actually contained some spaces. When reading in the CSV, Pyret retains the actual content in the cell. The empty string is only used if the CSV cell actually had no data at all.

Whether you are using Google Sheets or CSV files, the right way to address missing data (and conversion in general) is to indicate how to handle each column. This guarantees that the data will be as you expect after you read them in. We do this with an additional aspect of load-table called sanitizers. Here’s how we modify the code:

include data-source # to get the sanitizers

event-data =
  load-table: name, email, tickcount, discount, delivery, zip
    source: load-spreadsheet(ssid).sheet-by-name("Data", true)
    sanitize name using string-sanitizer
    sanitize email using string-sanitizer
    sanitize tickcount using num-sanitizer
    sanitize discount using string-sanitizer
    sanitize delivery using string-sanitizer
    sanitize zip using string-sanitizer
  end

Each of the sanitize lines tells Pyret what to do in the case of missing data in the respective column. string-sanitizer says to load missing data as an empty string (""). Sanitizers also handle simple data conversions. If the string-sanitizer were applied to a column with a number (like 3), the sanitizer would convert that number to a string (like "3"). Similarly, applying num-sanitizer to a column would convert number-strings (like "3") to an actual number (3).

Using sanitizers, the event-data table reads in as follows:

Do Now!

Did you notice that we sanitized the zip column with string-sanitizer instead of num-sanitizer? Aren’t zip codes numbers? Try the above code with each of string-sanitizer and num-sanitizer for code and see if you can spot the difference.

Zip codes are a terrific example of data that are written with digits, but aren’t meant to be used numerically. What does that mean? If data are meant to be used numerically, then standard arithmetic operations should make sense on them. What sense would it make to multiply a zip code by 3, for example? None. Similarly, we don’t write numbers with leading zeros, but zip codes can meaningfully start with 0. Treating zip codes as strings treats them as identifiers more than numbers. We’ll return to this point later in this chapter (Visualizations and Plots).

A note on default values: Unlike string-sanitizer, num-sanitizer does NOT convert blank cells to a default value (such as 0). There is no single default value that would make sense for all the ways in which numbers are used: while 0 would be a plausible default for missing numbers of tickets, it would not be a meaningful default for a missing age. It could create outright errors if used as the default for a missing exam grade (which was later used to compute a course grade). As a result, num-sanitizer reports an error if the data (or lack thereof) in a cell cannot be reliably interpreted as a number. Pyret allows you to write your own custom sanitizers (e.g., one that would default missing numbers to 0). If you want to do this, see the Pyret documentation for details.

The lack of meaningful default values is one reason why Pyret doesn’t leverage type annotations on columns to automatically sanitize imported data. Automation takes control away from the programmer; sanitizers provide the programmer with control over default values, as well as the option to use (or not) sanitizers at all.

Rule of thumb: when you load a table, use a sanitizer to guard against errors in case the original sheet is missing data in some cells.

4.2.1.3 Normalizing Data🔗

Next, let’s look at the "Discount Code" column. Our goal is to be able to accurately answer the question "How many orders were placing under each discount code". We would like to have the answer summarized in a table, where one column names the discount code and another gives a count of the rows that used that code.

How do we get to this table? How do we figure this out if we aren’t sure?

Start by looking in the documentation for any library functions that might help with this task. In the documentation for Pyret’s dcic2024 context, we find:

# count(tab :: Table, colname :: String) -> Table
# Produces a table that summarizes how many rows have
#   each value in the named column.

This sounds useful, as long as every column has a value in the "Discount code" column, and that the only values in the column are those in our desired output table. What do we need to do to achieve this?

We can capture these together in a function that takes in and produces a string:

fun cell-to-discount-code(str :: String) -> String:
  doc: ```uppercase all strings other than none,
       convert blank cells to contain none```
  if (str == "") or (str == "none"):
    "none"
  else:
    string-to-upper(str)
  end
where:
  cell-to-discount-code("") is "none"
  cell-to-discount-code("none") is "none"
  cell-to-discount-code("birthday") is "BIRTHDAY"
  cell-to-discount-code("Birthday") is "BIRTHDAY"
end

cell-to-discount-code("NoNe") is "none"

fun cell-to-discount-code(str :: String) -> String:
  doc: ```uppercase all strings other than none,
       convert blank cells to contain none```
  if (str == "") or (string-to-lower(str) == "none"):
    "none"
  else:
    string-to-upper(str)
  end
where:
  cell-to-discount-code("") is "none"
  cell-to-discount-code("none") is "none"
  cell-to-discount-code("NoNe") is "none"
  cell-to-discount-code("birthday") is "BIRTHDAY"
  cell-to-discount-code("Birthday") is "BIRTHDAY"
end

Using this function with transform-column yields a table with a standardized formatting for discount codes (reminder that you need to be working in the dcic2024 context for this to work):

discount-fixed =
  transform-column(event-data, "discount", cell-to-discount-code)

Exercise

Try it yourself: normalize the "delivery" column so that all "yes" values are converted to "email".

Now that we’ve cleaned up the codes, we can proceed to using the "count" function to extract our summary table:

count(discount-fixed, "discount")

This produces the following table:

Do Now!

What’s with that first row, with the discount code " "? Where might that have come from?

Maybe you didn’t notice this before (or wouldn’t have noticed it within a larger table), but there must have been a cell of the source data with a string of blanks, rather than missing content. How do we approach normalization to avoid missing cases like this?

4.2.1.4 Normalization, Systematically🔗

As the previous example showed, we need a way to think through potential normalizations systematically. Our initial discussion of writing examples gives an idea of how to do this. One of the guidelines there says to think about the domain of the inputs, and ways that inputs might vary. If we apply that in the context of loaded datasets, we should think about how the original data were collected.

Do Now!

Based on what you know about websites, where might the event code contents come from? How might they have been entered? What do these tell you about different plausible mistakes in the data?

A text-entry box means that any sort of typical human typing error could show up in your data: swapped letters, missing letters, leading spaces, capitalization, etc. You could also get data where someone just typed the wrong thing (or something random, just to see what your form would do).

But really, the moral of this is to just use drop-downs or other means to prevent incorrect data at the source whenever possible.

As you get more experience with programming, you will also learn to anticipate certain kinds of errors. Issues such as cells that appear empty will become second nature once you’ve processed enough tables that have them, for example. Needing to anticipate data errors is one reason why good data scientists have to understand the domain that they are working in.

The takeaway from this is how we talked through what to expect. We thought about where the data came from, and what errors would be plausible in that situation. Having a clear error model in mind will help you develop more robust programs. In fact, such adversarial thinking is a core skill of working in security, but now we’re getting ahead of ourselves.

Exercise

In spreadsheets, cells that appear empty sometimes have actual content, in the form of strings made up of spaces: both "" and " " appear the same when we look at a spreadsheet, but they are actually different values computationally.

How would you modify cell-to-discount-code so that strings containing only spaces were also converted to "none"? (Hint: look for string-replace in the strings library.)

4.2.1.5 Using Programs to Detect Data Errors🔗

Sometimes, we also look for errors by writing functions to check whether a table contains unexpected values. Let’s consider the "email" column: that’s a place where we should be able to write a program to flag any rows with invalid email addresses. What makes for a valid email address? Let’s consider two rules:

Exercise

Write a function is-email that takes a string and returns a boolean indicating whether the string satisfies the above two rules for being valid email addresses. For a bit more of a challenge, also include a rule that there must be some character between the @ and the .-based ending.

Assuming we had such a function, a routine filter-with could then produce a table identifying all rows that need to have their email addresses corrected. The point here is that programs are often helpful for finding data that need correcting, even if a program can’t be written to perform the fixing.

4.2.3.1 Creating bins🔗

The act of reducing one set of values (such as the tickcounts values) into a smaller set of categories (such as small/medium/large for orders, or morning/afternoon/etc. for timestamps) is known as binning. The bins are the categories. To put rows into bins, we create a function to compute the bin for a raw data value, then create a column for the new bin labels.

Here’s an example of creating bins for the scale of the ticket orders:

fun order-scale-label(r :: Row) -> String:
  doc: "categorize the number of tickets as small, medium, large"
  numtickets = r["tickcount"]

  if numtickets >= 10: "large"
  else if numtickets >= 5: "medium"
  else: "small"
  end
end

order-bin-data =
  build-column(cleaned-event-data, "order-scale", order-scale-label)

4.2.3.2 Splitting Columns🔗

The events table currently uses a single string to represent the name of a person. This single string is not useful if we want to sort data by last names, however. Splitting one column into several columns can be a useful step in preparing a dataset for analysis or use. Programming languages usually provide a variety of operations for splitting apart strings: Pyret has operations called string-split and string-split-all that split one string into several around a given character (like a space). You could, for example, write string-split("Josie Zhao", " ") to extract "Josie" and "Zhao" as separate strings.

Exercise

Write a task plan (not the code, just the plan) for a function that would replace the current name column in the events table with two columns called last-name and first-name.

Do Now!

Write down a collection of specific name strings on which you would want to test a name-splitting function.

Hopefully, you at least looked at the table and noticed that we have one individual, "Zander" whose entire name is a single string, rather than having both a first name and a last name. How would we handle middle names? Or names from cultures where a person’s name has the last names of both of their parents as part of their name? Or cultures that put the family name before the given name? Or cultures where names are not written as in the Latin alphabet. This is definitely getting more complicated.

Responsible Computing: Representing Names

Representing names as data is heavily context- and culture-dependent. Think carefully about the individuals your dataset needs to include and design your table structure accordingly. It’s okay to have a table structure that excludes names outside of the population you are trying to represent. The headache comes from realizing later that your dataset or program excludes data that need to be supported. In short, examine your table structure for assumptions it makes about your data and choose table structure after thinking about which observations or individuals it needs to represent.

For a deeper look at the complexity of representing real-world names and dates in programs, search for “falsehoods programmers believe about ...”, which turns up articles such as Falsehoods Programmers Believe About Names and Falsehoods Programmers Believe About Time.

Exercise

Write a program that filters a table to only include rows in which the name is not comprised of two strings separated by a space.

Exercise

Write a program that takes a table with a name column in "first-name last-name" format and replaces the name column with two columns called last-name and first-name. To extract the first- and last-names from a single name string, use:

string-split(name-string, " ").get(0)  # get first name
string-split(name-string, " ").get(1)  # get last name