Data Import Hands-on Practice

Files, databases, and web data

Data Import
R4DS
tidyverse
Author

Ian Weidling

Published

July 15, 2026

Overview

These activities accompany the R-LAB session on importing data into R. You will practice three common workflows:

  1. Importing a public CSV and controlling column types.
  2. Querying a database-backed table with dplyr.
  3. Scraping an HTML table from a public webpage.

Each activity is designed for approximately 15 minutes. Work through the tasks before opening the solution.

Before you begin

Install the required packages if needed:

install.packages(c(
  "tidyverse",
  "palmerpenguins",
  "DBI",
  "RSQLite",
  "dbplyr",
  "dplyr",
  "rvest",
  "janitor"
))

Load the packages used throughout the activities:

library(tidyverse)

Exercise 1: Importing files into R

Why this matters

Import functions such as read_csv() make an educated guess about the type of each column. Those guesses are convenient, but analysts should inspect them and override them when a variable should be interpreted differently.

Your task

Use the public Palmer Penguins CSV:

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

Complete the following:

  1. Import the CSV directly from the URL with read_csv().
  2. Use glimpse() to inspect the imported columns.
  3. Identify the type assigned to year.
  4. Re-import the CSV and force year to be a character column.
  5. Confirm that the type changed.
# Write your code here
TipFinish early

Try forcing species to a factor with col_factor() during import. Then inspect the result with glimpse().

Show solution 
penguins <- read_csv(penguins_url)

penguins |> glimpse()
Rows: 344
Columns: 8
$ species           <chr> "Adelie", "Adelie", "Adelie", "Adelie", "Adelie", "A…
$ island            <chr> "Torgersen", "Torgersen", "Torgersen", "Torgersen", …
$ bill_length_mm    <dbl> 39.1, 39.5, 40.3, NA, 36.7, 39.3, 38.9, 39.2, 34.1, …
$ bill_depth_mm     <dbl> 18.7, 17.4, 18.0, NA, 19.3, 20.6, 17.8, 19.6, 18.1, …
$ flipper_length_mm <dbl> 181, 186, 195, NA, 193, 190, 181, 195, 193, 190, 186…
$ body_mass_g       <dbl> 3750, 3800, 3250, NA, 3450, 3650, 3625, 4675, 3475, …
$ sex               <chr> "male", "female", "female", NA, "female", "male", "f…
$ year              <dbl> 2007, 2007, 2007, 2007, 2007, 2007, 2007, 2007, 2007…
penguins_year_chr <- read_csv(
  penguins_url,
  col_types = cols(
    year = col_character()
  )
)

penguins_year_chr |> glimpse()
Rows: 344
Columns: 8
$ species           <chr> "Adelie", "Adelie", "Adelie", "Adelie", "Adelie", "A…
$ island            <chr> "Torgersen", "Torgersen", "Torgersen", "Torgersen", …
$ bill_length_mm    <dbl> 39.1, 39.5, 40.3, NA, 36.7, 39.3, 38.9, 39.2, 34.1, …
$ bill_depth_mm     <dbl> 18.7, 17.4, 18.0, NA, 19.3, 20.6, 17.8, 19.6, 18.1, …
$ flipper_length_mm <dbl> 181, 186, 195, NA, 193, 190, 181, 195, 193, 190, 186…
$ body_mass_g       <dbl> 3750, 3800, 3250, NA, 3450, 3650, 3625, 4675, 3475, …
$ sex               <chr> "male", "female", "female", NA, "female", "male", "f…
$ year              <chr> "2007", "2007", "2007", "2007", "2007", "2007", "200…

Optional extension:

penguins_species_factor <- read_csv(
  penguins_url,
  col_types = cols(
    species = col_factor()
  )
)

penguins_species_factor |> glimpse()
Rows: 344
Columns: 8
$ species           <fct> Adelie, Adelie, Adelie, Adelie, Adelie, Adelie, Adel…
$ island            <chr> "Torgersen", "Torgersen", "Torgersen", "Torgersen", …
$ bill_length_mm    <dbl> 39.1, 39.5, 40.3, NA, 36.7, 39.3, 38.9, 39.2, 34.1, …
$ bill_depth_mm     <dbl> 18.7, 17.4, 18.0, NA, 19.3, 20.6, 17.8, 19.6, 18.1, …
$ flipper_length_mm <dbl> 181, 186, 195, NA, 193, 190, 181, 195, 193, 190, 186…
$ body_mass_g       <dbl> 3750, 3800, 3250, NA, 3450, 3650, 3625, 4675, 3475, …
$ sex               <chr> "male", "female", "female", NA, "female", "male", "f…
$ year              <dbl> 2007, 2007, 2007, 2007, 2007, 2007, 2007, 2007, 2007…

Check your understanding

  • Why might an analyst store a year as character instead of numeric?
  • What could go wrong if an identifier such as "001" were imported as a number?
  • Why should you inspect a dataset immediately after import?

Exercise 2: Databases and large datasets

Why this matters

A database can hold much more data than you want to load into memory. With dbplyr, familiar dplyr code is translated into SQL and run in the database. You should generally filter and summarize first, then use collect() only when the result is small enough to bring into R.

Setup

Run this code to create a temporary in-memory SQLite database containing Palmer Penguins:

library(DBI)
library(RSQLite)
library(dplyr)
library(dbplyr)
library(palmerpenguins)

con <- dbConnect(SQLite(), ":memory:")

copy_to(
  con,
  palmerpenguins::penguins,
  "penguins",
  temporary = FALSE,
  overwrite = TRUE
)

Your task

  1. Reference the database table with tbl().
  2. Filter to Gentoo penguins.
  3. Keep species, island, body_mass_g, and sex.
  4. Group by sex.
  5. Calculate mean body mass with missing values removed.
  6. Use collect() to bring the final summary into R.
  7. Print the result.
# Write your code here
TipFinish early

Before calling collect(), use show_query() to inspect the SQL generated by dbplyr.

Show solution 
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
# A tibble: 3 × 2
  sex    mean_body_mass_g
  <chr>             <dbl>
1 <NA>              4588.
2 female            4680.
3 male              5485.

Inspect the translated SQL:

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)
  ) |>
  show_query()
<SQL>
SELECT `sex`, AVG(`body_mass_g`) AS `mean_body_mass_g`
FROM `penguins`
WHERE (`species` = 'Gentoo')
GROUP BY `sex`

Disconnect when finished

dbDisconnect(con)

Check your understanding

  • At what point did the result become a regular local tibble?
  • Why is it better to summarize before calling collect()?
  • What would happen if you collected a very large table too early?

Exercise 3: Hierarchical data and web scraping

Why this matters

A webpage is a hierarchical document, not a ready-made data frame. Web scraping often returns a list of possible elements, such as several HTML tables. You must inspect that list, select the relevant element, and then clean the resulting rectangular data.

Your task

Use this public webpage:

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

Complete the following:

  1. Read the page with read_html().
  2. Extract every HTML table with html_elements("table").
  3. Convert those elements to tibbles with html_table().
  4. Determine how many tables were found.
  5. Inspect the first table with glimpse().
  6. Select the first four columns.
  7. Clean the column names with janitor::clean_names().
library(rvest)

# Write your code here
Note

Websites can change. Never assume that the table you want will always remain in the same position. Inspect the scraped result before choosing an element.

TipFinish early

Use slice_head(n = 10) to display only the first ten rows, or calculate which rows have the largest reported population.

Show solution 
library(rvest)

page <- read_html(url)

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

length(tables)
[1] 2
population <- tables[[1]]

population |> glimpse()
Rows: 238
Columns: 6
$ `Country or territory`       <chr> "World", "India", "China[a]", "United Sta…
$ `Population(1 July 2022)`    <chr> "8,021,407,192", "1,425,423,212", "1,425,…
$ `Population(1 July 2023)`    <chr> "8,091,734,930", "1,438,069,596", "1,422,…
$ `Change(%)`                  <chr> "+0.88%", "+0.89%", "−0.18%", "+0.57%", "…
$ `UN continentalregion[1]`    <chr> "–", "Asia", "Asia", "Americas", "Asia", …
$ `UN statisticalsubregion[1]` <chr> "–", "Southern Asia", "Eastern Asia", "No…
population_small <- population |>
  select(1:4) |>
  janitor::clean_names()

population_small
# A tibble: 238 × 4
   country_or_territory population_1_july_2022 population_1_july_2023
   <chr>                <chr>                  <chr>                 
 1 World                8,021,407,192          8,091,734,930         
 2 India                1,425,423,212          1,438,069,596         
 3 China[a]             1,425,179,569          1,422,584,933         
 4 United States        341,534,046            343,477,335           
 5 Indonesia            278,830,529            281,190,067           
 6 Pakistan             243,700,667            247,504,495           
 7 Nigeria              223,150,896            227,882,945           
 8 Brazil               210,306,415            211,140,729           
 9 Bangladesh           169,384,897            171,466,990           
10 Russia               145,579,899            145,440,500           
# ℹ 228 more rows
# ℹ 1 more variable: change_percent <chr>

Optional extension:

population_small |>
  slice_head(n = 10)
# A tibble: 10 × 4
   country_or_territory population_1_july_2022 population_1_july_2023
   <chr>                <chr>                  <chr>                 
 1 World                8,021,407,192          8,091,734,930         
 2 India                1,425,423,212          1,438,069,596         
 3 China[a]             1,425,179,569          1,422,584,933         
 4 United States        341,534,046            343,477,335           
 5 Indonesia            278,830,529            281,190,067           
 6 Pakistan             243,700,667            247,504,495           
 7 Nigeria              223,150,896            227,882,945           
 8 Brazil               210,306,415            211,140,729           
 9 Bangladesh           169,384,897            171,466,990           
10 Russia               145,579,899            145,440,500           
# ℹ 1 more variable: change_percent <chr>

Check your understanding

  • Why does extracting all tables produce a list?
  • Why should you inspect the tables before choosing tables[[1]]?
  • How is scraping a webpage different from importing a stable CSV?
  • When might an API be preferable to web scraping?

Wrap-up

After completing these activities, you should be able to:

  • Import a delimited text file from a URL.
  • Inspect and override guessed column types.
  • Query a database table with familiar dplyr syntax.
  • Delay collect() until the result is appropriately small.
  • Extract and inspect HTML tables from a webpage.
  • Recognize that web and API data often begin as hierarchical structures.