library(dplyr)
library(haven)
library(writexl)
library(readxl)
library(stringr)

# Cargar el archivo CSV
data <- read.csv("pobreza_22.csv")

# Crear las nuevas columnas 'ent' y 'mun'
data <- data %>%
  mutate(ent = substr(ubica_geo, 1, nchar(ubica_geo) - 3),
         mun = substr(ubica_geo, nchar(ubica_geo) - 2, nchar(ubica_geo))) %>%
  mutate(ent = ifelse(nchar(ent) == 1, paste0("0", ent), ent),
         mun = ifelse(nchar(mun) == 2, paste0("0", mun), ifelse(nchar(mun) == 1, paste0("00", mun), mun)))

# Mover las columnas 'ent' y 'mun' al principio del dataframe
data <- data %>%
  select(ent, mun, everything())

####

data <- data %>% mutate_if(is.character, as.numeric)

# Función para
get_mode <- function(v) {
  uniqv <- unique(v)
  uniqv[which.max(tabulate(match(v, uniqv)))]
}

# Función para reducir el data frame
reduce_dataframe <- function(df, id_vars) {
  
  # Resumir el data frame
  reduced_df <- df %>%
    group_by(across(all_of(id_vars))) %>%
    summarise(across(everything(), ~ mean(.x, na.rm = TRUE)),
              .groups = 'drop')
  
  return(reduced_df)
}

# Definir las variables de identificación (ent y mun)
id_vars <- c("ent", "mun")

# Reducir el data frame
reduced_data <- reduce_dataframe(data, id_vars)

# Imprimir el resultado
print(reduced_data)

####

# Leer y ajustar other_df
other_df <- as.data.frame(read_xlsx("e22c.xlsx"))

# Convertir ent y mun a character en ambos dataframes
reduced_data$ent <- as.character(reduced_data$ent)
reduced_data$mun <- as.character(reduced_data$mun)
other_df$ent <- as.character(other_df$ent)
other_df$mun <- as.character(other_df$mun)

# Eliminar espacios en blanco
reduced_data <- reduced_data %>%
  mutate(ent = trimws(ent),
         mun = trimws(mun))

other_df <- other_df %>%
  mutate(ent = trimws(ent),
         mun = trimws(mun))

# Asegurar ceros a la izquierda en ambos dataframes
reduced_data <- reduced_data %>%
  mutate(ent = str_pad(ent, width = 2, pad = "0"),
         mun = str_pad(mun, width = 3, pad = "0"))

other_df <- other_df %>%
  mutate(ent = str_pad(ent, width = 2, pad = "0"),
         mun = str_pad(mun, width = 3, pad = "0"))

# Verificar que los valores coincidan
print(unique(reduced_data$ent))
print(unique(reduced_data$mun))
print(unique(other_df$ent))
print(unique(other_df$mun))

# Unir los dataframes por las columnas 'ent' y 'mun'
combined_df <- inner_join(reduced_data, other_df, by = c("ent", "mun"))
write_xlsx(combined_df, "atlas_mun.xlsx")

