DSCI 100 Week 3 : Cleaning and Wrangling Data

Sky (Kehan) Sheng

Lecture 3 Slides

www.skysheng.io/dsci_100_sky_slides/slides/slides_03.html#/title-slide

🏠 Housekeeping

• Do not remove, delete or rename your files on Jupyter Server.
• iClicker attendence do not count towards your grade. Answering questions gets you 50% of the points, and answering it correctly gets you the other 50%.
• My OH is Thursday, 4-5pm, in my office: ICCS room 187

🏠 Housekeeping: Debugging Guide on Piazza

Check out Julia’s how to debug guide on Piazza. Most errors are quick to fix once you know what to look for.

Week 2 Recap: Reading in data

Recap: Tabs look like spaces

read_delim("data/penguin_notes.tsv", delim = "\t")

• Shortcut: read_tsv() is read_delim() with delim = "\t" already set.

Recap: The backslash is a secret code

• Backlash is escape character, or escape sequence introducer.
• Inside quotes, \ tells R: the next character is special, don’t read it normally.

• \t a tab
• \n a new line
• \\ a real backslash
• print() will “ignore” escape characters like \n and print out escape characters as strings
• cat() will honor the rules of escape characters and turn \t into a tab, \n into a new line, etc.

• Answers:

cat("Here is the path on my computer: sky\\images\\penguin.png")
cat("line one\nline two")

• Link back: delim = "\t" on the last slide was this same secret code.

iClicker: Why read from a database?

• (Optional note, will not be tested during exams):
  • RAM = Random Access Memory.
  • Short-term memory: It stores data your computer needs right now to run open apps and files.

UBC keeps 118 years of data about all the students who have ever attended a course at UBC in a database. Why not just export it all to one CSV?

• (A) Databases are easier to type than file paths
• (B) You can ask for only the rows and columns you need, instead of loading the whole thing into your computer
• (C) Data from a database is always tidy
• (D) CSV files cannot hold numbers

Answer: B. A big database can be far larger than your laptop’s memory. Load all of it and your computer runs out of memory and freezes. With a database you ask for just the slice that matters, and only that slice comes over to your computer

🔬 Demo: . and .. in relative paths

home/
└── skysheng/
    ├── images/
    └── story_of_otter/ ⬅️ cwd
        ├── clown_dungeon/
        │   └── ollie.txt
        ├── home_of_ollie/
        └── ocean/

• . means here, the folder I am in
• .. means one folder up
• ../images go up to skysheng/, then into images/
• ../.. go up twice, to home/
• Optional terminal commands (you will not be tested on them, for now!):
  • ls list files and folders in the current folder
  • pwd print the current working directory (where am I?)
  • cd change directory (go to a different folder)
  • cat print the contents of a file (print the text of a file)
• Additional resources about command line (you will not be tested on this!): Otter Rescue for Command Line Warriors

• Connect to R: read_csv("../data/file.csv") uses the exact same rules, relative to the notebook’s folder.

iClicker: Someone hands you a data file

What is the very first thing you should do?

• (A) Load it into R with read_csv() right away
• (B) Open the file and look at it with your own eyes, before loading anything
• (C) Make a plot to see what is in it
• (D) Delete the rows that look messy

Answer: B. Don’t even load it yet. Open it in a text editor and check: is there a header? Lines to skip? What separates the values: commas, tabs, spaces? You pick the read_* function and its arguments from what you see.

🔄 Where are we in the workflow?

Week 2 was import. Today is tidy and transform.

Data Scientists spend 80 to 90% of their time cleaning and wrangling data, not analyzing it.

🐄 Real data from the UBC Dairy Farm

Two screenshots from the same file. What problems do you spot?

• The header row and the ===== line repeat in the middle of the file, every time a new day starts.
• The columns change partway through: the right screenshot has two new columns, PEN and PPEN.
• no description of the columns, no metadata
• this is written in a weried file format, not csv, tsv or anything you learnt!
• the file name is not informative at all!

Q: what problems do you spot? Q: what do you feel if this is the data that is handed over to you, and you have to analyze it?

• This is real milking data from the UBC Dairy Education and Research Centre. I had to deal with it during my PhD. (frustration!)

🐄 My first real R script: 5000 lines

🔥 Roast my repo: Sky’s first R script for data cleaning

• ❌ slow, repetitive code
• ❌ only worked for this one dataset
• ✅ you will learn tidyverse: the same cleaning skills work on any dataset

Rewritten with tidyverse as an R package: moo4feed

