R for Lifestyle and Brain Health (R-LAB)

Data import

Data import is the bridge between real-world data and analysis-ready R objects.

Today we will focus on the practical question every data analysis begins with

Learning goals

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

Choose the right import function for common data sources
Use project-oriented file paths
Diagnose common import problems
Understand when data are loaded immediately versus queried lazily
Recognize hierarchical data and choose a rectangling strategy
Extract simple data from an HTML page

Class plan

We will alternate between short lecture/demo sections and coding breaks.

Section	Topic	Instructor-led	Student practice
1	Importing files into R	~15 min	Coding Break 1 — 10 min
2	Working with databases and large datasets	~15 min	Coding Break 2 — 15 min
3	Hierarchical and web data	~15 min	Coding Break 3 — 10 min

💡 The goal is not to memorize every argument. The goal is to practice the workflow: import → inspect → fix → continue.

The import mindset

Importing data is not complete when the file loads without an error.

A good import workflow asks:

Did the data load?
Are the rows and columns what I expected?
Did R guess the column types correctly?
Are missing values represented correctly?
Is the result ready for transformation or visualization?

Project-oriented workflow

Use relative paths that describe where files live inside your project.

data-import-workshop/
├── data/
│   ├── penguins.csv
│   └── sales.xlsx
├── data_import_practice.qmd
└── data-import-workshop.Rproj

read_csv("data/penguins.csv")

Avoid file paths that only work on your computer, like C:/Users/yourname/Downloads/data.csv.

Section 1: Importing files into R

CSV, TSV, delimited text files, URLs, and spreadsheets

We will start with the file types students are most likely to encounter first.

Common import functions

Data source	Package	Function
CSV	readr	`read_csv()`
TSV	readr	`read_tsv()`
Other delimiters	readr	`read_delim()`
Excel	readxl	`read_excel()`
Google Sheets	googlesheets4	`read_sheet()`

The function you choose should match how the data are stored.

Delimiters

A delimiter is the character that separates one column from the next.

File type	Delimiter	Function
`.csv`	comma	`read_csv()`
`.tsv`	tab	`read_tsv()`
`.txt`	varies	`read_delim()`

read_delim("data/labs.txt", delim = "|")

💡 File extensions can be misleading. If import looks wrong, open the file as plain text and inspect the delimiter.

Read a CSV file

library(tidyverse)

penguins <- read_csv("data/penguins.csv")
penguins
#> # A tibble: 344 × 8
#>   species island    bill_length_mm bill_depth_mm flipper_length_mm body_mass_g sex     year
#>   <chr>   <chr>              <dbl>         <dbl>             <dbl>       <dbl> <chr>  <dbl>
#> 1 Adelie  Torgersen           39.1          18.7               181        3750 male    2007
#> 2 Adelie  Torgersen           39.5          17.4               186        3800 female  2007
#> 3 Adelie  Torgersen           40.3          18                 195        3250 female  2007

Read directly from a URL

penguins_url <- "https://raw.githubusercontent.com/allisonhorst/palmerpenguins/master/inst/extdata/penguins.csv"

penguins <- read_csv(penguins_url)

readr functions can read from local files or URLs.

This is useful for reproducible examples, teaching materials, and public datasets.

Inspect after import

penguins |> glimpse()
#> Rows: 344
#> Columns: 8
#> $ species           <chr> "Adelie", "Adelie", "Adelie", ...
#> $ island            <chr> "Torgersen", "Torgersen", "Torgersen", ...
#> $ bill_length_mm    <dbl> 39.1, 39.5, 40.3, ...
#> $ bill_depth_mm     <dbl> 18.7, 17.4, 18.0, ...
#> $ flipper_length_mm <dbl> 181, 186, 195, ...
#> $ body_mass_g       <dbl> 3750, 3800, 3250, ...
#> $ sex               <chr> "male", "female", "female", ...
#> $ year              <dbl> 2007, 2007, 2007, ...

💡 A successful import should still be checked with glimpse() and, when needed, spec().

Column type guessing and specifications

read_csv() guesses column types automatically.

penguins <- read_csv(penguins_url)
spec(penguins)
#> cols(
#>   species = col_character(),
#>   island = col_character(),
#>   bill_length_mm = col_double(),
#>   bill_depth_mm = col_double(),
#>   flipper_length_mm = col_double(),
#>   body_mass_g = col_double(),
#>   sex = col_character(),
#>   year = col_double()
#> )

💡 Guessing is convenient, but not perfect. Use col_types when a column has meaning that R cannot infer, such as IDs, zip codes, or years you want treated as labels.

penguins <- read_csv(
  penguins_url,
  col_types = cols(
    year = col_character()
  )
)

Why spreadsheets are different

Spreadsheets are designed for humans first and computers second.

Common spreadsheet issues:

Multiple sheets
Extra titles or notes above the data
Merged cells
Non-data formatting
Inconsistent column names

library(readxl)

read_excel("data/sales.xlsx", sheet = "January")

