R Data Import/Export Version 2.13.1 (2011-07-08) R Development Core Team Permission is granted to make and distribute verbatim copies of this manual provided the copy- right notice and this permission notice are preserved on all copies. Permission is granted to copy and distribute modified versions of this manual under the condi- tions for verbatim copying, provided that the entire resulting derived work is distributed under the terms of a permission notice identical to this one. Permission is granted to copy and distribute translations of this manual into another language, under the above conditions for modified versions, except that this permission notice may be stated in a translation approved by the R Development Core Team. Copyright (cid:13)c 2000–2010 R Development Core Team ISBN 3-900051-10-0 i Table of Contents Acknowledgements ................................................ 1 1 Introduction ................................................... 2 1.1 Imports........................................................................... 2 1.1.1 Encodings.................................................................... 3 1.2 Export to text files................................................................ 3 1.3 XML.............................................................................. 5 2 Spreadsheet-like data.......................................... 6 2.1 Variations on read.table......................................................... 6 2.2 Fixed-width-format files........................................................... 8 2.3 Data Interchange Format (DIF).................................................... 9 2.4 Using scan directly................................................................ 9 2.5 Re-shaping data.................................................................. 10 2.6 Flat contingency tables........................................................... 11 3 Importing from other statistical systems................... 12 3.1 EpiInfo, Minitab, S-PLUS, SAS, SPSS, Stata, Systat.............................. 12 3.2 Octave........................................................................... 13 4 Relational databases ......................................... 14 4.1 Why use a database?............................................................. 14 4.2 Overview of RDBMSs............................................................ 14 4.2.1 SQL queries ................................................................. 15 4.2.2 Data types.................................................................. 15 4.3 R interface packages.............................................................. 16 4.3.1 Packages using DBI.......................................................... 16 4.3.2 Package RODBC............................................................ 17 5 Binary files.................................................... 20 5.1 Binary data formats.............................................................. 20 5.2 dBase files (DBF)................................................................ 20 6 Connections................................................... 21 6.1 Types of connections............................................................. 21 6.2 Output to connections............................................................ 22 6.3 Input from connections........................................................... 22 6.3.1 Pushback.................................................................... 23 6.4 Listing and manipulating connections............................................. 23 6.5 Binary connections............................................................... 23 6.5.1 Special values ............................................................... 24 7 Network interfaces ........................................... 25 7.1 Reading from sockets............................................................. 25 7.2 Using download.file............................................................ 25 7.3 DCOM interface.................................................................. 25 7.4 CORBA interface................................................................. 25 ii 8 Reading Excel spreadsheets ................................. 27 Appendix A References ....................................... 28 Function and variable index..................................... 29 Concept index.................................................... 31 Acknowledgements 1 Acknowledgements The relational databases part of this manual is based in part on an earlier manual by Douglas Bates and Saikat DebRoy. The principal author of this manual was Brian Ripley. Many volunteers have contributed to the packages used here. The principal authors of the packages mentioned are CORBA Duncan Temple Lang DBI David A. James dataframes2xls Guido van Steen foreign Thomas Lumley, Saikat DebRoy, Douglas Bates, Duncan Murdoch and Roger Bivand gdata Gregory R. Warnes hdf5 Marcus Daniels ncdf, ncdf4 David Pierce rJava Simon Urbanek RJDBC Simon Urbanek RMySQL David James and Saikat DebRoy RNetCDF Pavel Michna RODBC Michael Lapsley and Brian Ripley RMySQL David A, James and Saikat DebRoy ROracle David A, James RPostgreSQL Sameer Kumar Prayaga RSPerl Duncan Temple Lang RSPython Duncan Temple Lang RSQLite David A, James SJava John Chambers and Duncan Temple Lang WriteXLS Marc Schwartz XLConnect Mirai Solutions GmbH xlsReadWrite Hans-Peter Suter XML Duncan Temple Lang Brian Ripley is the author of the support for connections. Chapter 1: Introduction 2 1 Introduction Readingdataintoastatisticalsystemforanalysisandexportingtheresultstosomeothersystem forreportwritingcanbefrustratingtasksthatcantakefarmoretimethanthestatisticalanalysis itself, even though most readers will find the latter far more appealing. This manual describes the import and export facilities available either in R itself or via packages which are available from CRAN or elsewhere. Unlessotherwisestated,everythingdescribedinthismanualis(atleastinprinciple)available on all platforms running R. In general, statistical systems like R are not particularly well suited to manipulations of large-scale data. Some other systems are better than R at this, and part of the thrust of this manualistosuggestthatratherthanduplicatingfunctionalityinRwecanmakeanothersystem do the work! (For example Therneau & Grambsch (2000) comment that they prefer to do data manipulationinSASandthenusesurvivalinSfortheanalysis.) Databasemanipulationsystems are often very suitable for manipulating an extracting data: several packages to interact with DBMSs are discussed here. There are packages to allow functionality developed in languages such as Java, perl and python to be directly integrated with R code, making the use of facilities in these languages evenmoreappropriate. (SeetherJavapackagefromCRANandtheSJava,RSPerlandRSPython packages from the Omegahat project, http://www.omegahat.org.) It is also worth remembering that R like S comes from the Unix tradition of small re-usable tools, and it can be rewarding to use tools such as awk and perl to manipulate data before import or after export. The case study in Becker, Chambers & Wilks (1988, Chapter 9) is an example of this, where Unix tools were used to check and manipulate the data before input to S. The traditional Unix tools are now much more widely available, including for Windows. 1.1 Imports TheeasiestformofdatatoimportintoRisasimpletextfile,andthiswilloftenbeacceptablefor problems of small or medium scale. The primary function to import from a text file is scan, and this underlies most of the more convenient functions discussed in Chapter 2 [Spreadsheet-like data], page 6. However, all statistical consultants are familiar with being presented by a client with a memory stick (formerly, a floppy disc or CD-R) of data in some proprietary binary format, for example ‘an Excel spreadsheet’ or ‘an SPSS file’. Often the simplest thing to do is to use the originating application to export the data as a text file (and statistical consultants will have copies of the most common applications on their computers for that purpose). However, this is not always possible, and Chapter 3 [Importing from other statistical systems], page 12 discusses what facilities are available to access such files directly from R. For Excel spreadsheets, the available methods are summarized in Chapter 8 [Reading Excel spreadsheets], page 27. For ODS spreadsheets from Open Office, see the Omegahat package1 ROpenOffice. In a few cases, data have been stored in a binary form for compactness and speed of access. Oneapplicationofthisthatwehaveseenseveraltimesisimagingdata, whichisnormallystored asastreamofbytesasrepresentedinmemory,possiblyprecededbyaheader. Suchdataformats are discussed in Chapter 5 [Binary files], page 20 and Section 6.5 [Binary connections], page 23. For much larger databases it is common to handle the data using a database management system (DBMS). There is once again the option of using the DBMS to extract a plain file, but for many such DBMSs the extraction operation can be done directly from an R package: See 1 Currently not available from that repository but as a source package for download from http://www.omegahat.org/ROpenOffice/. Chapter 1: Introduction 3 Chapter 4 [Relational databases], page 14. Importing data via network connections is discussed in Chapter 7 [Network interfaces], page 25. 1.1.1 Encodings Unless the file to be imported from is entirely in ASCII, it is usually necessary to know how it was encoded. For text files, a good way to find out something about its structure is the file command-line tool (for Windows, included in Rtools). This reports something like text.Rd: UTF-8 Unicode English text text2.dat: ISO-8859 English text text3.dat: Little-endian UTF-16 Unicode English character data, with CRLF line terminators intro.dat: UTF-8 Unicode text intro.dat: UTF-8 Unicode (with BOM) text Modern Unix-alike systems, including Mac OS X, are likely to produce UTF-8 files. Windows may produce what it calls ‘Unicode’ files (UCS-2LE or just possibly UTF-16LE2). Otherwise most files will be in a 8-bit encoding unless from a Chinese/Japanese/Korean locale (which have a very wide range of encodings in common use). It is not possible to automatically detect with certainty which 8-bit encoding (although guesses may be possible and file may guess as it did intheexampleabove), soyoumaysimplyhavetoasktheoriginatorforsomeclues(e.g.‘Russian on Windows’). ‘BOMs’ (Byte Order Marks, http://en.wikipedia.org/wiki/Byte_order_mark) cause problems for Unicode files. In the Unix world BOMs are rarely used, whereas in the Win- dows world they almost always are for UCS-2/UTF-16 files, and often are for UTF-8 files. The file utility will not even recognize UCS-2 files without a BOM, but many other utilities will refuse to read files with a BOM and the IANA standards for UTF-16LE and UTF-16BE prohibit it. We have too often been reduced to looking at the file with the command-line utility od or a hex editor to work out its encoding. 1.2 Export to text files ExportingresultsfromRisusuallyalesscontentioustask,buttherearestillanumberofpitfalls. There will be a target application in mind, and normally a text file will be the most convenient interchange vehicle. (If a binary file is required, see Chapter 5 [Binary files], page 20.) Function cat underlies the functions for exporting data. It takes a file argument, and the append argument allows a text file to be written via successive calls to cat. Better, especially if this is to be done many times, is to open a file connection for writing or appending, and cat to that connection, then close it. The most common task is to write a matrix or data frame to file as a rectangular grid of numbers, possibly with row and column labels. This can be done by the functions write.table and write. Function write just writes out a matrix or vector in a specified number of columns (and transposes a matrix). Function write.table is more convenient, and writes out a data frame (or an object that can be coerced to a data frame) with row and column labels. There are a number of issues that need to be considered in writing out a data frame to a text file. 1. Precision Mostoftheconversionsofreal/complexnumbersdonebythesefunctionsistofullprecision, but those by write are governed by the current setting of options(digits). For more control, use format on a data frame, possibly column-by-column. 2 the distinction is subtle, http://en.wikipedia.org/wiki/UTF-16/UCS-2, and the use of surrogate pairs is very rare. Chapter 1: Introduction 4 2. Header line R prefers the header line to have no entry for the row names, so the file looks like dist climb time Greenmantle 2.5 650 16.083 ... Some other systems require a (possibly empty) entry for the row names, which is what write.tablewillprovideifargumentcol.names = NAisspecified. Excelisonesuchsystem. 3. Separator A common field separator to use in the file is a comma, as that is unlikely to appear in any of the fields in English-speaking countries. Such files are known as CSV (comma separated values)files,andwrapperfunctionwrite.csvprovidesappropriatedefaults. Insomelocales the comma is used as the decimal point (set this in write.table by dec = ",") and there CSV files use the semicolon as the field separator: use write.csv2 for appropriate defaults. There is an IETF standard for CSV files (which mandates commas and CRLF line endings, for which use eol = "\r\n"), RFC4180 (see http://tools.ietf.org/html/rfc4180), but whatismoreimportantinpracticeisthatthefileisreadablebytheapplicationitistargeted at. Using a semicolon or tab (sep = "\t") are probably the safest options. 4. Missing values By default missing values are output as NA, but this may be changed by argument na. Note that NaNs are treated as NA by write.table, but not by cat nor write. 5. Quoting strings By default strings are quoted (including the row and column names). Argument quote controls quoting of character and factor variables. Some care is needed if the strings contain embedded quotes. Three useful forms are > df <- data.frame(a = I("a \" quote")) > write.table(df) "a" "1" "a \" quote" > write.table(df, qmethod = "double") "a" "1" "a "" quote" > write.table(df, quote = FALSE, sep = ",") a 1,a " quote The second is the form of escape commonly used by spreadsheets. 6. Encodings Text files do not contain metadata on their encodings, so for non-ASCII data the file needs to be targetted to the application intended to read it. All of these functions can write to a connection which allows an encoding to be specified for the file, and as from R 2.13.0 write.table has a fileEncoding argument to make this easier. The hard part is to know what file encoding to use. For use on Windows, it is best to use what Windows calls ‘Unicode’3, that is "UTF-16LE". Using UTF-8 is a good way to make portable files that will not easily be confused with any other encoding, but even Mac OS X applications (where UTF-8 is the system encoding) may not recognize them, and Windows applications are most unlikely to. Apparently Excel:mac 2004/8 expects .csv files in "macroman" encoding (the encoding used in much earlier versions of Mac OS). 3 Even then, Windows applications may expect a Byte Order Mark which the implementation of iconv used by R may or may not add depending on the platform. Chapter 1: Introduction 5 Function write.matrix in package MASS provides a specialized interface for writing matri- ces, with the option of writing them in blocks and thereby reducing memory usage. It is possible to use sink to divert the standard R output to a file, and thereby capture the output of (possibly implicit) print statements. This is not usually the most efficient route, and the options(width) setting may need to be increased. Functionwrite.foreigninpackageforeignuseswrite.tabletoproduceatextfileandalso writes a code file that will read this text file into another statistical package. There is currently support for export to SAS, SPSS and Stata. 1.3 XML When reading data from text files, it is the responsibility of the user to know and to specify the conventions used to create that file, e.g. the comment character, whether a header line is present, the value separator, the representation for missing values (and so on) described in Section 1.2 [Export to text files], page 3. A markup language which can be used to describe not only content but also the structure of the content can make a file self-describing, so that one need not provide these details to the software reading the data. The eXtensible Markup Language – more commonly known simply as XML – can be used to provide such structure, not only for standard datasets but also more complex data structures. XML is becoming extremely popular and is emerging as a standard for general data markup and exchange. It is being used by different communities to describe geographical data such as maps, graphical displays, mathematics and so on. XML provides a way to specify the file’s encoding, e.g. <?xml version="1.0" encoding="UTF-8"?> although it does not require it. The XML package provides general facilities for reading and writing XML documents within R. A description of the facilities of the XML package is outside the scope of this document: see thepackage’sWebpageathttp://www.omegahat.org/RSXMLfordetailsandexamples. Package StatDataML on CRAN is one example building on XML. NB: XML is available for Windows, normally from the ‘CRAN extras’ repository (which is selected by default on Windows). Chapter 2: Spreadsheet-like data 6 2 Spreadsheet-like data In Section 1.2 [Export to text files], page 3 we saw a number of variations on the format of a spreadsheet-like text file, in which the data are presented in a rectangular grid, possibly with row and column labels. In this section we consider importing such files into R. 2.1 Variations on read.table The function read.table is the most convenient way to read in a rectangular grid of data. Because of the many possibilities, there are several other functions that call read.table but change a group of default arguments. Beware that read.table is an inefficient way to read in very large numerical matrices: see scan below. Some of the issues to consider are: 1. Encoding If the file contains non-ASCII character fields, ensure that it is read in the correct encoding. This is mainly an issue for reading Latin-1 files in a UTF-8 locale, which can be done by something like read.table("file.dat", fileEncoding="latin1") Note that this will work in any locale which can represent Latin-1 strings, but not many Greek/Russian/Chinese/Japanese ... locales. 2. Header line We recommend that you specify the header argument explicitly, Conventionally the header line has entries only for the columns and not for the row labels, so is one field shorter than the remaining lines. (If R sees this, it sets header = TRUE.) If presented with a file that has a (possibly empty) header field for the row labels, read it in by something like read.table("file.dat", header = TRUE, row.names = 1) Columnnamescanbegivenexplicitlyviathecol.names; explicitnamesoverridetheheader line (if present). 3. Separator Normally looking at the file will determine the field separator to be used, but with white- space separated files there may be a choice between the default sep = "" which uses any white space (spaces, tabs or newlines) as a separator, sep = " " and sep = "\t". Note that the choice of separator affects the input of quoted strings. If you have a tab-delimited file containing empty fields be sure to use sep = "\t". 4. Quoting By default character strings can be quoted by either ‘"’ or ‘’’, and in each case all the characters up to a matching quote are taken as part of the character string. The set of valid quoting characters (which might be none) is controlled by the quote argument. For sep = "\n" the default is changed to quote = "". If no separator character is specified, quotes can be escaped within quoted strings by im- mediately preceding them by ‘\’, C-style. Ifaseparatorcharacterisspecified,quotescanbeescapedwithinquotedstringsbydoubling them as is conventional in spreadsheets. For example ’One string isn’’t two’,"one more" can be read by read.table("testfile", sep = ",") This does not work with the default separator.