How to Read Different Tabs in Excel Using Readxl
Overview
The readxl package makes it easy to get data out of Excel and into R. Compared to many of the existing packages (due east.1000. gdata, xlsx, xlsReadWrite) readxl has no external dependencies, so it'south piece of cake to install and use on all operating systems. Information technology 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 abroad many of the complexities of the underlying binary format. To parse .xlsx, nosotros 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 still need to load readxl explicitly, because it is not 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 lot tin can see how to read data with readxl in the data import cheatsheet, which also covers similar functionality in the related packages readr and googlesheets4.
Usage
readxl includes several instance files, which nosotros use throughout the documentation. Use the helper readxl_example() with no arguments to listing them or phone call it with an case filename to become the path.
readxl_example ( ) #> [1] "clippy.xls" "clippy.xlsx" "datasets.xls" "datasets.xlsx" #> [5] "deaths.xls" "deaths.xlsx" "geometry.xls" "geometry.xlsx" #> [9] "blazon-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> #> ane v.1 3.five 1.four 0.2 setosa #> ii 4.9 3 1.4 0.2 setosa #> 3 4.7 3.2 1.3 0.two setosa #> # … with 147 more than rows xls_example <- readxl_example ( "datasets.xls" ) read_excel ( xls_example ) #> # A tibble: 150 × 5 #> Sepal.Length Sepal.Width Petal.Length Petal.Width Species #> <dbl> <dbl> <dbl> <dbl> <chr> #> 1 5.i three.v 1.4 0.two setosa #> 2 four.9 3 1.4 0.two setosa #> 3 4.seven iii.2 one.iii 0.2 setosa #> # … with 147 more rows Listing the canvass names with excel_sheets().
excel_sheets ( xlsx_example ) #> [ane] "iris" "mtcars" "chickwts" "quakes" Specify a worksheet by name or number.
read_excel ( xlsx_example, canvas = "chickwts" ) #> # A tibble: 71 × 2 #> weight feed #> <dbl> <chr> #> i 179 horsebean #> 2 160 horsebean #> 3 136 horsebean #> # … with 68 more rows read_excel ( xls_example, sheet = 4 ) #> # A tibble: 1,000 × 5 #> lat long depth mag stations #> <dbl> <dbl> <dbl> <dbl> <dbl> #> ane -20.iv 182. 562 4.8 41 #> 2 -20.vi 181. 650 four.2 15 #> 3 -26 184. 42 v.4 43 #> # … with 997 more rows There are diverse ways to control which cells are read. You lot tin can even specify the sail here, if providing an Excel-style prison cell range.
read_excel ( xlsx_example, n_max = 3 ) #> # A tibble: three × 5 #> Sepal.Length Sepal.Width Petal.Length Petal.Width Species #> <dbl> <dbl> <dbl> <dbl> <chr> #> 1 5.1 three.five 1.4 0.2 setosa #> 2 4.9 3 1.4 0.2 setosa #> 3 4.7 3.2 1.iii 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 #> 2 ane.four 0.2 setosa #> 3 i.3 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 3.5 one.4 0.2 setosa #> 2 four.ix 3 i.4 0.ii setosa #> 3 4.vii 3.ii 1.3 0.two setosa read_excel ( xlsx_example, range = cell_cols ( "B:D" ) ) #> # A tibble: 150 × iii #> Sepal.Width Petal.Length Petal.Width #> <dbl> <dbl> <dbl> #> 1 iii.5 1.iv 0.two #> two 3 ane.iv 0.2 #> 3 3.2 one.3 0.2 #> # … with 147 more rows read_excel ( xlsx_example, range = "mtcars!B1:D5" ) #> # A tibble: 4 × iii #> cyl disp hp #> <dbl> <dbl> <dbl> #> 1 half-dozen 160 110 #> 2 6 160 110 #> iii four 108 93 #> # … with ane more than row If NAdue south are represented past something other than blank cells, set the na statement.
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 5.1 3.5 1.iv 0.2 <NA> #> 2 4.9 three one.4 0.two <NA> #> 3 4.vii 3.2 i.iii 0.2 <NA> #> # … with 147 more rows If you are new to the tidyverse conventions for data import, you 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.g., 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, past default. User tin can exert more control with
range,skip, andn_max. -
Column names and types are determined from the data in the sheet, by default. User can also supply via
col_namesandcol_typesand control name repair via.name_repair. -
Returns a tibble, i.eastward. a data frame with an boosted
tbl_dfclass. Amidst other things, this provide nicer printing.
Other relevant packages
Here are some other packages with functionality that is complementary to readxl and that likewise avoid a Coffee dependency.
Writing Excel files: The case files datasets.xlsx and datasets.xls were created with the assist 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 ( 50, file = "inst/extdata/datasets.xlsx" ) writexl is a new option in this space, first released on CRAN in August 2017. It'south a portable and lightweight way to export a data frame to xlsx, based on libxlsxwriter. It is much more than minimalistic than openxlsx, but on simple examples, appears to be well-nigh twice as fast and to write smaller files.
Non-tabular data and formatting: tidyxl is focused on importing bad-mannered and non-tabular data from Excel. Information technology also "exposes jail cell content, position and formatting in a tidy structure for further manipulation".
Please notation that the readxl project is released with a Correspondent Code of Conduct. Past contributing to this project, you lot hold to abide by its terms.
Source: https://readxl.tidyverse.org/
0 Response to "How to Read Different Tabs in Excel Using Readxl"
Post a Comment