Tidying and joining data

Session 6

2026-03-25

1 Overview

Why tidy your data?

• Make it accessible for analysis
• Make it easier to visualize
• Make it easier to document and share
• Combine data from a variety of sources
• Use data with some other service (e.g. geocoding)

Where does untidy data come from?

• Software (e.g. Excel issues with auto-formatting of dates)
• Intentional and unintentional choices by users (e.g. color-coding to encode status or spelling errors)
• Policy changes (e.g. adding or removing categories in a scale)

2 What is involved in tidying messy datasets?

• Column headers are values, not variable names
• Multiple variables stored in one column
• Variables are stored in both rows and columns
• Multiple types in one table
• One type in multiple tables

See Tidy data vignette or the Tidy data paper by Hadley Wickham (2014) for more details.

from Tidy data vignette https://tidyr.tidyverse.org/articles/tidy-data.html

How can we use the tidyverse for tidying?

Tidying data often requires you to use packages focused on different types of data:

• stringr for strings (and regular expressions)
• forcats for factors (aka categorical variables)
• lubridate for date and times

What else can we use for tidying?

There are also specialized packages that can help:

• janitor for “examining and cleaning dirty data”
• naniar to help “summarise, visualise, and manipulate missing data”
• tidylog provides “feedback about dplyr and tidyr operations” (useful for interactive coding especially as a beginner)

What else can we use for tidying?

There are also non-R tools that can be helpful:

• Tabula for extracting tables from PDFs
• OpenRefine for reproducibly cleaning messy data
• Airtable for integrating tables into databases

3 Using tidyr

Categories of tidyr functions:

1. Pivoting
2. Rectangling**
3. Nesting and unnesting
4. Splitting and combining character columns
5. Handling missing values

From https://tidyr.tidyverse.org/#getting-started

“Pivoting” which converts between long and wide forms. tidyr 1.0.0 introduces pivot_longer() and pivot_wider(), replacing the older spread() and gather() functions. See vignette(“pivot”) for more details.

“Rectangling”, which turns deeply nested lists (as from JSON) into tidy tibbles. See unnest_longer(), unnest_wider(), hoist(), and vignette(“rectangle”) for more details.

Nesting converts grouped data to a form where each group becomes a single row containing a nested data frame, and unnesting does the opposite. See nest(), unnest(), and vignette(“nest”) for more details.

Splitting and combining character columns. Use separate() and extract() to pull a single character column into multiple columns; use unite() to combine multiple columns into a single character column.

Make implicit missing values explicit with complete(); make explicit missing values implicit with drop_na(); replace missing values with next/previous value with fill(), or a known value with replace_na().

4 Pivoting

5 How would this look if we turn it into tidy data?

City	2000	2010	2020
Baltimore city, Maryland	300000	290000	285000
Washington, DC	300000	315000	330000
Richmond, Virginia	200000	202000	210000

City	Year	Value
Baltimore city, Maryland	2000	300000
Baltimore city, Maryland	2010	290000
Baltimore city, Maryland	2020	285000

…

pivot_longer(
  cols = !any_of("City"),
  names_to = "Year",
  values_to = "Value"
)

6 Tidying messy datasets

• Column headers are values, not variable names
• Multiple variables stored in one column
• Variables are stored in both rows and columns
• Multiple types in one table
• One type in multiple tables

7 How would this look if we turn it into tidy data?

City name	2000	2010	2020
Baltimore city, Maryland	300000	290000	285000
Washington, DC	300000	315000	330000
Richmond, Virginia	200000	202000	210000

City	State	Year	Value
Baltimore city	Maryland	2000	300000
Washington	DC	2000	300000
Richmond	Virginia	2000	200000

…

8 Data organization in spreadsheets

What are the benefits of careful data organization in spreadsheets?

• reduce errors
• ease later analyses

What are the practical approaches for data organization in spreadsheets?

1. be consistent,
2. write dates like YYYY-MM-DD,
3. do not leave any cells empty,
4. put just one thing in a cell,
5. organize the data as a single rectangle (with subjects as rows and variables as columns, and with a single header row),
6. create a data dictionary,

What are the practical approaches for data organization in spreadsheets?

7. do not include calculations in the raw data files,
8. do not use font color or highlighting as data,
9. choose good names for things,
10. make backups,
11. use data validation to avoid data entry errors,
12. and save the data in plain text files.