R (4.6) Module 7 – Importing and exporting data
Excel, SPSS, Stata and SAS files
Read and write Excel workbooks in R with readxl and writexl, import SPSS, Stata and SAS files with haven, keep labels and missing codes, and pick a format.
What you will learn
- Read and write Excel workbooks with readxl and writexl, choosing sheets, cell ranges and column types
- Convert Excel date numbers into dates with the right origin
- Import SPSS, Stata and SAS files with haven and keep value labels and user-defined missing values
- Convert labelled data to factors deliberately, without losing information
- Choose an exchange format for collaborators who do not use R
Before you start
What you’ll learn · Before you start
What you will learn
- Read and write Excel workbooks with readxl and writexl, choosing sheets, cell ranges and column types
- Convert Excel date numbers into dates with the right origin
- Import SPSS, Stata and SAS files with haven and keep value labels and user-defined missing values
- Convert labelled data to factors deliberately, without losing information
- Choose an exchange format for collaborators who do not use R
Before you start
On this page
A field team’s workbook of six rice plots reads into R as 10 rows of text: a title above the table, a note below it, dates that are plain numbers and a yield written as a word. An SPSS survey of the same team reports that people sleep 1.83 hours a night, because two codes that mean “missing” were counted as hours.
On runs of R 4.6.1 with readxl 1.5.0.1, writexl 2.0.1 and haven 2.5.5, you will read exactly the table you want from a workbook, turn Excel day numbers into dates, decode a codes sheet into factors, read SPSS, Stata and SAS files with their labels and missing-value codes, and send each collaborator a file their own tool reads.
A workbook is sheets of cells; read one rectangle
A CSV is one table of text. An Excel workbook is a set of sheets, each a grid of cells that can hold numbers, text,
dates or nothing, laid out however a person liked. trial.xlsx has two sheets: data, with a title in row 1, a
header in row 3, six plots in rows 4 to 9 and a note in row 11, and codes, which names the three varieties. readxl
reads both .xls and .xlsx files and has no external dependencies, so Excel need not be installed:
from session import run, version
version()
# R reads workbook.R as if each line were typed at the console.
run("""
R --vanilla --quiet -f workbook.R
""")Output
# R version 4.6.1 -- "Happy Hop"
$ R --vanilla --quiet -f workbook.R
> library(readxl)
> excel_sheets("data/trial.xlsx")
[1] "data" "codes"
> # The first sheet from cell A1, as it is:
> read_excel("data/trial.xlsx")
New names:
• `` -> `...2`
• `` -> `...3`
• `` -> `...4`
• `` -> `...5`
• `` -> `...6`
# A tibble: 10 × 6
Rice variety trial: plot yields, kharif seaso…¹ ...2 ...3 ...4 ...5 ...6
<chr> <chr> <chr> <chr> <chr> <chr>
1 <NA> <NA> <NA> <NA> <NA> <NA>
2 plot block vari… sown harv… yiel…
3 1 1 2 46187 46302 41.2
4 2 1 1 46187 46302 38.5
5 3 1 3 46187 46303 <NA>
6 4 2 1 46188 46303 36.9
7 5 2 3 46188 46304 floo…
8 6 2 2 46188 46304 44
9 <NA> <NA> <NA> <NA> <NA> <NA>
10 Yields in kg per plot. Plot 5 was flooded befo… <NA> <NA> <NA> <NA> <NA>
# ℹ abbreviated name: ¹`Rice variety trial: plot yields, kharif season`
> # From the header row to the last plot:
> plots <- read_excel("data/trial.xlsx", sheet = "data", range = "A3:F9")
> plots
# A tibble: 6 × 6
plot block variety sown harvested yield_kg
<dbl> <dbl> <dbl> <dttm> <dbl> <chr>
1 1 1 2 2026-06-14 00:00:00 46302 41.2
2 2 1 1 2026-06-14 00:00:00 46302 38.5
3 3 1 3 2026-06-14 00:00:00 46303 <NA>
4 4 2 1 2026-06-15 00:00:00 46303 36.9
5 5 2 3 2026-06-15 00:00:00 46304 flooded
6 6 2 2 2026-06-15 00:00:00 46304 44
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 workbook_session.py
excel_sheets() lists the sheets by name. read_excel() with only a path reads the first sheet from cell A1, so the
title became the name of the first column, readxl invented names such as ...2 for the empty cells beside it, and
the header and the note became rows of data: 10 rows, every column text. Naming a
cell range: A rectangle of a spreadsheet named by its corners, such as A3:F9 for columns A to F of rows 3 to 9. read_excel(range = ...) reads only that rectangle, so a title or a note around the table stays out., range = "A3:F9", read exactly the header and the six plots. Step through
what each way of reading takes:
The drawing shows the whole scene; its 5 steps are listed below.
Every step as text
- From cell A1. read_excel() with only a path starts at cell A1 of the first sheet. The title in row 1 becomes the name of the first column, and the real header, row 3, becomes a row of data.
- Skipping two rows. skip = 2 starts at row 3, so the header names the columns plot, block, variety, sown, harvested and yield_kg.
- To the end of the sheet. skip still reads to the last used row. The blank row 10 and the note in row 11 arrive as two more rows, and the note's text turns the plot column into text.
- One rectangle. range = "A3:F9" reads exactly the header and the six plots, the rectangle a person sees as the table, and nothing around it.
- The other sheet. excel_sheets() listed a second sheet, codes, with the names of the three varieties. sheet = "codes" reads it the same way, from its own cell A1.
skip = 2 is the other common fix, and it fails in a quieter way: it moves the start of the read but not the end, so
a blank row and the note arrive as extra rows. Read by sheet name, sheet = "data", rather than by position, so the
script keeps working when someone adds or reorders sheets.
Anchor the start, check the end
When the number of rows changes from week to week, read with skip and then keep only the rows whose first column
holds a plot number. A fixed range is right only while the table never grows.
Checkpoint
Check: Sheets and cell ranges
Excel has one number type and counts dates in days
The six plots came back with plot as <dbl>, sown as a date-time and harvested as five-digit numbers. Each
follows from the way Excel stores cells, not from R. readxl’s guide to cell types lists four kinds of cell: empty, boolean, numeric and text. A
whole number is a numeric cell, which R receives as a double, and a date is a numeric cell too, shown as a date only
because of its number format. Name the types to stop the guessing:
from session import run, version
version()
# R reads col_types.R as if each line were typed at the console.
run("""
R --vanilla --quiet -f col_types.R
""")Output
# R version 4.6.1 -- "Happy Hop"
$ R --vanilla --quiet -f col_types.R
> library(readxl)
> plots <- read_excel("data/trial.xlsx", sheet = "data", range = "A3:F9",
+ col_types = c("numeric", "numeric", "numeric", "date", "numeric", "numeric"))
Warning message:
Expecting numeric in F8 / R8C6: got 'flooded'
> plots
# A tibble: 6 × 6
plot block variety sown harvested yield_kg
<dbl> <dbl> <dbl> <dttm> <dbl> <dbl>
1 1 1 2 2026-06-14 00:00:00 46302 41.2
2 2 1 1 2026-06-14 00:00:00 46302 38.5
3 3 1 3 2026-06-14 00:00:00 46303 NA
4 4 2 1 2026-06-15 00:00:00 46303 36.9
5 5 2 3 2026-06-15 00:00:00 46304 NA
6 6 2 2 2026-06-15 00:00:00 46304 44
> vapply(plots, function(x) class(x)[1], "")
plot block variety sown harvested yield_kg
"numeric" "numeric" "numeric" "POSIXct" "numeric" "numeric"
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 col_types_session.py
col_types takes one entry per column: "numeric", "text", "date", "logical", "skip", "guess" or
"list". The word
flooded in the yield of plot 5 cannot be a number, so readxl made it NA and warned with the cell’s address, F8.
Give the word to na instead, na = "flooded", and the read is quiet because you have said what the word means.
The harvested cells had lost their date format, so they arrived as the counts of days Excel stores, each an
Excel date number: The count of days that Excel stores for a date, in its 1900 or its 1904 date system. A cell without a date format reads into R as that plain number, which as.Date() with the system's origin turns back into a date.. Microsoft’s page on date systems says the 1900 system, the
default of Excel for Windows and current Excel for Mac, counts from the start of 1900, and that the 1904 system of
older Mac versions is 1,462 days behind it. Excel also counts a leap day that 1900 never had, for compatibility with
Lotus 1-2-3, Microsoft explains, so the origin that R’s help for as.Date() gives for dates from Windows Excel is
origin = "1899-12-30":
from session import run, version
version()
# R reads excel_dates.R as if each line were typed at the console.
run("""
R --vanilla --quiet -f excel_dates.R
""")Output
# R version 4.6.1 -- "Happy Hop"
$ R --vanilla --quiet -f excel_dates.R
> library(readxl)
> plots <- read_excel("data/trial.xlsx", sheet = "data", range = "A3:F9",
+ na = "flooded")
> class(plots$sown)
[1] "POSIXct" "POSIXt"
> plots$sown <- as.Date(plots$sown)
> plots$harvested
[1] 46302 46302 46303 46303 46304 46304
> # Day numbers of Excel's 1900 date system:
> plots$harvested <- as.Date(plots$harvested, origin = "1899-12-30")
> plots[, c("plot", "sown", "harvested")]
# A tibble: 6 × 3
plot sown harvested
<dbl> <date> <date>
1 1 2026-06-14 2026-10-07
2 2 2026-06-14 2026-10-07
3 3 2026-06-14 2026-10-08
4 4 2026-06-15 2026-10-08
5 5 2026-06-15 2026-10-09
6 6 2026-06-15 2026-10-09
> as.numeric(plots$harvested - plots$sown)
[1] 115 115 116 115 116 116
> # Microsoft's own example, the same day in its two date systems:
> as.Date(40729, origin = "1899-12-30")
[1] "2011-07-05"
> as.Date(39267, origin = "1904-01-01")
[1] "2011-07-05"
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 excel_dates_session.py
The sown dates came back as date-times at midnight UTC, and as.Date() made them dates. The harvest numbers 46302 to
46304 became dates with the 1900 origin, and the plots grew for 115 or 116 days. The last two lines check the method
on Microsoft’s own example, day 40729 in the 1900 system and day 39267 in the 1904 system, which come out as the same
day, 2011-07-05.
| The cell holds | readxl returns | Turn it into a Date with |
|---|---|---|
| a date, formatted as one | a date-time, <dttm> |
as.Date(x) |
| a day number of the 1900 system | a number | as.Date(x, origin = "1899-12-30") |
| a day number of the 1904 system | a number | as.Date(x, origin = "1904-01-01") |
| a date typed as text | text | as.Date(x, format = "%d/%m/%Y"), with the format it was typed in |
A warning on computers outside UTC
On a computer whose time zone is not UTC, R 4.6.1 on macOS can warn “datetimes before 1902 may not be accurate” the
first time it reads the origin "1899-12-30", as R 4.3.0’s news describes. The dates it returns are still right;
the runs on this page use UTC.
Checkpoint
Check: Excel numbers and dates
A codes sheet decides labels and their order
Field sheets often store a code, 2, where a report needs a name, Line 21, and keep the names on a sheet of their
own. The codes sheet then decides two things: what each code means and the order in which the categories appear. The
run reads both sheets, decodes the varieties and writes a clean workbook:
from session import run, version
version()
# R reads decode_write.R as if each line were typed at the console.
run("""
R --vanilla --quiet -f decode_write.R
""")Output
# R version 4.6.1 -- "Happy Hop"
$ R --vanilla --quiet -f decode_write.R
> library(readxl)
> plots <- read_excel("data/trial.xlsx", sheet = "data", range = "A3:F9",
+ na = "flooded")
> codes <- read_excel("data/trial.xlsx", sheet = "codes")
> codes
# A tibble: 3 × 2
code variety
<dbl> <chr>
1 1 Local check
2 2 Line 21
3 3 Line 34
> # The codes sheet decides the labels and their order:
> plots$variety <- factor(plots$variety, levels = codes$code,
+ labels = codes$variety)
> plots$variety
[1] Line 21 Local check Line 34 Local check Line 34 Line 21
Levels: Local check Line 21 Line 34
> table(plots$variety)
Local check Line 21 Line 34
2 2 2
> writexl::write_xlsx(list(plots = plots, codes = codes), "trial-clean.xlsx")
> excel_sheets("trial-clean.xlsx")
[1] "plots" "codes"
> back <- read_excel("trial-clean.xlsx", sheet = "plots")
> vapply(back, function(x) class(x)[1], "")
plot block variety sown harvested yield_kg
"numeric" "numeric" "character" "POSIXct" "numeric" "numeric"
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 decode_write_session.py
factor(plots$variety, levels = codes$code, labels = codes$variety) turned the codes into a
factor: R's type for categorical data: an integer vector of codes plus a levels attribute that lists the possible values, so the code 2 means the second level. More in “Factors and categorical data” whose levels are the codes sheet’s rows in their order, so every table and chart lists
Local check first, the variety the others are compared with. A code that is not on the sheet would become NA
without a warning, so count NA values before and after decoding.
writexl::write_xlsx() writes a data frame, or a named list of them as one sheet each, to .xlsx. Read back, the
dates and numbers are as they were, and variety is text: a workbook has no factor type, so the levels and their
order travel only on the codes sheet. By default writexl writes the values with a bold, centred header row; fonts,
fills, number formats and formulas take its wrapper functions, which a data file for analysis does not need.
Key idea
Read the table, name its types, then decode its codes from the sheet that defines them: the codes sheet is the one place that says what a category is called and where it comes in the order.
Checkpoint
Check: Decoding codes into factors
SPSS, Stata and SAS files carry labels R has no type for
Statistics packages store categories as numbers with names beside them. In an SPSS file, 1 and 2 in the column
smoker carry the value label: The text a statistics package shows for a stored code, such as Yes for 1 or Refused for -9. haven keeps value labels in the labels attribute of a labelled vector. Yes and No, the column carries a variable label, the
question “Do you smoke?”, and the code -9, Refused, is marked as a
user-defined missing value: A code that an SPSS file marks as missing, such as -9 for Refused. haven's read_sav() turns it into NA unless user_na = TRUE, which keeps the code and marks it as missing.. R has no type that holds all of that, so haven
reads such a column as a labelled vector: haven's type for a column of an SPSS, Stata or SAS file: the values, often numeric codes, with value labels such as 1 = Yes beside them and an optional variable label. as_factor() turns it into a factor.. make_survey.R writes six answers to
survey.sav, the way SPSS stores them:
# Writes survey.sav: six answers to a short survey, coded as SPSS stores them.
library(haven)
survey <- data.frame(id = 1:6)
survey$smoker <- labelled_spss(c(1, 2, 2, -9, 1, 3),
labels = c(Yes = 1, No = 2, Refused = -9), na_values = -9,
label = "Do you smoke?")
survey$sleep <- labelled_spss(c(7, 6, -8, 8, -9, 7),
labels = c("Don't know" = -8, Refused = -9), na_values = c(-9, -8),
label = "Hours of sleep last night")
write_sav(survey, "survey.sav")The run reads the file with the defaults, then with user_na = TRUE:
from session import run, version
version()
# R reads labelled.R as if each line were typed at the console.
run("""
R --vanilla --quiet -f labelled.R
""")Output
# R version 4.6.1 -- "Happy Hop"
$ R --vanilla --quiet -f labelled.R
> library(haven)
> source("make_survey.R")
> s <- read_sav("survey.sav")
> s
# A tibble: 6 × 3
id smoker sleep
<dbl> <dbl+lbl> <dbl+lbl>
1 1 1 [Yes] 7
2 2 2 [No] 6
3 3 2 [No] NA
4 4 NA 8
5 5 1 [Yes] NA
6 6 3 7
> s$smoker
<labelled<double>[6]>: Do you smoke?
[1] 1 2 2 NA 1 3
Labels:
value label
1 Yes
2 No
-9 Refused
> attr(s$sleep, "label")
[1] "Hours of sleep last night"
> # Keep SPSS's own missing-value codes:
> u <- read_sav("survey.sav", user_na = TRUE)
> u$sleep
<labelled_spss<double>[6]>: Hours of sleep last night
[1] 7 6 -8 8 -9 7
Missing values: -9, -8
Labels:
value label
-8 Don't know
-9 Refused
> is.na(u$sleep)
[1] FALSE FALSE TRUE FALSE TRUE FALSE
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 labelled_session.py
With the defaults, the -9 of answer 4 and the -8 and -9 of the sleep hours are NA: haven’s help says that, unless
user_na = TRUE, user-defined missing values are converted to NA. The labels are kept, the question is in the
column’s label attribute, and the answer 3, which has no label, stays the number 3. With user_na = TRUE the codes
stay, marked as missing values: is.na() reports them, but the numbers are still -8 and -9. Step through what each
call makes of the column:
The drawing shows the whole scene; its 6 steps are listed below.
Every step as text
- Codes and their labels. The file stores numbers, and beside them value labels, so 1 means Yes and 2 means No. haven reads the column as a labelled vector that keeps both, with the question as its variable label.
- A missing code. -9 means Refused, and the file marks it as a user-defined missing value. read_sav() turns it into NA by default, so it never counts as an answer.
- Keeping the code. With user_na = TRUE the -9 stays, still marked as missing. is.na() says TRUE for it, but mean() adds it in, which is how the sleep hours averaged 1.83 instead of 7.
- A value with no label. 3 has no label, an answer added after the labels were written. Every read keeps it as the number 3, and as_factor() will name its level "3".
- Labels become levels. as_factor() makes a factor whose levels are the labels, in the order of their values. A kept -9 becomes the ordinary level Refused, counted as one more answer.
- Missing first, then labels. zap_missing() turns the user-missing codes into NA first. as_factor() of the result counts Refused 0 times and shows the missing answer as NA.
haven reads the other packages the same way. read_dta() reads Stata files, whose missing values are tagged NA such
as .r, a missing value that remembers why; read_sas() reads SAS data files, and read_xpt() the SAS transport
format. Each returns labelled vectors where the file has labels, and the run below writes and reads both formats.
Checkpoint
Check: Labelled data from SPSS, Stata and SAS
Turn labels into factors once, after the missing codes
A labelled vector is a place to stop on the way to a factor, and two orders of the same two steps give different data. The run computes the sleep hours and tabulates the smoking answers both ways:
from session import run, version
version()
# R reads as_factor.R as if each line were typed at the console.
run("""
R --vanilla --quiet -f as_factor.R
""")Output
# R version 4.6.1 -- "Happy Hop"
$ R --vanilla --quiet -f as_factor.R
> library(haven)
> source("make_survey.R")
> u <- read_sav("survey.sav", user_na = TRUE)
> mean(u$sleep, na.rm = TRUE)
[1] 1.833333
> mean(zap_missing(u$sleep), na.rm = TRUE)
[1] 7
> as_factor(u$smoker)
[1] Yes No No Refused Yes 3
attr(,"label")
[1] Do you smoke?
Levels: Refused Yes No 3
> table(as_factor(u$smoker))
Refused Yes No 3
1 2 2 1
> table(as_factor(zap_missing(u$smoker)), useNA = "ifany")
Refused Yes No 3 <NA>
0 2 2 1 1
> as_factor(u$smoker, levels = "values")
[1] 1 2 2 -9 1 3
attr(,"label")
[1] Do you smoke?
Levels: -9 1 2 3
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 as_factor_session.py
mean(u$sleep, na.rm = TRUE) returned 1.83 hours: na.rm drops values that are NA, and the kept codes -8 and -9
are numbers, so they were averaged in. After zap_missing(), which converts user-defined and tagged missing values to
ordinary NA, the mean is 7. In the same way, as_factor() of the column read with user_na = TRUE made Refused an
ordinary level and counted it as one more answer; as_factor(zap_missing(...)) counted it 0 times and showed the
missing answer as NA.
as_factor() levels |
What it uses | The answer 3, which has no label |
|---|---|---|
"default" |
labels where there are any, values otherwise | the level 3 |
"label" |
labels only | NA |
"values" |
the codes | the level 3 |
"both" |
[code] label |
the level 3 |
as.numeric() to get the codes back
as.numeric() of the factor as_factor() made gives the levels’ positions, 2 for Yes, not the code 1. Keep the
labelled vector, or the codes in a column of their own, if anyone needs the codes later.
Older code reads SPSS files with the foreign package, which the R Core Team maintains, and the run shows it too:
foreign::read.spss() turned both labelled columns into factors as it read, the hours of sleep included, and warned
that it had added “undeclared levels” 6, 7 and 8. With haven you choose when a column becomes a factor, and which
columns do.
Key idea
Deal with the missing codes on the labelled vector first, then turn labels into factor levels, once, for the columns that are categories.
Checkpoint
Check: as_factor() and missing codes
Send each collaborator a file their tool reads
The labels, codes and types that R now holds are worth keeping on the way out. haven writes SPSS, Stata and SAS
transport files too, with write_sav(), write_dta() and write_xpt():
from session import run, version
version()
# R reads other_formats.R as if each line were typed at the console.
run("""
R --vanilla --quiet -f other_formats.R
""")Output
# R version 4.6.1 -- "Happy Hop"
$ R --vanilla --quiet -f other_formats.R
> library(haven)
> source("make_survey.R")
> s <- read_sav("survey.sav")
> # Stata keeps value labels; its missing values are tagged NA, not codes.
> write_dta(s, "survey.dta")
> read_dta("survey.dta")$smoker
<labelled<double>[6]>: Do you smoke?
[1] 1 2 2 NA 1 3
Labels:
value label
-9 Refused
1 Yes
2 No
> x <- labelled(c(1, 2, tagged_na("r")),
+ labels = c(Yes = 1, No = 2, Refused = tagged_na("r")))
> as_factor(x)
[1] Yes No Refused
Levels: Yes No Refused
> # SAS transport files keep the values, not the value labels:
> write_xpt(zap_labels(s), "survey.xpt")
> read_xpt("survey.xpt")
# A tibble: 6 × 3
id smoker sleep
<dbl> <dbl> <dbl>
1 1 1 7
2 2 2 6
3 3 2 NA
4 4 NA 8
5 5 1 NA
6 6 3 7
> # Base R's foreign package turns labelled columns into factors as it reads:
> f <- foreign::read.spss("survey.sav", to.data.frame = TRUE)
Warning messages:
1: In foreign::read.spss("survey.sav", to.data.frame = TRUE) :
Undeclared level(s) 3 added in variable: smoker
2: In foreign::read.spss("survey.sav", to.data.frame = TRUE) :
Undeclared level(s) 6, 7, 8 added in variable: sleep
> f$smoker
[1] Yes No No <NA> Yes 3
Levels: Yes No 3
> f$sleep
[1] 7 6 <NA> 8 <NA> 7
Levels: 6 7 8
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 other_formats_session.py
The Stata file kept the value labels of smoker. A vector built with tagged_na("r"), the kind of missing value
haven reads from Stata and SAS files, became the level Refused under as_factor(), because that label belongs to the
tag. The SAS transport file holds the values only: the run removed the value labels with zap_labels() before
writing it. Which file fits depends on the person who receives it. Answer two questions about yours:
Answer the questions above: the table then marks what fits your case and says why the rest does not.
| Format | Opens without R | Keeps column types | Keeps value labels | Keeps missing codes |
|---|---|---|---|---|
| CSV and a data dictionary Holds: text, plus a table of types, labels and codes | Good any spreadsheet, editor or language | Mixed text only; the dictionary states each type | Mixed as rows of the dictionary | Mixed only if the codes are written and listed |
| .xlsx from writexl Holds: sheets of cells: numbers, text and dates | Good Excel, other spreadsheets and readxl | Mixed numbers and dates survive; the factor came back as text | Mixed the labels survive as text and the codes are lost | Poor a blank cell, with the reason gone |
| .sav from write_sav() Holds: SPSS values with their labels | Mixed SPSS and other tools that read .sav | Good numbers and text with their formats | Good value and variable labels came back | Good user-defined missing values, with user_na = TRUE |
| .dta from write_dta() Holds: Stata values with their labels | Mixed Stata and other tools that read .dta | Good numbers and text with their formats | Good the smoker labels came back from survey.dta | Mixed tagged NA such as Refused, not SPSS codes |
| RDS from saveRDS() Holds: one R object, exactly | Poor only R reads it | Good identical() was TRUE after the round trip | Good a labelled vector keeps its attributes | Good kept exactly as R held them |
How the answers decide
- “Which program will your collaborator open the data in?” SPSS: rules out .dta from write_dta() and RDS from saveRDS(). SPSS reads its own .sav files with their labels; a .dta or an RDS file needs converting first.
- “Which program will your collaborator open the data in?” Stata: rules out .sav from write_sav() and RDS from saveRDS(). Stata reads .dta files with their value labels; a .sav or an RDS file needs converting first.
- “Which program will your collaborator open the data in?” A spreadsheet, such as Excel: rules out .sav from write_sav(), .dta from write_dta() and RDS from saveRDS(). A spreadsheet opens .xlsx and CSV files, not SPSS, Stata or R files.
- “Which program will your collaborator open the data in?” Python or another language: rules out RDS from saveRDS() and adds a caveat to .sav from write_sav() and .dta from write_dta(). Every language reads CSV. Check that the other tool reads .sav or .dta before you send one, and that it keeps the labels.
- “Must the value labels and the missing-value codes arrive as they are?” Yes, both: adds a caveat to CSV and a data dictionary and .xlsx from writexl. In a CSV or a workbook the labels and missing codes survive only if a dictionary lists them; a blank cell carries no reason.
For anyone without a statistics package, the safe choice is a CSV, or an .xlsx with a sheet for the codes, with a
data dictionary: A table that describes each column of a data file: its name, type, unit, value labels, missing-value codes and level order. It travels with a CSV to carry what the CSV cannot.: one row per column with its name, type, unit, value labels, missing
codes and level order. Two limits decide the rest. A worksheet holds at most 1,048,576 rows, Microsoft’s
specifications say, so a larger table goes as a summary in the workbook and in full in a format such as Parquet, a
column-oriented file format that stores a type for every column. And keep Excel out of the analysis path:
Microsoft’s guide on leading zeros says Excel removes them and keeps only 15 digits of long numbers when values are
typed or imported, so a file that has been through Excel and back is new input, to be read and checked again.
Checkpoint
Check: Choosing an exchange format
Practise: predict, then check
Eight lines go back over the lesson. Predict each result before you look at the run.
Before you look
Predict the class of plot read with range = "A3:F9", the number of rows that skip = 2 returns, whether day
46187 of the 1900 system and day 46187 − 1462 of the 1904 system are the same date, what factor() makes of a code
missing from its levels, and the mean and the factor of a labelled vector holding 5 and the missing code -9. Then
type the lines into your own R.
from session import run, version
version()
# R reads practice.R as if each line were typed at the console.
run("""
R --vanilla --quiet -f practice.R
""")Output
# R version 4.6.1 -- "Happy Hop"
$ R --vanilla --quiet -f practice.R
> library(readxl)
> library(haven)
> # Predict each result before you look.
> class(read_excel("data/trial.xlsx", range = "A3:F9")$plot)
[1] "numeric"
> nrow(read_excel("data/trial.xlsx", skip = 2))
[1] 8
> as.Date(46187, origin = "1899-12-30") == as.Date(46187 - 1462, origin = "1904-01-01")
[1] TRUE
> factor(c(2, 4), levels = 1:3, labels = c("Local check", "Line 21", "Line 34"))
[1] Line 21 <NA>
Levels: Local check Line 21 Line 34
> x <- labelled_spss(c(5, -9), labels = c(Refused = -9), na_values = -9)
> mean(x)
[1] -2
> mean(zap_missing(x), na.rm = TRUE)
[1] 5
> as_factor(x)
[1] 5 Refused
Levels: Refused 5
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 practice_session.py
plot is numeric, skip = 2 brought back 8 rows with the blank row and the note, the two date systems agreed, the
unknown code 4 became NA without a word, the mean of 5 and -9 is -2 until zap_missing() leaves 5, and
as_factor() gave the code -9 its label, Refused, as a level.
The questions come next, starting from the idea you have shown least so far, with every answer explained as soon as you check it:
Practice that adapts to you
Practise this lesson
14 questions about 6 ideas of this lesson. Each answer is explained at once, and what you get right is kept in this browser.
Interview questions
Warm-up: what does haven’s as_factor() do to an SPSS column with value labels, and when should you not call it yet?
as_factor() turns a labelled vector into a factor whose levels are the value labels, in the order of their codes,
and uses the code itself as the level for a value without a label, so 1 = Yes, 2 = No and an unlabelled 3 become the
levels Yes, No and 3. levels = "values" uses the codes instead, and levels = "label" makes unlabelled values NA.
Do not call it yet if the column was read with user_na = TRUE: the missing codes still have labels, so Refused would
become an ordinary level and count as an answer. Run zap_missing() first. And do not call it on columns that are
quantities, such as hours or age, whose only labels name the missing codes: they should stay numbers.
Model answers open with any pass: ₹79 for 1 week ($2.99), one payment that never renews.
-
Fresher to mid
Two analysts read the same SPSS survey of 400 people into R. One reports a mean age of 38.9, the other 37.5, and a third gets NA with a warning. The file codes Refused as -9, marked as missing. Explain how all three results can come from one file, and which is right.
-
Mid to senior
A field team sends a workbook every week: a title in row 1, the header in row 3, notes under the table, a codes sheet, and dates that are sometimes date cells and sometimes plain numbers. Your import breaks every few weeks. How do you make it robust, and how do you know when it has gone wrong?
Key takeaways
- A workbook is sheets of cells: read by sheet name and name the table’s rectangle with
range, or useskipand then check the rows, because a title and a note around the table become data otherwise. - Excel has one numeric cell type, and a date is a count of days: a cell without a date format arrives as a number
that
as.Date(x, origin = "1899-12-30")converts in the 1900 system. - Decode codes with
factor(x, levels = codes, labels = names)from the sheet that defines them, countNAvalues around it, and keep the codes sheet beside any workbook you write. - haven reads SPSS, Stata and SAS files into labelled vectors, turns user-defined missing values into
NAunlessuser_na = TRUE, and keeps variable and value labels as attributes. - Run
zap_missing()before arithmetic oras_factor(), or missing codes count as numbers and as categories. - Send a
.savto SPSS users, a.dtato Stata users, and everyone else a CSV or a workbook with a data dictionary; a table of more than 1,048,576 rows does not fit in one worksheet.
Terms in this lesson
- cell range
- A rectangle of a spreadsheet named by its corners, such as A3:F9 for columns A to F of rows 3 to 9. read_excel(range = ...) reads only that rectangle, so a title or a note around the table stays out.
- data dictionary
- A table that describes each column of a data file: its name, type, unit, value labels, missing-value codes and level order. It travels with a CSV to carry what the CSV cannot.
- Excel date number
- The count of days that Excel stores for a date, in its 1900 or its 1904 date system. A cell without a date format reads into R as that plain number, which as.Date() with the system's origin turns back into a date.
- labelled vector
- haven's type for a column of an SPSS, Stata or SAS file: the values, often numeric codes, with value labels such as 1 = Yes beside them and an optional variable label. as_factor() turns it into a factor.
- user-defined missing value
- A code that an SPSS file marks as missing, such as -9 for Refused. haven's read_sav() turns it into NA unless user_na = TRUE, which keeps the code and marks it as missing.
- value label
- The text a statistics package shows for a stored code, such as Yes for 1 or Refused for -9. haven keeps value labels in the labels attribute of a labelled vector.
References
- readxl: Read Excel files (readxl authors (Posit))
- readxl: Read xls and xlsx files (read_excel) (readxl authors (Posit))
- readxl: Specify cells for reading (cell specification) (readxl authors (Posit))
- readxl: Cell and column types (readxl authors (Posit))
- writexl: Export to xlsx (write_xlsx) (rOpenSci)
- haven: Read and write SPSS files (read_spss, read_sav) (haven authors (Posit))
- haven: Convert input to a factor (as_factor) (haven authors (Posit))
- haven: Labelled vectors for SPSS (labelled_spss) (haven authors (Posit))
- haven: Zap special missings to regular R missings (zap_missing) (haven authors (Posit))
- haven: Read and write Stata DTA files (read_dta) (haven authors (Posit))
- haven: 'Tagged' missing values (tagged_na) (haven authors (Posit))
- haven: Read and write SAS transport files (read_xpt) (haven authors (Posit))
- haven: Conversion semantics (haven authors (Posit))
- foreign: Read an SPSS data file (read.spss) (The R Core Team)
- Date Conversion Functions to and from Character (as.Date) (The R Core Team)
- R Data Import/Export (The R Core Team)
- Date systems in Excel (Microsoft)
- Excel incorrectly assumes that the year 1900 is a leap year (Microsoft)
- Excel specifications and limits (Microsoft)
- Keeping leading zeros and large numbers (Microsoft)
- Apache Parquet: Types (The Apache Software Foundation)
- Apache Parquet: Overview (The Apache Software Foundation)
Related tools
Report a problem with this lesson
Kept only in this browser. Your Learn progress