From excel to R: Part 1

To read data from multiple sheets in an Excel workbook, combine them into a single dataframe using R and to deal with mixed date formats using the janitor package

R
Author

Zahier Nasrudin

Published

March 8, 2023

Welcome welcome.

In this blog post, we will be exploring:

  1. How to efficiently read data from multiple sheets in an Excel workbook and combine them into a single dataframe using R.
  2. Dealing with mixed date formats using the janitor package

By the end of this post, you & me should have a solid understanding of how to read and combine data from multiple sheets in an Excel workbook using R, and how to handle mixed date formats to ensure consistent data analysis.

Load library

Code
library(readxl)
library(dplyr)
library(purrr)
library(janitor)

Define path

First, let’s define the path to the Excel file that we want to read in. In this example, we’ll be using the here package to make the path relative to the current project directory. This data set can be obtained from my github repository

Code
## Define path
path <- here::here("posts/2023-03-08-excel-to-r/input/store-sales.xlsx")

Reading all sheets

To read all the sheets:

Code
df_excel <- path %>% 
  excel_sheets() %>%
  map_df(~read_excel(.x, path = path) %>%
           mutate(sheet_name = .x)) 
  1. The excel_sheets() function from the readxl package is used to extract the names of all the sheets in the Excel file.
  2. The map_df() function from the purrr package meanwhile is used to apply a function to each element of a vector and combine the results into a data frame
  3. In this case, we are using map_df() to read in each sheet of the Excel file; using the read_excel() function from the readxl package.
  4. Finally, we add a new column. This new column is called sheet_name and contains the name of the sheet that the data came from.

The dataframe:

Code
df_excel %>%
  knitr::kable()
Year Date Store Sales sheet_name
2012 1/26/2012 A 2000 Year1
2012 1/26/2012 B 2500 Year1
2012 1/28/2012 C 3400 Year1
2012 Jan 21, 2012 D 6000 Year1
2013 1/26/2013 A 2000 Year2
2013 1/21/2013 B 2500 Year2
2013 1/28/2013 C 3400 Year2
2013 Jan 25, 2013 D 6000 Year2
2013 41302 E 5500 Year2
2014 1/26/2014 A 2000 Year3
2014 1/28/2014 B 2500 Year3
2014 2/28/2014 C 3400 Year3
2014 March 25, 2014 D 6000 Year3
2014 41789 E 5500 Year3

Mixed date formats

We have successfully loaded the information into one dataframe. However, it appears that this dataframe contains a column (Date) with mixed date formats that have been imported from Excel. This can often be a problem when working with date data, as it can make it difficult to perform consistent operations and analyses on the data. To fix this issue, we will need to standardize the date format across the entire column, so that all dates can be treated consistently. One way to accomplish this is to use a function like convert_to_date() from the janitor package, which can convert the dates to a consistent format while also handling any errors or inconsistencies in the data:

Code
df_excel <- df_excel %>%
  mutate(Date_change = convert_to_date(Date, string_conversion_failure = "warning",
                                       character_fun = lubridate::mdy))
  1. To explain,convert_to_date() function from the janitor package is used to convert the Date column to a consistent date format.
  2. The string_conversion_failure parameter is set to “warning”, to indicate that any non-date strings in the Date column will be converted to NA values and a warning message will be displayed, just to be safe.
  3. The character_fun parameter is set to lubridate::mdy, which indicates the expected order of month, day, and year in the date string.

Please refer to Date_change for the standardized date column:

Code
## Show dataset


df_excel %>%
  knitr::kable()
Year Date Store Sales sheet_name Date_change
2012 1/26/2012 A 2000 Year1 2012-01-26
2012 1/26/2012 B 2500 Year1 2012-01-26
2012 1/28/2012 C 3400 Year1 2012-01-28
2012 Jan 21, 2012 D 6000 Year1 2012-01-21
2013 1/26/2013 A 2000 Year2 2013-01-26
2013 1/21/2013 B 2500 Year2 2013-01-21
2013 1/28/2013 C 3400 Year2 2013-01-28
2013 Jan 25, 2013 D 6000 Year2 2013-01-25
2013 41302 E 5500 Year2 2013-01-28
2014 1/26/2014 A 2000 Year3 2014-01-26
2014 1/28/2014 B 2500 Year3 2014-01-28
2014 2/28/2014 C 3400 Year3 2014-02-28
2014 March 25, 2014 D 6000 Year3 2014-03-25
2014 41789 E 5500 Year3 2014-05-30