Reading and Importing Excel Files Into R
Overview
The readxl package makes information technology easy to get information out of Excel and into R. Compared to many of the existing packages (e.g. gdata, xlsx, xlsReadWrite) readxl has no external dependencies, so it's easy to install and use on all operating systems. It is designed to piece of work with tabular data.
readxl supports both the legacy .xls format and the modern xml-based .xlsx format. The libxls C library is used to support .xls, which abstracts away many of the complexities of the underlying binary format. To parse .xlsx, we use the RapidXML C++ library.
Installation
The easiest way to install the latest released version from CRAN is to install the whole tidyverse.
Note: you volition even so need to load readxl explicitly, because information technology is non a core tidyverse package loaded via library(tidyverse).
Alternatively, install simply readxl from CRAN:
Or install the development version from GitHub:
# install.packages("devtools") devtools :: install_github ( "tidyverse/readxl" ) Cheatsheet
You can see how to read data with readxl in the data import cheatsheet, which likewise covers similar functionality in the related packages readr and googlesheets4.
Usage
readxl includes several example files, which we use throughout the documentation. Use the helper readxl_example() with no arguments to listing them or call information technology with an instance filename to get the path.
readxl_example ( ) #> [one] "clippy.xls" "clippy.xlsx" "datasets.xls" "datasets.xlsx" #> [five] "deaths.xls" "deaths.xlsx" "geometry.xls" "geometry.xlsx" #> [9] "type-me.xls" "type-me.xlsx" readxl_example ( "clippy.xls" ) #> [ane] "/Users/jenny/resource/R/library/readxl/extdata/clippy.xls" read_excel() reads both xls and xlsx files and detects the format from the extension.
xlsx_example <- readxl_example ( "datasets.xlsx" ) read_excel ( xlsx_example ) #> # A tibble: 150 × 5 #> Sepal.Length Sepal.Width Petal.Length Petal.Width Species #> <dbl> <dbl> <dbl> <dbl> <chr> #> 1 5.1 3.v 1.4 0.2 setosa #> 2 4.9 iii 1.4 0.2 setosa #> 3 4.7 3.2 1.three 0.2 setosa #> # … with 147 more rows xls_example <- readxl_example ( "datasets.xls" ) read_excel ( xls_example ) #> # A tibble: 150 × v #> Sepal.Length Sepal.Width Petal.Length Petal.Width Species #> <dbl> <dbl> <dbl> <dbl> <chr> #> 1 five.1 iii.v 1.iv 0.2 setosa #> ii 4.ix iii ane.4 0.two setosa #> 3 4.7 iii.2 1.3 0.2 setosa #> # … with 147 more rows List the sheet names with excel_sheets().
excel_sheets ( xlsx_example ) #> [1] "iris" "mtcars" "chickwts" "quakes" Specify a worksheet by name or number.
read_excel ( xlsx_example, sheet = "chickwts" ) #> # A tibble: 71 × 2 #> weight feed #> <dbl> <chr> #> ane 179 horsebean #> ii 160 horsebean #> 3 136 horsebean #> # … with 68 more than rows read_excel ( xls_example, sheet = 4 ) #> # A tibble: 1,000 × five #> lat long depth mag stations #> <dbl> <dbl> <dbl> <dbl> <dbl> #> i -20.4 182. 562 4.8 41 #> ii -xx.6 181. 650 four.2 fifteen #> iii -26 184. 42 5.4 43 #> # … with 997 more rows In that location are various ways to control which cells are read. You lot tin even specify the canvas hither, if providing an Excel-style prison cell range.
read_excel ( xlsx_example, n_max = 3 ) #> # A tibble: 3 × v #> Sepal.Length Sepal.Width Petal.Length Petal.Width Species #> <dbl> <dbl> <dbl> <dbl> <chr> #> ane 5.ane 3.5 ane.4 0.2 setosa #> 2 4.9 3 one.4 0.2 setosa #> iii iv.7 iii.two ane.3 0.2 setosa read_excel ( xlsx_example, range = "C1:E4" ) #> # A tibble: 3 × 3 #> Petal.Length Petal.Width Species #> <dbl> <dbl> <chr> #> 1 1.4 0.2 setosa #> two 1.4 0.2 setosa #> iii ane.3 0.2 setosa read_excel ( xlsx_example, range = cell_rows ( 1 : 4 ) ) #> # A tibble: 3 × 5 #> Sepal.Length Sepal.Width Petal.Length Petal.Width Species #> <dbl> <dbl> <dbl> <dbl> <chr> #> one 5.1 3.5 1.four 0.2 setosa #> two 4.9 3 1.4 0.2 setosa #> three 4.seven three.two 1.3 0.ii setosa read_excel ( xlsx_example, range = cell_cols ( "B:D" ) ) #> # A tibble: 150 × iii #> Sepal.Width Petal.Length Petal.Width #> <dbl> <dbl> <dbl> #> ane 3.5 1.iv 0.two #> ii 3 ane.iv 0.2 #> three 3.2 1.3 0.two #> # … with 147 more rows read_excel ( xlsx_example, range = "mtcars!B1:D5" ) #> # A tibble: 4 × iii #> cyl disp hp #> <dbl> <dbl> <dbl> #> 1 6 160 110 #> 2 half-dozen 160 110 #> 3 iv 108 93 #> # … with 1 more row If NAs are represented by something other than bare cells, set the na argument.
read_excel ( xlsx_example, na = "setosa" ) #> # A tibble: 150 × 5 #> Sepal.Length Sepal.Width Petal.Length Petal.Width Species #> <dbl> <dbl> <dbl> <dbl> <chr> #> 1 v.1 3.5 1.4 0.2 <NA> #> 2 4.ix 3 1.4 0.ii <NA> #> 3 four.7 3.2 1.iii 0.ii <NA> #> # … with 147 more rows If you are new to the tidyverse conventions for data import, you lot may want to consult the data import chapter in R for Data Science. readxl will become increasingly consistent with other packages, such as readr.
Features
-
No external dependency on, e.1000., Java or Perl.
-
Re-encodes non-ASCII characters to UTF-8.
-
Loads datetimes into POSIXct columns. Both Windows (1900) and Mac
- date specifications are processed correctly.
-
Discovers the minimal data rectangle and returns that, by default. User can exert more control with
range,skip, andn_max. -
Cavalcade names and types are determined from the data in the sheet, past default. User tin can also supply via
col_namesandcol_typesand control name repair via.name_repair. -
Returns a tibble, i.e. a data frame with an additional
tbl_dfclass. Among other things, this provide nicer printing.
Other relevant packages
Hither are some other packages with functionality that is complementary to readxl and that also avoid a Java dependency.
Writing Excel files: The case files datasets.xlsx and datasets.xls were created with the assistance of openxlsx (and Excel). openxlsx provides "a loftier level interface to writing, styling and editing worksheets".
l <- list (iris = iris, mtcars = mtcars, chickwts = chickwts, quakes = quakes ) openxlsx :: write.xlsx ( l, file = "inst/extdata/datasets.xlsx" ) writexl is a new option in this infinite, get-go released on CRAN in August 2017. It'due south a portable and lightweight style to consign a information frame to xlsx, based on libxlsxwriter. It is much more minimalistic than openxlsx, just on uncomplicated examples, appears to be about twice equally fast and to write smaller files.
Not-tabular data and formatting: tidyxl is focused on importing awkward and non-tabular information from Excel. Information technology besides "exposes cell content, position and formatting in a tidy construction for further manipulation".
Please note that the readxl projection is released with a Contributor Code of Conduct. Past contributing to this project, you concur to abide past its terms.
Source: https://readxl.tidyverse.org/
Belum ada Komentar untuk "Reading and Importing Excel Files Into R"
Posting Komentar