LEI <- read_xlsx("LEI.xlsx")

LEI<-LEI[,-1];colnames(LEI) <- LEI[1,];LEI<-LEI[-1,]
LEI$cod_com <- as.numeric(LEI$cod_com)
LEI$LEI_2018 <- LEI$LEI_2017
LEI$LEI_2019 <- LEI$LEI_2017
LEI$LEI_2020 <- LEI$LEI_2017
colnames(LEI) <- c("cod_com","a2010","a2011","a2012","a2013","a2014","a2015","a2016","a2017","a2018","a2019","a2020")
LEI$sub_indice <- "LEI"
datatable(head(LEI), extensions = 'Buttons', escape = FALSE, rownames = TRUE,
          options = list(dom = 'Bfrtip',
          buttons = list('colvis', list(extend = 'collection',
          buttons = list(
          list(extend='copy'),
          list(extend='excel',
            filename = 'tabla'),
          list(extend='pdf',
            filename= 'tabla')),
          text = 'Download')), scrollX = TRUE))

II <- read_xlsx("II.xlsx")

II<-II[,-1];colnames(II) <- II[1,];II<-II[-1,];names(II)[1]<-"cod_com"
II$cod_com <- as.numeric(II$cod_com)
II$II_2012 <- II$II_2013
II$II_2011 <- II$II_2013
II$II_2010 <- II$II_2013
II <- II[,c(1,13,12,11,2:9)]
colnames(II) <- c("cod_com","a2010","a2011","a2012","a2013","a2014","a2015","a2016","a2017","a2018","a2019","a2020")
II$sub_indice <-"II"
datatable(head(II), extensions = 'Buttons', escape = FALSE, rownames = TRUE,
          options = list(dom = 'Bfrtip',
          buttons = list('colvis', list(extend = 'collection',
          buttons = list(
          list(extend='copy'),
          list(extend='excel',
            filename = 'tabla'),
          list(extend='pdf',
            filename= 'tabla')),
          text = 'Download')), scrollX = TRUE))

EI <- read_xlsx("EI.xlsx")

EI<-EI[,-1];colnames(EI) <- EI[1,];EI<-EI[-1,];EI<-EI[,-c(2,3)]
EI$cod_com <- as.numeric(EI$cod_com)
colnames(EI) <- c("cod_com","a2010","a2011","a2012","a2013","a2014","a2015","a2016","a2017","a2018","a2019","a2020")
EI$sub_indice <- "EI"
datatable(head(EI), extensions = 'Buttons', escape = FALSE, rownames = TRUE,
          options = list(dom = 'Bfrtip',
          buttons = list('colvis', list(extend = 'collection',
          buttons = list(
          list(extend='copy'),
          list(extend='excel',
            filename = 'tabla'),
          list(extend='pdf',
            filename= 'tabla')),
          text = 'Download')), scrollX = TRUE))

indices <- rbind(LEI,EI,II)
# indices <- mutate_if(indices, is.character, as.numeric)
datatable(indices, extensions = 'Buttons', escape = FALSE, rownames = TRUE,
          options = list(dom = 'Bfrtip',
          buttons = list('colvis', list(extend = 'collection',
          buttons = list(
          list(extend='copy'),
          list(extend='excel',
            filename = 'tabla'),
          list(extend='pdf',
            filename= 'tabla')),
          text = 'Download')), scrollX = TRUE))

indices <- mutate_if(indices, is.character, as.numeric)

comunas <- LEI$cod_com
contenedor2 <- tibble("cod_com"=comunas)
for (i in 2:12) {
  contenedor <- tibble()
  for (j in comunas) {
  filtro <- filter(indices, cod_com == j)
  
  idh <- (filtro[1,c(i)]*filtro[2,c(i)]*filtro[3,c(i)])^(1/3)
  
  tabla1 <- tibble(
    "cod_com" = j,
    "IDH" = paste0(idh))
  
  names(tabla1)[2] <- str_replace_all(paste0("IDH_",colnames(idh)),"a","") 
  contenedor <- rbind(contenedor,tabla1)
  }
  contenedor2 <- merge(contenedor2,contenedor,by="cod_com", all = T )
  
}

demografia <- read_xlsx("demografia.xlsx")
names(demografia)[1] <- "cod_com";demografia <- demografia[,c(1:2)]

idh <- merge(contenedor2,demografia,by="cod_com")
idh <- idh[,c(1,13,2:12)]
datatable(idh, extensions = 'Buttons', escape = FALSE, rownames = TRUE,
          options = list(dom = 'Bfrtip',
          buttons = list('colvis', list(extend = 'collection',
          buttons = list(
          list(extend='copy'),
          list(extend='excel',
            filename = 'tabla'),
          list(extend='pdf',
            filename= 'tabla')),
          text = 'Download')), scrollX = TRUE))