library(readxl)
library(dplyr)
## 
## Attaching package: 'dplyr'
## The following objects are masked from 'package:stats':
## 
##     filter, lag
## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union
library(ggplot2)
library(readr)  
library(plotly) 
## 
## Attaching package: 'plotly'
## The following object is masked from 'package:ggplot2':
## 
##     last_plot
## The following object is masked from 'package:stats':
## 
##     filter
## The following object is masked from 'package:graphics':
## 
##     layout
library(DT)     
library(htmlwidgets)


###
datos <- read_excel("DATABASEDIEGOINDICADOR.xlsx")

###
datos <- datos %>%
  rename(estado = `Entidad federativa`) %>%
  select(estado, starts_with("ICT"), starts_with("PSP"), starts_with("PAGS"), starts_with("PAAA"), starts_with("PACC"), starts_with("PASA"))

###
datos <- datos %>% filter(estado != "Toda la república")

###
datos <- datos %>%
  mutate(across(-estado, ~parse_number(as.character(.))))

###
normalizar <- function(x) {
  (x - min(x, na.rm = TRUE)) / (max(x, na.rm = TRUE) - min(x, na.rm = TRUE))
}
datos_norm <- datos %>%
  mutate(across(-estado, normalizar))

###
datos_norm <- datos_norm %>%
  rowwise() %>%
  mutate(Indicador_Compuesto = mean(c_across(where(is.numeric)), na.rm = TRUE))

###
tabla_interactiva <- DT::datatable(
  datos_norm %>% select(estado, Indicador_Compuesto),
  options = list(pageLength = 5),
  rownames = FALSE
)

###
p <- ggplot(datos_norm, aes(x = reorder(estado, Indicador_Compuesto), y = Indicador_Compuesto)) +
  geom_bar(stat = "identity") +
  coord_flip() +
  theme_minimal() +
  labs(x = "Entidad Federativa", y = "Indicador Compuesto", title = "Indicador Compuesto de Rendimiento contra el Cambio Climático")

###
p_interactivo <- ggplotly(p)

###
saveWidget(p_interactivo, "Indicador_Compuesto.html")

###
p_interactivo
DT::datatable(datos_norm, options = list(pageLength = 5, scrollX = TRUE))