Writing Data, Splitting and Combining Data Frames

Week 6 · Thursday, Oct 29 · Lecture

Dr. Marcela Alfaro Córdoba

Today’s plan

Time	What
1:30	DS 5 check: swap laptops
1:45	Writing data: from code, into `results/`
2:10	Splitting: one data frame into many
2:30	Combining: stacking rows, matching by key
2:55	Tuesday, and the mid-quarter report

Learning goals: write a data frame to CSV and check it by reading it back, split a data frame by group, stack pieces back together, and attach a lookup table by a key column, counting rows at every step.

Still no AI. Before we start, download sites.csv into your data/ folder.

DS 5 check: swap laptops

⏱ 15 min

Swap laptops with your neighbor and open their week05/ds05_notes.R.

Explain what you did. Each of you walks the other through one exercise, on the screen, line by line
Discuss the differences. Where did your code differ? Do both versions do the same thing?
Agree on one topic: the most difficult one this week, or one that you didn’t understand

On the Ed thread “DS 5 check”, one post per pair: your topic, and one sentence on why it was hard.

The 1-point discussion check. I read every post, and they decide what we review.

Writing data

Today’s script

Create week06/writing-combining.R:

# Week 6 · Writing data, splitting and combining
source("scripts/load-gapminder.R")
source("scripts/check-gapminder.R")
source("scripts/clean-survey.R")
library(dplyr)

And a new folder, next to data/ and scripts/:

dir.create("results")

Run dir.create() once, in the console. It doesn’t belong in the script.

Outputs belong in `results/`

stat266a/
├── data/        <- raw files, never edited
├── scripts/     <- code you source()
├── figures/     <- plots, made by ggsave()
├── results/     <- new: cleaned data and tables, made by code
└── week06/

Anything in figures/ or results/ can be deleted and remade by running a script.

Important

Never write over a file in data/. If the raw file changes, you can’t check your cleaning against it anymore.

`write.csv()`

write.csv(survey, "results/survey_clean.csv")
readLines("results/survey_clean.csv", n = 3)
#> [1] "\"\",\"id\",\"site\",\"age\",\"commute_min\",\"visits\""
#> [2] "\"1\",1,\"east\",26,23,1"
#> [3] "\"2\",2,\"north\",38,11,6"

An unnamed first column appeared. Read the file back and see what it becomes:

names(read.csv("results/survey_clean.csv"))
#> [1] "X"           "id"          "site"        "age"         "commute_min" "visits"

Those are the row names, written as data. Read the file back and you get a column X you never made. Every time you round-trip the file, you add another one.

`row.names = FALSE`

write.csv(survey, "results/survey_clean.csv", row.names = FALSE)
readLines("results/survey_clean.csv", n = 3)
#> [1] "\"id\",\"site\",\"age\",\"commute_min\",\"visits\""
#> [2] "1,\"east\",26,23,1"
#> [3] "2,\"north\",38,11,6"

Tip

Every write.csv() you write in this course: row.names = FALSE. (readr::write_csv() does this by default, if you prefer it.)

Check the round trip

back <- read.csv("results/survey_clean.csv")
dim(back)                 #> [1] 45  5
dim(survey)               #> [1] 45  5
str(back)
#> 'data.frame':    45 obs. of  5 variables:
#>  $ id         : int  1 2 3 4 5 6 7 8 9 10 ...
#>  $ site       : chr  "east" "north" "east" "north" ...
#>  $ age        : int  26 38 39 18 21 NA 23 28 39 37 ...
#>  $ commute_min: int  23 11 17 7 18 21 16 24 14 18 ...
#>  $ visits     : int  1 6 0 0 4 6 0 5 5 5 ...

Same rows, same columns, the NAs are back as NA
commute_min was num and came back int: a CSV only stores text, so R guesses the type again when it reads

A CSV keeps values, not types. Factors, dates, and the order of factor levels are not saved. That’s why every analysis script reads the CSV and then checks it.

Several plots, one PDF

Week 4: ggsave() writes one plot per file. For a multi-page PDF, open a device, print each plot, close it:

library(ggplot2)
p <- ggplot(gapminder, aes(x = gdpPercap, y = lifeExp)) +
  geom_point() +
  scale_x_log10()

pdf("figures/le-gdp-pages.pdf", width = 8, height = 5)
print(p)                               # page 1
print(p + facet_wrap(~ continent))     # page 2
dev.off()

Warning

Until dev.off() runs, the file isn’t finished, and your plots won’t show in Positron either. If plots “disappear”, run dev.off() until it says null device.

Your turn: write it, read it back

⏱ 8 min

In your script:

Make gm07: the gapminder rows for 2007, with only country, continent, lifeExp and gdpPercap
Write it to results/gm07.csv, with no row names
Read it back into back. Check three things: dim(), names(), and that back$lifeExp matches gm07$lifeExp
Open results/gm07.csv in Positron. What’s in the first line?

