Tidy Data (Sep 8) — Session Notes

Notes for the Tidy Data session, Tuesday September 8, 2026, 9:00 am to 12:00 pm Pacific, online. Based on Library Carpentry’s Spreadsheets / Tidy Data for Librarians lesson, taught in Google Sheets.

The first part of this page is a reference you can use during and after the session. The run sheet and instructor guide further down are open for anyone who wants to see how the session is put together or teach it themselves.

• Full self-paced lesson: https://librarycarpentry.github.io/lc-spreadsheets/aio.html
• Collaborative notes (Etherpad): https://pad.carpentries.org/2026-fall-uc-carpentries-tidydate
• Practice sheet (Google Sheets): https://docs.google.com/spreadsheets/d/1NuHEr-1xDKpZHq77e5XDBdRAdROWbwEG/copy — this link prompts you to make your own copy
• Practice data, original xlsx: training_attendance_practice.xlsx
• Slides: </2026-09-08-uc-carpentries/slides/tidy-data/> (press S for the instructor script)
• Instructor run card, one page: RUN-CARD.md
• Full slide-by-slide script, rendered: script.html (plain-markdown source: SCRIPT.md)
• Etherpad starter to paste into the live pad: tidy-data-etherpad.txt

The practice data

training_attendance_practice.xlsx is a teaching build derived from the Library Carpentry training_attendance.xlsx (fictional records of library training sessions, 2015–2017). It is published as a Google Sheet; the “practice sheet” link above prompts you to save your own copy, which you edit for the whole session. Tabs:

• 2016_messy — two side-by-side tables, merged titles, a PGR|PDRA|other column that packs three numbers into one cell, durations written as text (1.5 hours), two dates that landed in 1900, cancelled sessions shown only by grey row shading.
• 2017_messy — schema drifts from 2016; some dates typed as text (7/8 Feb); one date in 1970; two trainers in one cell (GQ & DF); a legend row inside the data.
• dates — already close to tidy. len_hours mixes hours, minutes, and text; two rows dated 2017 should be 2015; cancelled rows carry num_attended 0.
• checkpoint_clean / checkpoint_combined / checkpoint_dates — answer keys.
• ai_input — a tidy table with a source_row_id column, for the AI exercise.
• Notes — kept from the original, an example of metadata living inside the workbook.

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 (GQ & DF in one cell is two people; 45|0|0 in one cell is three counts)
• treat the source file as read-only; work in a named copy

Everything in this session is a version of this rule. Some layouts are built to be read at a glance (titles, colour, spacing). A rectangular table is built to be processed. You can always build a nice view from the table; you cannot reliably process the pretty layout.

Google Sheets: where things live

Functions we use

ISNUMBER returning TRUE means the cell holds a real date, not that the date is correct.

Missing values

• Pick one representation per column and use it everywhere.
• For this session: a blank cell for a genuinely missing value; 0 only for a measured zero.
• Never use 0 for missing. Never use -999.
• In real work: decide what “missing” means here, pick one representation, write it down, then check how the next tool reads it.

Dates

A date in a spreadsheet is in one of three states, and all three can look identical in the cell:

1. a recognised date value — stored as a number, shown through a display format
2. text that happens to look like a date
3. a recognised date with the wrong value in it

Locale (File → Settings → Locale) sets the default date format and how Sheets reads ambiguous date text (03/04/2016 as March 4 or April 3). Once Sheets has stored a real date, changing locale changes how it is written on screen, not which day it is.

The defensive move

• store dates as separate year / month / day columns, or as a recognised date displayed yyyy-mm-dd
• set the locale on purpose and record it
• format dates with the full year before you export — a CSV keeps only what is shown
• after any export, open the file and check the dates

Export to CSV

• A Google Sheet or an .xlsx keeps formulas, formatting, tabs, and validation. Standardised but complex, and software-dependent.
• A CSV is a plain text table: one table, no colour, no formulas, no tabs. Simpler and more portable for a single table, which is why the Library of Congress treats CSV as a preferred format for datasets.
• File → Download → CSV exports the current sheet only. Five tabs means five downloads.
• A value containing a comma (GQ, DF) is written wrapped in quotes ("GQ, DF") so the comma is not read as a new column. Sheets does this for you. The deeper problem is two values in one cell at all.
• CSV does not carry types, formulas, tabs, validation, or your change log. Keep the working Google Sheet alongside the CSV, or export the change log as its own file.

AI tools and your data

• This session’s dataset is fictional. Do not paste real patron, student, personnel, health, licensed, or unpublished research data into an AI service unless that service and that use are approved by your institution.
• Everything that reads your sheet next consumes the structure you built: R, Python, a colleague, a preservation system, Gemini, ChatGPT.
• Tidy data makes all of them work better. Merged cells and colour-as-data break all of them.
• AI does not fix messy data. It produces a fluent, plausible answer even when its assumptions are wrong. Fluent is not correct.
• Check its output the same way every time: compare row count and values against the raw data you kept.

Self-check questions

1. Look at 2016_messy. Name one thing a program would choke on.
2. In a tidy table, the months of the year should be: (a) twelve columns Jan–Dec, (b) one column called month, (c) colour-coded rows. (Answer: b)
3. Turn the RDM table in 2016_messy into a tidy table: one table, header in row 1, one value per cell (split PGR|PDRA|other), a real cancelled column, consistent nulls. Write down your data row count.
4. In 2017_messy, run =ISNUMBER() down the RDM Date column. Which rows come back FALSE (stored as text)? Then in dates, add year with =YEAR(). Which two rows have the wrong year? (the events were in 2015)
5. True or false: if a cell shows 2016-03-04, the file definitely stores March 4 2016. (Answer: false — it could be text, or a real date with a wrong value)
6. Sort the whole dates table by len_hours, largest to smallest. What junk shows up at each end?
7. You sort a table to find outliers. What two things do you do first? (Answer: select the whole table; tick Data has header row)
8. An AI hands you a clean-looking table. First thing you do before you use it? (Answer: compare row count and values against the raw data)

