Import & Tidy Data

Maya runs a small neighbourhood bakery. Every analysis she will ever do, counting her best-selling loaf, charting sales by week, forecasting next month, starts with two unglamorous steps: getting the numbers off her till and into R, and arranging them into a shape R can actually work with.

Those two steps are this lesson. Skip them and everything downstream fights you; get them right and the rest of the course flows.

By the end you will be able to:

• Read a real CSV into R with read_csv() and check the column types it picked
• Spot and fix the single most common import snag: a number column read as text
• State the three rules of tidy data and reshape a messy table to obey them

Prerequisites: you can run R and load a package with library(). We define every other term as it appears.

A CSV is just text, read_csv brings it in

Maya exports her sales as sales.csv. Open it in any text editor and it is plain text: the first line names the columns, every line after is one sale, with commas between the values. Your R session starts empty, so we write Maya's exact export to a file here, then read it back the way you would read one of your own:

The read_csv() function from the readr package turns that file into a tibble: a tidy table of data, the modern data frame. Each named column becomes a column, each line becomes a row.

Look under each column name: <date>, <chr>, <dbl>. Those are the column types R inferred. They matter, and one of them is already wrong.

readr guesses each column's type

read_csv() reads a sample of values from each column and guesses one type for the whole column: <dbl> for numbers (a "double", any number with or without decimals), <chr> for text ("character"), <date> for ISO dates like 2024-03-01. You can ask it exactly what it decided with spec():

See the problem? units is a count of loaves, it should be a number (<dbl>), but readr filed it under col_character(), text. The culprit is row 4: Maya typed n/a the day she forgot to count the croissants. Because n/a is not a number, readr decided the safest type for the whole column was text.

This is the most common import snag there is. The good news: it is a one-line fix.

Why did units come in as text?

read_csv() read Maya's units column as <chr> (text) instead of <dbl> (a number). What caused that?

Fix it in one argument

Tell read_csv() that n/a means "missing". The na argument lists every token that should be read as a missing value (NA). Add n/a to the usual set so the units column parses as a number. Fill in the blank.

sales <- read_csv("sales.csv",
                  na = c("", "NA", "n/a"))

The data is in. Is it in the right shape?

Maya keeps a second little sheet: loaves sold per item, per week. She built it the natural human way, one column per week:

In R that sheet is just a tibble we can build directly:

Easy to read, but awkward for analysis: the variable week is hidden in the column headers, and the variable units sold is smeared across three columns. To plot units over time, or average them, you would be wrestling the table first.

In 2014 Hadley Wickham named the shape that avoids this. A table is tidy when it follows three rules:

1. One variable per column (here: item, week, and units should each be their own column)
2. One observation per row (one item-week sale per row)
3. One value per cell (never 12 loaves or 2024-03-01/02 crammed into a single cell)

Maya's sheet breaks rule 1: week1, week2, week3 are not three variables, they are three values of the one variable week.

Watch a messy table become tidy

Here is Maya's weekly sheet. Toggle between Wide (her layout) and Long (tidy) and watch what happens: the three week columns collapse into two tidy columns, a week column holding the old headers and a units column holding the values. The tinted cells show exactly where each number lands.

Same numbers, new shape. The tidy table is taller (one row per item-week) and, as you will see, far easier to work with. The reshape itself is one tidyr function, pivot_longer(), which you will run in a moment.

Which table is tidy?

Maya's weekly sheet has item, week1, week2, week3. The reshaped version has item, week, units. Which one is tidy, and why?

Tidy shape makes everything downstream easy

Tidiness is not tidiness for its own sake. The whole tidyverse, every dplyr verb, every ggplot, most models, is built to expect one-variable-per-column, one-observation-per-row. Once Maya's sheet is tidy (you reshape it on the very next screen), the analysis becomes one readable line each. (The %>% below is the pipe; read it as "and then": it hands the table on its left to the next function. Lesson 2 covers it properly.)

# average loaves per item: trivial when week and units are columns
tidy_weekly %>%
  group_by(item) %>%
  summarise(avg_units = mean(units))
#> # A tibble: 3 x 2
#>   item      avg_units
#>   <chr>         <dbl>
#> 1 Bagel          40.7
#> 2 Croissant      15.3
#> 3 Sourdough      21.7

# units over time, one line per item, because week is now a column:
library(ggplot2)
ggplot(tidy_weekly, aes(week, units, colour = item)) +
  geom_line()

Try either of those on the wide sheet and you cannot: there is no single week column to group by or put on the x-axis. You would have to reshape first anyway.

Reshape Maya's sheet to tidy

Use pivot_longer() to fold the three week columns into a tidy pair. cols says which columns to fold, names_to names the column that will hold the old headers (week), and values_to names the column that will hold the numbers. Fill in the last blank.

tidy_weekly <- weekly %>%
  pivot_longer(cols = week1:week3,
               names_to  = "week",
               values_to = "units")

References

A few authoritative places to take this further:

• R for Data Science (2e), Data import - readr, read_csv(), column types, and reading real files, free and canonical.
• R for Data Science (2e), Data tidying - the three rules of tidy data with worked pivot_longer() examples.
• Wickham (2014), Tidy Data, Journal of Statistical Software 59(10) - the original paper that defined the tidy-data principles you just used.
• readr documentation - every argument of read_csv(), including na and col_types for taking control of the import.

Lesson 1 complete

You can now get data off the disk and into the right shape, the foundation everything else stands on. You read a CSV with read_csv(), diagnosed and fixed a column read as the wrong type, and reshaped a messy wide table into tidy form with pivot_longer().

Next, Lesson 2: The dplyr Verbs. With Maya's data now tidy, you will meet the handful of verbs, filter, select, mutate, arrange, that do the actual analysis, and the pipe that chains them into a sentence you can read aloud.