I/O for foreign tables (Excel, SAS, SPSS, Stata) - R Full Tutorial

R - I/O for foreign tables (Excel, SAS, SPSS, Stata)

Start

Start
Importing data with rio A very simple way to import data from many common file formats is with rio (opens new window). This package provides a function `import()` that wraps many commonly used data import functions, thereby providing a standard interface. It works simply by passing a file name or URL to `import()`: `import("example.csv") # comma-separated values import("example.tsv") # tab-separated values import("example.dta") # Stata import("example.sav") # SPSS import("example.sas7bdat") # SAS import("example.xlsx") # Excel` `import()` can also read from compressed directories, URLs (HTTP or HTTPS), and the clipboard. A comprehensive list of all supported file formats is available on the rio package github repository (opens new window). It is even possible to specify some further parameters related to the specific file format you are trying to read, passing them directly within the `import()` function: `import("example.csv", format = ",") #for csv file where comma is used as separator import("example.csv", format = ";") #for csv file where semicolon is used as separator`
Read and write Stata, SPSS and SAS files The packages `foreign` and `haven` can be used to import and export files from a variety of other statistical packages like Stata, SPSS and SAS and related software. There is a `read` function for each of the supported data types to import the files. `# loading the packages library(foreign) library(haven) library(readstata13) library(Hmisc)` Some examples for the most common data types: # reading Stata files with `foreign` read.dta("path\to\your\data") # reading Stata files with `haven` read_dta("path\to\your\data") The `foreign` package can read in stata (.dta) files for versions of Stata 7-12. According to the development page, the `read.dta` is more or less frozen and will not be updated for reading in versions 13+. For more recent versions of Stata, you can use either the `readstata13` package or `haven`. For `readstata13`, the files are # reading recent Stata (13+) files with `readstata13` read.dta13("path\to\your\data") For reading in SPSS and SAS files # reading SPSS files with `foreign` read.spss("path\to\your\data.sav", to.data.frame = TRUE) # reading SPSS files with `haven` read_spss("path\to\your\data.sav") read_sav("path\to\your\data.sav") read_por("path\to\your\data.por") # reading SAS files with `foreign` read.ssd("path\to\your\data") # reading SAS files with `haven` read_sas("path\to\your\data") # reading native SAS files with `Hmisc` sas.get("path\to\your\data") #requires access to saslib # Reading SA XPORT format ( *.XPT ) files sasxport.get("path\to\your\data.xpt") # does not require access to SAS executable The `SAScii` package provides functions that will accept SAS SET import code and construct a text file that can be processed with `read.fwf`. It has proved very robust for import of large public-released datasets. Support is at https://github.com/ajdamico/SAScii (opens new window) To export data frames to other statistical packages you can use the write functions `write.foreign()`. This will write 2 files, one containing the data and one containing instructions the other package needs to read the data. # writing to Stata, SPSS or SAS files with `foreign` write.foreign(dataframe, datafile, codefile, package = c("SPSS", "Stata", "SAS"), ...) write.foreign(dataframe, "path\to\data\file", "path\to\instruction\file", package = "Stata") # writing to Stata files with `foreign` write.dta(dataframe, "file", version = 7L, convert.dates = TRUE, tz = "GMT", convert.factors = c("labels", "string", "numeric", "codes")) # writing to Stata files with `haven` write_dta(dataframe, "path\to\your\data") # writing to Stata files with `readstata13` save.dta13(dataframe, file, data.label = NULL, time.stamp = TRUE, convert.factors = TRUE, convert.dates = TRUE, tz = "GMT", add.rownames = FALSE, compress = FALSE, version = 117, convert.underscore = FALSE) # writing to SPSS files with `haven` write_sav(dataframe, "path\to\your\data") File stored by the SPSS can also be read with `read.spss` in this way: `foreign::read.spss('data.sav', to.data.frame=TRUE, use.value.labels=FALSE, use.missings=TRUE, reencode='UTF-8') # to.data.frame if TRUE: return a data frame # use.value.labels if TRUE: convert variables with value labels into R factors with those levels # use.missings if TRUE: information on user-defined missing values will used to set the corresponding values to NA. # reencode character strings will be re-encoded to the current locale. The default, NA, means to do so in a UTF-8 locale, only.`
Import or Export of Feather file Feather (opens new window) is an implementation of Apache Arrow (opens new window) designed to store data frames in a language agnostic manner while maintaining metadata (e.g. date classes), increasing interoperability between Python and R. Reading a feather file will produce a tibble, not a standard data.frame. library(feather) path <- "filename.feather" df <- mtcars write_feather(df, path) df2 <- read_feather(path) head(df2) ## A tibble: 6 x 11 ## mpg cyl disp hp drat wt qsec vs am gear carb ## <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> ## 1 21.0 6 160 110 3.90 2.620 16.46 0 1 4 4 ## 2 21.0 6 160 110 3.90 2.875 17.02 0 1 4 4 ## 3 22.8 4 108 93 3.85 2.320 18.61 1 1 4 1 ## 4 21.4 6 258 110 3.08 3.215 19.44 1 0 3 1 ## 5 18.7 8 360 175 3.15 3.440 17.02 0 0 3 2 ## 6 18.1 6 225 105 2.76 3.460 20.22 1 0 3 1 head(df) ## mpg cyl disp hp drat wt qsec vs am gear carb ## Mazda RX4 21.0 6 160 110 3.90 2.620 16.46 0 1 4 4 ## Mazda RX4 Wag 21.0 6 160 110 3.90 2.875 17.02 0 1 4 4 ## Datsun 710 22.8 4 108 93 3.85 2.320 18.61 1 1 4 1 ## Hornet 4 Drive 21.4 6 258 110 3.08 3.215 19.44 1 0 3 1 ## Hornet Sportabout 18.7 8 360 175 3.15 3.440 17.02 0 0 3 2 ## Valiant 18.1 6 225 105 2.76 3.460 20.22 1 0 3 1 The current documentation contains this warning: Note to users: Feather should be treated as alpha software. In particular, the file format is likely to evolve over the coming year. Do not use Feather for long-term data storage.

