Working with Excel Files in R Programming

Working with Excel Files in detail

Excel files commonly have extensions such as .xls, .xlsx, and .csv (comma-separated values). To begin working with Excel files in R, they need to be imported into RStudio or any other R-compatible Integrated Development Environment (IDE).

Reading Excel Files in R

Before reading Excel files, the readxl package must be installed and loaded. Below is an example demonstrating how to do so.

Example Excel Files:

data1.xlsx:

ID    Name    Age
1     Alex    25
2     Bob     30
3     Cathy   22

data2.xlsx:

ID    City       Country
1     New York   USA
2     London     UK
3     Sydney     Australia

Reading Files from the Working Directory

# Installing the required package
install.packages("readxl")

# Loading the package
library(readxl)

# Importing Excel files
data1 <- read_excel("data1.xlsx")
data2 <- read_excel("data2.xlsx")

# Printing the data
head(data1)
head(data2)

Output:

data1:

ID   Name    Age
1  1   Alex    25
2  2   Bob     30
3  3   Cathy   22

data2:

ID    City      Country   Region
1  1    New York USA       Unknown
2  2    London   UK        Unknown
3  3    Sydney   Australia Unknown

Deleting Content from Files

Columns can be removed using the - sign in R.

# Deleting columns
data1 <- data1[-2]
data2 <- data2[-3]

# Printing updated data
head(data1)
head(data2)

Output:

data1:

ID   Age   Status
1  1   25    Active
2  2   30    Active
3  3   22    Active

data2:

ID    City      Region
1  1    New York Unknown
2  2    London   Unknown
3  3    Sydney   Unknown

Writing Data to New Excel Files

After making modifications, the datasets can be saved into new Excel files using the writexl package.

# Installing the package
install.packages("writexl")

# Loading the package
library(writexl)

# Writing modified data to new Excel files
write_xlsx(data1, "Updated_data1.xlsx")
write_xlsx(data2, "Updated_data2.xlsx")

These files will be saved in the current working directory. The final datasets include all modifications and can be used for further analysis.