From R to excel: Part 1

Introduction

Welcome welcome.

In this blog post, we will be exploring:

1. How to export multiple data frames into a single Excel file with multiple sheets using the writexl and openxlsx packages

By the end of this post, you & I should have a solid understanding of how to export multiple data frames into 1 excel file (.xlsx)

Purpose

Why do you need to export your R output to Excel?

1. Data Sharing: Excel is a widely used tool for data analysis and reporting.

2. Further Analysis: Some users might want to perform additional analyses or calculations on the exported data using Excel’s functions, pivot tables, or other features.

3. User Preference: For some tasks or audiences/stakeholders, Excel might be the preferred tool due to its user-friendly interface or specific features.

How-to

Load library

We will first load the dplyr library:

library(dplyr)

Data sets

For the data frames, we will use datasets available from the dplyr package.

Here are the datasets available in dplyr:

However, for this, we will focus only just four of them:

band_instruments (3 rows and 2 columns)

band_members (3 rows and 2 columns)

star_wars (87 rows and 14 columns)

storms (19,066 rows and 13 columns)

Export (writexl)

I will demonstrate two methods for exporting to an Excel file: using the writexl and openxlsx packages

• Using writexl package
  • First, define the data frames within a list.
  • Export using write_xlsx

library(writexl)

excel_list <- list("Instruments" = band_instruments,
                   "Members" = band_members,
                   "Star Wars" = starwars,
                   "Storms" = storms)


write_xlsx(excel_list, "file_with_sheets_version1.xlsx")

If you prefer not to format the header (no bold header), you may include format_headers = FALSE:

write_xlsx(excel_list, "file_with_sheets_version1.xlsx", format_headers = FALSE)

Export (openxlsx)

• Using openxlsx package

  • Follow the same process: define the list.
  • Export using write.xlsx

library(openxlsx)

write.xlsx(excel_list, "file_with_sheets_version2.xlsx")

And there you have it! Now you can send the file to stakeholders who prefer Excel.