• This is the first piece of code I wrote right after finishing my bachelor’s, to clean the dairy data from the last slide. It is about 5000 lines.

Q: what are the problems with this script?

• I had learnt Java before, but never learnt R properly, so I wrote R like Java. Look at the screenshot: one if / else if branch for every month name, then the exact same block again for the end date.
• Many of the ways I handled the data were inefficient, and the script only worked for this one dataset. A new file format meant rewriting big chunks.
• Good news: you will learn the best practice from day one. tidyverse functions work the same way on every data frame, so what you learn cleaning penguins today transfers to cows, or to your own data.
• I did end up rewriting the entire script with tidyverse and turning it into an R package, moo4feed. Click the screenshot to see the old script on GitHub, and the sticker for the package website.

Today’s dataset: the penguins!

344 penguins from 3 islands

Dr. Kristen Gorman and the Palmer Station team measured 344 penguins of three species on three islands in Antarctica.

• Real field work. Somebody stood on a rock in the wind writing these numbers down.
• This is the same data set your worksheet uses this week.

🐧 Let’s meet the data

• 🤔 What is the difference between Character and Factor?

• Walk the columns out loud: species, island, the four measurements, sex, year.

• Q: is this tidy? One row per penguin, one column per measurement, one value per box. Yes.

• What is the difference between Character and Factor?

🗑️ But data rarely arrives this way

Somebody hands you “the data”.

• inconsistent formats: commas, tabs, semicolons, blank lines
• inconcsistent number of columns and column names across files
• spelling mistakes, “Febuurrrary”, “Feb”, “Febarary”, “Feburary” … 🤯
• missing values written as NA, -999, n/a, or nothing at all

Reading it in successfully does not mean it is ready to analyze.

🧹 What makes data tidy?

Q: anything else that makes a data tidy?

• don’t store data in your column name!
• column name need to be informative!

🧹 What is Tidy Data?

📋 Tidy data example

student	major	year
Ama	CPSC	2
Bo	STAT	1
Cai	BIOL	3

One row per student. One column per thing about them. One value per box.

Is this data tidy?

Goal: compare tuberculosis rates, taking population into account.

country	year	rate
Afghanistan	1999	745/19987071
Afghanistan	2000	2666/20595360
Brazil	1999	37737/172006362
Brazil	2000	80488/174504898
China	1999	212258/1272915272
China	2000	213766/1280428583

• (A) True, this is tidy
• (B) False, this is not tidy

Answer: B, false. The rate box holds two values, cases and population, glued together with a slash. R sees text, not two numbers. You cannot take a mean of it, plot it, or filter on it.

Follow up: what would you do about it? Split it into a cases column and a population column.

Is this one tidy?

country	cases (1999)	cases (2000)	population (1999)	population (2000)
Afghanistan	745	2666	19987071	20595360
Brazil	37737	80488	172006362	174504898
China	212258	213766	1272915272	1280428583

• (A) True, this is tidy
• (B) False, this is not tidy

• Answer: B, false. year is a variable, and it has been smeared across four column names instead of living in a column of its own.
• Don’t store data in your column name!

This shape has a name: wide. pivot_longer() is what turns it back into tidy. That is the worksheet’s job this week.

Is this data tidy?

country	1999	2000
Afghanistan	745	2666
Brazil	37737	80488
China	212258	213766

• (A) True, this is tidy
• (B) False, this is not tidy

Answer: B, false. Same problem as the last one. year is a variable, and it is smeared across the column names 1999 and 2000 instead of living in a column of its own.

Column name also needs to be informative!

Is this one tidy?

country	year	key	value
Afghanistan	1999	cases	745
Afghanistan	1999	population	19987071
Afghanistan	2000	cases	2666
Afghanistan	2000	population	20595360
Brazil	1999	cases	37737
Brazil	1999	population	172006362
Brazil	2000	cases	80488
Brazil	2000	population	174504898
China	1999	cases	212258
China	1999	population	1272915272
China	2000	cases	213766
China	2000	population	1280428583

• (A) True, this is tidy
• (B) False, this is not tidy

Answer: B, false. Each observation, one country in one year, is split across two rows here: one for cases and one for population. A tidy table needs one row per observation, so cases and population should each get their own column.

✅ This one is tidy

country	year	cases	population
Afghanistan	1999	745	19987071
Afghanistan	2000	2666	20595360
Brazil	1999	37737	172006362
Brazil	2000	80488	174504898
China	1999	212258	1272915272
China	2000	213766	1280428583

