UC Carpentries Workshop Series · Fall 2026

Tidy Data

Tuesday, September 8 · 9:00 am – 12:00 pm PT · Google Sheets

Notes: tim-dennis.com/2026-09-08-uc-carpentries/tidy-data-notes/

Etherpad: pad.carpentries.org/2026-fall-uc-carpentries-tidydate

Practice sheet: link in the Etherpad - open it and make your own copy

Library Carpentry, Spreadsheets / Tidy Data for Librarians (CC BY 4.0)

Open script.html (link, bottom-right of this slide) on a second screen for the full per-slide script. Reveal's own presenter view (S) is unreliable from a CDN.

Holding slide - learners arriving (9:00)

Greet people by name
Ask them to: (1) add their name to the Etherpad; (2) click the practice-sheet link; (3) File ▸ Make a copy; (4) rename it tidy-data-yourname
Helpers watch the chat

Etherpad - pin at the top: practice-sheet copy link
docs.google.com/spreadsheets/d/1NuHEr-1xDKpZHq77e5XDBdRAdROWbwEG/copy

Zoom model, all session:

You screen-share the practice sheet during demos
Learners work in their own copy the whole time
Done / stuck = a reaction icon, or a line in the chat
Pair / group work happens in the chat and the Etherpad; no breakout rooms

Before we start demo

Everyone: make your copy

Open the practice sheet link from the Etherpad
File ▸ Make a copy - this is now your sheet, in your Drive
Rename it tidy-data-yourname
You'll edit your copy all session. I'll drive a copy of my own on screen.

Helpers: anyone without the link or a Google account, jump in via chat now.

the original is left immutable; your copy is a working copy

To start discuss

Two questions

What do you use spreadsheets for? What are they good at?
What have you accidentally done in a spreadsheet, or been frustrated you couldn't do easily?

On Zoom: drop answers in the chat. I'll read a few out.

spreadsheets are strong for data entry and quick views; weaker for reproducible analysis and version control

Before we start

Habits for data you're creating

One rectangular table per sheet - no side-by-side tables, one table per tab
A real header row, row 1, one short name per column
One value per cell; one column per variable
Raw data is read-only - no notes, no colour, no maths in the data cells

Be consistent: same spelling, same units, same date format, every row
Fill in zeros where you mean zero; use one null value for missing

Today we practise spotting and fixing where these have been broken, on real messy data.

these are the lesson's keypoints; consistent names and codes are a controlled vocabulary, which librarians already keep

Concept 2 demo exercise

Work in a copy: your first edit

The source file is read-only. You never edit what you were given.
Work in a named copy - 2016_clean, not Copy of Sheet1
A duplicated tab is a working copy, not a backup - keep the raw tab too

Never modify the original raw data.

Everyone, now

Right-click the 2016_messy tab ▸ Duplicate
Rename the new tab 2016_clean
Delete the merged title row and the blank spacer rows

raw data as a single source of truth, kept immutable; the lesson's first rule

9:09-9:14 · your first edit · DEMO + EXERCISE · LC Episode 1

Completes the warm-up: one real edit before minute 10.

SHOW: this slide, the "Everyone, now" box. Drop these steps in the Etherpad.

DO - share the Sheet, demo slowly, narrate each click:

Right-click the 2016_messy tab ▸ Duplicate
Double-click the new tab, type 2016_clean
The top of 2016_clean has a blank row and a merged "RDM training" / "Open access" title row. Select those row numbers ▸ right-click ▸ Delete rows, so the real header (Date, Length (hours), …) becomes row 1
Switch back to this slide; wait while everyone does the same in their own copy
Watch the chat for check / x; helpers DM the stuck

THEN

Reiterate: from here on every edit happens in 2016_clean; 2016_messy is never touched. That's the habit.
→ slide 6

Concept 1

The one rule

A rectangular table processes the same way on every row:

every column is one variable
every row is one observation
each cell holds one value for that column

Some layouts are built to be read. A table is built to be processed. You can always build a nice view from a table; you cannot reliably process a pretty layout.

column = one variable ↓

session	date	registered	attended
RDM intro	2016-03-04	18	12
Open access	2016-03-11	22	19
Data mgmt	2016-04-01	15	11
Metadata	2016-04-08	20	17
Licensing	2016-04-15	14	9

