How to Read a Xls File in R

Read xlsx & xls Excel File in R (half dozen Examples)

This tutorial explains how to read Excel files in R. More than precisely, I will show y'all how to:

  • Read an xlsx File with the read.xlsx Function of the xlsx Package (Case 1)
  • Read an xlsx File with the read_excel Office of the readxl Package (Example 2)
  • Read an xlsx File with the read.xlsx Function of the openxlsx Package (Example 3)
  • Read a Specific Canvas of an Excel File (Example four)
  • Read Multiple Excel Files (Instance 5)
  • Read an xls File (Example six)

Then without further ado, let'due south move on to the examples!

Case 1: Read xlsx File with read.xlsx Role (xlsx Bundle)

Before nosotros can start with the examples, we need to create some example data and store information technology as xlsx Excel file on our computer.

The following R lawmaking loads the iris data set to RStudio:

data(                "iris"                )                # Load iris data to RStudio                head(iris)                # Print start 6 rows of iris              

data("iris") # Load iris data to RStudio caput(iris) # Impress first 6 rows of iris

In club to write this data as xlsx file to our estimator, nosotros need to install and load the xlsx R package:

install.                packages                (                "xlsx"                )                # Install xlsx R bundle                library(                "xlsx"                )                # Load xlsx R bundle to RStudio              

install.packages("xlsx") # Install xlsx R package library("xlsx") # Load xlsx R package to RStudio

The xlsx package includes the write.xlsx R function, which allows us to write Excel files to a working directory on our computer as follows:

xlsx::                write                .                xlsx                (iris,                # Write example Excel xlsx file                "C:/ ... Your Path ... /iris.xlsx",            row.                names                =                Faux                )              

xlsx::write.xlsx(iris, # Write example Excel xlsx file "C:/ ... Your Path ... /iris.xlsx", row.names = Simulated)

Afterward running the previous R syntax, you lot should find an xlsx file at the path that you take specified inside the write.xlsx function. When yous open the xlsx table it should wait equally follows:

Excel File Exported by R

Figure 1: Iris Information Set Exported every bit xlsx Excel File.

Finally, we are fix to read an xlsx Excel file to R!

The xlsx package, which we accept simply used to write an xlsx file to our PC, as well provides the read.xlsx R function. We can use the function to load our Excel file to R every bit follows:

data1                <-                xlsx::                read                .                xlsx                (                "C:/ ... Your Path ... /iris.xlsx",                # Read xlsx file with read.xlsx                sheetIndex                =                1                )              

data1 <- xlsx::read.xlsx("C:/ ... Your Path ... /iris.xlsx", # Read xlsx file with read.xlsx sheetIndex = 1)

Call the data object data1 in your RStudio. You lot will see that it contains the iris information set that nosotros have exported above.

In my personal opinion, the xlsx bundle is the bundle to go if you have to deal with xlsx files in R. However, there are several alternatives bachelor and depending on your specific situation, you might prefer one of the other solutions.

In Examples ii and 3 I'm going to explain two alternative packages for importing xlsx files (Tidyverse included). Go along on reading!

Example two: Read xlsx File with read_excel Role (readxl Packet)

The most pop alternative to the xlsx package (shown in Example 1) is the readxl package. The readxl bundle is office of the Tidyverse and therefore highly compatible with Tidyverse'due south family of R packages (e.thou. ggplot2 or dplyr).

First, we demand to install and load the readxl bundle to R:

install.                packages                (                "readxl"                )                # Install readxl R parcel                library(                "readxl"                )                # Load readxl R packet to RStudio              

install.packages("readxl") # Install readxl R package library("readxl") # Load readxl R package to RStudio

Now, nosotros can use the read_excel function to load our example xlsx tabular array into R:

data2                <-                readxl::                read_excel                (                "C:/ ... Your Path ... /iris.xlsx"                )                # Read xlsx file with read_excel              

data2 <- readxl::read_excel("C:/ ... Your Path ... /iris.xlsx") # Read xlsx file with read_excel

If you print the data object data2 to your RStudio console, yous volition see the following output:

DPlyr tibble

Figure two: Screenshot of Tibble in R.

As you can run into, the read_excel command returns a tibble instead of a information frame (as read.xlsx did in Instance i).

Tibbles are the typical data representation of the Tidyverse and at that place accept been hundreds of discussions whether tibbles are preferable to data frames.

Withal, in instance y'all are working a lot with the dplyr or other packages of the Tidyverse environment, you might prefer read_excel instead of read.xlsx. In the cease, it's a matter of taste!

Example 3: Read xlsx File with read.xlsx Function (openxlsx Package)

I'1000 showing you the 3rd example due to two reasons:

  1. The openxlsx packet is as well fairly pop among R users.
  2. The openxlsx and the xlsx package oftentimes atomic number 82 to confusion, since both packages contain a function that is called read.xlsx.

Let's have a look at the R syntax!

First, we demand to install and load the openxlsx package in R…

install.                packages                (                "openxlsx"                )                # Install openxlsx R parcel                library(                "openxlsx"                )                # Load openxlsx R package to R              

install.packages("openxlsx") # Install openxlsx R parcel library("openxlsx") # Load openxlsx R packet to R

…so we can apply the read.xlsx role equally follows:

data3                <-                openxlsx::                read                .                xlsx                (                "C:/ ... Your Path ... /iris.xlsx"                )                # Read with read.xlsx of openxlsx              

data3 <- openxlsx::read.xlsx("C:/ ... Your Path ... /iris.xlsx") # Read with read.xlsx of openxlsx

Note: I had to specify explicitly that I want to use the openxlsx package (by typing openxlsx::) instead of the xlsx package, since both packages contain a read.xlsx function.

The basic R output of both functions is the aforementioned. Nonetheless, openxlsx and xlsx provide unlike boosted specifications for the read.xlsx functions.

There are even more packages and functions that enable the import of Excel files to R (east.k. gdata, XLConnect, RODBC, xlsReadWrite [not on CRAN anymore], or the read.table role which is available in base R).

However, I think we covered the nigh important ones and for that reason I'm now going to move on to some more complex situations.

Permit'due south dive in!

Example 4: Read Specific Sheet of Excel File in R

Excel files oft comprise several worksheets in i file. This can lead to issues, since each data matrix is usually stored in a unmarried data object in R.

Fortunately, we are able to load only one specific worksheet from an Excel file – And that's what I'm going to show y'all in this instance.

Let's first extent our previously created instance file (i.e. the iris data ready) with some other sheet. The post-obit R code loads the mtcars data prepare to R:

data(                "mtcars"                )                # Load mtcars data to RStudio                head(mtcars)                # Print first six rows of mtcars              

data("mtcars") # Load mtcars data to RStudio head(mtcars) # Impress first 6 rows of mtcars

Now we tin can append this data ready to our already existing Excel file as follows:

xlsx::                write                .                xlsx                (mtcars,                # Write 2nd canvass to Excel file                "C:/ ... Your Path ... /iris.xlsx",                  row.                names                =                FALSE,                  append                =                TRUE,                # Specify that data gets appended                sheetName                =                "mtcars"                )              

xlsx::write.xlsx(mtcars, # Write second sheet to Excel file "C:/ ... Your Path ... /iris.xlsx", row.names = FALSE, append = TRUE, # Specify that data gets appended sheetName = "mtcars")

If you take a look at our Excel file, y'all will run across that the file at present contains a 2nd canvas:

Excel File with 2 Sheets

Figure iii: Excel File with 2 Sheets.

Now let's assume that nosotros only want to import the second sheet (i.east. the mtcars data frame) into R. And so nosotros can merely specify that with the sheetName selection of the read.xlsx function of the xlsx package:

data_sh2                <-                xlsx::                read                .                xlsx                (                "C:/ ... Your Path ... /iris.xlsx",                # Read second sheet of Excel file                sheetName                =                "mtcars"                )              

data_sh2 <- xlsx::read.xlsx("C:/ ... Your Path ... /iris.xlsx", # Read 2d sheet of Excel file sheetName = "mtcars")

If you now have a look at data_sh2, y'all will se that it contains the mtcars information gear up.

Example 5: Import Multiple Excel Files to R

Reading every Excel file on your computer one by ane might exist time consuming, depending on the corporeality of Excel files y'all want to import. But no worries, R allows to load multiple Excel files at the aforementioned time.

Starting time, let's create a second Excel file in our currently used working directory:

xlsx::                write                .                xlsx                (mtcars,                # Write mtcars as separate Excel file                "C:/ ... Your Path ... /mtcars.xlsx",                  row.                names                =                Faux                )              

xlsx::write.xlsx(mtcars, # Write mtcars every bit separate Excel file "C:/ ... Your Path ... /mtcars.xlsx", row.names = FALSE)

If we want to read both our example files (i.e. iris.xlsx and mtcars.xlsx), we get-go demand to extract the names of all files. Of course nosotros could do that manually, but using the listing.files function as in the following R code is much quicker:

all_file_names                <-                listing.                files                (pattern                =                "*.xlsx"                )                # Grab list of all xlsx files                all_file_names                # Print list of xlsx files                # "iris.xlsx"   "mtcars.xlsx"              

all_file_names <- list.files(design = "*.xlsx") # Grab listing of all xlsx files all_file_names # Print list of xlsx files # "iris.xlsx" "mtcars.xlsx"

The vector all_file_names includes all xlsx file names as character string.

Now we can combine lapply and the read_excel function of the readxl package to store the content of all Excel files in a listing:

data_list                <-                lapply(all_file_names, read_excel)                # Read multiple excel files                data_list                # Print list of information.frames              

data_list <- lapply(all_file_names, read_excel) # Read multiple excel files data_list # Print list of data.frames

Note that all data were stored every bit tibbles, since we are using the readxl package:

List of Data Frames

Figure 4: Importing Multiple Excel Files to R.

Example 6: Read xls File in R

Onetime simply aureate?! To be honest, I'chiliad not really using the xls file format anymore.

However, if you have some xls files that yous need to read into R, yous might exist request yourself how you could do this. For that reason, I'thou going to prove you in this case how to read an xls file to R.

Showtime; let'due south create an example xls file on our PC. Similarly as in the previous examples, we tin can use the write.xlsx function of the xlsx package. We just need to modify the file extension from xlsx to xls:

xlsx::                write                .                xlsx                (iris,                # Write xls Excel file                "C:/ ... Your Path ... /iris.xls",                  row.                names                =                FALSE                )              

xlsx::write.xlsx(iris, # Write xls Excel file "C:/ ... Your Path ... /iris.xls", row.names = Imitation)

Now we can load this xls file to R as follows:

data_xls                <-                xlsx::                read                .                xlsx                (                "C:/ ... Your Path ... /iris.xls",                # Read xls Excel file                sheetIndex                =                1                )              

data_xls <- xlsx::read.xlsx("C:/ ... Your Path ... /iris.xls", # Read xls Excel file sheetIndex = 1)

Exactly the same as in Instance 1, just with xls instead of xlsx.

Further Resources for the Handling of Excel Files

At this betoken, I accept shown you basically all I know about reading Excel tables into R. Withal, it wouldn't be the R programming language if there would non be more interesting stuff to acquire.

If you want to deepen your knowledge about the handling of Excel files from within R, I tin can recommend the following YouTube video of the MarinStatsLectures – R Programming & Statistics aqueduct. The video explains how to deal with Excel files in R in a more than general manner, also including csv and txt files.

Furthermore, you might also want to have a look at the other R tutorials of this website. I am publishing new articles on a regular basis:

  • How to Consign Data from R to Excel
  • Salvage & Load RData Workspace Files
  • List of Useful R Functions
  • The R Programming Language

I hope I was able to teach y'all how to read Excel files into R. However, in case you accept whatever farther questions, don't hesitate to let me know in the comments!

robertsvengland.blogspot.com

Source: https://statisticsglobe.com/r-read-excel-file-xlsx-xls

0 Response to "How to Read a Xls File in R"

Postar um comentário

Iklan Atas Artikel

Iklan Tengah Artikel 1

Iklan Tengah Artikel 2

Iklan Bawah Artikel