I am using this function to take in multiple .xlsx files (each with three sheets and different number of total columns) and then merging them all together into one dataframe.
files <- list.files(path = "PATH GOES HERE",pattern='*.xlsx',full.names = T)
data_xlsx_df <- map_df(set_names(files), function(file) {
file %>%
excel_sheets() %>%
set_names() %>%
map_df(
~ read_xlsx(path = file, sheet = .x,skip = 1),
.id = "sheet")
}, .id = "file")
The function works fine and does its job correctly. The problem I am encountering is that some of the excel sheets have some encoding issues thanks to some accents. I use the following code rename_with(., ~ gsub("'", "", iconv(.x,from = 'UTF-8', to='ASCII//TRANSLIT'))) to fix the accent issues with the results of the function but unfortunately by then the function created multiple new columns because it treats the the columns with the accent issues as different names. I just simply want to fix these columns with accent issues inside the function so that way the result doesn't have unnecessary repeated columns and therefore messing the whole final dataset up. Admittedly, I haven't dealt with purr in a while and how the map_df function works properly (I modified it from a post here). The image below has an example of the excel files look (there's three sheets, one is total, the others are female and male). These excel files are repeated by year. Basically I want to get rid of the accents inside the function so no additional unnecessary columns are created.
UPDATE: janitor clean_names fixed it...
map_df(
~ read_xlsx(path = file, sheet = .x,skip = 1)%>% clean_names(),
.id = "sheet")
