From excel to R: Part 1

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

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

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

Reading all sheets

To read all the sheets:

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:

df_excel %>%
  knitr::kable()

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:

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:

## Show dataset


df_excel %>%
  knitr::kable()