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, we need to first import excel files in RStudio or any other R supporting IDE(Integrated development environment).

First install readxl package in R to load excel files. Various methods including there 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 working directory.



filter_none

edit
close

play_arrow

link
brightness_4
code

# 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)

chevron_right



The excel files are loaded into variable 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.

filter_none

edit
close

play_arrow

link
brightness_4
code

# Modifying the files
Data1$Pclass <- 0
  
Data2$Embarked <- "S"
  
# Printing the data
head(Data1)
head(Data2)

chevron_right



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.

filter_none

edit
close

play_arrow

link
brightness_4
code

# Deleting from files
Data1 <- Data1[-2]
  
Data2 <- Data2[-3]
  
# Printing the data
Data1
Data2

chevron_right



The - sign is used to delete column or attributes from dataset. Column 2 is deleted from Data1 dataset and Column 3 is deleted from 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.

filter_none

edit
close

play_arrow

link
brightness_4
code

# Merging Files
Data3 <- merge(Data1, Data2, all.x = TRUE, all.y = TRUE)
  
# Dsiplaying the data
head(Data3)

chevron_right


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.

filter_none

edit
close

play_arrow

link
brightness_4
code

# Creating feature in Data1 dataset
Data1$Num <- 0
  
# Creating feature in Data2 dataset
Data2$Code <- "Mission"
  
# Printing the data
head(Data1)
head(Data2)

chevron_right



Num is a new feature that is created with 0 default value in Data1 dataset. Code is a new feature that is created with 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.

filter_none

edit
close

play_arrow

link
brightness_4
code

# 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")

chevron_right



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 present working directory.




My Personal Notes arrow_drop_up

Technology Enthusiast

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :

Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.