CS 101

Spreadsheets and Data

Announcements

  • Midterm is in two weeks in class. Tyler will email students who need an alternate exam in the next couple of days; contact him if you haven't heard from him.
  • We don't maintain the course readings

Plan for Today

  • Over the past few weeks, we've been using JavaScript to learn about code.
  • Now, we will apply many programming concepts to spreadsheets, using computers to manipulate text-based data.

Spreadsheets: Logistics

  • Using Google Sheets
  • All our in-class spreadsheets can be viewed here in a Google Drive.
  • Note: to edit files, you can make a copy through file on the top menu

Spreadsheets: An Introduction

  • Used to store textual information (as opposed to images)
  • Links information together in rows and columns

Variables in Spreadsheets

  • There are three "types" of variables
  • Text (words): left-justified
  • "Boolean" values (true and false): center-justified
  • Numbers (percents, decimals, and monetary amounts): right-justified
  • Experiment: are the numbers always perfectly accurate? What happens if you type a really big number and subtract it from a number one bigger than it?

Our Datasets

Data Layout in Spreadsheets

  • Rows = objects (samples in data)
    • Each row has multiple pieces of information
  • Column = fields
    • "properties" of the row
    • Labelled with a header (should freeze the headers)

Functions

  • Recall: JavaScript functions
    • print(image);
    • image.getPixel(x, y);
  • Way of manipulating data
  • FUNCTION_NAME(arg1, arg2,...)
  • Almost always used on columns
  • Exercise: How many appearances of comic book characters have there been?

Functions behind the scenes

  • Look at every row of data
  • Perform the calculation on that row of data
  • Just like a for loop!

If statements

  • IF(test, true case, false case)
  • Automatically includes an if and an else
  • Note: use = instead of ==
  • Logical operators (and/or) still work, but written as AND/OR
  • Exercise: Let's make a new column that is true if the character is female and introduced before 2000.

Useful functions

  • SUM(range)
  • COUNTIF(range, condition)
  • SUMIF(range, condition)
  • COUNTUNIQUE(range)
  • UNIQUE(range)
  • MINIFS(range, criteria_range, criteria)
  • MAXIFS(range, criteria_range, criteria)

Exercises

  • How many characters have a non-empty GSM column?
  • What is the average number of appearances for a male comic book character? What about a female character? What about any character whose GSM column is not empty?
  • Are male characters more likely to be heroes or villains? What about female characters?
  • What is the most popular ID?
  • Which alignment of character is most likely to die?
  • What else can you find out?

Recap

Today, we saw how computers can store and manipulate data in spreadsheets using the same coding principles from JavaScript and images. Next week, we'll talk about Artificial Intelligence.