Unir hojas de calculo de Excel en un solo data frame que tienen encabezado

General
12

This version of the code uses as.numeric() to coerce any text in columns 6:132 into NA. It throws one warning when it coerces the text in sheet 9 to NA.

library(openxlsx)
library(purrr)
library(dplyr)

FileName <- "~/R/Play/BDD_esquema_regular_2015.xlsx"
SheetNames <- as.character(1:12)
#Define a function to get data from one sheet
GetData <- function(Nm) {
  tmp <- read.xlsx(FileName, sheet = Nm, 
                                  colNames = TRUE, 
                                  startRow = 7, cols = 1:132)
  tmp |> mutate(across(.cols = 6:132, as.numeric))
}
  

#Get data from all the sheets
AllSheets <- map(SheetNames, GetData)
#> Warning: There was 1 warning in `mutate()`.
#> ℹ In argument: `across(.cols = 6:132, as.numeric)`.
#> Caused by warning:
#> ! NAs introduced by coercion
names(AllSheets) <- SheetNames
AllData <- bind_rows(AllSheets, .id = "Hoja")
AllData[1:5, 1:7]
#>   Hoja       A        B     C      D              E   1
#> 1    1 Bolivar Guaranda 02D01 000125     4 ESQUINAS  37
#> 2    1 Bolivar Guaranda 02D01 000154     CACHISAGUA  15
#> 3    1 Bolivar Guaranda 02D01 000130 CORDERO CRESPO 104
#> 4    1 Bolivar Guaranda 02D01 000126   EL RAYO BAJO   3
#> 5    1 Bolivar Guaranda 02D01 000120   FACUNDO VELA  14

Created on 2023-11-23 with reprex v2.0.2

1 Like