Introduction
R can read data from various types of files having data in different formats. One such data format is the format of files created using Microsoft Excel. Files saved in Microsoft Excel are generally in .xls or .xlsx format.
Data available in files supporting .xls or .xlsx format can be read by R with the help of various packages provided in the directory of R.
In this article, we will be using the xlsx package to read Excel data from files available in .xls or .xlsx format. Data can also be written back to an Excel file using the same xlsx package.
Installing the Package
To install a package in the R environment, we can execute the below command on the console of R.
install.packages("xlsx")
It will generate the following output.
> install.packages("xlsx")
WARNING: Rtools is required to build R packages but is not currently installed. Please download and install the appropriate version of Rtools before proceeding:
https://cran.rstudio.com/bin/windows/Rtools/
Installing package into ‘R/win-library/4.0’ (as ‘lib’ is unspecified)
trying URL 'https://cran.rstudio.com/bin/windows/contrib/4.0/xlsx_0.6.5.zip'
Content type 'application/zip' length 374680 bytes (365 KB)
downloaded 365 KB
package ‘xlsx’ successfully unpacked and MD5 sums checked
The downloaded binary packages are in
RtmpKoVlUZ\downloaded_packages
>
We can also execute the command below to check if xlsx package has been installed or not.
> any(grepl("xlsx", installed.packages()))
The above code will generate the following output.
[1] TRUE
>
So, as we can see, the syntax is returning the value as TRUE, which means that the xlsx package is installed properly.
To load the xlsx package, we need to install and load the rJava package as well.
install.packages("rJava")
We can also execute the command below to check if the rJava package has been installed or not.
> any(grepl("rJava", installed.packages()))
[1] TRUE
After installing the package we can load the package as a library into the R environment and later use it to load excel data in R.
To load a rJava package library in R, we can run the below syntax as follows.
library(rJava)
After installing the package we can load the package as a library into the R environment and later use it to load excel data in R.
To load a xlsx package library in R, we can run the below syntax as follows.
library(xlsx)
Therefore, the above syntax loads the package into the environment of R.
Loading the Data
Now we will be reading and loading data from a file named workers.xlsx. The read.xlsx() function can be used to load excel data from a file supporting .xls and .xlsx format. Once the data gets loaded, it is stored in a data frame.
Instead of loading the entire data of the workers.xlsx file, we can load a single worksheet as well using the below syntax.
data_excel <- read.xlsx("CHIT_Profile.xlsx", sheetIndex = 1)
print(data_excel)
The above code snippet will generate the following.
> data_excel = read.xlsx("CHIT_Profile.xlsx", sheetIndex = 1)
> print(data_excel)
Month Installment After.Lifting Yield.by.Month Total.amount.to.pay Interest.Premium
1 1 5000 7000 200000 280000 80000
2 2 5000 7000 201000 278000 77000
3 3 5000 7000 202000 276000 74000
4 4 5000 7000 203000 274000 71000
5 5 5000 7000 204000 272000 68000
6 6 5000 7000 205000 270000 65000
7 7 5000 7000 206000 268000 62000
8 8 5000 7000 207000 266000 59000
9 9 5000 7000 208000 264000 56000
10 10 5000 7000 209000 262000 53000
11 11 5000 7000 210000 260000 50000
12 12 5000 7000 211000 258000 47000
13 13 5000 7000 212000 256000 44000
14 14 5000 7000 213000 254000 41000
15 15 5000 7000 214000 252000 38000
16 16 5000 7000 215000 250000 35000
17 17 5000 7000 216000 248000 32000
18 18 5000 7000 217000 246000 29000
19 19 5000 7000 218000 244000 26000
20 20 5000 7000 219000 242000 23000
21 21 5000 7000 220000 240000 20000
22 22 5000 7000 221000 238000 17000
23 23 5000 7000 222000 236000 14000
24 24 5000 7000 223000 234000 11000
25 25 5000 7000 224000 232000 8000
26 26 5000 7000 225000 230000 5000
27 27 5000 7000 226000 228000 2000
28 28 5000 7000 227000 226000 -1000
29 29 5000 7000 228000 224000 -4000
30 30 5000 7000 229000 222000 -7000
31 31 5000 7000 230000 220000 -10000
32 32 5000 7000 231000 218000 -13000
33 33 5000 7000 232000 216000 -16000
34 34 5000 7000 233000 214000 -19000
35 35 5000 7000 234000 212000 -22000
36 36 5000 7000 235000 210000 -25000
37 37 5000 7000 236000 208000 -28000
38 38 5000 7000 237000 206000 -31000
39 39 5000 7000 238000 204000 -34000
40 40 5000 7000 239000 202000 -37000
>
Here is another example of an Excel file loading via the read.xlsx function.
> movies_data = read.xlsx("movies_data.xlsx",sheetIndex = 1)
> head(movies_data,5)
serial
1 1
2 3
3 4
4 5
5 6
Plot
Despite his tarnished reputation after the events of The Dark Knight, in which he took the rap for Dent's crimes, Batman feels compelled to intervene to assist the city and its police force which is struggling to cope with Bane's plans to destroy the city.
Based on the novel written by Stephen Chbosky, this is about 15-year-old Charlie (Logan Lerman), an endearing and naive outsider, coping with first love (Emma Watson), the suicide of his best friend, and his own mental illness while struggling to find a group of people with whom he belongs. The introvert freshman is taken under the wings of two seniors, Sam and Patrick, who welcome him to the real world.
Mike Lane is a thirty-year old living in Tampa,Florida. By day he works as a roofer whilst at night, as Magic Mike, he is the star attraction of the Kings of Tampa, a group of male strippers. Secretly he wants out in order to further a projected furniture-making business but his credit rating precludes a bank loan for this despite his considerable savings. One night Adam, a teen-aged work-mate of Mike, follows him to the club and, when one of the acts is unable to go on,he is prevailed upon to strip - becoming a huge hit. However success goes to his head and his foolish actions not only threaten to jeopardize his sister Brooke ambitions as well.
When Bond's latest assignment goes gravely wrong and agents around the world are exposed, MI6 is attacked forcing M to relocate the agency. These events cause her authority and position to be challenged by Gareth Mallory (Ralph Fiennes), the new Chairman of the Intelligence and Security Committee. With MI6 now compromised from both inside and out, M is left with one ally she can trust: Bond. 007 takes to the shadows - aided only by field agent, Eve (Naomie Harris) - following a trail to the mysterious Silva (Javier Bardem), whose lethal and hidden motives have yet to reveal themselves.
5 Against medical advice and without the knowledge of her husband Pat Solatano Sr., caring Dolores Solatano discharges her adult son, Pat Solatano Jr., from a Maryland mental health institution after his minimum eight month court ordered stint. The condition of the release includes Pat Jr. s institutionalization was due to his beating up the lover of his wife Nikki, he was diagnosed with bipolar disorder. Nikki has since left him and has received a restraining order against him. Although he is on medication (which he doesn cope well, however no less so than some others who have never been institutionalized, such as his Philadelphia Eagles obsessed OCD father who has resorted to being a bookie to earn a living, his best friend Ronnie who quietly seethes over the control wielded by his wife Veronica, and Veronica's widowed sister, Tiffany Maxwell, a recovering sex addict. In their fragile mental states, Pat Jr. and Tiffany embark on a love/hate friendship based primarily on what help the other can provide in achieving their inpidual goals. But they may reevaluate their goals as their relationship progresses.
Title imdbVotes
1 The Dark Knight Rises 2679
2 The Perks of Being a Wallflower 1270
3 Magic Mike 2580
4 Skyfall 1807
5 Silver Linings Playbook 1828
Poster imdbRating
1 http://ia.media-imdb.com/images/M/MV5BMTk4ODQzNDY3Ml5BMl5BanBnXkFtZTcwODA0NTM4Nw@@._V1_SX300.jpg 75
2 http://ia.media-imdb.com/images/M/MV5BMzIxOTQyODU1OV5BMl5BanBnXkFtZTcwMDQ4Mjg4Nw@@._V1_SX300.jpg 71
3 http://ia.media-imdb.com/images/M/MV5BMTQzMDMzOTA5M15BMl5BanBnXkFtZTcwMjc4MTg4Nw@@._V1_SX300.jpg 51
4 http://ia.media-imdb.com/images/M/MV5BMjAyODkzNDgzMF5BMl5BanBnXkFtZTcwMDMxMDI4Nw@@._V1_SX300.jpg 68
5 http://ia.media-imdb.com/images/M/MV5BMTM2MTI5NzA3MF5BMl5BanBnXkFtZTcwODExNTc0OA@@._V1_SX300.jpg 68
Genre imdbID Year Language
1 Action, Thriller tt1345836 2012 English
2 Drama, Romance tt1659337 2012 English
3 Comedy, Drama tt1915581 2012 English
4 Action, Thriller tt1074638 2012 English
5 Comedy, Drama, Romance tt1045658 2012 English