Reading and Writing Data

Loading and Unloading

Last Time

Data cleaning is a set of decisions to ensure the data meets expections given its context and intended use.
Clean reproducibly: raw data is read-only; changes lives in code.
Audit a data set against a checklist before analyzing.

Key Ideas for Today

Plain text files store characters; binary files need special software.
YAML, CSV, and JSON add structure to plain text.

Reading (and Writing) Data

Plain Text File

A file that stores only characters, so it can be opened and read by any text editor, by cat in the shell, or by readLines() in R.

Binary File

A file that stores raw bytes meant to be decoded by specific software. Opened as text, it looks like gibberish.

Every file is bytes on a disk. Only plaintext encodes characters you can read.

Binary Files: PDF and Excel

PDF (.pdf)

Describes a printed page: fonts, positions, images
Great for humans to read
Data inside a PDF table is hard to get back out

Excel (.xlsx)

A zipped bundle of files: cell values, formulas, formatting, multiple sheets
Requires Excel or a package like readxl to decode

readLines("data/New_ZEV_Sales_Last_updated_07-17-2026_ada.xlsx", n = 2)

[1] "PK\003\004\024"                                                                                                                                                                           
[2] "?\x889L\xecҙ\025\xc8sbgٮ|\xc8l!\xf5\xf9\032USh9i\xb0b\x9er:\"y_dl\xc0\xf3D\x9b\xbf\023\xfd|-N\x9c\xc8R\"4\022\xf82\xcfG\xc7%\xa0\xf5\177Z\xb44\xf1˝y\xc47\të\xc8\xf0ɂ\x8b\037\xa8\xde\001"

Reading Excel Files

Recall the ZEV data from the Data Cleaning lecture.

library(readxl)
excel_sheets("data/New_ZEV_Sales_Last_updated_07-17-2026_ada.xlsx")
zev <- read_excel("data/New_ZEV_Sales_Last_updated_07-17-2026_ada.xlsx",
                  sheet = "County")
zev

[1] "Readme" "County" "ZIP"

# A tibble: 77,898 × 7
   `Data Year` Quarter COUNTY        FUEL_TYPE MAKE  MODEL  `Number of Vehicles`
         <dbl>   <dbl> <chr>         <chr>     <chr> <chr>                 <dbl>
 1        2008       3 Alameda       Electric  Tesla Roads…                    1
 2        2008       3 San Francisco Electric  Tesla Roads…                    1
 3        2008       3 San Mateo     Electric  Tesla Roads…                    3
 4        2008       3 Santa Clara   Electric  Tesla Roads…                    4
 5        2008       3 Sonoma        Electric  Tesla Roads…                    1
 6        2008       4 Alameda       Electric  Tesla Roads…                    5
 7        2008       4 Contra Costa  Electric  Tesla Roads…                    1
 8        2008       4 Los Angeles   Electric  Tesla Roads…                   17
 9        2008       4 Marin         Electric  Tesla Roads…                    3
10        2008       4 Napa          Electric  Tesla Roads…                    1
# ℹ 77,888 more rows

readxl only reads. To write a data frame out to .xlsx, use writexl::write_xlsx(), but consider whether a CSV would do.

Plain Text

Extension	Structure	Example from class
`.txt`	None; just lines of characters	Index cards
`.md`	Markdown syntax for formatted prose	`README.md`
`.R`	R code	Your scripts
`.qmd`	YAML + Markdown + code cells	`project.qmd`
`.yml`	`key: value` pairs, nested by indentation	`_schedule.yml`
`.csv`	Rows on lines, columns split by commas	EV registrations
`.json`	Nested `{"key": value}` objects and `[arrays]`	Project 3

`.txt`: Index Cards Revisited

From Data in Trees:

127.txt

Golden Klaxons
3000?

The only structure is the line break.

Base R

readLines("data/127.txt")

[1] "Golden Klaxons" "3000?"

readr

library(readr)
read_lines("data/127.txt")

[1] "Golden Klaxons" "3000?"