Column names from Excel

Sometimes the first row is not the real header.

read_excel("data/sales.xlsx", skip = 2)

Or you can specify a range:

read_excel("data/sales.xlsx", range = "A3:F20")

💡 janitor::clean_names() can standardize messy names, and purrr::map() can help read many sheets using the same workflow.

Coding Break 1: Read functions

Goal: import a public CSV and control one column type.

Open your practice file and complete the following tasks:

Load the tidyverse.
Read the Palmer Penguins CSV directly from the URL.
Use glimpse() to inspect the imported data.
Re-import the data and force year to be a character column.
Confirm that year changed from numeric to character.

penguins_url <- "https://raw.githubusercontent.com/allisonhorst/palmerpenguins/master/inst/extdata/penguins.csv"

💡 Finish early? Try forcing species to a factor with col_factor().

Coding Break 1: Solution

library(tidyverse)

penguins_url <- "https://raw.githubusercontent.com/allisonhorst/palmerpenguins/master/inst/extdata/penguins.csv"

penguins <- read_csv(penguins_url)
penguins |> glimpse()

penguins_year_chr <- read_csv(
  penguins_url,
  col_types = cols(
    year = col_character()
  )
)

penguins_year_chr |> glimpse()

Section 2: Working with databases and large datasets

Sometimes the data are too large, too shared, or too structured to live in a single CSV.

This section introduces database-backed tables and larger-than-memory data workflows.

Why databases?

Compared with a CSV, a database can:

Store many related tables
Support larger datasets
Let multiple users access the same data
Query only the rows and columns you need
Preserve structure and relationships

With databases, you usually query first and collect later.

Database workflow: connect, reference, then query

When working with a database, R does not usually import the whole dataset right away.

Instead, the workflow is:

Connect to the database
Reference a table with tbl()
Build a query using dplyr verbs
Collect only the result you need

tbl() is like a pointer to a table that lives somewhere else. It lets you work with the table without copying everything into R.

💡 In the coding break, we will create a small temporary database from Palmer Penguins so you can practice this workflow without needing access to a real institutional database.

Use dplyr on a database

You can write familiar dplyr code:

adelie_query <- penguins_db |>
  filter(species == "Adelie") |>
  select(species, island, body_mass_g, sex)

adelie_query

Behind the scenes, dbplyr translates the dplyr pipeline into SQL.

adelie_query |> show_query()

The code feels like dplyr, but the work happens in the database.

Collect results into R

Use collect() when you are ready to bring the result into memory.

adelie_local <- adelie_query |>
  collect()

adelie_local

💡 Disconnect when finished:

dbDisconnect(con)

Where Arrow and Parquet fit

Parquet is a file format; Arrow is a toolkit for working with columnar data efficiently.

Why this matters:

Parquet files are often smaller than CSV files
Columnar storage is efficient for selecting variables
Arrow can work with large data without loading everything at once
The workflow often resembles database work: filter first, collect later

For this course, focus on the concept: large data workflows often avoid loading everything into memory immediately.

Coding Break 2: Database queries

Goal: query a simulated database using dplyr and collect a summary.

Run this setup code first:

library(tidyverse)
library(DBI)
library(RSQLite)
library(dbplyr)
library(palmerpenguins)

con <- dbConnect(SQLite(), ":memory:")
copy_to(con, penguins, "penguins", temporary = FALSE)

Then:

Reference the penguins table with tbl().
Filter to Gentoo penguins.
Select species, island, body_mass_g, and sex.
Group by sex and calculate mean body mass.
Use collect() to bring the result into R.

💡 Finish early? Run show_query() before collect().

Coding Break 2: Solution

library(tidyverse)
library(DBI)
library(RSQLite)
library(dbplyr)
library(palmerpenguins)

con <- dbConnect(SQLite(), ":memory:")
copy_to(con, penguins, "penguins", temporary = FALSE)

penguins_db <- tbl(con, "penguins")

body_mass_summary <- penguins_db |>
  filter(species == "Gentoo") |>
  select(species, island, body_mass_g, sex) |>
  group_by(sex) |>
  summarize(mean_body_mass_g = mean(body_mass_g, na.rm = TRUE)) |>
  collect()

body_mass_summary

dbDisconnect(con)

Section 3: Hierarchical and web data

Not all data arrive as rectangles.

This section covers:

Lists and list-columns
Rectangling nested data
JSON
HTML tables and web scraping

Rectangular versus hierarchical data

A rectangular dataset has rows and columns:

species | island    | body_mass_g
Adelie  | Torgersen | 3750
Adelie  | Torgersen | 3800

A hierarchical dataset is tree-like:

penguin
├── species
├── island
└── measurements
    ├── bill_length_mm
    └── body_mass_g

Rectangling means converting tree-like data into rows and columns.

Lists store hierarchical data

Lists can contain different kinds of objects.

x <- list(
  species = "Adelie",
  measurements = list(
    bill_length_mm = 39.1,
    body_mass_g = 3750
  ),
  tags = c("Torgersen", "male")
)

