How to Read in a Excel File in R
Overview
The readxl package makes it easy to go data out of Excel and into R. Compared to many of the existing packages (e.g. gdata, xlsx, xlsReadWrite) readxl has no external dependencies, and so information technology's like shooting fish in a barrel to install and use on all operating systems. It is designed to work with tabular data.
readxl supports both the legacy .xls format and the mod 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.
Annotation: you will nevertheless demand to load readxl explicitly, because information technology is non a cadre tidyverse package loaded via library(tidyverse).
Alternatively, install just readxl from CRAN:
Or install the development version from GitHub:
# install.packages("devtools") devtools :: install_github ( "tidyverse/readxl" ) Cheatsheet
You can run into how to read data with readxl in the data import cheatsheet, which also covers like functionality in the related packages readr and googlesheets4.
Usage
readxl includes several case files, which we use throughout the documentation. Use the helper readxl_example() with no arguments to listing them or phone call it with an example filename to get the path.
readxl_example ( ) #> [1] "clippy.xls" "clippy.xlsx" "datasets.xls" "datasets.xlsx" #> [5] "deaths.xls" "deaths.xlsx" "geometry.xls" "geometry.xlsx" #> [9] "type-me.xls" "type-me.xlsx" readxl_example ( "clippy.xls" ) #> [i] "/Users/jenny/resources/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.i iii.five 1.4 0.2 setosa #> ii iv.nine 3 i.four 0.2 setosa #> 3 iv.7 three.two 1.3 0.2 setosa #> # … with 147 more than 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 3.5 1.4 0.2 setosa #> ii 4.9 iii ane.4 0.2 setosa #> 3 4.7 iii.two 1.3 0.2 setosa #> # … with 147 more rows List the canvass names with excel_sheets().
excel_sheets ( xlsx_example ) #> [1] "iris" "mtcars" "chickwts" "quakes" Specify a worksheet by proper noun or number.
read_excel ( xlsx_example, sheet = "chickwts" ) #> # A tibble: 71 × 2 #> weight feed #> <dbl> <chr> #> ane 179 horsebean #> two 160 horsebean #> three 136 horsebean #> # … with 68 more rows read_excel ( xls_example, sheet = iv ) #> # A tibble: one,000 × v #> lat long depth mag stations #> <dbl> <dbl> <dbl> <dbl> <dbl> #> one -20.4 182. 562 four.8 41 #> 2 -20.6 181. 650 4.2 15 #> iii -26 184. 42 five.iv 43 #> # … with 997 more rows There are diverse ways to control which cells are read. Y'all can fifty-fifty specify the sail here, if providing an Excel-manner cell range.
read_excel ( xlsx_example, n_max = 3 ) #> # A tibble: iii × five #> Sepal.Length Sepal.Width Petal.Length Petal.Width Species #> <dbl> <dbl> <dbl> <dbl> <chr> #> 1 five.ane 3.5 1.4 0.ii setosa #> 2 four.9 3 1.4 0.2 setosa #> 3 4.7 3.ii i.3 0.2 setosa read_excel ( xlsx_example, range = "C1:E4" ) #> # A tibble: 3 × three #> Petal.Length Petal.Width Species #> <dbl> <dbl> <chr> #> 1 ane.4 0.2 setosa #> 2 i.4 0.2 setosa #> 3 i.iii 0.2 setosa read_excel ( xlsx_example, range = cell_rows ( i : 4 ) ) #> # A tibble: 3 × 5 #> Sepal.Length Sepal.Width Petal.Length Petal.Width Species #> <dbl> <dbl> <dbl> <dbl> <chr> #> 1 5.1 iii.5 i.4 0.2 setosa #> 2 4.9 3 1.4 0.2 setosa #> 3 iv.7 three.2 1.three 0.ii setosa read_excel ( xlsx_example, range = cell_cols ( "B:D" ) ) #> # A tibble: 150 × three #> Sepal.Width Petal.Length Petal.Width #> <dbl> <dbl> <dbl> #> 1 3.5 ane.4 0.2 #> 2 3 one.4 0.2 #> 3 3.2 1.3 0.2 #> # … with 147 more rows read_excel ( xlsx_example, range = "mtcars!B1:D5" ) #> # A tibble: 4 × 3 #> cyl disp hp #> <dbl> <dbl> <dbl> #> i 6 160 110 #> 2 half dozen 160 110 #> iii iv 108 93 #> # … with 1 more than row If NAs are represented by something other than blank cells, set the na argument.
read_excel ( xlsx_example, na = "setosa" ) #> # A tibble: 150 × v #> Sepal.Length Sepal.Width Petal.Length Petal.Width Species #> <dbl> <dbl> <dbl> <dbl> <chr> #> 1 5.1 three.five 1.iv 0.2 <NA> #> two 4.9 three 1.4 0.2 <NA> #> 3 four.7 3.2 1.three 0.2 <NA> #> # … with 147 more rows If you are new to the tidyverse conventions for data import, yous 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, east.yard., Coffee or Perl.
-
Re-encodes not-ASCII characters to UTF-8.
-
Loads datetimes into POSIXct columns. Both Windows (1900) and Mac
- date specifications are processed correctly.
-
Discovers the minimal information rectangle and returns that, past default. User tin can exert more control with
range,skip, andn_max. -
Column names and types are adamant from the information in the sheet, by default. User can likewise supply via
col_namesandcol_typesand control proper noun repair via.name_repair. -
Returns a tibble, i.east. a data frame with an boosted
tbl_dfcourse. Among other things, this provide nicer press.
Other relevant packages
Here are some other packages with functionality that is complementary to readxl and that too avert a Java dependency.
Writing Excel files: The example files datasets.xlsx and datasets.xls were created with the help of openxlsx (and Excel). openxlsx provides "a high level interface to writing, styling and editing worksheets".
50 <- list (iris = iris, mtcars = mtcars, chickwts = chickwts, quakes = quakes ) openxlsx :: write.xlsx ( l, file = "inst/extdata/datasets.xlsx" ) writexl is a new selection in this infinite, first released on CRAN in August 2017. It'south a portable and lightweight manner to export a information frame to xlsx, based on libxlsxwriter. It is much more than minimalistic than openxlsx, but on elementary examples, appears to exist about twice every bit fast and to write smaller files.
Not-tabular data and formatting: tidyxl is focused on importing awkward and non-tabular data from Excel. Information technology too "exposes prison cell content, position and formatting in a tidy structure for further manipulation".
Please annotation that the readxl project is released with a Contributor Code of Conduct. Past contributing to this projection, y'all agree to abide by its terms.
petersonhoust1940.blogspot.com
Source: https://readxl.tidyverse.org/
Post a Comment for "How to Read in a Excel File in R"