Programming for Applications

Chapter 12: Preparing Data

Yu-You Liou (NTU)

Shih Chien University

2026-08-03

Where 80% of the Time Goes

The Dirty Secret of Data Analysis

The book’s author started as a biochemist, fled to computer science to escape endless lab preparation — and landed in data mining, where ~80% of project effort goes to finding, cleaning, and preparing data, under 5% to actual analysis (the rest to writing it up).

“It came from computers, so it’s perfect, right?” In practice data is almost never stored in the right form, and even well-formed data hides surprises. This chapter is the toolbox: combining, transforming, binning, subsetting, summarizing, reshaping, cleaning, and sorting.

Combining Data Sets

paste: Concatenating Text

paste concatenates character vectors element-wise (coercing other types first):

x <- c("a", "b", "c", "d", "e")
y <- c("A", "B", "C", "D", "E")
paste(x, y)
[1] "a A" "b B" "c C" "d D" "e E"
paste(x, y, sep="-")
[1] "a-A" "b-B" "c-C" "d-D" "e-E"
paste(x, y, sep="-", collapse="#")   # collapse: squash into ONE value
[1] "a-A#b-B#c-C#d-D#e-E"

cbind: Adding Columns

cbind combines objects horizontally. Starting from the NFL salary table of Chapter 11:

top.5.salaries <- data.frame(
  name.last = c("Manning", "Brady", "Pepper", "Palmer", "Manning"),
  name.first = c("Peyton", "Tom", "Julius", "Carson", "Eli"),
  team = c("Colts", "Patriots", "Panthers", "Bengals", "Giants"),
  position = c("QB", "QB", "DE", "QB", "QB"),
  salary = c(18700000, 14626720, 14137500, 13980000, 12916666))
more.cols <- data.frame(year = rep(2008, 5), rank = 1:5)
cbind(top.5.salaries, more.cols)
  name.last name.first     team position   salary year rank
1   Manning     Peyton    Colts       QB 18700000 2008    1
2     Brady        Tom Patriots       QB 14626720 2008    2
3    Pepper     Julius Panthers       DE 14137500 2008    3
4    Palmer     Carson  Bengals       QB 13980000 2008    4
5   Manning        Eli   Giants       QB 12916666 2008    5

rbind: Adding Rows

rbind combines vertically — here appending salaries six through eight:

next.three <- data.frame(
  name.last = c("Favre", "Bailey", "Harrison"),
  name.first = c("Brett", "Champ", "Marvin"),
  team = c("Packers", "Broncos", "Colts"),
  position = c("QB", "CB", "WR"),
  salary = c(12800000, 12690050, 12000000))
rbind(top.5.salaries, next.three)
  name.last name.first     team position   salary
1   Manning     Peyton    Colts       QB 18700000
2     Brady        Tom Patriots       QB 14626720
3    Pepper     Julius Panthers       DE 14137500
4    Palmer     Carson  Bengals       QB 13980000
5   Manning        Eli   Giants       QB 12916666
6     Favre      Brett  Packers       QB 12800000
7    Bailey      Champ  Broncos       CB 12690050
8  Harrison     Marvin    Colts       WR 12000000

Extended Example: Fetch and Bind Stock Quotes

The book builds one data frame of quotes for all 30 Dow stocks. get.quotes assembles a Yahoo! Finance CSV URL with paste (format discovered by trial and error), fetches it with read.csv, and cbinds on a ticker column:

get.quotes <- function(ticker, from=(Sys.Date()-365), to=(Sys.Date()),
                       interval="d") {
  base <- "http://ichart.finance.yahoo.com/table.csv?"
  symbol <- paste("s=", ticker, sep="")
  from.month <- paste("&a=",                      # months number 00-11!
    formatC(as.integer(format(from,"%m"))-1, width=2, flag="0"), sep="")
  from.day  <- paste("&b=", format(from,"%d"), sep="")
  from.year <- paste("&c=", format(from,"%Y"), sep="")
  to.month  <- paste("&d=",
    formatC(as.integer(format(to,"%m"))-1, width=2, flag="0"), sep="")
  to.day    <- paste("&e=", format(to,"%d"), sep="")
  to.year   <- paste("&f=", format(to,"%Y"), sep="")
  inter     <- paste("&g=", interval, sep="")
  url <- paste(base, symbol, from.month, from.day, from.year,
               to.month, to.day, to.year, inter, "&ignore=.csv", sep="")
  tmp <- read.csv(url)
  cbind(symbol=ticker, tmp)
}

