From the previous blogpost , we had learnt how to Import TSV or CSV files into R using the 3 base packages available with R by default . [ read.table() , read.csv() , read.delim() ]

Click here for the previous blogpost on importing TSV & CSV files into R.

Now , lets understand the readxl package more and its features.

What is readxl Package

The readxl package is a package ( not available by default in R environment & has to be installed from CRAN location ) which makes it very easy to read/import data from excel into R and this package has no external dependencies.

readxl package supports importing of both the excel formats (  legacy .xls format & the modern XML based .xlsx format )..

How to use readxl package for Importing Excel Files

Step 1 : Installing the Package 

As we know that we have to always first Install a package which is not already available in our R environment.

In this case , readxl package can be installed by 2 ways :

a) Install the Package Tidyverse ( which will install readxl package as well ) 

> install.packages('tidyverse' , dependencies = T)


b) Install the Package readxl only 

> install.packages('readxl' , dependencies = T)

Step 2 : Loading & Attaching the Package

After Installing a Package , the next immediate step is to Load the Package into R  and also attach the package to the Search path of R 

> require(readxl)
Loading required package: readxl

Note - even if you had Installed the tidyverse package instead of readxl , for loading the readxl in R , you have to run the load command for readxl only ( not tidyverse )

Step 3 : Getting the Excel files to be used in the examples below 

The readxl package while installing also saves some excel files ( .xls & .xlsx ) in the folder where the Package gets saved in your system.

These excel files can be used for practice .

To know the list of files saved by the Installation process of readxl -

> readxl_example()
 [1] "clippy.xls"    "clippy.xlsx"   "datasets.xls" 
 [4] "datasets.xlsx" "deaths.xls"    "deaths.xlsx"  
 [7] "geometry.xls"  "geometry.xlsx" "type-me.xls"  
[10] "type-me.xlsx" 

To know the folder path in your system where these excel files are saved  -

> readxl_example("clippy.xls")
[1] "D:/Users/XYZ/Documents/R/R-3.5.1/library/readxl/extdata/clippy.xls"

To use these excel files , either change the working directory by using setwd() to the folder path where these excels are saved by readxl installation process or copy the excel files from that folder path to your working directory ( to know your current working directory , run the command getwd() )

Step 4 : Reading the excel files 

read_excel() reads both types of excel files ( .xls , .xlsx )  & it automatically detects the format from the extension of the file.

Example : Reading .xls file using read_excel()
> Clippy_DF <- read_excel("clippy.xls")

Example : Reading .xlsx file using read_excel()
> Clippy_xlsx_DF <- read_excel("clippy.xlsx")

excel_sheets() provides the names of all the sheets / tabs available within the excel file 

> excel_sheets("datasets.xls")
[1] "iris"     "mtcars"   "chickwts" "quakes"  

We can access the content of a specific sheet with in an excel by specifying the sheet name or sheet number -
> Read_excel_Sheet_By_Name <- read_excel("datasets.xls" , sheet = "mtcars" )


> Read_excel_Sheet_By_Num <- read_excel("datasets.xls" , sheet = 2 )

There are various other attributes / parameters available for read_excel() ,  using which you can control the number of rows to be read or range of cells to be read ..etc .

Example : Read first 2 rows only .
> read_excel("clippy.xlsx" , n_max = 2)

Example : Read only rows & columns which are present in excel cell range A2:B4 .
> read_excel("clippy.xlsx" , range = "A2:b4")

Example : Skip 1st record from input excel file while reading into R Dataframe .
> read_excel("clippy.xlsx" , skip =1)

Example : Find the Excel file format .
> excel_format("deaths.xlsx")
[1] "xlsx"

Thanks & happy Learning 
Priyaranjan Mohanty

@AUTHOR : Admin

Tags:Eco, Water, Air, Environment

Comments (0)

    No Comments Found
Leave a Comment