Writing `.txt`

Each element of a character vector becomes one line.

card <- c("Golden Klaxons", "3000")

# base R
writeLines(card, "data/127-clean.txt")

# readr
write_lines(card, "data/127-clean.txt")

$ cat data/127-clean.txt
Golden Klaxons
3000

`.md`: Text Plus Markup

A Markdown file is a .txt file with markup: extra characters that tell a renderer how to display the text.

127.md

# Song of Summer

**Golden Klaxons**, card `127`

- Distance: *3000?* miles
- Source: index card

Song of Summer

Golden Klaxons, card 127

Distance: 3000? miles
Source: index card

readLines("data/127.md")

[1] "# Song of Summer"               ""                              
[3] "**Golden Klaxons**, card `127`" ""                              
[5] "- Distance: *3000?* miles"      "- Source: index card"

`.R`

An R script is plain text containing R code.

read-cards.R

contents <- c()
notecard_files <- dir()

for (notecard_file in notecard_files) {
    notecard <- readLines(notecard_file)
    contents <- c(contents, notecard)
}

`.qmd`: Three Languages in One File

hello-penguins.qmd

---
title: "Hello, Penguins!"
format: html
---

## Data

For this analysis we'll use the penguins dataset from the palmerpenguins package.


::: {.cell}

```{.r .cell-code}
library(tidyverse)
library(ggthemes)
library(palmerpenguins)
library(gt)
```
:::

YAML metadata, Markdown prose, and R code cells, all in one plain text file that Quarto knows how to split apart.

YAML

A plain text format for configuration and metadata built from key: value pairs. Indentation marks nesting and a leading - marks an item in a list.

The course website’s schedule is a YAML file:

_schedule.yml (excerpt)

topic: "Data in Trees"
display-date: Aug 28
date: "2026-08-28"
image: 02-data-in-trees/images/tree-banner.webp
docs:
  - type: Slides
    href: 02-data-in-trees/slides.qmd
  - type: Project
    href: projects/1-index-cards/project.qmd

Reading YAML into an R list

library(yaml)
day <- read_yaml("data/data-in-trees-day.yml")
str(day)

List of 5
 $ topic       : chr "Data in Trees"
 $ display-date: chr "Aug 28"
 $ date        : chr "2026-08-28"
 $ image       : chr "02-data-in-trees/images/tree-banner.webp"
 $ docs        :List of 2
  ..$ :List of 2
  .. ..$ type: chr "Slides"
  .. ..$ href: chr "02-data-in-trees/slides.qmd"
  ..$ :List of 2
  .. ..$ type: chr "Project"
  .. ..$ href: chr "projects/1-index-cards/project.qmd"

day$docs[[2]]$href

[1] "projects/1-index-cards/project.qmd"

Write a list back out with write_yaml(day, "day.yml").

CSV

A comma-separated values file stores tabular data with each row on a new line and each column delimited by a comma. A lingua franca for storing data frames in your file system.

What does a data frame look like as plain text?

library(palmerpenguins)
penguins |>
  head(3) |>
  format_csv() |>
  cat()

species,island,bill_length_mm,bill_depth_mm,flipper_length_mm,body_mass_g,sex,year
Adelie,Torgersen,39.1,18.7,181,3750,male,2007
Adelie,Torgersen,39.5,17.4,186,3800,female,2007
Adelie,Torgersen,40.3,18,195,3250,female,2007

Structure of a CSV

First line: column names (the header)
One row per line, columns separated by commas
Missing values written as NA

A CSV in the Wild

Electric vehicle registrations by state, which we’ll explore next time.