…and Bind the Tickers Together

get.multiple.quotes loops over tickers, rbinding as it goes:

get.multiple.quotes <- function(tkrs, from=(Sys.Date()-365),
                                to=(Sys.Date()), interval="d") {
  tmp <- NULL
  for (tkr in tkrs) {
    if (is.null(tmp)) tmp <- get.quotes(tkr, from, to, interval)
    else tmp <- rbind(tmp, get.quotes(tkr, from, to, interval))
  }
  tmp
}
dow.tickers <- c("MMM","AA","AXP","T","BAC","BA","CAT","CVX","CSCO",
  "KO","DD","XOM","GE","HPQ","HD","INTC","IBM","JNJ","JPM","KFT",
  "MCD","MRK","MSFT","PFE","PG","TRV","UTX","VZ","WMT","DIS")
dow30 <- get.multiple.quotes(dow.tickers)

Warning

Yahoo! retired this API — the URL no longer works (use quantmod today). The pattern — build URLs with paste, accumulate with rbind — remains everyday practice. To keep the chapter’s later examples runnable, we reconstruct the book’s small five-stock extract by hand on the next slide.

Reconstructing my.quotes

The book’s five-stock, three-month sample, entered directly:

my.quotes <- data.frame(
  symbol = rep(c("GE","GOOG","AAPL","AXP","GS"), each=3),
  Date = rep(as.Date(c("2009-03-02","2009-02-02","2009-01-02")), 5),
  Open  = c(8.29,12.03,16.51, 333.33,334.29,308.60, 88.12,89.10,85.88,
            11.68,16.35,18.57, 87.86,78.78,84.02),
  High  = c(11.35,12.90,17.24, 359.16,381.00,352.33, 109.98,103.00,97.17,
            15.24,18.27,21.38, 115.65,98.66,92.20),
  Low   = c(5.87,8.40,11.87, 289.45,329.55,282.75, 82.33,86.51,78.20,
            9.71,11.44,14.72, 72.78,78.57,59.13),
  Close = c(10.11,8.51,12.13, 348.06,337.99,338.53, 105.12,89.31,90.13,
            13.63,12.06,16.73, 106.02,91.08,80.73),
  Volume = c(277426300,194928800,117846700, 5346800,6158100,5727600,
             25963400,27394900,33487900, 31136400,24297100,19110000,
             30196400,28301500,22764300),
  Adj.Close = c(10.11,8.51,11.78, 348.06,337.99,338.53, 105.12,89.31,90.13,
                13.45,11.90,16.51, 106.02,91.08,80.29))
head(my.quotes, 6)
  symbol       Date   Open   High    Low  Close    Volume Adj.Close
1     GE 2009-03-02   8.29  11.35   5.87  10.11 277426300     10.11
2     GE 2009-02-02  12.03  12.90   8.40   8.51 194928800      8.51
3     GE 2009-01-02  16.51  17.24  11.87  12.13 117846700     11.78
4   GOOG 2009-03-02 333.33 359.16 289.45 348.06   5346800    348.06
5   GOOG 2009-02-02 334.29 381.00 329.55 337.99   6158100    337.99
6   GOOG 2009-01-02 308.60 352.33 282.75 338.53   5727600    338.53

merge: Joining on Common Fields

Baseball player ages live in Master; batting statistics in Batting; both carry playerID. To show batting with names and ages, merge:

batting <- dbGetQuery(con, "SELECT * FROM Batting")
master  <- dbGetQuery(con, "SELECT * FROM Master")
intersect(names(batting), names(master))
## [1] "playerID"
batting.w.names <- merge(batting, master)   # joins on playerID

By default merge keys on all common column names — here just one, so no extra arguments needed.

merge: Arguments and Join Semantics

merge(x, y, by =, by.x =, by.y =, all =, all.x =, all.y =,
      sort =, suffixes =, incomparables =, ...)
Argument Description Default
x, y the two data frames
by / by.x, by.y key columns (shared / per side) common names
all keep unmatched rows from both sides (OUTER JOIN) FALSE
all.x / all.y keep unmatched rows from x / y (LEFT / RIGHT OUTER) all
sort sort result by the by columns? TRUE
suffixes renaming for same-named non-key columns c(".x",".y")
incomparables values that never match NULL