Each row is one country in one year. Each column is one variable. Each box is one value.

🧰 Your data wrangling toolbox

All of this lives inside tidyverse, the one package you already load every time:

• dplyr reshapes the rows and columns: select, filter, mutate, group_by, summarize
• tidyr changes the shape itself: pivot_longer, pivot_wider
• purrr repeats a job across many columns: map_dfr

• Remember week 2: a package is the box of tools, a function is one tool, an argument is what you feed to functions to customize the tool to do work for you!
• Tidyverse is a collection of packages including dplyr, tidyr, and purrr.

🔎 select(): choose columns

🔎 select() one column

select() keeps a subset of the columns (variables) and drops the rest.

Answer:

select(penguins, bill_depth_mm)

Say the shape out loud as it appears: still 344 rows, now 1 column. select() never touches rows.

🔎 select() many columns

Answer:

select(penguins, bill_length_mm, bill_depth_mm)

No quotation marks. These are column names, not strings.

🔎 select() and keep the result

Answer:

penguins_measurements <- select(penguins, bill_length_mm, bill_depth_mm)
penguins_measurements

The big idea: select() hands you a new data frame. It does not change penguins. Run penguins again afterwards to prove it.

🔎 select() everything except some columns

Answer:

select(penguins, -island)

The minus sign means “drop this one”.

🔎 (Bonus Slide) select() by a pattern in the name

• Helpers that select by name pattern: starts_with(), ends_with(), contains(), matches(), num_range().

• Note the quotation marks here. "bill_" is a piece of text to match, not a column name, so it does need quotes.

🎯 filter(): choose rows

🎯 filter() with one condition

filter() keeps a subset of the rows (observations) and drops the rest.

Goal: keep only penguins with flippers longer than 190 mm.

• How about “keep only penguins with flippers equal to 190 mm”?
• What is the difference between == and =?

• exaplain logical statement

Answer:

filter(penguins, flipper_length_mm > 190)

Watch the row count fall from 344. Columns stay at 8. This is the mirror image of select().

Worth saying: rows where flipper_length_mm is NA are dropped too, because R cannot tell whether an unknown number is bigger than 190.

🎯 filter() with two conditions

Goal: Chinstrap penguins and flippers longer than 190 mm.

• “A and B” : A & B
• “A or B” : A | B
• “not A” : !A
• “A is equal to B” : A == B
• “A is not equal to B” : A != B
• “A is greater than B” : A > B
• “A is less than B” : A < B
• “A is greater than or equal to B” : A >= B
• “A is less than or equal to B” : A <= B

Answer:

filter(penguins, flipper_length_mm > 190, species == "Chinstrap")

1. The comma between conditions means and. Use | for or.
2. == is a question (“are these equal?”), = sets an argument, <- creates a variable. Three different jobs, three different symbols. This is the single most common error in the worksheet.

"Chinstrap" is in quotes because it is a value inside the data, not a column name.

🎯 filter() and keep the result

Answer:

penguins_filtered <- filter(penguins, flipper_length_mm > 190, species == "Chinstrap")
penguins_filtered

➕ mutate(): add a column

➕ mutate()

mutate() builds a new column out of the columns you already have.

Goal: body mass in pounds instead of grams, saved as penguins_lbs. 454 g is 1 lb.

Answer:

penguins_lbs <- mutate(penguins, body_mass_lb = body_mass_g / 454)
penguins_lbs

Note = here, not <-. Inside a function call, = is how you name an argument, and the new column name is exactly that.

Also: the new column appears at the far right. Scroll over to it.

body_mass_g is still there. mutate() adds, it does not replace.

Three ways to chain steps together

You often need several data cleaning steps in a row. Three ways to write it:

1. Save an intermediate object after each step
2. Nest the functions inside each other
3. Use the pipe |>

Let’s look at each one

1️⃣ Save intermediate objects

• A reader has to hunt for where penguins_1 and penguins_2 get used
• The names suggest those objects matter. They don’t
• Three copies of a big data set now sit in memory (waste of memory)

2️⃣ Nest the functions

• You have to start reading from the middle of the code
• Hard to read! 🤯

3️⃣ Pipe it with |>

The pipe takes whatever is on its left and hands it to the first argument of the function on its right.

• Reads top to bottom, in the order it happens
• No intermediate names to invent or remember
• The data set is not repeated inside each call
• %>% is also a pipe operator, but was used in the previous versions of R.