(base) andrewbray@MacBookPro fall-2026-private % cd 16-reading-writing/data
(base) andrewbray@MacBookPro data % ls
ev-registrations.csv    ev-xlsx.png
(base) andrewbray@MacBookPro data % cat ev-registrations.csv
State,Count
Alabama,"13,047"
Alaska,"2,697"
Arizona,"89,798"
Arkansas,"7,108"
California,"1,256,646"
Colorado,"90,083"
Connecticut,"31,557"
Delaware,"8,435"
District of Columbia,"8,066"
Florida,"254,878"
Georgia,"92,368"
Hawaii,"25,565"
Idaho,"8,501"
Illinois,"99,573"
Indiana,"26,101"
Iowa,"9,031"
Kansas,"11,271"
Kentucky,"11,617"
Louisiana,"8,150"
Maine,"7,377"
Maryland,"72,139"
Massachusetts,"73,768"
Michigan,"50,284"
Minnesota,"37,050"
Mississippi,"3,590"
Missouri,"26,861"
Montana,"4,608"
Nebraska,"6,920"
Nevada,"47,361"
New Hampshire,"9,861"
New Jersey,"134,753"
New Mexico,"10,276"
New York,"131,250"
North Carolina,"70,164"
North Dakota,959
Ohio,"50,393"
Oklahoma,"22,843"
Oregon,"64,361"
Pennsylvania,"70,154"
Rhode Island,"6,396"
South Carolina,"20,873"
South Dakota,"1,675"
Tennessee,"33,221"
Texas,"230,125"
Utah,"39,998"
Vermont,"7,816"
Virginia,"84,936"
Washington,"152,101"
West Virginia,"2,758"
Wisconsin,"24,943"
Wyoming,"1,139"

Reading CSVs

ev <- read.csv("data/ev-registrations.csv")
str(ev)

'data.frame':   51 obs. of  2 variables:
 $ State: chr  "Alabama" "Alaska" "Arizona" "Arkansas" ...
 $ Count: chr  "13,047" "2,697" "89,798" "7,108" ...

Count came in as character: "13,047" isn’t a number to base R.

Reading CSVs

`readr`

library(readr)
# library(tidyverse) # easy alternative
ev <- read_csv("data/ev-registrations.csv")
ev

# A tibble: 51 × 2
   State                  Count
   <chr>                  <dbl>
 1 Alabama                13047
 2 Alaska                  2697
 3 Arizona                89798
 4 Arkansas                7108
 5 California           1256646
 6 Colorado               90083
 7 Connecticut            31557
 8 Delaware                8435
 9 District of Columbia    8066
10 Florida               254878
# ℹ 41 more rows

. . .

read_csv() reports the column types it guessed and recognizes "13,047" as the number 13047.

Writing CSVs

# base R
write.csv(penguins, "data/penguins.csv", row.names = FALSE)

# readr
write_csv(penguins, "data/penguins.csv")

Without row.names = FALSE, base R adds an unnamed column of row numbers to the file.

JSON

JavaScript Object Notation: a plain text format of nested objects ({"key": value}) and arrays ([a, b, c]). The most common format for data sent over the web.

cards.json

[
  {"id": 127, "song": "Golden Klaxons", "distance": "3000?"},
  {"id": 14,  "song": "Espresso",       "distance": "40"},
  {"id": 3,   "song": "Let It Go",      "distance": "1200"}
]

Reading and Writing JSON

An array of objects that share the same keys becomes a data frame.

library(jsonlite)
cards <- fromJSON("data/cards.json")
cards

   id           song distance
1 127 Golden Klaxons    3000?
2  14       Espresso       40
3   3      Let It Go     1200

Reading and Writing JSON

An array of objects that share the same keys becomes a data frame.

toJSON(cards[1, ], pretty = TRUE)

[
  {
    "id": 127,
    "song": "Golden Klaxons",
    "distance": "3000?"
  }
]

To write to a file: write_json(cards, "data/cards.json").

Writing JSON and YAML

A key-value pair: "key": "value" wrapped in { } (JSON); key: value (YAML)
Multiple pairs:
A list:
A list of key-value pairs:

playlist.json

{
  "name": "Study Mix"
}

playlist.yml

name: Study Mix

Writing JSON and YAML

A key-value pair: "key": "value" wrapped in { } (JSON); key: value (YAML)
Multiple pairs: separated by commas (JSON); one per line (YAML)
A list:
A list of key-value pairs:

