I'm trying to knit a html document in rmarkdown with a code that works perfect in a script.
In the script I load a lot of excel files that's why I use a function named input_files
, then list.files
and run it with lapply
.
This is the code in rmarkdown, the same from script. In my original script I have to use absolute path to get the excel files.
---
title: "Energías"
date: "5/31/2021"
output:
html_document: default
---
```{r include=FALSE}
rm(list=ls()) # borrar todo lo que haya en memoria
gc()
options(scipen=999) # evitar notacion cientifica
options(encoding = "Latin1")
#Instalar librerias necesarias
if(!require(knitr)){install.packages("knitr")} #compilar en html
if(!require(rmdformats)){install.packages("rmdformats")}#compilar en html
if(!require(tidyverse)){install.packages("tidyverse")}
if(!require(readr)){install.packages("openxlsx")}
if(!require(RPostgreSQL)){install.packages("lubridate")}
if(!require(DBI)){install.packages("reshape2")}
library(knitr)
library(rmdformats)
library(openxlsx)
library(tidyverse)
library(lubridate)
library(reshape2)
library(ggplot2)
```
```{r include=FALSE}
# UBICACION ARCHIVOS----
path <- "/Datos/real"
#path <- "C:/Google Drive/CELEC/Despachos_2020/Datos/real" #compu trabajo
# FUNCION PARA CARGAR DE ARCHIVOS----
input_files <- function(path) {
require(stringr)
ernc <- read.xlsx(xlsxFile = path,
sheet = "Generación Renovable",
rows = 5:54,
fillMergedCells = TRUE,
colNames = TRUE, detectDates = T)
names(ernc)[2] <- "HORA"
names(ernc)[1] <- "Fecha"
f <- ernc[2,1]
ernc$Fecha <- f
ernc <- ernc[-1, 1:(length(ernc)-1)]
should_prefix <- function(x) any(stringr::str_detect(x,
"^X[0-9]"))
apply_prefix <- function(colname, prefix) paste0(prefix, colname)
ernc <- ernc %>%
dplyr::rename_if(
map_lgl(names(.), should_prefix),
~apply_prefix(., "MVAR_")
)
ernc <- rename_at(ernc, vars(contains("MVAR")),
~ paste0(colnames(ernc)[parse_number(.) -1], "_MVAR"))
ernc <- melt(ernc, id.vars = c("Fecha", "HORA"))
return(ernc)
}
# PATH COMPLETO DE ARCHIVOS----
paths <- list.files(path=path,
pattern="\\.xlsx$",
full.names=TRUE)
# CARGA DE ARCHIVOS A UNA LISTA----
out.lst <- lapply(paths, input_files)
# GENERACION DE DATA FRAME A PARTIR DE LISTA----
ernc <- bind_rows(out.lst, .id = "ID")
ernc$value <- as.numeric(ernc$value)
ernc$Fecha <- as.Date(ernc$Fecha)
ernc$tecnologia <- "renovables"
```
``` {r}
head(ernc, 4)
```
When I use relative path, It doesn't load any file and I get this as output:
> # Energías
> 5/31/2021
> head(ernc, 4)
> A tibble: 0 x 3
> ... with 3 variables: value <dbl>, Fecha <date>, tecnologia <chr>
There's 0 data and 3 variables when it's supposed to be 242592 observations with 6 variables.
And when I use absolute path I get this error:
Quitting from lines 37-85 (Energias.Rmd)
Error in read.xlsx.default(xlsxFile = path, sheet = "Generación Renovable", :
Workbook has no worksheets
Calls: <Anonymous> ... eval -> lapply -> FUN -> read.xlsx -> read.xlsx.default
All the files have worksheets, as I said the same code works if I run it in a normal script, the problem came with rmarkdown.
I've found some similar errors in other posts but the solutions where changing relative path to absolute path or in the other way. In my case, it doesn't work.
Please some advice.