2.5 Files and Formats
"Write programs to handle text streams, because that is a universal interface."
The 1880 census of the United States took eight years to count. The constitution demands one every ten years, so the arithmetic was becoming absurd: by the time the country knew what it had been in 1880, it was nearly 1890 and the question had changed.
A young engineer at the Census Office thought the problem was not the counting but the paper. Herman Hollerith proposed that each person be represented by a card with holes punched in it — one position for each answer — and that a machine, rather than a clerk, add them up. The Census Office ran a competition in 1888. Hollerith captured the data in 72.5 hours; his two competitors needed 144.5 and 100.5.26
The machine is worth picturing, because it explains everything that follows. A card sat under a frame of spring-loaded wires, and beneath the card were small cups of mercury. Lower the frame: where the card was solid, the wire stopped; where a hole had been punched, the wire went through and dipped into the mercury, closing an electrical circuit. The circuit advanced a dial. Forty dials on the front of the machine, forty things counted at once.
The first machine-readable data set in history was a social survey. The holes recorded age, sex, state of residence, citizenship and race — the variables of any survey since. The 1890 census produced roughly a hundred million cards and finished months ahead of schedule. Hollerith's company later merged into what became IBM.
Figure 2.8 reconstructs one of those cards, with the labels of the punch board drawn in so that it can be read at all.
Figure 2.8: A card from the 1890 census, reconstructed with the punch board's labels drawn onto it. Every question owns a patch of positions and one hole is punched in each, so the position is the answer. Age is the exception, and the reason nobody on such a card can be older than 99.
2.5.1 The tyranny of the column
The figure is a reconstruction, and the reconstruction is generous: the cards themselves were blank. Not sparsely labelled — blank. A real card from 1890 is a rectangle of cardboard with holes in it and nothing else.
The labels existed, but they were attached to the machine rather than to the data. A clerk punching cards never looked at the card: it sat in the punch station above a template carrying the layout of the fields, and the clerk moved a stylus across the template. To read a deck afterwards there was a second board, which Columbia's computing history collection calls the decoder ring for the holes.27 Layout on the board, values on the card, the two kept in different pieces of furniture.
Why not print the field names along the edge? Because the printing would have belonged to the form, not to the record. A hundred million cards would have carried a hundred million copies of the same twenty words, and the census would have paid for the paper. One template served the whole deck — and one codebook still serves a million rows of a CSV today. The economy survived; so did its price.
The failure mode is worth keeping. Punch a value one column too far right and nothing breaks: the card is valid, the machine reads it, the dial turns, and an age of 34 has quietly become something else. There is no error message, because there is no way for a hole to be wrong. Programmers lived under the same discipline — on a Fortran card, columns 1 to 5 held a statement label, 7 to 72 the program, and a variable named xnew that began too far right silently became x. The last eight columns held nothing but a card number, for one reason: so that whoever dropped the deck had a chance of putting it back in order.28
Eighty columns, and you are still living in them
The IBM card held eighty characters per line. The terminals that replaced it — DEC's VT52 and VT100 — showed eighty. The original IBM PC text mode was 80 × 25. And the style guides of Python, Java and R still recommend that a line of code stay under eighty characters.
The margin line in your RStudio editor is at column 80. It is the width of a punched card, and nobody has quite got round to changing it.29
Seventy-five years later the card had changed in one decisive way, shown in Figure 2.9: the twelve rows were given names, so that the row a hole sits in is a digit. Every answer now had to become a number before it could be stored — female becomes 2, Ohio becomes 39.
Figure 2.9: The same respondent on an IBM card, seventy-five years later. The row a hole sits in is the digit, so every answer has to become a number first - and no column says what it holds.
The escape from the column was a comma. Position stopped mattering, a mispunched digit became a visible mistake rather than a silent one, and a file could be read by a program that had never seen the codebook. The IBM Fortran compiler under OS/360 read such lists in 1972; the name comma-separated values is in print by 1983; a specification followed in 2005.30 CSV is still the first thing every statistical office and survey archive offers you. The oldest format is also the most used one — which raises the question this section is about. If CSV is enough, why does anyone build anything else?
2.5.2 The codebook never went away
Because the comma solved the problem of position and left the problem of meaning untouched.
Open any CSV of survey data and you face what the card faced. Does 0 mean male, or the first category of something else? Is 1 a yes, a Monday, or one point on a scale that starts at zero? And what is -7? That last one is not hypothetical: the SOEP data used throughout this book code missing answers as negative numbers — -1 for no answer, -2 for does not apply, -7 for only in the restricted edition. Read those columns without the codebook and you compute an average life satisfaction below zero on a scale from 0 to 10, and nothing anywhere objects.
Figure 2.10 puts the two halves side by side: the digits are the same in 1965 and today, and what turns them into a person is a document that travels separately.
Figure 2.10: Eleven digits, and what stands between them and a person. The card and the CSV row hold identical information, and neither can be read without the table in the middle.
So every format invented since is an attempt at the second problem, and one question separates them: does the codebook travel in the same box as the data, or does it stay in the drawer? CSV leaves it in the drawer. Excel leaves it in the drawer and rewrites some of your values on the way. Stata and SPSS put it in the box — which is why the UK Data Service, an institution whose profession is long-term preservation, recommends those proprietary formats ahead of any open one for tabular data with metadata.31 They are the only ones that carry the codebook.
That last one is why this book reads the SOEP data as .dta rather than as CSV. It is worth watching exactly what that buys, because it is less than the word labelled suggests. Four rows of one respondent, first the way any CSV would hand them over:
soep %>% filter(id == 194) %>%
select(id, syear, sex, bildung, lebensz_org) %>%
haven::zap_labels() %>% head(4) # throw the metadata away
#> # A tibble: 4 × 5
#> id syear sex bildung lebensz_org
#> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1 194 2015 1 10.5 6
#> 2 194 2016 1 10.5 5
#> 3 194 2017 1 10.5 7
#> 4 194 2018 1 10.5 5Five numbers per row, and not one of them interpretable. Is sex = 1 a woman or a man? Is a 6 a good life or a poor one? And what is 10.5 supposed to be a count of? Now the same four rows out of the .dta:
soep %>% filter(id == 194) %>%
select(id, syear, sex, bildung, lebensz_org) %>% head(4)
#> # A tibble: 4 × 5
#> id syear sex bildung lebensz_org
#> <dbl> <dbl> <dbl+lbl> <dbl+lbl> <dbl+lbl>
#> 1 194 2015 1 [[1] female] 10.5 6
#> 2 194 2016 1 [[1] female] 10.5 5
#> 3 194 2017 1 [[1] female] 10.5 7
#> 4 194 2018 1 [[1] female] 10.5 5<dbl+lbl>: a number plus what the number means. The analysis works with the code, the reader sees the label, and neither has to be converted into the other. Ninety years after a wire dipped into a cup of mercury, the hole and the codebook are finally in the same place.
For one column out of three. The other two are labelled as well, and print exactly as they did before, because a value label answers one question only — which category is this code? — and the answer is a short dictionary:
attr(soep$lebensz_org, "labels") # a scale, so only the two anchors
#> [0] completely dissatisfied 0 [10] completely satisfied 10
#> 0 10
attr(soep$bildung, "labels") # missing codes, none of which occur here
#> [-7] Only available in less restricted edition
#> -7
#> [-2] does not apply
#> -2
#> [-1] no answer
#> -1Life satisfaction is a scale rather than a set of categories, so the dictionary holds its two ends and nothing in between: the 6 stays a 6, and the column label Life Satisfaction never mentions that the range is 0 to 10 or which end is the good one. Years of education has three labels and all three are missing-value codes the file no longer contains. Neither label explains how a person manages to spend ten and a half years in school.
A third layer is lying in the same folder, and almost nobody opens it. Beside the .dta the SOEP ships a meta/ directory: one CSV with a row per variable, one with 142 rows of value labels for the ten coded variables, German and English side by side. This is a codebook a machine can read.
meta <- read_csv(file.path("data", "SOEP", "practice_en", "meta", "variables.csv"),
show_col_types = FALSE)
meta %>% filter(variable %in% c("sex", "bildung", "lebensz_org")) %>%
select(variable, label, type)
#> # A tibble: 3 × 3
#> variable label type
#> <chr> <chr> <chr>
#> 1 sex Sex byte
#> 2 bildung Number of Years of Education byte
#> 3 lebensz_org Life Satisfaction byteIt repeats the labels and adds one thing they do not carry: a declared storage type. Which is where it goes wrong. A Stata byte is a whole number, and bildung is declared byte while containing 10.5 in 8,241 of its 21,911 observed values. The codebook is not describing this file.
And a codebook that got the type right would still stop short of what an analysis needs. It would not say what was asked: the SOEP wording is "How satisfied are you with your life, all things considered?", 0 = completely dissatisfied to 10 = completely satisfied, put to every adult respondent in every wave.32 It would not say who was asked, and this file keeps only people who completed an individual questionnaire, so no children are in it. And it would not say what was never asked at all — alter is the survey year minus the year of birth, and bildung is the SOEP's generated variable pgbilzeit, in which a school-leaving certificate has been converted into a number of years. That is where the half years come from. Nobody sat in a classroom for ten and a half of them.
All of that stands in the construction file, a Stata script shipped with the data, which also records two things the file itself will never admit to.33 The first is a recode. The SOEP codes sex as 1 and 2; this data set wanted 0 and 1; and the label dictionary was modified rather than replaced, so the old value 2 is still in it, still called female:
table(haven::as_factor(soep$sex)) # eleven levels, two of them occupied
#>
#> [-8] Question not part of the survey program this year
#> 0
#> [-7] Only available in less restricted edition
#> 0
#> [-6] Version of questionnaire with modified filtering
#> 0
#> [-5] Not included in this version of the questionnaire
#> 0
#> [-4] Inadmissable multiple response
#> 0
#> [-3] not valid
#> 0
#> [-2] does not apply
#> 0
#> [-1] no answer
#> 0
#> [0] male
#> 10762
#> [1] female
#> 12760
#> [2] female
#> 0
attr(soep$erwerb, "labels")[8:9] # the bracketed codes contradict the values
#> [-1] Employed full-time [-2] Employed part-time
#> 1 2Eleven levels, two occupied. Eight are missing-value codes that the same script turned into system missing before saving, and the ninth is the ghost of the old coding. Underneath sits a plain error: the value 1 of erwerb carries the label text [-1] Employed full-time, and negative numbers are precisely what SOEP data use for missing values (Section 4.1). The values are sound; their description is not.
The second admission is larger. The four income columns are not the incomes anybody reported. The script draws rnormal(0, 1678) and adds it, so that a teaching data set is not real microdata. No label says so, no codebook entry says so, and every income figure computed from this file in this book is therefore deliberately jittered — which is entirely fine, as long as you know, and there is exactly one document in which you can find out.
A label says what a value means. A codebook says what a variable is. Only a handbook says how the variable came to exist — what was asked, of whom, and what was recoded, generated or perturbed afterwards. Labels resolve codes, handbooks resolve variables, and metadata are data: they can be missing, they can travel with the file, and they can be wrong.
Two answers exist for the formats that cannot carry a codebook themselves. A Data Package is a small datapackage.json lying beside the CSV that declares column types, missing-value markers and constraints; the data stay a plain CSV anyone can open, and the meaning sits in a text file next to them that a machine can read.34 And DDI is the same idea at the scale of a discipline: an XML standard describing every variable, category and question wording of a study, which is how GESIS, ICPSR and the SOEP publish their codebooks.35 It is the punched-card codebook, finally machine-readable and finally shipped in the same box as the deck.
2.5.3 What a file is, and what a format is
Definition
A file is a sequence of bytes with a name. That is all the operating system guarantees.
A file format is an agreement about what those bytes mean — which of them are a number, where one record ends, whether the first line is a header. The agreement lives in documentation and in software, never in the file's name.
The extension (.csv, .dta) is a hint about which agreement applies. It is written by whoever saved the file and is checked by nobody.
So a file can lie about itself. Rename data.csv to data.xlsx and not one byte inside it changes; only the label on the tin is now wrong, and the next program to open it will complain about something unrelated to the real problem.
Windows hides the extension by default. In Explorer, Hide extensions for known file types is on out of the box, so a file displayed as survey.csv may actually be called survey.csv.txt. Turn extensions on once (View → Show → File name extensions) and leave them on. You cannot reason about a file whose name you are not being shown.
Size is the one property you get for free, and it is more informative than it looks. The same table — same rows, same columns, same numbers — written five ways:
tmp <- tempdir()
write_csv(soep, file.path(tmp, "soep.csv"))
write_csv(soep, file.path(tmp, "soep.csv.gz")) # readr compresses by extension
saveRDS(soep, file.path(tmp, "soep.rds"))
saveRDS(soep, file.path(tmp, "soep_raw.rds"), compress = FALSE)
tibble(
Format = c("`.rds` (R, no compression)", "`.csv`", "`.dta` (as delivered)",
"`.csv.gz`", "`.rds` (R, compressed)"),
KB = round(c(
file.size(file.path(tmp, "soep_raw.rds")),
file.size(file.path(tmp, "soep.csv")),
file.size(file.path("data", "SOEP", "practice_en", "practice_dataset_eng.dta")),
file.size(file.path(tmp, "soep.csv.gz")),
file.size(file.path(tmp, "soep.rds"))
) / 1024),
`Keeps the labels` = c("yes", "no", "yes", "no", "yes")
) %>%
kable(caption = "One table, five files. Parquet would sit at about 426 KB.")| Format | KB | Keeps the labels |
|---|---|---|
.rds (R, no compression) |
2767 | yes |
.csv |
1339 | no |
.dta (as delivered) |
1152 | yes |
.csv.gz |
386 | no |
.rds (R, compressed) |
286 | yes |
Three things there matter more than the numbers. Compression is its own dimension: the same .rds is 2,767 KB or 286 KB depending on one argument. Text does not have to be big: write_csv() compresses when the name ends in .gz and read_csv() unpacks it again unasked, which takes the most transparent format below the size of Parquet. And size says nothing about content: the largest file in the table carries the least, because the CSV lost every value label on the way out.
That trade-off — readable, complete, small: pick two — is what the whole zoo of formats is about. Each of them was built for a wall the earlier ones could not get past.
| Format | Since | From | Answers the question |
|---|---|---|---|
.csv |
1972 | IBM Fortran | how do I write a table anyone can read? |
.xlsx |
2006 | Microsoft, ECMA-376 | how do I keep formatting, formulas and sheets? |
.dta / .sav |
1985 / 1968 | Stata, SPSS | how do I keep types and value labels? |
.rds / .RData |
2001 | R 1.4.0 | how do I save an R object exactly as it is? |
.pkl / .npy |
2007 (.npy) |
Python, NumPy | the same, for Python |
.json / .jsonl |
2001 / 2013 | Douglas Crockford | how do I write something that is not a rectangle? |
.parquet / .feather |
2013 / 2016 | Twitter & Cloudera, Arrow | how do I read one column out of a huge table? |
.duckdb |
2019 | CWI Amsterdam | how do I query a file I never loaded? |
.shp (+ 4) |
1998 | Esri | how do I store a shape rather than a number? |
Read the last column downwards and the history is one sentence repeated. Position was the first wall, and the comma got past it. Meaning was the second: a column of zeros and ones could be sex, employment or a coin flip, so statistical packages built formats that carry the dictionary along — SPSS from 1968, Stata from January 1985.36 Hierarchy was the third: a household containing persons containing jobs is not a rectangle, so XML arrived in 1998 and, when it proved too heavy for the web, JSON in 2001.37 Excel's own wall was openness — the sealed binary .xls was replaced in 2006 by a ZIP archive full of XML.38 And size was the fourth: when a table has a billion rows and you want three of fifty columns, a row-by-row format makes you read all fifty, which is what Parquet fixed in 2013.39
Nobody replaced CSV, and nothing here is obsolete. Each format answers a question the earlier ones could not, and each is still the best answer to its own.
2.5.4 Everything is a text file (until it is not)
There is one way to settle what a file contains, and it works for every format above: look at the bytes. Most files in this repository survive that treatment intact, because they are text — the .Rmd files you are reading, the .R scripts, DESCRIPTION, references.bib, every .csv, and the .do file Stata used to build the SOEP practice data.
Definition
A plain text file is one whose bytes are meant to be read as characters. What turns bytes into characters is the character encoding, today almost always UTF-8 — sketched out, in Rob Pike's account, "on a placemat in a New Jersey diner one night in September or so 1992".40
Plain text is not a format. It is the material most formats are written on.
Here is the tool. It reads the first bytes of a file and replaces everything unprintable with a dot, exactly as a hex editor does:
peek <- function(path, n = 240) {
b <- readBin(path, what = "raw", n = n)
keep <- b == as.raw(9) | b == as.raw(10) | (b >= as.raw(32) & b <= as.raw(126))
b[!keep] <- as.raw(46) # 46 is a full stop
cat(rawToChar(b))
}Point it at a CSV and the whole format is visible in two lines — a header, then one record per line, fields separated (here by semicolons, because the file was written by a German spreadsheet where the comma is already the decimal mark):
peek(file.path("data", "Course", "GF_AllTime.csv"), 200)
#> Term;Academic.level;Gender;Age;Total.Semesters;Background.in.Statistics;Background.in.R;Background.in.Academic.Writing;Expectations.
#> SS 2020;Bachelor;Female;25;6;3;1;3;doing an empirical analysis withPoint it at a spreadsheet and you get PK, the initials of Phil Katz, who wrote the ZIP format:
peek(file.path("data", "Course", "CourseData.xlsx"), 120)
#> PK..........!.b..h^...........[Content_Types].xml ...(..................................................................An .xlsx is a zipped folder of XML documents — a spreadsheet built out of the two formats above, and .docx and .pptx are the same:
xlsx <- file.path("data", "Course", "CourseData.xlsx")
head(unzip(xlsx, list = TRUE)$Name, 8)
#> [1] "[Content_Types].xml" "_rels/.rels"
#> [3] "xl/_rels/workbook.xml.rels" "xl/workbook.xml"
#> [5] "xl/sharedStrings.xml" "xl/theme/theme1.xml"
#> [7] "xl/styles.xml" "xl/worksheets/sheet1.xml"Point it at a Stata file and you get markup, then rubble. Since Stata 13 the skeleton of a .dta is XML-like text — format version, byte order, the moment the file was written — while the values themselves are binary:41
peek(file.path("data", "SOEP", "practice_en", "practice_dataset_eng.dta"), 200)
#> <stata_dta><header><release>118</release><byteorder>LSF</byteorder><K>..</K><N>.[......</N><label>..</label><timestamp>. 6 Jan 2022 15:18</timestamp></header><map>........................X............And point it at Arrow or at R's own format and you get almost nothing:
peek(file.path("data", "mtcars.feather"), 60) # Arrow
#> ARROW1................
#> .........
#> .............
#> .........
#> ...
saveRDS(head(soep), file.path(tempdir(), "demo.rds"))
peek(file.path(tempdir(), "demo.rds"), 60) # R's own format
#> ...........Z......,`0.@...&.....w.H+.V...5_6..W.k.Z..4f>D..vARROW1, then silence. Those first bytes are magic numbers, and most binary formats begin with one: PK for anything zipped, %PDF for PDF, <stata_dta> for modern Stata. The extension is a claim made by whoever named the file; the magic number is a claim made by whoever wrote it.
Being readable is not the same as being unambiguous. is not the same as being unambiguous. CSV is the clearest case: the most widely used data format in the world, and for its first three decades nobody had written down what it was. RFC 4180 opens by conceding the point — "Surprisingly, while this format is very common, it has never been formally documented." A CSV records no types, no separator, no encoding and no missing-value convention, so every reader guesses, and readers guess differently:
csv_demo <- "postcode,share\n01067,0.5\n10115,0.25\n"
read.csv(text = csv_demo)$postcode # base R
#> [1] 1067 10115
read_csv(csv_demo, show_col_types = FALSE)$postcode # readr
#> [1] "01067" "10115"01067 is the postcode of central Dresden. One reader makes it the number 1067 and loses the zero for good; the other keeps it as text. Nothing failed and nothing warned. The remedy is to stop hoping: col_types = cols(postcode = col_character()). The same applies to encoding — "Müller" written in UTF-8 and read as Latin-1 becomes "Müller", so pass locale(encoding = ...) rather than repairing letters afterwards.
The genes that had to be renamed
In 2016 Mark Ziemann and colleagues screened the supplementary Excel files of eighteen genomics journals. In 19.6 % of the papers, gene names had been silently converted: SEPT2 had become the date 2 September, MARCH1 had become 1 March.42 Five years later the same group repeated the screen; the rate had risen to 30.9 %.43
At which point the field gave up on the software and changed the data. In 2020 the HUGO Gene Nomenclature Committee renamed the affected genes — SEPT1 became SEPTIN1 — so that a spreadsheet would stop reinterpreting them.44
Human genetics adjusted its naming conventions to accommodate a file format's opinion about what the data means.
Read spreadsheets with readxl::read_xlsx(), which never opens Excel and therefore never triggers the conversions — and where a column matters, pass col_types = "text" and convert deliberately afterwards.
2.5.5 When the rectangle ends
Everything so far had a fixed number of columns. Two formats in this book do not, and they fail in the same interesting way.
The first is JSON. Section 2.4.2 already used one without saying what it is; this is what it is:
peek(file.path("data", "Geo", "Germany", "Germany_Coordinator_60.json"), 160)
#> [[540,332],[522.7839050292969,294.9807434082031],[515.7578125,251.5562744140625],[492.917236328125,214.66256713867188],[502.08544921875,170.2926483154297],[480.Brackets inside brackets.
Definition
JSON (JavaScript Object Notation) writes data as nested objects and arrays. A field may hold a value, another object, or a list of any length — so two records in the same file need not have the same fields at all.
It is not a table format. It is a tree, and using it means deciding how to flatten it.
That decision has consequences, and they are quiet ones:
records <- '[
{"id": 1, "name": "Anna", "langs": ["de", "en", "fr"]},
{"id": 2, "name": "Boris", "langs": []}
]'
people <- jsonlite::fromJSON(records)
tibble::as_tibble(people) %>% tidyr::unnest_longer(langs)
#> # A tibble: 3 × 3
#> id name langs
#> <int> <chr> <chr>
#> 1 1 Anna de
#> 2 1 Anna en
#> 3 1 Anna frTwo people went in and one came out. langs is not a column of values but a column of vectors, and unnest_longer() drops a row whose vector is empty unless you pass keep_empty = TRUE. No warning was issued. Every conversion from a tree to a rectangle contains a choice like this, and it is yours whether you make it deliberately or not.
The second is the shapefile, and it is stranger, because it is not a file at all:
list.files(file.path("data", "Geo"), pattern = "MS_Gebiete")
#> [1] "MS_Gebiete.dbf" "MS_Gebiete.prj" "MS_Gebiete.qpj" "MS_Gebiete.shp"
#> [5] "MS_Gebiete.shx".shp holds the geometry, .shx an index into it, .dbf the attribute table — a format from dBASE, 1983 — and .prj the coordinate reference system. Open the set and what arrives is disarmingly ordinary:
library(sf)
regions <- st_read(file.path("data", "Geo", "MS_Gebiete.shp"), quiet = TRUE)
regions %>% st_drop_geometry() %>% select(Code, Name) %>% head(4)
#> Code Name
#> 1 40255 Zürich
#> 2 40256 Glattal-Furttal
#> 3 40257 Limmattal
#> 4 40258 KnonaueramtA hundred and six Swiss regions, four attribute columns, and row one is Zürich. It is a data frame. What makes it different is the fifth column, where a value would normally sit:
st_geometry(regions)[1] # one cell of the table
#> Geometry set for 1 feature
#> Geometry type: MULTIPOLYGON
#> Dimension: XY
#> Bounding box: xmin: 676227 ymin: 241585 xmax: 689618 ymax: 254315
#> Projected CRS: Hotine_Oblique_Mercator_Azimuth_Center
nrow(st_coordinates(regions[1, ])) # how many numbers are in it
#> [1] 85Eighty-five pairs of coordinates, in one cell, for one row. That is the same border crossing as the JSON list: a cell that contains something which is not a value. The file sizes say the rest — the attribute table is 8 KB, the geometry 276 KB. Ninety-seven per cent of a shapefile is outlines.
And the fifth file, the small one, is plain text:
peek(file.path("data", "Geo", "MS_Gebiete.prj"), 200)
#> PROJCS["Hotine_Oblique_Mercator_Azimuth_Center",GEOGCS["GCS_Bessel 1841",DATUM["D_unknown",SPHEROID["bessel",6377397.155,299.1528128]],PRIMEM["Greenwich",0],UNIT["Degree",0.017453292519943295]],PROJECA projection in Well-Known Text: an oblique Mercator on the Bessel ellipsoid of 1841. Without it the numbers in .shp are coordinates on an unnamed planet, which is why sending a colleague only the .shp sends them nothing usable. sf::st_read() reads the whole set when you name the .shp, and Section 3.3 does the rest.
Look closely at Zürich in that table. The .dbf format has no reliable way of stating its own encoding, so the umlaut arrives as an escape sequence — the problem from two sections ago, in a format from 1983 that is still shipping geodata today.
2.5.6 When one machine is not enough
Every language has a format that keeps everything and that nobody else can read. R's is .rds: it saves anything — a fitted model, a list, a labelled tibble — and restores it exactly, which makes it the right choice for an intermediate result and the wrong choice for a deliverable. Never hand an .rds to a colleague, and never use it as an archive.45
Python's equivalent, .pkl, has one extra property worth knowing before you double-click anything. Unpickling a file runs code from it, so Python's own manual says plainly: "The pickle module is not secure. Only unpickle data you trust."46 A pickle from an unknown source is not a data file; it is a program. Its sibling .npy is harmless by comparison, and interesting for one detail: it opens with a header in readable ASCII giving the shape and type of the array — the same solution as Stata's, arrived at independently.47
The way out of both is a pair of formats designed together. Feather stores a table in exactly the layout Arrow uses in memory, so opening the file requires no translation at all — the program maps it in and the columns are simply there.48 No parsing, no type conversion: the step that consumes most of the time when reading a CSV does not happen. Parquet spends effort where Feather refuses to, with dictionary and run-length encoding and compression on top, which makes it small and stable enough to publish at the price that it must be decoded before anything can compute with it. Parquet trades reading speed for compactness, Feather disk space for speed, and both store data by column: to read three of fifty columns you read three fiftieths of the file.
# nanoparquet is tiny and needs no system libraries.
nanoparquet::write_parquet(soep, file.path("data", "soep.parquet"))That last property is what makes the size limit go away. A Parquet file can be queried without being loaded — DuckDB reads one directly from disk, and everything from Section 2.4 applies unchanged, dbplyr and all:49
con <- DBI::dbConnect(duckdb::duckdb())
DBI::dbGetQuery(con, "
SELECT syear, AVG(lebensz_org) AS satisfaction
FROM 'data/soep.parquet' -- the file is never loaded
WHERE alter BETWEEN 25 AND 65
GROUP BY syear ORDER BY syear
")At this book's scale none of it matters. 23,522 rows load in under a second whatever the format. These formats earn their place because public data are increasingly published as Parquet, and because they are the door to tables that do not fit in memory.
2.5.7 Where this leaves us
| If you want to | Use |
|---|---|
| hand data to someone whose software you do not know | .csv |
| hand over a CSV and still say what the columns mean | .csv + datapackage.json |
| keep the codebook attached to the values | .dta / .sav |
| save a fitted model or any other R object | .rds |
| store something that is not a rectangle | .json |
| append records all day without rewriting the file | .jsonl |
| publish or archive a large table | .parquet |
| pass a table between R and Python | .feather |
| query something too large to load | .duckdb |
| let a colleague edit it by hand | .xlsx, and check what it changed |
Three habits cover the rest. Look at the first bytes before trusting a file — one call to peek() settles what a download actually contains. State what you know instead of letting a reader guess: col_types, the separator, the encoding. And keep the raw file, whatever it is; every conversion loses something, and the only copy that has lost nothing is the one you were given.
Five sections asked what a row means and where the table lives. This one asked what the file promises, and the answers ran from 1890 to 2019 without any of them going out of date. The wire dipping into mercury did something no clerk could do at that speed, and also something no clerk would ever have done: it counted a hole without knowing what the hole meant. Everything since — value labels, schemas, DDI, column types in Parquet — has been an attempt to put the meaning back in the box with the data.
And the last two entries are a door. A shapefile stores a shape, JSON stores a tree, and neither is a rectangle — not because they are badly designed, but because a web page, a paragraph of text and a map have structures of their own that a table cannot hold. HTML is a tree, language has a grammar, geometry has a projection. That is what the next chapter is about, and the work in it is always the same walk: from their structure to ours.
Your Turn
Point peek() at three files of your own — a spreadsheet, a download from a public portal, and something exported from a statistics package. Which of the three is text?
.csv with an agreed encoding is usually right — but only if you read it with explicit col_types, or the postcodes lose their leading zeros and the dates are parsed in whichever order the reader's locale prefers. A datapackage.json beside it removes even that. .xlsx is the dangerous one: the conversion happens before you ever see the file. .dta and .parquet keep the types, at the price of requiring particular software.
Read More
The haven vignette Conversion semantics explains exactly what as_factor() and zap_labels() do to a labelled column: https://haven.tidyverse.org/articles/semantics.html. The Frictionless specification is short enough to read in an afternoon: https://datapackage.org/. And for the columnar idea in its original form, the Dremel paper is unusually readable for a systems paper.50
U.S. Census Bureau, The Hollerith Machine: https://www.census.gov/about/history/bureau-history/census-innovations/technology/hollerith-machine.html. In the 1888 trial Hollerith needed 72.5 hours against 144.5 and 100.5 for the competing methods, and 5.5 hours of preparation against 44.5 and 55.5. A clerk could punch about 500 cards a day; an experienced operator could feed 80 cards a minute through the tabulator, which carried 40 counting dials. The 1890 census produced roughly 100 million cards.↩︎
The pantograph punch and its celluloid template are described in the Columbia University computing history collection, https://www.columbia.edu/cu/computinghistory/census-tabulator.html; the board itself is in the Smithsonian's National Museum of American History, inscribed "The Hollerith Electric Tabulating System, System Patented January 8th 1889". An operator managed about 500 cards a day. The 1888 trial figures and the forty counting dials are from the U.S. Census Bureau, The Hollerith Machine: https://www.census.gov/about/history/bureau-history/census-innovations/technology/hollerith-machine.html.↩︎
Column layout and the purpose of columns 73-80 as described in R. J. LeVeque's course notes on punched cards, University of Washington: https://faculty.washington.edu/rjl/classes/am583s2014/notes/punchcard.html↩︎
Teletypewriters printed 72 characters per line, typewriter carriages allowed 80 to 90, and an IBM punched card held 80. The DEC VT52 and VT100 showed 80 columns, the IBM PC text mode was 80 x 25, and the style guides of several languages still recommend the same width.↩︎
The IBM Fortran (level H extended) compiler under OS/360 supported list-directed input with commas between values in 1972; the term comma-separated values was in print by 1983. The format was first written down in Shafranovich, Y. (2005), Common Format and MIME Type for Comma-Separated Values (CSV) Files, RFC 4180, Informational, https://doi.org/10.17487/RFC4180.↩︎
UK Data Service, File formats: https://ukdataservice.ac.uk/learning-hub/research-data-management/format-your-data/file-formats/. The alternative it offers in the same cell is telling: delimited text plus setup files plus DDI XML.↩︎
SOEPcompanion, Life Satisfaction: https://companion.soep.de/Survey%20Design/Life%20Satisfaction.html. The same wording, with the same eleven-point scale, is analysed in Section 8.3.↩︎
practice_data.do, shipped in the same folder as the data. It mergesppathl,pgenandpequiv, keeps the survey years 2015 to 2019 and the respondents with a completed individual questionnaire, renames and relabels, recodes sex, generates age, replaces the negative missing codes with system missing, draws a twenty per cent random sample, adds normal noise to the four income variables, and finally swaps the German labels for English ones. Every one of those steps is invisible in the resulting.dta.↩︎The Data Package standard is maintained by the Frictionless Data project at Open Knowledge Foundation: https://datapackage.org/. For the argument in a social science context, see Excel Can Corrupt Research Data - Data Packages to the Rescue, SAGE Research Methods Community.↩︎
Data Documentation Initiative, https://ddialliance.org/. DDI Codebook describes a single study; DDI Lifecycle covers the whole research cycle from concept to archive.↩︎
SPSS was released in 1968; Stata followed in January 1985, from the Computing Resource Center in California.↩︎
Crockford published json.org in 2001. The format was standardised as ECMA-404 in October 2013 and as RFC 8259 in December 2017.↩︎
ECMA International (2006). ECMA-376: Office Open XML File Formats, approved December 2006; ISO/IEC 29500 followed in 2008. https://ecma-international.org/publications-and-standards/standards/ecma-376/↩︎
Parquet was released by Twitter and Cloudera on 13 March 2013 and became a top-level Apache project on 27 April 2015. It implements the record shredding and assembly algorithm of Google's Dremel.↩︎
Pike, R. (2003). UTF-8 history. Electronic mail, 30 April 2003, archived by Markus Kuhn: https://www.cl.cam.ac.uk/~mgk25/ucs/utf-8-history.txt↩︎
The XML-style container was introduced with Stata 13; release 118, used here, is the format of Stata 14 and later. https://www.stata.com/help.cgi?dta↩︎
Ziemann, M., Eren, Y., & El-Osta, A. (2016). Gene name errors are widespread in the scientific literature. Genome Biology 17, 177. https://doi.org/10.1186/s13059-016-1044-7↩︎
Abeysooriya, M., Soria, M., Kasu, M. S., & Ziemann, M. (2021). Gene name errors: Lessons not learned. PLoS Computational Biology 17(7), e1008984. https://doi.org/10.1371/journal.pcbi.1008984↩︎
Bruford, E. A., Braschi, B., Denny, P., Jones, T. E. M., Seal, R. L., & Tweedie, S. (2020). Guidelines for human gene nomenclature. Nature Genetics 52, 754-758. https://doi.org/10.1038/s41588-020-0669-3↩︎
Serialisation format version 2 has been R's default since R 1.4.0, released in December 2001. See the Library of Congress format description of the R Data Format Family: https://www.loc.gov/preservation/digital/formats/fdd/fdd000470.shtml.
.RData(viasave()andload()) stores several objects at once under their own names, which is why it is the worse choice:load()silently overwrites whatever in your workspace shares a name.↩︎Python Software Foundation, pickle - Python object serialization: https://docs.python.org/3/library/pickle.html↩︎
NumPy Enhancement Proposal 1, A simple file format for NumPy arrays, 20 December 2007: https://numpy.org/neps/nep-0001-npy-format.html. The header is a Python dictionary written as ASCII text, holding the dtype, the storage order and the shape.↩︎
Apache Arrow, Frequently Asked Questions: https://arrow.apache.org/faq/. "There is no translation necessary between the on-disk representation and the in-memory representation" - whereas Parquet data "cannot be directly operated on but must be decoded in large chunks". Feather was introduced by McKinney, W., & Wickham, H. (2016), Feather: A Fast On-Disk Format for Data Frames for R and Python, RStudio Blog, 29 March 2016.↩︎
Mark Raasveldt and Hannes Mühleisen began DuckDB at the Centrum Wiskunde & Informatica in Amsterdam in 2018 and presented version 0.1 at SIGMOD in 2019: https://duckdb.org/history/. The same institute is where Guido van Rossum started Python.↩︎
Melnik, S., Gubarev, A., Long, J. J., Romer, G., Shivakumar, S., Tolton, M., & Vassilakis, T. (2010). Dremel: Interactive Analysis of Web-Scale Datasets. Proceedings of the VLDB Endowment 3(1), 330-339. https://doi.org/10.14778/1920841.1920886↩︎