How to Read Xlsx File in Rstudio
Overview
The readxl package makes it like shooting fish in a barrel to get data out of Excel and into R. Compared to many of the existing packages (east.g. gdata, xlsx, xlsReadWrite) readxl has no external dependencies, and then information technology's easy to install and use on all operating systems. It is designed to work with tabular information.
readxl supports both the legacy .xls format and the mod xml-based .xlsx format. The libxls C library is used to back up .xls, which abstracts away many of the complexities of the underlying binary format. To parse .xlsx, we utilise the RapidXML C++ library.
Installation
The easiest way to install the latest released version from CRAN is to install the whole tidyverse.
NOTE: yous volition still demand to load readxl explicitly, because it is not a core tidyverse parcel 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 as well covers like functionality in the related packages readr and googlesheets4.
Usage
readxl includes several example files, which we utilize throughout the documentation. Apply the helper readxl_example() with no arguments to listing them or call information technology with an example filename to become the path.
readxl_example ( ) #> [one] "clippy.xls" "clippy.xlsx" "datasets.xls" "datasets.xlsx" #> [5] "deaths.xls" "deaths.xlsx" "geometry.xls" "geometry.xlsx" #> [ix] "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.1 iii.5 ane.4 0.2 setosa #> 2 4.9 three 1.4 0.2 setosa #> 3 4.7 iii.2 1.iii 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 5.1 3.5 one.4 0.2 setosa #> 2 4.nine iii one.4 0.2 setosa #> iii 4.7 3.ii 1.3 0.2 setosa #> # … with 147 more rows List the canvas names with excel_sheets().
excel_sheets ( xlsx_example ) #> [ane] "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> #> 1 179 horsebean #> 2 160 horsebean #> iii 136 horsebean #> # … with 68 more rows read_excel ( xls_example, canvass = 4 ) #> # A tibble: 1,000 × 5 #> lat long depth mag stations #> <dbl> <dbl> <dbl> <dbl> <dbl> #> 1 -20.4 182. 562 4.8 41 #> 2 -20.six 181. 650 4.two fifteen #> three -26 184. 42 5.iv 43 #> # … with 997 more than rows There are various ways to control which cells are read. You tin can even specify the sail here, if providing an Excel-style cell range.
read_excel ( xlsx_example, n_max = 3 ) #> # A tibble: 3 × 5 #> Sepal.Length Sepal.Width Petal.Length Petal.Width Species #> <dbl> <dbl> <dbl> <dbl> <chr> #> 1 5.1 3.5 1.4 0.2 setosa #> 2 4.9 3 1.4 0.2 setosa #> iii 4.7 three.ii 1.3 0.ii setosa read_excel ( xlsx_example, range = "C1:E4" ) #> # A tibble: iii × 3 #> Petal.Length Petal.Width Species #> <dbl> <dbl> <chr> #> one i.four 0.2 setosa #> 2 1.four 0.ii setosa #> iii i.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 five.1 3.five 1.iv 0.two setosa #> 2 iv.9 3 1.four 0.ii setosa #> 3 four.seven three.2 1.three 0.2 setosa read_excel ( xlsx_example, range = cell_cols ( "B:D" ) ) #> # A tibble: 150 × 3 #> Sepal.Width Petal.Length Petal.Width #> <dbl> <dbl> <dbl> #> 1 3.5 i.iv 0.2 #> 2 three 1.4 0.2 #> iii 3.two 1.3 0.2 #> # … with 147 more than rows read_excel ( xlsx_example, range = "mtcars!B1:D5" ) #> # A tibble: four × iii #> cyl disp hp #> <dbl> <dbl> <dbl> #> 1 6 160 110 #> 2 6 160 110 #> iii 4 108 93 #> # … with one more row If NAs are represented by something other than blank cells, set the na argument.
read_excel ( xlsx_example, na = "setosa" ) #> # A tibble: 150 × five #> Sepal.Length Sepal.Width Petal.Length Petal.Width Species #> <dbl> <dbl> <dbl> <dbl> <chr> #> 1 five.i iii.5 1.4 0.2 <NA> #> 2 4.9 3 1.4 0.ii <NA> #> 3 4.vii 3.2 one.3 0.2 <NA> #> # … with 147 more than 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 volition become increasingly consistent with other packages, such as readr.
Features
-
No external dependency on, e.g., Coffee or Perl.
-
Re-encodes non-ASCII characters to UTF-8.
-
Loads datetimes into POSIXct columns. Both Windows (1900) and Mac
- appointment specifications are processed correctly.
-
Discovers the minimal data rectangle and returns that, by default. User can exert more than control with
range,skip, andn_max. -
Cavalcade names and types are determined from the data in the canvas, by default. User can also supply via
col_namesandcol_typesand control proper noun repair via.name_repair. -
Returns a tibble, i.due east. a data frame with an additional
tbl_dfclass. Among other things, this provide nicer press.
Other relevant packages
Here are another packages with functionality that is complementary to readxl and that as well avoid a Coffee dependency.
Writing Excel files: The case 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 option in this space, beginning released on CRAN in August 2017. It'south a portable and lightweight way to consign a data frame to xlsx, based on libxlsxwriter. It is much more minimalistic than openxlsx, but on elementary examples, appears to be about twice as fast and to write smaller files.
Not-tabular data and formatting: tidyxl is focused on importing awkward and non-tabular information from Excel. It also "exposes cell content, position and formatting in a tidy structure for farther manipulation".
Please annotation that the readxl project is released with a Contributor Code of Conduct. By contributing to this project, you lot agree to abide past its terms.
Source: https://readxl.tidyverse.org/
0 Response to "How to Read Xlsx File in Rstudio"
Post a Comment