In SQL terms: the default is a NATURAL JOIN; chosen keys give an INNER JOIN; all=TRUE a FULL OUTER JOIN — and no matching names at all yields the full Cartesian product.

Transformations

Reassigning Variables

Fix variables in place with the assignment operator. Imported dates often arrive as text — convert them to real Date objects; derived columns join by the same notation:

my.quotes$Date <- as.Date(my.quotes$Date)   # idempotent here; the book's
class(my.quotes$Date)                        # dow30 needed factor -> Date
[1] "Date"
my.quotes$mid <- (my.quotes$High + my.quotes$Low) / 2
names(my.quotes)
[1] "symbol"    "Date"      "Open"      "High"      "Low"       "Close"    
[7] "Volume"    "Adj.Close" "mid"

The transform Function

transform(data, ...) applies a set of expressions inside the data frame — no data$ prefixes — and returns the modified frame:

my.quotes.transformed <- transform(my.quotes, Date = as.Date(Date),
                                   mid = (High + Low) / 2)
names(my.quotes.transformed)
[1] "symbol"    "Date"      "Open"      "High"      "Low"       "Close"    
[7] "Volume"    "Adj.Close" "mid"

apply: Functions over Array Margins

apply(X, MARGIN, FUN, ...) applies FUN along chosen dimensions (extra arguments pass through to FUN). With max, the workings are visible at a glance:

x <- 1:20
dim(x) <- c(5, 4)
x
     [,1] [,2] [,3] [,4]
[1,]    1    6   11   16
[2,]    2    7   12   17
[3,]    3    8   13   18
[4,]    4    9   14   19
[5,]    5   10   15   20
apply(X=x, MARGIN=1, FUN=max)   # per row
[1] 16 17 18 19 20
apply(X=x, MARGIN=2, FUN=max)   # per column
[1]  5 10 15 20

apply in Three Dimensions

MARGIN can name several dimensions. Switching to paste to see which elements group together:

x <- 1:27
dim(x) <- c(3, 3, 3)
apply(X=x, MARGIN=1, FUN=paste, collapse=",")
[1] "1,4,7,10,13,16,19,22,25" "2,5,8,11,14,17,20,23,26"
[3] "3,6,9,12,15,18,21,24,27"
apply(X=x, MARGIN=3, FUN=paste, collapse=",")
[1] "1,2,3,4,5,6,7,8,9"          "10,11,12,13,14,15,16,17,18"
[3] "19,20,21,22,23,24,25,26,27"
apply(X=x, MARGIN=c(1,2), FUN=paste, collapse=",")
     [,1]      [,2]      [,3]     
[1,] "1,10,19" "4,13,22" "7,16,25"
[2,] "2,11,20" "5,14,23" "8,17,26"
[3,] "3,12,21" "6,15,24" "9,18,27"

The last call computes, for each (i, j), FUN of x[i, j, 1], x[i, j, 2], x[i, j, 3].

lapply and sapply: Lists, Vectors, Data Frames

lapply(X, FUN, ...) applies FUN to each element, returning a list — and a data frame counts as a list of columns:

x <- as.list(1:5)
lapply(x, function(x) 2^x)[1:2]
[[1]]
[1] 2

[[2]]
[1] 4
d <- data.frame(x=1:5, y=6:10)
lapply(d, FUN=max)
$x
[1] 5

$y
[1] 10

Prefer a vector or matrix? sapply simplifies when it can:

sapply(d, FUN=function(x) 2^x)
      x    y
[1,]  2   64
[2,]  4  128
[3,]  8  256
[4,] 16  512
[5,] 32 1024

mapply: the Multivariate Version

mapply(FUN, ..., MoreArgs=, SIMPLIFY=, USE.NAMES=) applies FUN to the first elements of all vectors, then the second, and so on:

mapply(paste, c(1,2,3,4,5), c("a","b","c","d","e"),
       c("A","B","C","D","E"), MoreArgs=list(sep="-"))
[1] "1-a-A" "2-b-B" "3-c-C" "4-d-D" "5-e-E"

Arguments: the vectors in ...; MoreArgs — a list of fixed extra arguments for FUN; SIMPLIFY — simplify the result (default TRUE); USE.NAMES — name results from the first vector.

The plyr Library

Confused by the apply zoo — inconsistent names, arguments, returns? The plyr package offers 12 logically named functions: input type (array a / data frame d / list l) × output type (a/d/l/_ to discard):