← row = one observation

one value per cell

tidy data (Wickham). The grid is rows & columns in a database, cases & variables in stats, observations & variables here

Concept 1 discuss

What would a program choke on?

Look at what's left in 2016_messy. 60 seconds.

Drop one thing in the chat that a script could not read as data.

On Zoom: everyone types at once. No wrong answers; we're building the list together.

you're reading a schema - the column structure a tool expects. When it changes between files that's schema drift

Concept 1 · in practice

One value per cell

GQ & DF in one cell is two people
45|0|0 in one cell is three counts
If you'd sum, filter, or group by it, it needs its own column

atomic values / first normal form (1NF). The Sheets tool is Data ▸ Split text to columns (splitting on a delimiter)

The core exercise demo exercise

Clean the messy training data

Various people recorded training attendance across 2016 and 2017, each their own way. You need to evaluate the programme and start doing statistics.

Working in your copies, make the 2016_messy and 2017_messy data something a computer can read, and put them together in one table.

How we'll run it

I demo the RDM table in 2016_clean
You clean the Open access table beside it, then the 2017 tab
Check against checkpoint_clean / checkpoint_combined
Post your row count in the chat

On Zoom: own copies. Green check = done, red x = stuck. Helpers watch the chat and DM you.

the moves: decomposition (split packed columns), a cancelled flag not colour, consistent nulls; combining years then checking counts is basic reconciliation

9:28-9:53 · core exercise (25 min) · DEMO + EXERCISE · LC Episode 2

SHOW: keep this slide up all segment. Put the task and the checkpoint tab names in the Etherpad.

Layout: 2016_messy and 2017_messy each hold two tables side by side - RDM training on the left, Open access on the right. You demo the left table; learners take the right table, then the 2017 tab.

DO - (a) demo the RDM (left) table in 2016_clean, ~8 min:

Title / blank rows already gone from slide 5. If not: select them ▸ right-click ▸ Delete rows
The PGR|PDRA|other column packs three counts in one cell (e.g. 45|0|0 = 45 postgrads, 0 postdocs, 0 other). Select that column ▸ Data ▸ Split text to columns ▸ separator: Custom ▸ type |
Rename the three new columns pgr, pdra, other
Nulls: replace any stray na / n/a / - in the count columns with an empty cell (this session's null is a blank)
You'll pass a row with a 1900 date - say "that's a date bug, we fix dates after the break", leave it
Narrate every click

DO - (b) learners, ~12 min:

Open access (right) table in 2016_clean: the Len column mixes "1.5 hours", "1 hour", "1 hours" - make each a plain number of hours. Watch for name drift in Delivered by. One row is grey-shaded / has a stray "cancelled" note - add a cancelled column (yes/no), then clear the shading and the note
2017 tab (same side-by-side layout): text dates like "7/8 Feb" and "2 June?"; a 1970 date; GQ & DF in one Delivered-by cell (split into two columns); packed counts with leading zeros like 15|03|00; blank / legend rows at the bottom to delete
Check the result against checkpoint_clean
Post your data row count (rows, not counting the header) in the chat

THEN - reconvene:

Show checkpoint_combined - 2016 and 2017 cleaned the same way, stacked into one table, with a year column added
Row-count check aloud: 2016 rows + 2017 rows = combined table minus one header
Ask whose count differed and why (a cancelled row kept vs dropped, or a stray blank row)
Reiterate the four moves: split packed columns; meaning as a value not colour; one consistent null; then stack the years
→ slide 10

Concept 3

Formatting is not data

Merged titles, colour, spacing, row shading: not data
Grey-for-cancelled had to become a cancelled column
Comments and units in cells go too - use another column
Metadata (the Notes tab) belongs in a separate file

separation of data from presentation - the same idea as content vs. style on the web. Colour-as-data is a known anti-pattern

Practice 1

Zeros and nulls

Fill in zero when you mean a measured zero
Pick one null value for genuinely missing and use it everywhere - for us, a blank cell
Never 0 for missing. Never -999.
Decide what "missing" means here, write it down, check how the next tool reads it

-999 and 0-for-missing are sentinel values / magic numbers. Missing ≠ not-applicable ≠ zero ≠ unknown is null semantics

Concept 4 discuss

A date has three states

These three cells all display 2016-03-04. What might differ underneath? What could be ambiguous?

a stored value vs. how it looks is data type vs. lexical representation; Sheets guessing on entry is type inference. Dates are stored as serial numbers

10:05-10:15 · a date has three states · DISCUSS · LC Episode 3

SHOW: this slide (three identical-looking cells + the question). Leave it up.

Ask: "these all show 2016-03-04 - what could be different underneath? what's ambiguous?" Take 2-3 chat answers
Walk the three states on the slide: a real date value / text that looks like a date / a real date with the wrong day
Switch to the Sheet (dates tab): click a real date, Format ▸ Number ▸ Number - it shows the underlying serial number (e.g. 42433). Click a text date - Format ▸ Number shows nothing numeric, and it's left-aligned
Type =ISNUMBER(A2) next to one of each: TRUE vs FALSE

THEN

Reiterate: ISNUMBER TRUE means it's a real date value - not that the date is correct
→ slide 13 (locale)

Concept 4

Locale, and the safe move

File ▸ Settings ▸ Locale sets the default date format and how Sheets reads ambiguous date text (03/04/2016 as Mar 4 or Apr 3)
Once a real date is stored, locale changes how it looks, not which day it is
Don't use ISDATE() - ISDATE("July 20 1969") is TRUE
Use =ISNUMBER() / =ISTEXT()

Store as year/month/day, or a real date shown yyyy-mm-dd
Set the locale on purpose and record it
Format with the full year before export
After any export, open the file and check the dates

date-text ambiguity is a locale / internationalisation (i18n) problem; yyyy-mm-dd is the ISO 8601 standard

Concept 4 demo exercise

Dates as data

Part 1 · pull the pieces

In dates (2015 data), add three columns: =MONTH(date), =DAY(date), =YEAR(date). Format them as number. Which two rows show the wrong year?

Part 2 · through a CSV and back

Download the dates tab as CSV. Open it in a plain-text editor - what happened to the dates? Re-open it in Sheets - now what?

On Zoom: own copies. Put the wrong-year row numbers in the chat; then say in the chat what the CSV did to the dates.

the wrong-year rows are a data-entry error (entry year vs. event year) that passes every format check; the CSV round-trip is lossy and not reversible

10:20-10:32 · dates as data · DEMO + EXERCISE · LC Episode 3, Challenges 1 & 2

SHOW: this slide, both boxes. Put both in the Etherpad.

DO - Part 1 (~6 min), in the dates tab:

In three empty columns to the right of the data, first data row (row 2): =MONTH(A2), =DAY(A2), =YEAR(A2) (A2 is the date cell)
Format those cells as plain number: Format ▸ Number ▸ Number, so the year doesn't display as a date
Select the three cells, drag the fill handle down to the last data row
Learners do the same; ask for the row numbers where YEAR shows 2017 (should be 2015) in the chat

DO - Part 2 (~4 min):

File ▸ Download ▸ Comma-separated values (.csv) - downloads the dates tab only
Open the downloaded file in TextEdit (Mac) or Notepad (Windows): the dates show as month/day, no year - the CSV kept only the displayed string
Bring it back: File ▸ Import ▸ Upload ▸ pick the CSV ▸ "Insert new sheet(s)". The dates now show with the current year, not 2015
Ask in the chat: what changed, and why

THEN - reconvene:

The two wrong-year rows read 2017 - the year someone typed the data - not 2015, the year of the event. A real date value, wrong day; every format check passes
The same =YEAR trick catches the 1900 date back in 2016_clean
Reiterate: store dates as yyyy-mm-dd or as year/month/day columns; format the full year before you export; open the file and check dates after any export
→ slide 15

Concept 5

QA blocks · QC finds

Data validation on num_registered: whole number, 1 to 100.

Sort and colour-scale to surface odd values already present.

QA = prevention (process), QC = detection (product). "Reject the input" is a constraint / input validation

10:32-10:40 · QA blocks / QC finds · one demo

SHOW: this slide - QA (the gate) vs QC (the flashlight)

DO - switch to the Sheet (dates tab):

Select the num_registered column
Data ▸ Data validation ▸ Add rule
Criteria: Value is a number between 1 and 100 (or Advanced ▸ Whole number, min 1, max 100)
Set "If the data is invalid" to Reject the input
Click a cell in that column, type something invalid (500, or "abc") - Sheets refuses it
Say: you could do the same with a dropdown list on the session-type column, so no one free-types a new spelling

THEN

Reiterate: QA stops bad data at the point of entry; QC finds bad data already in the sheet
Next slide is the QC side
→ slide 16, leave it up

Concept 5 exercise

Sort, then colour-scale

Sort

In dates: Data ▸ Sort range ▸ Advanced, tick Data has header row. Sort len_hours largest to smallest.

Notice anything strange? Try other columns.

Colour scale

Select num_attended. Format ▸ Conditional formatting ▸ Colour scale. Any strange values stand out?

On Zoom: own copies, ~6 minutes. Put what you find in the chat.

scanning sorted extremes is range checking / basic outlier detection. Sorting a partial selection corrupts a dataset - always select the whole table

10:40-10:52 · sort, then colour-scale · EXERCISE · LC Episode 4

SHOW: this slide (Sort box + Colour scale box). Put both in the Etherpad.

DO - no demo; learners work in their own copy of the dates tab (~6 min):

Sort: select the whole dates table (all columns, all rows incl. header) ▸ Data ▸ Sort range ▸ Advanced range sorting options ▸ tick "Data has header row" ▸ sort by len_hours, Z → A
What's odd: "90 min" / "1 hour" / "15 min" as text sort to the top; the big raw numbers 90 / 60 / 15 stand out too
Colour scale: select the num_attended column ▸ Format ▸ Conditional formatting ▸ Colour scale ▸ scan for outliers - two cells sit at 0

THEN - reconvene:

Those two 0s are the cancelled classes - is a 0 a real zero, or a stand-in for "didn't happen"? A data-dictionary call; tie back to slide 11 (zeros and nulls)
Why select the whole table first: sorting a partial selection shears rows away from their keys and corrupts the data
→ slide 17

Practice 2

Export to CSV

A CSV KEEPS

one table of plain text
the values, exactly as displayed

A CSV DROPS

formulas, formatting, tabs, validation
data types - everything becomes text
your change log

File ▸ Download ▸ CSV = current sheet only. Five tabs, five files.
Format dates with the full year first - a CSV keeps only what's shown
GQ, DF is written "GQ, DF" so the comma isn't a new column; the real fix is one value per cell

a CSV is a flat file, machine-readable and non-proprietary; the export is lossy and doesn't round-trip. Library of Congress lists it as a preferred format

Practice 3

Write down what you changed

Keep every clean-up step in a plain text file beside the data - the lesson's advice
The Notes tab in the workbook shows the wrong way: metadata inside the data
"Split trainer column 2026-09-08; 3 rows had two names" is enough

"Why is this number different?" - the answer is your change log.

where data came from is provenance; what happened to it is lineage; the goal is reproducible cleaning

Framing idea

Everything downstream reads your structure

AI does not fix messy data. It gives a fluent, plausible answer even when its assumptions are wrong. Fluent is not correct.

reshape-then-hand-off is the "T" in ETL (extract, transform, load); the whole chain is a data pipeline. Fluent-but-wrong output is hallucination

You now have the words

What the field calls this

one value per cell → atomic values, first normal form
months-as-columns → wide vs long; the reshape is pivoting
value vs. how it looks → data type, type coercion
yyyy-mm-dd → ISO 8601
date-text ambiguity → locale / i18n
one code for "missing" → controlled vocabulary; -999 is a sentinel value

reject bad input → data validation, constraint
QA vs QC → prevention vs detection
CSV → flat file, lossy export
keep the raw file + a log → provenance, lineage, reproducibility
reshape then hand off → the T in ETL
check the AI's rows → reconciliation on a key

You don't need these today. You'll meet them again in R, in a database, in a data-management plan - now they won't be new.

Recap

one column for one variable
one row for one observation
one cell for one value
never modify your raw data; always copy first
keep your clean-up steps in a plain text file

a date is a value plus a display, or just text
QA blocks bad input; QC finds bad values already there
export to CSV for storage and processing
downstream tools, including AI, consume your structure; verify by counting

librarycarpentry.github.io/lc-spreadsheets · Wickham, "Tidy Data", J. Stat. Soft. 59(10) · Cornell, Guide to writing README files · feedback link in the Etherpad