Working with Excel Files in R Programming
Excel files are of extension .xls, .xlsx and .csv(comma-separated
values). To start working with excel files in R Programming
Language, we need to first import excel files in RStudio or any
other R supporting IDE(Integrated development environment).
Reading Excel Files in R Programming
Language
First, install readxl package in R to load excel files. Various
methods including their subparts are demonstrated further.
Sample_data1.xlsx:
Sample_data2.xlsx:
Reading Files:
The two excel files Sample_data1.xlsx and Sample_data2.xlsx and
read from the working directory.
R
# Working with Excel Files
# Installing required package
install.packages("readxl")
# Loading the package
library(readxl)
# Importing excel file
Data1 < - read_excel("Sample_data1.xlsx")
Data2 < - read_excel("Sample_data2.xlsx")
# Printing the data
head(Data1)
head(Data2)
The excel files are loaded into variables Data_1 and Data_2 as
a dataframes and then variable Data_1 and Data_2 is called that
prints the dataset.
Modifying Files
The Sample_data1.xlsx file and Sample_file2.xlsx are modified.
R
# Modifying the files
Data1$Pclass <- 0
Data2$Embarked <- "S"
# Printing the data
head(Data1)
head(Data2)
The value of the P-class attribute or variable of Data1 data is
modified to 0. The value of Embarked attribute or variable of
Data2 is modified to S.
Deleting Content from files
The variable or attribute is deleted from Data1 and Data2
datasets containing Sample_data1.xlsx and Sample_data2.xlsx
files.
R
# Deleting from files
Data1 <- Data1[-2]
Data2 <- Data2[-3]
# Printing the data
Data1
Data2
The – sign is used to delete columns or attributes from the
dataset. Column 2 is deleted from the Data1 dataset and Column
3 is deleted from the Data2 dataset.
Merging Files
The two excel datasets Data1 and Data2 are merged
using merge() function which is in base package and comes pre-
installed in R.
R
# Merging Files
Data3 <- merge(Data1, Data2, all.x = TRUE, all.y = TRUE)
# Displaying the data
head(Data3)
Data1 and Data2 are merged with each other and the resultant
file is stored in the Data3 variable.
Creating new columns
New columns or features can be easily created in Data1 and
Data2 datasets.
R
# Creating feature in Data1 dataset
Data1$Num < - 0
# Creating feature in Data2 dataset
Data2$Code < - "Mission"
# Printing the data
head(Data1)
head(Data2)
Num is a new feature that is created with 0 default value in
Data1 dataset. Code is a new feature that is created with the
mission as a default string in Data2 dataset.
Writing Files
After performing all operations, Data1 and Data2 are written into
new files using write.xlsx() function built in writexl package.
R
# Installing the package
install.packages("writexl")
# Loading package
library(writexl)
# Writing Data1
write_xlsx(Data1, "New_Data1.xlsx")
# Writing Data2
write_xlsx(Data2, "New_Data2.xlsx")
The Data1 dataset is written New_Data1.xlsx file and Data2
dataset is written in New_Data2.xlsx file. Both the files are
saved in the present working directory.