Input ↓ Output → array data frame list discard
array aaply adply alply a_ply
data frame daply ddply dlply d_ply
list laply ldply llply l_ply

Shared arguments: .data, .fun, .progress (progress bar), .parallel (via foreach), ... to .fun; arrays add .margins; data frames add .variables (split-by) and .drop.

library(plyr)
llply(.data=d, .fun=function(x) 2^x)$x   # = lapply
[1]  2  4  8 16 32
aaply(.data=d, .margins=2, .fun=max)     # labeled results
 x  y 
 5 10

(No plyr equivalent of tapply exists. Modern note: plyr’s ideas grew into dplyr and purrr.)

Binning Data

Shingles and equal.count

Binning groups observations by a variable’s value — e.g., daily data summarized monthly. Shingles (Chapter 7) represent possibly overlapping intervals, used in lattice for conditioning on numeric variables:

shingle(x, intervals=sort(unique(x)))   # intervals: vector or 2-col matrix
equal.count(x, ...)                     # bins with equal observation counts

cut: Continuous to Discrete

cut splits a numeric vector into a factor, one level per interval (a Date version exists too):

cut(x, breaks, labels = NULL, include.lowest = FALSE, right = TRUE,
    dig.lab = 3, ordered_result = FALSE, ...)
Argument Description Default
breaks bin count (one integer) or break points (vector)
labels level labels NULL
include.lowest does the boundary value join the end bin? FALSE
right intervals closed on the right? TRUE
dig.lab digits in auto-generated labels 3
ordered_result return an ordered factor? FALSE

cut in Action: Batting Averages

Count players by batting-average range — the book’s batting.2008 data set, fetched per course convention:

temp <- tempfile(fileext = ".rda")
download.file("https://raw.githubusercontent.com/cran/nutshell/master/data/batting.2008.rda",
              temp, mode = "wb")
load(temp)
batting.2008.AB <- transform(batting.2008, AVG = H/AB)
batting.2008.over100AB <- subset(batting.2008.AB, subset=(AB > 100))
battingavg.2008.bins <- cut(batting.2008.over100AB$AVG, breaks=10)
table(battingavg.2008.bins)
battingavg.2008.bins
(0.137,0.163] (0.163,0.189] (0.189,0.215]  (0.215,0.24]  (0.24,0.266] 
            4             6            24            67           121 
(0.266,0.292] (0.292,0.318] (0.318,0.344]  (0.344,0.37]  (0.37,0.396] 
          132            70            11             5             2

make.groups: Combine with a Source Label

make.groups (lattice) stacks similar objects into one data frame, labeling each row’s origin:

library(lattice)
hat.sizes <- seq(from=6.25, to=7.75, by=.25)
pants.sizes <- c(30, 31, 32, 33, 34, 36, 38, 40)
shoe.sizes <- seq(from=7, to=12)
head(make.groups(hat.sizes, pants.sizes, shoe.sizes), 9)
              data       which
hat.sizes1    6.25   hat.sizes
hat.sizes2    6.50   hat.sizes
hat.sizes3    6.75   hat.sizes
hat.sizes4    7.00   hat.sizes
hat.sizes5    7.25   hat.sizes
hat.sizes6    7.50   hat.sizes
hat.sizes7    7.75   hat.sizes
pants.sizes1 30.00 pants.sizes
pants.sizes2 31.00 pants.sizes

Subsets

Bracket Notation

Too much data — irrelevant rows, or simply more than memory holds? Slice it. A logical expression indexes the rows; a name vector picks columns:

batting.2008.short <- batting.2008[batting.2008$yearID==2008,
                       c("nameFirst", "nameLast", "AB", "H", "BB")]
head(batting.2008.short, 4)
  nameFirst nameLast  AB   H BB
1     Bobby    Abreu 609 180 73
2    Moises     Alou  49  17  2
3    Garret Anderson 557 163 29
4    Marlon Anderson 138  29  9

The subset Function

subset(x, subset, select, drop=FALSE) does nothing brackets can’t — but reads better, since column names work bare inside it:

batting.2008.short <- subset(batting.2008, yearID==2008,
                             c("nameFirst", "nameLast", "AB", "H", "BB"))
head(batting.2008.short, 4)
  nameFirst nameLast  AB   H BB
