7 Clean and Shape Columns

7.1 Why This Module Matters

Real data arrive with problems nobody warned you about. A column of percentages is stored as text because one person typed "N/A". A weight column mixes 1035.6, "1,035.60", and ".9 /K". A length of 999.99 millimetres is not a fish; it is a code for “unknown”.

If you skip past these problems, every number you report afterwards is built on sand. This module teaches the first move of every real analysis:

Make the columns trustworthy, then keep only the rows and columns the question needs, and count rows at every step.

flowchart LR
  A[Messy file] --> B[Import + problems]
  B --> C[clean_names]
  C --> D[Check types]
  D --> E[Fix types]
  E --> F[select / filter / arrange]
  F --> G[mutate]
  G --> H[Answer]

We practise on a large, real dataset: the Great Lakes Fishery Commission fish stocking database (56,232 stocking events, 31 columns, 1950-2018), used exactly as released.

7.2 Learning Goals

By the end of this module, you should be able to:

import a large CSV, read the import message, and inspect problems()
declare missing-value codes at import with na =
clean column names with janitor::clean_names()
check column types with glimpse(), class(), summary(), and count(), and explain why a number column was read as text
fix types with parse_number(), as.numeric(), as.integer(), as.Date(), and col_types
keep columns with select(), rows with filter(), and sort with arrange()
report row counts before and after every filter
create new variables with mutate() and keep them by reassigning
render your first Quarto document

7.3 Vocabulary

Term	Meaning	Example
column type	How R stores a column	`<dbl>` number, `<int>` integer, `<chr>` text, `<date>` date, `<lgl>` TRUE/FALSE
missing-value code	A value used to mean “unknown”	`"N/A"`, `"ZZZZ"`, `-999`, `999.99`
coercion	Converting a column to another type	`as.numeric("12")` gives `12`; `as.numeric("N/A")` gives `NA` with a warning
parsing problem	A value `read_csv()` could not fit into its guessed type	`"63.2 /k"` in a number column
sentinel value	A number that is really a code	`LENGTH = 999.99`
row count check	Counting rows before and after a step	`nrow()` before and after `filter()`
pipe	Passes the result on the left into the function on the right	`data \|> filter(...)`

7.4 Before You Start

Open this module’s repository (created in Classroom 50) and its week_04.Rproj.
The class data are in class/data/stocked.csv; the assignment data are in assignment/data/.
Load the packages:

library(tidyverse)
library(janitor)
library(here)

7.5 The Main Idea

R guesses a type for every column from its first 1,000 rows. When a later value does not fit the guess, R either makes the whole column text or quietly turns the misfit into NA. Both are silent unless you look.

So after every import of real data, ask three questions:

Did R complain? Read the message; run problems().
Is every column the type it should be? Run glimpse().
Are the values possible? Run summary() and count().

Only then filter, summarise, or plot.

7.6 Build The Workflow

7.6.1 Import And Read The Message

stocked_raw <- read_csv(here("class", "data", "stocked.csv"))
problems(stocked_raw)

R warns about parsing issues. problems() lists 22 rows, all in column 25 (WEIGHT), where R expected a number and found values like "1,035.60" and "63.2 /k". Those 22 cells are now NA, and nothing else would have told you.

7.6.2 Clean The Names

stocked <- stocked_raw |>
  clean_names()

NO_STOCKED becomes no_stocked, STATE_PROV becomes state_prov. Clean names are lowercase, use underscores, and never need backticks.

7.6.3 Check The Types

glimpse(stocked)

Look down the type labels. mark_eff and tag_ret are percentages, but they show <chr>. Why? Count the values:

stocked |>
  count(mark_eff, sort = TRUE)

Among the numbers is the text "N/A". One text value forces the whole column to be text.

7.6.4 Fix Types: Three Ways

After import, with as.numeric():

mark_eff_num <- as.numeric(stocked$mark_eff)

R warns: NAs introduced by coercion. That warning is honest news: some values became NA. Count how many (sum(is.na(...)) before and after) and make sure they are the ones you expect (here, the 35 "N/A" values).

At import, by declaring missing codes:

stocked <- read_csv(
  here("class", "data", "stocked.csv"),
  na = c("", "NA", "N/A")
) |>
  clean_names()

Now mark_eff and tag_ret import as numbers. But run problems() again: two new problems appear in tag_ret. One cell reads "64.0, 100.0", two numbers in one cell. The column type now looks right, and that value is silently gone. This is why you run problems() after every import.

By declaring a type, then fixing on purpose:

stocked_weight <- read_csv(
  here("class", "data", "stocked.csv"),
  na = c("", "NA", "N/A"),
  col_types = cols(WEIGHT = col_character())
) |>
  clean_names() |>
  mutate(weight = parse_number(weight))

parse_number() drops commas and trailing text: "1,035.60" becomes 1035.6. But ".9 /K" becomes 0.9, and /K probably means a different unit. parse_number() made it a number; it did not make it correct. Flag records like that and ask the data owner.

7.6.5 Find Impossible Values

summary(stocked$length)
summary(stocked$day)