gm07 <- gapminder[gapminder$year == 2007, c("country", "continent", "lifeExp", "gdpPercap")]
write.csv(gm07, "results/gm07.csv", row.names = FALSE)
back <- read.csv("results/gm07.csv")
dim(back)                                  #> [1] 142   4
identical(names(back), names(gm07))        #> [1] TRUE
all.equal(back$lifeExp, gm07$lifeExp)      #> [1] TRUE

all.equal(), not ==: it allows for the tiny rounding of numbers written as text.

Splitting

One data frame, one piece per group

by_site <- split(survey, survey$site)
class(by_site)         #> [1] "list"
names(by_site)         #> [1] "east"  "north" "south"
by_site$east[1:3, ]
#>   id site age commute_min visits
#> 1  1 east  26          23      1
#> 3  3 east  39          17      0
#> 7  7 east  23          16      0

split(data, group) gives a list of data frames, one per value of group
Each piece keeps every column, and its original row names

Count the rows

sapply(by_site, nrow)
#>  east north south
#>    11    15    18
sum(sapply(by_site, nrow))     #> [1] 44

The survey has 45 rows.

Warning

split() drops rows whose group is NA: here, the person with no site (id 27). No warning. You only know because you counted.

What you do with the pieces

The same thing to every piece, with Week 5’s sapply():

sapply(by_site, function(d) mean(d$commute_min, na.rm = TRUE))
#>     east    north    south
#> 21.18182 20.78571 19.22222

Or one file per group, with Week 3’s loop:

for (s in names(by_site)) {
  write.csv(by_site[[s]], paste0("results/survey_", s, ".csv"), row.names = FALSE)
}
list.files("results")
#> [1] "gm07.csv"         "survey_clean.csv" "survey_east.csv"
#> [4] "survey_north.csv" "survey_south.csv"

by_site[[s]]: double brackets give you the data frame itself; single brackets would give you a list of one.

Your turn: split gapminder

⏱ 6 min

Split gapminder by continent into by_cont
How many rows in each piece? Do they add up to 1704?
One line: the maximum lifeExp in each continent
Predict: how many rows would split(gapminder, gapminder$year) give per piece? Check

by_cont <- split(gapminder, gapminder$continent)
sapply(by_cont, nrow)
#>   Africa Americas     Asia   Europe  Oceania
#>      624      300      396      360       24
sapply(by_cont, function(d) max(d$lifeExp))

624 + 300 + 396 + 360 + 24 = 1704: no NAs in continent, so nothing dropped. By year: 12 pieces of 142, one row per country.

Combining

Two ways to combine

Stacking: same columns, more rows

id site ...       id site ...
 1 east     +    101 east
 3 east          102 south

rbind(), bind_rows()

Matching: same rows, more columns

site  ...   +   site  building
east            east  Oak Hall
north           north Pine Hall

merge(), next week: joins

Important

Before and after every combine: count the rows. Know what number you expect before you look.

Stacking the pieces back

all3 <- rbind(by_site$east, by_site$north, by_site$south)
nrow(all3)                   #> [1] 44

bind_rows() from dplyr stacks a whole list at once, and can record where each row came from:

all3 <- bind_rows(by_site, .id = "group")
nrow(all3)                   #> [1] 44
head(all3, 3)
#>   group id site age commute_min visits
#> 1  east  1 east  26          23      1
#> 2  east  3 east  39          17      0
#> 3  east  7 east  23          16      0

Still 44: what split() dropped doesn’t come back. Splitting and stacking are only a round trip when the group column has no NAs.

When the columns don’t match

Take away commute_min from one piece:

x <- by_site$east                                       # 11 rows, 5 columns
y <- by_site$north[, c("id", "site", "age", "visits")]  # 15 rows, 4 columns

rbind(x, y)
#> Error in rbind(deparse.level, ...) : numbers of columns of arguments do not match

z <- bind_rows(x, y)
nrow(z)                      #> [1] 26
sum(is.na(z$commute_min))    #> [1] 15

	Columns don’t match
`rbind()`	error: you have to fix it
`bind_rows()`	fills the gaps with `NA`, no message

bind_rows() is more convenient and less careful. After it: colSums(is.na(z)). 15 new NAs you didn’t have before means a column didn’t line up.

Matching: a lookup table

data/sites.csv: one row per site.

sites <- read.csv("data/sites.csv")
sites
#>    site   building km_to_library
#> 1  east   Oak Hall           1.2
#> 2 north  Pine Hall           0.6
#> 3 south Cedar Hall           0.9
#> 4  west   Elm Hall           1.8

Question: do people living farther from the library visit it less? We need km_to_library on every row of survey.

west is a site where nobody in the survey lives. Keep an eye on it.

`cbind()` is not the tool

