May 12, 2021 R language tutorial
Microsoft Excel is the most widely used spreadsheet program that stores .xls data .xlsx or other formats. T he R language can use some excel-specific packages directly from these files. R arely are such packages - XLConnect, xlsx, gdata, etc. W e will use the xlsx package. T he R language can also use this package to write to excel files.
You can use the following commands in the R console to install the "xlsx" package. I t may require the installation of some additional packages that this package relies on. I nstall additional packages with the same command that has the desired package name.
install.packages("xlsx")
Use the following commands to validate and load the "xlsx" package.
# Verify the package is installed. any(grepl("xlsx",installed.packages())) # Load the library into R workspace. library("xlsx")
When the script runs, we get the following output.
[1] TRUE Loading required package: rJava Loading required package: methods Loading required package: xlsxjars
Open Microsoft Excel. C opy and paste the following data into a worksheet named sheet1.
id name salary start_date dept 1 Rick 623.3 1/1/2012 IT 2 Dan 515.2 9/23/2013 Operations 3 Michelle 611 11/15/2014 IT 4 Ryan 729 5/11/2014 HR 5 Gary 843.25 3/27/2015 Finance 6 Nina 578 5/21/2013 IT 7 Simon 632.8 7/30/2013 Operations 8 Guru 722.5 6/17/2014 Finance
Also copy and paste the following data into another worksheet and rename it "city."
name city Rick Seattle Dan Tampa Michelle Chicago Ryan Seattle Gary Houston Nina Boston Simon Mumbai Guru Dallas
Save the Excel file as input .xlsx. I t should be saved in the current working directory of the R work area.
Read the input .xlsx by using the read.xlsx () function, as shown below. T he results are stored as data frames in the R-language environment.
# Read the first worksheet in the file input.xlsx. data <- read.xlsx("input.xlsx", sheetIndex = 1) print(data)
When we execute the code above, it produces the following results -
id, name, salary, start_date, dept 1 1 Rick 623.30 2012-01-01 IT 2 2 Dan 515.20 2013-09-23 Operations 3 3 Michelle 611.00 2014-11-15 IT 4 4 Ryan 729.00 2014-05-11 HR 5 NA Gary 843.25 2015-03-27 Finance 6 6 Nina 578.00 2013-05-21 IT 7 7 Simon 632.80 2013-07-30 Operations 8 8 Guru 722.50 2014-06-17 Finance