Resources

• Library Carpentry Spreadsheets lesson: https://librarycarpentry.github.io/lc-spreadsheets/
• All-in-one page: https://librarycarpentry.github.io/lc-spreadsheets/aio.html
• Hadley Wickham, “Tidy Data”: https://www.jstatsoft.org/article/view/v059i10
• Cornell, “Guide to writing README files”: https://data.research.cornell.edu/data-management/sharing/readme/

Run sheet

180 minutes. First keyboard action at minute 8; a curated slice of the data rather than a full year; a real 10-minute buffer at the end.

Instructor guide

Should you teach the lesson as-is?

Teach it, but reframe it. The lesson was written around Excel in 2016 and it shows in places (1900 vs 1904 date systems, CR/LF line endings, “which application should I install”). Little of that matters to a general library audience. What the lesson gets right has only become more important: data structure literacy. Columns are variables, rows are observations, one value per cell, raw data left untouched, dates are dangerous, export to open formats.

Since 2016, fewer people need to hand-clean a spreadsheet. Google Sheets autoformats, has version history, and now has Gemini in the sidebar. Excel has Copilot. ChatGPT and Claude will reshape a table on request. So the mechanical skills in the lesson are increasingly things a tool does for you. That makes the lesson a supervision skill: to get usable output from an AI tool or a research assistant, you have to know what tidy data looks like and be able to check whether what came back is correct.

What is timeless

• columns are variables, rows are observations, one value per column per cell
• treat the source file as read-only; work in a named copy
• formatting (colour, position, merged cells) is not data and is absent from a CSV
• a date cell can hold a real date, text that looks like a date, or a real date with a wrong value
• be explicit and consistent about missing values, and decide what missing means
• export to CSV or another open, text-based format
• write down what you changed, somewhere the CSV does not drop it

Adapting the Excel lesson for Google Sheets

Pedagogical approach

• Live demo, learners follow in their own copy. No slides for the hands-on parts.
• Never talk more than ~15 minutes without learners doing something.
• Make deliberate mistakes and recover them on screen.
• Formative checks with a committed answer before the reveal, not “any questions?”.
• Done / stuck signal (reactions or sticky notes); helpers move toward “stuck”.
• Shared collaborative notes with the schedule, the practice Sheet link, and every function and link at the top, so latecomers self-onboard.
• Pair a confident spreadsheet user with a new one, on purpose.
• For a mixed / drop-in group: plan the demo for the middle, give helpers the bottom and an extension task the top, and provide checkpoint_* tabs so anyone can rejoin.

Concept budget

Five core concepts, three practices, one framing idea. Do not add a sixth core concept mid-session.

• C1 a rectangular table processes consistently: columns = variables, rows = observations, one value per column per cell
• C2 treat the source file as read-only; work in a named copy
• C3 formatting is not data
• C4 a recognised date is a value plus a display format; text that looks like a date may still be text
• C5 QA blocks known-invalid entries; QC finds bad values already present
• P1 represent missing values consistently, and decide what missing means
• P2 export to CSV for a portable single table; keep documentation alongside
• P3 write down what you changed, somewhere the CSV does not drop
• F1 downstream tools including AI consume your structure; verify AI output against the raw data

Prep

• The practice Google Sheet is at docs.google.com/spreadsheets/d/1NuHEr-1xDKpZHq77e5XDBdRAdROWbwEG — the /copy link above is the force-copy form. Before the session: confirm sharing is set to “Anyone with the link → Viewer” so learners can copy it, and consider File → Save as Google Sheets to convert it from the uploaded .xlsx for a cleaner experience. Open your own copy to drive the demo.
• The planted problems: 2016_messy has two side-by-side tables, merged titles, the packed PGR|PDRA|other column, 1 hour / 1.5 hours text durations, two dates in 1900, cancelled shown by grey row shading plus one stray cancelled note. 2017_messy has text dates, a 1970 date, GQ & DF, a legend row, missing values. dates has the two 2017→2015 values, a mixed len_hours column, a 1.5 in num_registered, and 0 attendance on cancelled rows.
• Set the Sheet locale on purpose and record it.
• Confirm the current Data Validation wording in your own Google account.
• Run the AI prompt on ai_input; save one genuine flawed output; record model, date, prompt, and input / output row counts. Decide which AI tool you drive; do not assume learners have Gemini in Sheets.

The AI segment

Do not rely on a live model failing in an instructive way. Run “Clean this up and make it tidy” on the ai_input tab before class (12 rows, source_row_id 1–12; rows 5, 7, 11 have a blank num_attended), save an output that dropped a row or mangled a value, and use that as the audit exercise. In class: show the saved output next to ai_input, state the row counts, and have learners find the missing row by comparing source_row_id values. Then ask how they would catch it without the row IDs (row counts before and after; compare to the raw data). Optional live rerun to show output varies.

Takeaway: AI may shift work toward specifying and verifying transformations. It does not remove the need to inspect and clean, and it does not count reliably.

This page and the practice materials are shared under CC BY 4.0. The underlying lesson is Library Carpentry’s, also CC BY 4.0.