str(x)
#> List of 3
#>  $ species     : chr "Adelie"
#>  $ measurements:List of 2
#>   ..$ bill_length_mm: num 39.1
#>   ..$ body_mass_g   : num 3750
#>  $ tags        : chr [1:2] "Torgersen" "male"

List-columns

A list-column is a list stored inside a tibble.

df <- tibble(
  id = 1:2,
  bird = c("A", "B"),
  measurements = list(
    list(bill = 39.1, mass = 3750),
    list(bill = 46.5, mass = 4200)
  )
)

df
#> # A tibble: 2 × 3
#>      id bird  measurements
#>   <int> <chr> <list>
#> 1     1 A     <named list [2]>
#> 2     2 B     <named list [2]>

df |> pull(measurements) |> str()
#> List of 2
#>  $ :List of 2
#>   ..$ bill: num 39.1
#>   ..$ mass: num 3750
#>  $ :List of 2
#>   ..$ bill: num 46.5
#>   ..$ mass: num 4200

💡 Tibbles summarize list-columns as <list>. Use pull() + str() to inspect what is inside.

Rectangling decision guide

Choose the function based on what the nested values represent.

Situation	Common tool	Result
Named values should become variables	`unnest_wider()`	More columns
Repeated values should become observations	`unnest_longer()`	More rows
You only need a few pieces	`hoist()`	Selected columns
You are not sure yet	`str()` / `View()`	Inspect structure

Rectangling is iterative: inspect → choose wider/longer/hoist → inspect again.

Compact rectangling example

df <- tibble(
  id = 1:2,
  measurements = list(
    list(bill = 39.1, mass = 3750),
    list(bill = 46.5, mass = 4200)
  ),
  tags = list(
    c("Torgersen", "male"),
    c("Biscoe", "female")
  )
)

# Named elements become columns
df |> unnest_wider(measurements)

# Repeated values become rows
df |> unnest_longer(tags)

What is JSON?

JSON stands for JavaScript Object Notation. It is a common format for storing and exchanging hierarchical data.

You will often encounter JSON when data comes from:

Web APIs
Public data portals
Application logs
Web services
Nested metadata files

JSON can represent structures that do not fit neatly into rows and columns right away:

{
  "name": "Ava",
  "scores": [90, 92],
  "metadata": {"group": "A", "site": "KC"}
}

💡 Many APIs return JSON because it can store nested information. In R, JSON often becomes a list or list-column, so we use rectangling tools to turn it into a tibble.

Read JSON, then rectangle it

library(jsonlite)
library(tidyr)

json <- '[
  {"id": 1, "name": "Ava", "scores": [90, 92]},
  {"id": 2, "name": "Ben", "scores": [85, 88, 91]}
]'

students <- fromJSON(json, simplifyVector = FALSE)

tibble(student = students) |>
  unnest_wider(student) |>
  unnest_longer(scores)
#> # A tibble: 5 × 3
#>      id name  scores
#>   <int> <chr>  <dbl>
#> 1     1 Ava       90
#> 2     1 Ava       92
#> 3     2 Ben       85
#> 4     2 Ben       88
#> 5     2 Ben       91

Web scraping

Web scraping means extracting data from HTML.

A common workflow is:

library(rvest)

page <- read_html("https://example.com")

tables <- page |>
  html_elements("table") |>
  html_table()

💡 Always check whether a site allows scraping, and prefer stable public pages or official APIs when available.

Coding Break 3: Web scraping challenge

Goal: scrape a public HTML table and turn it into a tibble.

Use this public page:

url <- "https://en.wikipedia.org/wiki/List_of_countries_by_population_(United_Nations)"

Tasks:

Read the page with read_html().
Extract all tables with html_elements("table") and html_table().
Pull out the first table.
Use glimpse() to inspect it.
Select a few columns that look useful.

💡 Finish early? Clean the column names with janitor::clean_names(). Because web pages change, inspect the tables before assuming which one you need.

Coding Break 3: Solution

library(tidyverse)
library(rvest)

url <- "https://en.wikipedia.org/wiki/List_of_countries_by_population_(United_Nations)"

page <- read_html(url)

tables <- page |>
  html_elements("table") |>
  html_table()

population <- tables[[1]]

population |> glimpse()

population_small <- population |>
  select(1:4)

population_small

Summary

Source	Key idea
Text files	Choose the function that matches the delimiter
Spreadsheets	Find the actual rectangle inside the workbook
Databases	Query first, `collect()` later
Arrow / Parquet	Large data workflows often avoid loading everything at once
Hierarchical data	Inspect lists and rectangle iteratively
Web pages	Extract HTML elements carefully and check the result

Import is successful only when the object in R matches the meaning of the original data.

Practice files

Continue working in the companion activity file.

Recommended files:

data_import_activities.qmd for the three coding breaks
The rendered slides for reference
The R4DS import chapters for deeper examples