Lengths of 0 mm and 13,780 mm, a 999.99 code, and a day of 0. Seeing them is the first step; deciding what to do is a documented choice.

7.6.6 Keep The Columns You Need: `select()`

stocked |> select(year, lake, species, no_stocked)   # by name
stocked |> select(year:lake)                         # a range
stocked |> select(-tag_no, -tag_ret)                 # drop
stocked |> select(starts_with("tag"))                # helpers
stocked |> select(contains("stock"))

7.6.7 Keep The Rows You Need: `filter()`

nrow(stocked)

superior_lat <- stocked |>
  filter(lake == "SU", species == "LAT")   # comma means AND
nrow(superior_lat)

stocked |> filter(lake == "ER" | lake == "ON")          # OR
stocked |> filter(species %in% c("CHS", "COS"))         # one column, many values
stocked |> filter(between(year, 1990, 1999))            # a range
stocked |> filter(!is.na(month))                        # known values only

Count rows before and after every filter. A filter that drops more rows than you expected is a question to answer, not a detail to hide.

7.6.8 Sort: `arrange()`

superior_lat |> arrange(desc(no_stocked))            # largest first
superior_lat |> arrange(year, desc(no_stocked))      # by year, then largest

7.6.9 Create Variables: `mutate()`

stocked <- stocked |>
  mutate(
    fish_thousands = round(no_stocked / 1000, 1),
    decade = (year %/% 10) * 10
  ) |>
  relocate(decade, .after = year)

%/% is integer division: 1987 %/% 10 is 198, times 10 is 1980. mutate() returns a new data frame; reassign (stocked <- ...) to keep it.

7.6.10 Put It In A Quarto Document

Assignment 04 is your first Quarto document. A .qmd file holds:

a YAML header between --- lines (title, author, format)
prose, written in Markdown, for explanations and answers
code chunks between ```{r} and ```, which run when you click Render

Written answers go in the prose between chunks, never as # comments inside a chunk. Render, open the .html, and check every result appears.

7.7 Read The Code

huron_walleye <- read_csv(
  here("class", "data", "stocked.csv"),
  na = c("", "NA", "N/A")
) |>
  clean_names() |>
  select(year, lake, agency, site, species, stage, no_stocked, length) |>
  filter(species == "WAE", lake == "HU", between(year, 2010, 2019))

Read it as: import the raw file, treating three codes as missing; clean the names; keep eight columns; keep walleye stocked in Lake Huron in the 2010s. The result has 109 events.

7.8 Check Your Work

Did you run problems() after importing, and can you explain each problem?
Does glimpse() show every column with the type it should have?
Did summary() reveal any impossible values or codes?
Can you state the row count before and after each filter, and why rows were dropped?
Did you reassign after mutate() and clean_names()?

7.9 Common Problems

Problem	What you see	Fix
`=` instead of `==` in `filter()`	Error suggesting `==`	Use `==` to compare
Forgot to reassign	Changes vanish on the next line	`data <- data \|> mutate(...)`
`as.numeric("1,035.60")`	`NA` with a warning	`parse_number()`
Exact-spelling filter	`filter(x == "Approach")` misses `"APPROACH"`	`count(x)` first to see every spelling
Missing values in a condition	Filters do not add up to the total	`filter()` drops rows where the condition is `NA`
Column names after `clean_names()`	`WEIGHT` not found	Use the new name, `weight`
Answers as comments	Text shows as grey code in the report	Write prose between chunks

7.10 Try It

Import stocked.csv without any na = argument. Which columns are <chr> that should be numbers? Fix them two different ways.
Which agency stocked the most lake trout events in Lake Superior since 2000? (Hint: filter(), then count(agency, sort = TRUE).)
Lake Huron walleye in the 2010s: drop events with no recorded length. How many did you drop, and which event stocked the largest fish?
Run count(stage). How many codes do you see? How many real stages do you think there are?

7.11 Connect To The Assignment

Assignment 04, Wildlife Strikes Cleanup, uses the FAA Wildlife Strike Database (56,978 strikes). It is new data, but every skill is from this module:

find the codes that mean “missing” and declare them at import
clean names and fix two column types
keep the columns you need and the strikes with a known height, with row counts
create new variables and sort
notice what an exact-spelling filter misses

It also has an Investigate task: build a weekend column. We did not cover dates; the hint points you to lubridate::wday(), and you already know %in%. Reading a help page and applying a new function is a skill in itself.

Everything you need is in assignment/assignment.qmd. Only assignment/ and README.md are checked and graded; the grading criteria are in assignment/rubric.md.

7.12 Finish The Professional Workflow

Render assignment/assignment.qmd and open assignment/assignment.html.
Edit the About this repository section of README.md.
Commit and push assignment/assignment.qmd, assignment/assignment.html, and README.md by Sunday, Oct 4, 11:59 PM.
On GitHub, check Actions and Issues. A red X is feedback, not a grade: read the first failing step, fix, and push again.

Data sources: Great Lakes Fishery Commission Fish Stocking Database and FAA Wildlife Strike Database, both via TidyTuesday (https://github.com/rfordatascience/tidytuesday).