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

df <- as.data.frame(read_xlsx("variables_dependientes_mun.xlsx"))
df <- df %>% 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_df <- reduce_dataframe(df, id_vars)

write_xlsx(reduced_df, "e22e.xlsx")

df2 <- as.data.frame(read_xlsx("e22cBK.xlsx"))

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

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

reduced_df$ent <- as.character(reduced_df$ent)
reduced_df$mun <- as.character(reduced_df$mun)

comvined_df <- inner_join(reduced_df, df2, by = c("ent", "mun"))
write_xlsx(comvined_df, "e22c.xlsx")