• If R sees |> at the end of a line, it keeps reading the next line before evaluating.
• Keyboard shortcut in JupyterLab and RStudio: Ctrl+Shift+M (Cmd+Shift+M on Mac).

🍳 The pipe is a kitchen line

veggies → cut it up → keep the big pieces → fry everything in a pan

Each station does one job and slides the bowl to the next.

💪 Try it yourself

Using the avocado sales data, write a pipeline that:

1. adds a column total_volume (the sum of small_hass_volume, large_hass_volume and extra_l_hass_volume)
2. keeps only organic avocados
3. keeps only the Date, average_price and total_volume columns

Answer:

avocado_summary <- avocado |>
  mutate(total_volume = small_hass_volume + large_hass_volume + extra_l_hass_volume) |>
  filter(type == "organic") |>
  select(Date, average_price, total_volume)

📊 group_by() + summarize()

Q: what would you do if you are asked to count how many penguis there are in each species?

Grouping and Summarizing

Grouping splits the rows into piles based on the value in a column.

Summarizing combines data from each pile into a few summary values.

How heavy is each species on average?

Answer:

penguins |>
  group_by(species) |>
  summarize(avg_body_mass = mean(body_mass_g, na.rm = TRUE))

Do it in two moves so they see the trick:

1. Run it without na.rm = TRUE first. Two of the three species come back NA. Let them sit with that.
2. Then add na.rm = TRUE and it works.

344 rows went in. 3 rows came out. That is what summarize() is for.

📊 Watch 344 penguins become 3 numbers

viewof show_means = Inputs.toggle({label: "run summarize()", value: false})

{
  // Draw only: every dot position and every average was computed in R.
  const dots = transpose(penguin_dots);
  const means = transpose(penguin_means);
  const order = means.map(d => d.species);
  const palette = ["#2a78d6", "#eb6834", "#1baf7a"];
  return Plot.plot({
    width: 880, height: 330, marginLeft: 120, marginRight: 40, marginBottom: 46,
    style: {fontSize: "16px", background: "transparent"},
    x: {label: "body mass (g) →", labelAnchor: "center", grid: true},
    y: {axis: null, domain: [-0.5, 0.5]},
    fy: {label: null, domain: order},
    color: {domain: order, range: palette, legend: false},
    marks: [
      Plot.dot(dots, {fy: "species", x: "body_mass_g", y: "jitter", fill: "species",
                      r: 3.5, fillOpacity: show_means ? 0.16 : 0.55}),
      show_means
        ? Plot.ruleX(means, {fy: "species", x: "avg_body_mass",
                             stroke: "species", strokeWidth: 3})
        : null,
      show_means
        ? Plot.text(means, {fy: "species", x: "avg_body_mass", y: 0,
                            text: d => `${d.avg_body_mass} g`,
                            dy: -44, fontWeight: "bold", fill: "species"})
        : null
    ]
  });
}

{
  // Print only: both counts were computed in R (penguin_counts).
  const c = transpose(penguin_counts)[0];
  return html`<div class="ojs-readout">
    <b class="blue">${c.n_in}</b> penguins went in
    &nbsp;·&nbsp; <b class="orange">${c.n_out}</b> rows came out.</div>`;
}

🔁 Iteration: The same job, over and over

You want the average of four columns. You could write this:

• Four chances to create a typo writing typing out column names. Now imagine if you have 40 columns…
• Iteration is when you need to do something repeatedly (e.g., ringing in and bagging groceries at the till)
• In data science, it’s doing the same thing over and over again
  • Important to reduce duplication of code
  • easier to see the intent of your code
  • likely have fewer bugs

🔁 map_dfr() does it once

map_dfr() is a function that iterates over columns. For example, below we are calculating the mean of each column in the penguins data frame.

• bill_length_mm:body_mass_g means “these two columns and everything between them”
• map_dfr() applies one function to every column and returns a data frame of the results
• ⚠️ Be careful: There are a lot of map_… functions that you could use (map, map_lgl, map_chr, etc). Usually in this course we’ll only use map_dfr, but you should investigate the others yourself!

📅 Upcoming Due Dates

• worksheet_wrangling due SATURDAY, Sep 26, 11:59pm
• tutorial_wrangling due SATURDAY, Sep 26, 11:59pm

There are NO late submissions for worksheets. We drop the lowest worksheet and the lowest tutorial score. See the policy for late registrants.

❓ Questions?

If not, let’s work on this week’s worksheet together.

• Want to save these slides? Right click → Print → Save to PDF.
• Your code and notes only save if you save as a PDF!