Microsoft Excel is another tool which is commonly used for data analysis. There are many R packages that can be used to interact with Excel. Once such package is
readxl which was developed by Hadley Wickham.
An Excel file contains tabular data in multiple sheets. For this lesson, we have an excel sheet named
stock-prices.xlsx (Downloadable above) that contains stock price data for December 2016 and November 2016 in two different sheets labeled – ‘Dec-2016’ and ‘Nov-2016’.
It is generally a good idea to first explore your data by directly opening it in MS Excel before you import it in R. Now that we know what data we are importing, let’s look at how we can import it using
We will use two functions from
excel_sheets()is used to get a list of sheets in the excel file
read_excel()is used to actually import the data from an excel sheet
These functions support both
.xlsx file formats.
Install and Load read.xl() Package
The first thing we need to do is to install and load the desired package.
> install.packages("readxl") > library(readxl)
Now that the
readxl package is loaded in our R session, we can use the functions to read the data.
We will first place the excel sheet into our working directory so that it is easy to refer to in the functions. The
dir() function can be used to confirm that the file is in the working directory. The function
excel_sheets() can be used to get a list of all sheets in our excel file. To use the function simply pass the file name to the function and it will return a character vector with a list of sheet names.
> dir()  "GS-Stock-Prices.txt" "stock-prices.xlsx" "top-100-stocks.csv" > excel_sheets("stock-prices.xlsx")  "Dec-2016" "Nov-2016" "Sheet3" >
We can now use the
read_excel() function to import the data from an excel sheet. If we just pass the excel file name to the function, it will import data from the first sheet by default. The imported data is in the form of a tibble. If we want to load data from a specific sheet we can specify the sheet name or the sheet index in the
# The following function will load data from first sheet read_excel("stock-prices.xlsx") # The following two commands will load data from the second sheet read_excel("stock-prices.xlsx", sheet=2) read_excel("stock-prices.xlsx", sheet="Nov-2016")
Once the data is imported, we can explore it using the standard R functions.
> stockdata <- read_excel("stock-prices.xlsx", sheet="Nov-2016") > str(stockdata) Classes ‘tbl_df’, ‘tbl’ and 'data.frame': 21 obs. of 6 variables: $ Time : POSIXct, format: "2016-11-01" "2016-11-02" ... $ Open : num 179 177 177 176 179 ... $ High : num 179 178 178 177 182 ... $ Low : num 177 176 176 175 179 ... $ Last : num 178 177 176 176 181 ... $ Volume: num 2900600 2104700 1972700 1846400 3338700 ... > summary(stockdata) Time Open High Low Min. :2016-11-01 00:00:00 Min. :176.3 Min. :177.3 Min. :174.7 1st Qu.:2016-11-08 00:00:00 1st Qu.:180.1 1st Qu.:182.7 1st Qu.:179.0 Median :2016-11-15 00:00:00 Median :206.3 Median :209.7 Median :204.8 Mean :2016-11-14 21:42:51 Mean :198.1 Mean :201.3 Mean :197.2 3rd Qu.:2016-11-22 00:00:00 3rd Qu.:210.0 3rd Qu.:211.9 3rd Qu.:209.6 Max. :2016-11-30 00:00:00 Max. :215.2 Max. :220.8 Max. :215.0 Last Volume Min. :175.9 Min. : 1846400 1st Qu.:181.9 1st Qu.: 2646100 Median :209.2 Median : 3338700 Mean :200.1 Mean : 4306352 3rd Qu.:211.1 3rd Qu.: 5276100 Max. :219.3 Max. :11345400 >
We can also use the
xlsx package to access Excel files. The first row should contain variable/column names.
# read in the first worksheet from the workbook stock-prices.xlsx # first row contains variable names library(xlsx) stock_data <- read.xlsx("stock-prices.xlsx", 1) # read in the worksheet named Nov-2016 mydata <- read.xlsx("stock-prices.xlsx", sheetName = "Nov-2016")
The imported data is loaded as a data frame.
Another alternative package for working with Excel data in R is the
gdata package which is maintained by Gregory Warnes.
gdata is actually an entire suite of tools for data manipulation. The package makes handling and management of data very convenient in R. In the
gdata package the function we use to read Excel data is the
read.xls() function. By default it only supports XLS files. However, the support for
.xlsx files can be incorporated by simply installing a driver.
read.xls() makes use of the read.table() function to import the data as a data frame. So, all the arguments for
read.table() are available for us to use while importing data from Excel using
gdata package requires perl interpreter to be installed on the system. Without that the function will not run.