Importing data with rio

A very simple way to import data from many common file formats is with rio (opens new window). This package provides a function import() that wraps many commonly used data import functions, thereby providing a standard interface. It works simply by passing a file name or URL to import():

import("example.csv")       # comma-separated values
import("example.tsv")       # tab-separated values
import("example.dta")       # Stata
import("example.sav")       # SPSS
import("example.sas7bdat")  # SAS
import("example.xlsx")      # Excel

import() can also read from compressed directories, URLs (HTTP or HTTPS), and the clipboard. A comprehensive list of all supported file formats is available on the rio package github repository (opens new window).

It is even possible to specify some further parameters related to the specific file format you are trying to read, passing them directly within the import() function:

import("example.csv", format = ",") #for csv file where comma is used as separator
import("example.csv", format = ";") #for csv file where semicolon is used as separator

Read and write Stata, SPSS and SAS files

The packages foreign and haven can be used to import and export files from a variety of other statistical packages like Stata, SPSS and SAS and related software. There is a read function for each of the supported data types to import the files.

# loading the packages
library(foreign)
library(haven)
library(readstata13)
library(Hmisc)

Some examples for the most common data types:

# reading Stata files with `foreign`
read.dta("path\to\your\data")
# reading Stata files with `haven`
read_dta("path\to\your\data")

The foreign package can read in stata (.dta) files for versions of Stata 7-12. According to the development page, the read.dta is more or less frozen and will not be updated for reading in versions 13+. For more recent versions of Stata, you can use either the readstata13 package or haven. For readstata13, the files are

# reading recent Stata (13+) files with `readstata13`
read.dta13("path\to\your\data")

For reading in SPSS and SAS files

# reading SPSS files with `foreign`
read.spss("path\to\your\data.sav", to.data.frame = TRUE)
# reading SPSS files with `haven`
read_spss("path\to\your\data.sav")
read_sav("path\to\your\data.sav")
read_por("path\to\your\data.por")

