R - Excel Files

Modern R and Excel Files

Microsoft Excel (.xls, .xlsx) is a ubiquitous spreadsheet program. Interacting with Excel files is a common task in data analysis. While there are several packages to connect R and Excel, the modern standard is to use the readxl and writexl packages.

Why readxl and writexl? Older packages like xlsx or gdata often relied on Java or Perl, which could be slow and difficult to set up. The readxl and writexl packages are:

Fast: They are written in C/C++ for high performance.
Dependency-Free: They have no external dependencies like Java, making installation and use seamless on any system.
Tidyverse-Friendly: readxl is part of the core tidyverse and plays nicely with other packages like dplyr and ggplot2.

Step 1: Install the Required Packages

You can install both packages from CRAN using the following command in the R console.

install.packages(c("readxl", "writexl"))

Step 2: Prepare the Excel Input File

Create an Excel file named input.xlsx. In the first worksheet (which is usually named Sheet1 by default), enter the following employee data:

id  name  salary  start_date  dept
1  Rick  623.3  2012-01-01  IT
2  Dan  515.2  2013-09-23  Operations
3  Michelle  611  2014-11-15  IT
4  Ryan  729  2014-05-11  HR
5  Gary  843.25  2015-03-27  Finance
6  Nina  578  2013-05-21  IT
7  Simon  632.8  2013-07-30  Operations
8  Guru  722.5  2014-06-17  Finance

Next, create a second worksheet and name it cities. Enter the following location data:

name  city
Rick  Seattle
Dan  Tampa
Michelle  Chicago
Ryan  Seattle
Gary  Houston
Nina  Boston
Simon  Mumbai
Guru  Dallas

Save the file input.xlsx in your R project’s working directory.

Step 3: Reading Data from the Excel File

We use the read_excel() function from the readxl package. The result is imported as a tibble, a modern data frame.

# Load the library
library(readxl)

# Read the first worksheet by its index (1-based)
# read_excel() is smart enough to find the first sheet automatically if not specified.
employee_data <- read_excel("input.xlsx", sheet = 1)
print(employee_data)

# Read the second worksheet by its name
city_data <- read_excel("input.xlsx", sheet = "cities")
print(city_data)

When we execute the above code, it produces the following output:

# A tibble: 8 × 5
     id name     salary start_date          dept
  <dbl> <chr>     <dbl> <dttm>              <chr>
1     1 Rick       623. 2012-01-01 00:00:00 IT
2     2 Dan        515. 2013-09-23 00:00:00 Operations
3     3 Michelle   611  2014-11-15 00:00:00 IT
4     4 Ryan       729  2014-05-11 00:00:00 HR
5     5 Gary       843. 2015-03-27 00:00:00 Finance
6     6 Nina       578  2013-05-21 00:00:00 IT
7     7 Simon      633. 2013-07-30 00:00:00 Operations
8     8 Guru       722. 2014-06-17 00:00:00 Finance

# A tibble: 8 × 2
  name     city
  <chr>    <chr>
1 Rick     Seattle
2 Dan      Tampa
3 Michelle Chicago
4 Ryan     Seattle
5 Gary     Houston
6 Nina     Boston
7 Simon    Mumbai
8 Guru     Dallas

Step 4: Writing Data to an Excel File

The writexl package makes writing data frames or tibbles to an .xlsx file incredibly simple with the write_xlsx() function.

Let’s imagine we’ve joined our two datasets and want to export the result.

# Load necessary libraries
library(writexl)
library(dplyr)

# Join the two datasets we imported earlier
full_data <- left_join(employee_data, city_data, by = "name")

# Write the combined data to a new Excel file
# The file will be created in your working directory
write_xlsx(full_data, path = "output_data.xlsx")

cat("Successfully wrote combined data to output_data.xlsx")

After running this code, a new file named output_data.xlsx will appear in your project folder, containing the merged data. You can even write multiple data frames to different sheets in the same file by passing them as a named list:

# Example of writing to multiple sheets
list_of_datasets <- list("Employees" = employee_data, "Locations" = city_data)
write_xlsx(list_of_datasets, path = "multisheet_output.xlsx")