cbind(survey[1:4, ], sites)
#>   id  site age commute_min visits  site   building km_to_library
#> 1  1  east  26          23      1  east   Oak Hall           1.2
#> 2  2 north  38          11      6 north  Pine Hall           0.6
#> 3  3  east  39          17      0 south Cedar Hall           0.9
#> 4  4 north  18           7      0  west   Elm Hall           1.8

Row 3 lives in east and got Cedar Hall. No error.

cbind() glues by position: row 1 with row 1. That’s right only when both tables are in the same order, row for row. A lookup table almost never is.

`merge()`: match by key

m <- merge(survey, sites, by = "site")
nrow(m)                     #> [1] 44
head(m, 3)
#>   site id age commute_min visits building km_to_library
#> 1 east 23  37          42      3 Oak Hall           1.2
#> 2 east  3  39          17      0 Oak Hall           1.2
#> 3 east 30  22          13      4 Oak Hall           1.2

For each row of survey, find the row of sites with the same site, and add its columns
The key column comes first, and the rows are re-sorted by it
44 again: the person with site = NA had no match, and merge() dropped them

Keep every survey row: `all.x = TRUE`

m <- merge(survey, sites, by = "site", all.x = TRUE)
nrow(m)                     #> [1] 45
m[is.na(m$site), ]
#>    site id age commute_min visits building km_to_library
#> 45 <NA> 27  33          11      5     <NA>            NA

	Keeps	Rows here
`merge(x, y)`	only rows with a match in both	44
`merge(x, y, all.x = TRUE)`	every row of `x`; `NA` where `y` has no match	45
`merge(x, y, all = TRUE)`	every row of both	46: adds `west`, with `NA` survey values

For “add information to my data”, you almost always want all.x = TRUE: the number of rows should stay what it was. Next week, dplyr calls this a left_join().

Your turn: combine and count

⏱ 10 min

Before each line: write down the number of rows you expect. Then run it.

merge() survey with sites, keeping every survey row. Call it survey_sites
Average visits for each value of km_to_library (hint: tapply()). Closer, more visits?
Run merge(survey, sites, all = TRUE). Which row is new, and what’s in it?
Write survey_sites to results/survey_sites.csv, read it back, check dim()

survey_sites <- merge(survey, sites, by = "site", all.x = TRUE)    # 45
tapply(survey_sites$visits, survey_sites$km_to_library, mean)
#>      0.6      0.9      1.2
#> 3.400000 3.944444 2.545455

No clear pattern: east is farthest and visits least, but north is closest and doesn’t visit most. Three sites isn’t much to go on.

Key points

Outputs go to results/ and figures/, made by code. data/ is never written to
write.csv(x, file, row.names = FALSE), then read it back and check dim(), names(), str()
A CSV stores values, not types: R guesses them again on every read
pdf(), print() each plot, dev.off(): several plots in one file
split(df, group) gives a list of data frames, and drops rows where group is NA
Stack with rbind() (errors on mismatched columns) or bind_rows() (fills with NA, silently)
Match by key with merge(), never cbind(). all.x = TRUE keeps every row of your data
Count rows before and after every split, stack, or merge

Tuesday, Nov 3

Tuesday is a full day

9:50-10:55 am · DS 6

Lesson 12: Writing Data and Lesson 13: dplyr I, alone, no AI. Two PDFs.

1:30-3:05 pm · Combining data practice I

Starts with the DS 6 check: swap laptops, same as today. Then a second wave of the survey to stack, match and check.

Important

Mid-quarter report due Tuesday. AI is allowed with disclosure: include the “AI use” section (template) and, in a pair, a contribution statement.

Before you go

week06/writing-combining.R restarts and sources cleanly
results/ has gm07.csv, the three survey_<site>.csv files, and survey_sites.csv
Project: does your cleaning script write the cleaned data to results/? Does your report read it from there?
Project: if you combine two sources, write down the row count you expect, and check it

Before Tuesday: read the Lesson 12 and Lesson 13 pages. Lesson 13 introduces the pipe |>; the DS is your first practice with it.

Next: Tuesday, Nov 3 · DS 6 in the morning, combining data practice in the afternoon.

Writing Data, Splitting and Combining Data Frames

Today’s plan

DS 5 check: swap laptops

Writing data

Today’s script

Outputs belong in results/

write.csv()

row.names = FALSE

Check the round trip

Several plots, one PDF

Your turn: write it, read it back

Splitting

One data frame, one piece per group

Count the rows

What you do with the pieces

Your turn: split gapminder

Combining

Two ways to combine

Stacking the pieces back

When the columns don’t match

Matching: a lookup table

cbind() is not the tool

merge(): match by key

Keep every survey row: all.x = TRUE

Your turn: combine and count

Key points

Tuesday, Nov 3

Tuesday is a full day

Before you go

Outputs belong in `results/`

`write.csv()`

`row.names = FALSE`

`cbind()` is not the tool

`merge()`: match by key

Keep every survey row: `all.x = TRUE`