How to build a function that loops through data frames and transforms the data in them (R)

user_12585535 0
Viewed 48

I am trying to import data from an excel spreadsheet, only selecting a set of sheets (not all sheets) and then performing a few steps of data transformation (pivot longer and change data type).

I have got this in 4 lines of code and I could theoretically copy and paste this block for each sheet (8 in total), but I would like to see if I could loop through this all with a function.

The steps are the following:

1) Read the specific sheets and save them as data frames

2) Pivot longer taking columns 2:13

3) Change the new column values from excel date type (numeric) to the actual date

4) Change the date into yearmon format

Here is my code for doing this for one sheet:

kerrie <- read_excel("report.xlsx", sheet = "KERRIE_DATA") # load Kerrie data sheet
kerrie <- pivot_longer(kerrie, 2:13, names_to = "month") # put months into rows
kerrie$month <- excel_numeric_to_date(as.numeric(kerrie$month), date_system = "modern") # change date data type
kerrie$month <- as.yearmon(kerrie$month) # turn into yearmon

Here is a vector with all the sheet names

sheetNames <- c("KERRIE_DATA", "GAIL_DATA", "DANIELLE_DATA", "ISABELLE_DATA","ESTELLE_DATA", "CLARE_DATA", "JEN_DATA", "CHRIS_DATA")

And here is the solution I've found for step 1: it loops through the excel workbook and imports my desired sheets, saving them as data frames

library(openxlsx)
a <- loadWorkbook("report.xlsx")
for(i in 1:length(sheetNames))
{
  assign(sheetNames[i],readWorkbook(a,sheet = i))
}

Can you help me put steps 2:4 into a function/loop, please?

dataframe r for-loop excel function
1 Answers

You could write a function with all the steps : 

apply_fun <- function(sheet_name) {
     kerrie <- readxl::read_excel("report.xlsx", sheet = sheet_name) 
     kerrie <- tidyr::pivot_longer(kerrie, 2:13, names_to = "month")
     kerrie$month <- janitor::excel_numeric_to_date(as.numeric(kerrie$month), 
                      date_system = "modern") 
     kerrie$month <- zoo::as.yearmon(kerrie$month) 
     return(kerrie)
}

and apply it for all sheets

complete_data <- lapply(sheetNames, apply_fun)

This will return you list of dataframes in complete_data.

user_39629140
Related