1     Bobby    Abreu 609 180 73
2    Moises     Alou  49  17  2
3    Garret Anderson 557 163 29
4    Marlon Anderson 138  29  9

Arguments: subset — logical expression for rows; select — expression for columns; drop — passed to [.

Random Sampling

sample(x, size, replace=FALSE, prob=NULL) draws a random sample of a vector’s elements. Nonintuitively, on a data frame it samples columns (a data frame is a list of columns!) — so sample row numbers instead:

batting.2008[sample(1:nrow(batting.2008), 5), 9:14]
      playerID yearID stint teamID lgID   G
1115 clemeje01   2008     1    SEA   AL  66
1145 dewitbl01   2008     1    LAN   NL 117
1243  mockga01   2008     1    WAS   NL  26
508  quinthu01   2008     1    HOU   NL  59
112  elartsc01   2008     1    CLE   AL   8

Cleverer subsets follow the same trick — all statistics for three randomly chosen teams:

batting.2008$teamID <- as.factor(batting.2008$teamID)
batting.2008.3teams <- batting.2008[is.element(batting.2008$teamID,
                         sample(levels(batting.2008$teamID), 3)), ]
summary(batting.2008.3teams$teamID)[1:10]
ARI ATL BAL BOS CHA CHN CIN CLE COL DET 
  0   0   0   0  39   0   0   0   0   0

For stratified, cluster, or maximum-entropy sampling, see the sampling package.

Summarizing Functions

tapply

tapply(X, INDEX, FUN=, ..., simplify=) summarizes vector X within groups defined by the factor list INDEX. Home runs by team:

tapply(X=batting.2008$HR, INDEX=list(batting.2008$teamID), FUN=sum)
ARI ATL BAL BOS CHA CHN CIN CLE COL DET FLO HOU KCA LAA LAN MIL MIN NYA NYN OAK 
159 130 172 173 235 184 187 171 160 200 208 167 120 159 137 198 111 180 172 125 
PHI PIT SDN SEA SFN SLN TBA TEX TOR WAS 
214 153 154 124  94 174 180 194 126 117

Arguments: X — the object; INDEX — list of factors, each as long as X; FUN; ... to FUN; simplify — array if FUN is scalar (default), list otherwise.

tapply: Richer Summaries, More Dimensions

FUN may return several values — fivenum (min, lower hinge, median, upper hinge, max) of batting average by league:

tapply(X=(batting.2008$H/batting.2008$AB),
       INDEX=list(batting.2008$lgID), FUN=fivenum)
$AL
[1] 0.0000000 0.1758242 0.2487923 0.2825485 1.0000000

$NL
[1] 0.0000000 0.0952381 0.2172524 0.2679739 1.0000000

And INDEX may hold several factors — mean home runs by league × batting hand:

tapply(X=(batting.2008$HR),
       INDEX=list(batting.2008$lgID, batting.2008$bats), FUN=mean)
          B        L        R
AL 4.254902 4.564516 2.980198
NL 4.104478 3.981395 3.203905

by: tapply for Data Frames

by is tapply’s data-frame sibling (INDICES replaces INDEX) — whole-column means per group:

by(batting.2008[, c("H", "2B", "3B", "HR")],
   INDICES=list(batting.2008$lgID, batting.2008$bats), FUN=colMeans)
: AL
: B
        H        2B        3B        HR 
52.058824 10.647059  1.058824  4.254902 
------------------------------------------------------------ 
: NL
: B
        H        2B        3B        HR 
53.761194 10.641791  1.477612  4.104478 
------------------------------------------------------------ 
: AL
: L
        H        2B        3B        HR 
39.924731  8.053763  1.016129  4.564516 
------------------------------------------------------------ 
: NL
: L
         H         2B         3B         HR 
32.2511628  6.5813953  0.7813953  3.9813953 
------------------------------------------------------------ 
: AL
: R
         H         2B         3B         HR 
26.7821782  5.5544554  0.4084158  2.9801980 
------------------------------------------------------------ 
: NL
: R
         H         2B         3B         HR 
27.1908894  5.6420824  0.4577007  3.2039046

aggregate

aggregate(x, by, FUN, ...) summarizes several columns at once (a time-series variant takes nfrequency, ndeltat, ts.eps instead). Team totals:

aggregate(x=batting.2008[, c("AB", "H", "BB", "2B", "3B", "HR")],
          by=list(batting.2008$teamID), FUN=sum)[1:8, ]
  Group.1   AB    H  BB  2B 3B  HR
1     ARI 5409 1355 587 318 47 159
2     ATL 5604 1514 618 316 33 130
3     BAL 5559 1486 533 322 30 172
4     BOS 5596 1565 646 353 33 173
5     CHA 5553 1458 540 296 13 235
6     CHN 5588 1552 636 329 21 184
7     CIN 5465 1351 560 269 24 187
8     CLE 5543 1455 560 339 22 171

Arguments: x; by — list of grouping elements as long as x; FUN — scalar summary (time-series default: sum); ... to FUN.

rowsum

Just sums by group? rowsum(x, group, reorder=TRUE) is the direct route:

rowsum(batting.2008[, c("AB", "H", "BB", "2B", "3B", "HR")],
       group=batting.2008$teamID)[1:8, ]
      AB    H  BB  2B 3B  HR
ARI 5409 1355 587 318 47 159
ATL 5604 1514 618 316 33 130
BAL 5559 1486 533 322 30 172
BOS 5596 1565 646 353 33 173
CHA 5553 1458 540 296 13 235
CHN 5588 1552 636 329 21 184
CIN 5465 1351 560 269 24 187
CLE 5543 1455 560 339 22 171

Counting: tabulate and table

tabulate counts occurrences of the integers 1..max — zeros are not counted, so label from 1 (players with 0 HR simply don’t appear):

HR.cnts <- tabulate(batting.2008$HR)
names(HR.cnts) <- 1:length(HR.cnts)
HR.cnts[1:12]
 1  2  3  4  5  6  7  8  9 10 11 12 
92 63 45 20 15 26 23 21 22 15 15 18

For categorical values, table returns a table object of counts (SAS users: this is PROC FREQ):

table(batting.2008$bats)

  B   L   R 
118 401 865

Arguments of table: ... — factors (or coercibles); exclude — levels to drop; useNA — "no"/"ifany"/"always"; dnn — dimension names; deparse.level — how to auto-name dimensions.

Multidimensional Tables and xtabs

More factors, more dimensions — batting hand × throwing hand (add lgID for a third):

table(batting.2008[, c("bats", "throws")])
    throws
bats   L   R
   B  10 108
   L 240 161
   R  25 840

xtabs builds the same contingency tables from a formula — often less typing:

xtabs(~bats+lgID, batting.2008)
    lgID
bats  AL  NL
   B  51  67
   L 186 215
   R 404 461

And numeric variables join the party via cut first, table second — exactly the batting-average binning shown earlier.

Reshaping Data

Narrow vs. Wide, and t

Data warehouses often store one fact per row (“narrow”); analysis may need one column per concept (“wide”) — or vice versa. The simplest reshaper is t, transpose (a vector becomes a one-row matrix):

m <- matrix(1:10, nrow=5)
t(m)
     [,1] [,2] [,3] [,4] [,5]
[1,]    1    2    3    4    5
[2,]    6    7    8    9   10
t(1:10)
     [,1] [,2] [,3] [,4] [,5] [,6] [,7] [,8] [,9] [,10]
[1,]    1    2    3    4    5    6    7    8    9    10

stack and unstack

Working on the narrow quote table (symbol, date, close):

my.quotes.narrow <- my.quotes[, c("symbol", "Date", "Close")]
unstacked <- unstack(my.quotes.narrow, form=Close~symbol)
unstacked
    AAPL   AXP    GE   GOOG     GS
1 105.12 13.63 10.11 348.06 106.02
2  89.31 12.06  8.51 337.99  91.08
3  90.13 16.73 12.13 338.53  80.73
stack(unstacked)[1:6, ]
  values  ind
1 105.12 AAPL
2  89.31 AAPL
3  90.13 AAPL
4  13.63  AXP
5  12.06  AXP
6  16.73  AXP

In the form formula, the right side names the unstacking variable, the left the values. Order survives; the Date column does not — unstack suits data where only two variables matter.

reshape: Long to Wide

reshape is the powerful (and notoriously two-faced) tool. Rows = dates, columns = stocks:

my.quotes.wide <- reshape(my.quotes.narrow, idvar="Date",
                          timevar="symbol", direction="wide")
my.quotes.wide
        Date Close.GE Close.GOOG Close.AAPL Close.AXP Close.GS
1 2009-03-02    10.11     348.06     105.12     13.63   106.02
2 2009-02-02     8.51     337.99      89.31     12.06    91.08
3 2009-01-02    12.13     338.53      90.13     16.73    80.73

Its parameters ride along as attributes (attributes(my.quotes.wide)$reshapeWide stores idvar, timevar, varying…) — which is why reshape(my.quotes.wide) with no arguments reverses the operation.

reshape: Variations

Swap the roles — rows = stocks, columns = dates; or carry several value columns at once:

reshape(my.quotes.narrow, idvar="symbol", timevar="Date",
        direction="wide")
   symbol Close.2009-03-02 Close.2009-02-02 Close.2009-01-02
1      GE            10.11             8.51            12.13
4    GOOG           348.06           337.99           338.53
7    AAPL           105.12            89.31            90.13
10    AXP            13.63            12.06            16.73
13     GS           106.02            91.08            80.73
my.quotes.oc <- my.quotes[, c("symbol", "Date", "Close", "Open")]
reshape(my.quotes.oc, timevar="Date", idvar="symbol",
        direction="wide")[, 1:5]
   symbol Close.2009-03-02 Open.2009-03-02 Close.2009-02-02 Open.2009-02-02
1      GE            10.11            8.29             8.51           12.03
4    GOOG           348.06          333.33           337.99          334.29
7    AAPL           105.12           88.12            89.31           89.10
10    AXP            13.63           11.68            12.06           16.35
13     GS           106.02           87.86            91.08           78.78

reshape: Arguments

Two functions in one — direction="wide" requires idvar + timevar; direction="long" requires varying:

Argument Description
data data frame to reshape
varying wide-format variables that become unique rows in long format
v.names long-format variables that become columns in wide format
timevar / idvar long-format variables identifying observation / group (defaults "time" / "id")
ids / times values for new id / time variables
drop variables to exclude
direction "wide" or "long"
new.row.names build row names from id × time?
sep / split how wide names encode variable × time (default sep ".")

The reshape Package: Melt and Cast

Hadley Wickham’s reshape package (not the function!) replaces all that with one intuitive model: melt a table into transaction-like records, then cast the records into any shape:

library(reshape)
my.molten.quotes <- melt(my.quotes[, 1:8], id.vars=c("symbol", "Date"))
head(my.molten.quotes)
  symbol       Date variable  value
1     GE 2009-03-02     Open   8.29
2     GE 2009-02-02     Open  12.03
3     GE 2009-01-02     Open  16.51
4   GOOG 2009-03-02     Open 333.33
5   GOOG 2009-02-02     Open 334.29
6   GOOG 2009-01-02     Open 308.60

(The book’s call omitted id.vars — its CSV-imported Date was a factor, which melt auto-detects as an id. Ours is a true Date, which would otherwise be melted as a measurement, so we name the ids explicitly.)

cast: Three Shapes from One Molten Frame

cast(data=my.molten.quotes, variable~Date, subset=(symbol=='GE'))
   variable   2009-01-02   2009-02-02   2009-03-02
1      Open        16.51        12.03         8.29
2      High        17.24        12.90        11.35
3       Low        11.87         8.40         5.87
4     Close        12.13         8.51        10.11
5    Volume 117846700.00 194928800.00 277426300.00
6 Adj.Close        11.78         8.51        10.11
cast(data=my.molten.quotes, symbol~Date, subset=(variable=='Adj.Close'))
  symbol 2009-01-02 2009-02-02 2009-03-02
1   AAPL      90.13      89.31     105.12
2    AXP      16.51      11.90      13.45
3     GE      11.78       8.51      10.11
4   GOOG     338.53     337.99     348.06
5     GS      80.29      91.08     106.02

A | in the formula returns a list of tables — cast(my.molten.quotes, Date~variable|symbol) yields one quote table per stock.

melt and cast: Arguments

melt.data.frame(data, id.vars, measure.vars, variable_name, na.rm, ...) — id.vars identify observations, measure.vars are the measurements (unspecified: factors/characters are ids, the rest measures); variable_name defaults to "variable". Array and list methods exist too (melt.array takes varnames; melt.list melts recursively).

cast(data, formula, fun.aggregate=NULL, ..., margins, subset, fill, add.missing, value):

  • formula — x_1 + x_2 ~ y_1 ~ ... | list_var, with ... = “all other variables” and . = “none” (default ...~variable);
  • fun.aggregate — how to combine multiple values per cell; margins — compute margins; subset — filter the molten data; fill / add.missing — handle absent combinations; value — name of the value column.

(Modern note: reshape begat reshape2, now succeeded by tidyr’s pivot_longer/pivot_wider — same melt/cast ideas.)

Data Cleaning

What Cleaning Means

Note

Surprises are the rule. The author’s credit-data war story: valid FICO scores span 340–840, yet the data brimmed with 997/998/999 — special codes like “insufficient data,” not stellar credit. Hospital records: two patients sharing a name get fused into one record; one patient seeing two doctors becomes two records. Cleaning does not change data’s meaning — it removes artifacts of collection, processing, and storage so they can’t contaminate the analysis.

Finding and Removing Duplicates

Suppose GE sneaked into the ticker list twice. duplicated flags rows that repeat earlier rows; filter with it, or let unique do both steps:

my.quotes.2 <- rbind(my.quotes, my.quotes[my.quotes$symbol == "GE", ])
duplicated(my.quotes.2)
 [1] FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE
[13] FALSE FALSE FALSE  TRUE  TRUE  TRUE
my.quotes.unique <- my.quotes.2[!duplicated(my.quotes.2), ]
my.quotes.unique <- unique(my.quotes.2)        # same result
nrow(my.quotes.unique)
[1] 15

Sorting

sort

w <- c(5, 4, 7, 2, 7, 1)
sort(w)
[1] 1 2 4 5 7 7
sort(w, decreasing=TRUE)
[1] 7 7 5 4 2 1
length(w) <- 7                      # introduce an NA
sort(w)                             # na.last=NA: NAs dropped
[1] 1 2 4 5 7 7
sort(w, na.last=TRUE)
[1]  1  2  4  5  7  7 NA
sort(w, na.last=FALSE)
[1] NA  1  2  4  5  7  7

order: Sorting Data Frames

sort sorts a vector; data frames need a permutation of row indices, supplied by order(..., na.last=, decreasing=) — it sorts by each argument vector, breaking ties with the next:

v <- c(11, 12, 13, 15, 14)
order(v)
[1] 1 2 3 5 4
v[order(v)]
[1] 11 12 13 14 15
u <- c("pig", "cow", "duck", "horse", "rat")
w <- data.frame(v, u)
w[order(w$v), ]
   v     u
1 11   pig
2 12   cow
3 13  duck
5 14   rat
4 15 horse

Sorting by One or More Columns

The quotes by closing price; then by symbol, ties broken by price:

my.quotes[order(my.quotes$Close), ][1:5, 1:6]
   symbol       Date  Open  High   Low Close
2      GE 2009-02-02 12.03 12.90  8.40  8.51
1      GE 2009-03-02  8.29 11.35  5.87 10.11
11    AXP 2009-02-02 16.35 18.27 11.44 12.06
3      GE 2009-01-02 16.51 17.24 11.87 12.13
10    AXP 2009-03-02 11.68 15.24  9.71 13.63
my.quotes[order(my.quotes$symbol, my.quotes$Close), ][1:6, 1:6]
   symbol       Date  Open   High   Low  Close
8    AAPL 2009-02-02 89.10 103.00 86.51  89.31
9    AAPL 2009-01-02 85.88  97.17 78.20  90.13
7    AAPL 2009-03-02 88.12 109.98 82.33 105.12
11    AXP 2009-02-02 16.35  18.27 11.44  12.06
10    AXP 2009-03-02 11.68  15.24  9.71  13.63
12    AXP 2009-01-02 18.57  21.38 14.72  16.73

Sorting by All Columns: do.call

Whole-frame sorting holds a trap: order(my.quotes) treats the data frame as one long vector. order wants a list of vectors — deliver the data frame’s columns as arguments via do.call:

do.call(order, my.quotes)
 [1]  9  8  7 12 11 10  3  2  1  6  5  4 15 14 13
my.quotes[do.call(order, my.quotes), ][1:6, 1:6]
   symbol       Date  Open   High   Low  Close
9    AAPL 2009-01-02 85.88  97.17 78.20  90.13
8    AAPL 2009-02-02 89.10 103.00 86.51  89.31
7    AAPL 2009-03-02 88.12 109.98 82.33 105.12
12    AXP 2009-01-02 18.57  21.38 14.72  16.73
11    AXP 2009-02-02 16.35  18.27 11.44  12.06
10    AXP 2009-03-02 11.68  15.24  9.71  13.63