playlist.json

{
  "name": "Study Mix",
  "owner": "Ana"
}

playlist.yml

name: Study Mix
owner: Ana

Writing JSON and YAML

A key-value pair: "key": "value" wrapped in { } (JSON); key: value (YAML)
Multiple pairs: separated by commas (JSON); one per line (YAML)
A list: items in [ ], separated by commas (JSON); indented items, each starting with - (YAML)
A list of key-value pairs:

playlist.json

{
  "name": "Study Mix",
  "owner": "Ana",
  "tags": ["lofi", "focus"]
}

playlist.yml

name: Study Mix
owner: Ana
tags:
  - lofi
  - focus

Writing JSON and YAML

A key-value pair: "key": "value" wrapped in { } (JSON); key: value (YAML)
Multiple pairs: separated by commas (JSON); one per line (YAML)
A list: items in [ ], separated by commas (JSON); indented items, each starting with - (YAML)
A list of key-value pairs: { } objects inside [ ] (JSON); pairs indented under each - (YAML)

playlist.json

{
  "name": "Study Mix",
  "owner": "Ana",
  "tags": ["lofi", "focus"],
  "songs": [
    {"title": "Espresso", "artist": "Sabrina Carpenter"},
    {"title": "Let It Go", "artist": "Idina Menzel"}
  ]
}

playlist.yml

name: Study Mix
owner: Ana
tags:
  - lofi
  - focus
songs:
  - title: Espresso
    artist: Sabrina Carpenter
  - title: Let It Go
    artist: Idina Menzel

Reading and Writing: Summary

Format	Base R	Tidyverse and friends
`.txt`	`readLines()` / `writeLines()`	`read_lines()` / `write_lines()` (`readr`)
`.csv`	`read.csv()` / `write.csv()`	`read_csv()` / `write_csv()` (`readr`)
`.yml`		`read_yaml()` / `write_yaml()` (`yaml`)
`.json`		`fromJSON()` / `write_json()` (`jsonlite`)
`.xlsx`		`read_excel()` (`readxl`) / `write_xlsx()` (`writexl`)

Readr

Why prefer readr?

Returns a tibble
Reports the column types it guessed
Consistent read_*() / write_*() naming
Faster on large files

Guess the Metaphor

Reading and writing data is like working the docks: cargo (data) arrives packed in containers (files). Some crates you can pry open by hand (plain text); others need special tools (binary). Reading unloads the cargo into town (R); writing packs it back up to ship out.

Project 3: Your Life in Data

Potential Apps

Request your data now!

Google Takeout (Chrome, Search, Maps)
Netflix
Spotify
Uber / Lyft

Youtube
Instagram / Facebook / Discord
Doordash
Venmo

Look for multiple data files and at least one JSON.

Key Ideas for Today

Plain text files store characters; binary files need special software.
YAML, CSV, and JSON add structure to plain text.

For Next Time

PS 3 due Saturday 10/03 at 11:59 pm.
Quiz 3 is Mon/Tue (10/5-10/6). Sign up on PrairieTest.
Request your personal data exports for Project 3.

Reading and Writing Data

Last Time

Key Ideas for Today

Reading (and Writing) Data

Binary Files: PDF and Excel

Reading Excel Files

Plain Text

.txt: Index Cards Revisited

Writing .txt

.md: Text Plus Markup

.R

.qmd: Three Languages in One File

Reading YAML into an R list

Structure of a CSV

A CSV in the Wild

Reading CSVs

Reading CSVs

readr

Writing CSVs

Reading and Writing JSON

Reading and Writing JSON

Writing JSON and YAML

Writing JSON and YAML

Writing JSON and YAML

Writing JSON and YAML

Reading and Writing: Summary

Readr

Guess the Metaphor

Project 3: Your Life in Data

Potential Apps

Key Ideas for Today

For Next Time

`.txt`: Index Cards Revisited

Writing `.txt`

`.md`: Text Plus Markup

`.R`

`.qmd`: Three Languages in One File

`readr`