# reading SAS files with `foreign`
read.ssd("path\to\your\data")
# reading SAS files with `haven`
read_sas("path\to\your\data")
# reading native SAS files with `Hmisc`
sas.get("path\to\your\data")   #requires access to saslib 
# Reading SA XPORT format ( *.XPT ) files
sasxport.get("path\to\your\data.xpt")  # does not require access to SAS executable

The SAScii package provides functions that will accept SAS SET import code and construct a text file that can be processed with read.fwf. It has proved very robust for import of large public-released datasets. Support is at https://github.com/ajdamico/SAScii (opens new window)

To export data frames to other statistical packages you can use the write functions write.foreign(). This will write 2 files, one containing the data and one containing instructions the other package needs to read the data.

# writing to Stata, SPSS or SAS files with `foreign`
write.foreign(dataframe, datafile, codefile,
              package = c("SPSS", "Stata", "SAS"), ...)
write.foreign(dataframe, "path\to\data\file", "path\to\instruction\file", package = "Stata")

# writing to Stata files with `foreign`
write.dta(dataframe, "file", version = 7L,
          convert.dates = TRUE, tz = "GMT",
          convert.factors = c("labels", "string", "numeric", "codes"))

# writing to Stata files with `haven`
write_dta(dataframe, "path\to\your\data")

# writing to Stata files with `readstata13`
save.dta13(dataframe, file, data.label = NULL, time.stamp = TRUE,
  convert.factors = TRUE, convert.dates = TRUE, tz = "GMT",
  add.rownames = FALSE, compress = FALSE, version = 117,
  convert.underscore = FALSE)

# writing to SPSS files with `haven`
write_sav(dataframe, "path\to\your\data")

File stored by the SPSS can also be read with read.spss in this way:


foreign::read.spss('data.sav', to.data.frame=TRUE, use.value.labels=FALSE, 
                     use.missings=TRUE, reencode='UTF-8')
# to.data.frame if TRUE: return a data frame
# use.value.labels if TRUE: convert variables with value labels into R factors with those levels
# use.missings if TRUE: information on user-defined missing values will used to set the corresponding values to NA.
# reencode character strings will be re-encoded to the current locale. The default, NA, means to do so in a UTF-8 locale, only.

Import or Export of Feather file

Feather (opens new window) is an implementation of Apache Arrow (opens new window) designed to store data frames in a language agnostic manner while maintaining metadata (e.g. date classes), increasing interoperability between Python and R. Reading a feather file will produce a tibble, not a standard data.frame.

library(feather)

path <- "filename.feather"
df <- mtcars

write_feather(df, path)

df2 <- read_feather(path)

head(df2)
##  A tibble: 6 x 11
##     mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb
##   <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
## 1  21.0     6   160   110  3.90 2.620 16.46     0     1     4     4
## 2  21.0     6   160   110  3.90 2.875 17.02     0     1     4     4
## 3  22.8     4   108    93  3.85 2.320 18.61     1     1     4     1
## 4  21.4     6   258   110  3.08 3.215 19.44     1     0     3     1
## 5  18.7     8   360   175  3.15 3.440 17.02     0     0     3     2
## 6  18.1     6   225   105  2.76 3.460 20.22     1     0     3     1

head(df)
##                    mpg cyl disp  hp drat    wt  qsec vs am gear carb
## Mazda RX4         21.0   6  160 110 3.90 2.620 16.46  0  1    4    4
## Mazda RX4 Wag     21.0   6  160 110 3.90 2.875 17.02  0  1    4    4
## Datsun 710        22.8   4  108  93 3.85 2.320 18.61  1  1    4    1
## Hornet 4 Drive    21.4   6  258 110 3.08 3.215 19.44  1  0    3    1
## Hornet Sportabout 18.7   8  360 175 3.15 3.440 17.02  0  0    3    2
## Valiant           18.1   6  225 105 2.76 3.460 20.22  1  0    3    1

The current documentation contains this warning:

Note to users: Feather should be treated as alpha software. In particular, the file format is likely to evolve over the coming year. Do not use Feather for long-term data storage.

Read Article
https://develop.horje.com/learn/1434/reference