Untitled

library(readxl)
library(dplyr)
library(tidyverse)
file_path <- "C:/Users/marti/Downloads/1015-2003.xlsx"

years <- 2003:2015
quarters <- c("1", "2", "3", "4")

eph2 <- list()

for (year in years) {
  for (quarter in quarters) {
    sheet_name <- paste0(year, ".", quarter)
    if (sheet_name %in% excel_sheets(file_path)) { # Check if the sheet exists
      eph2[[sheet_name]] <- read_excel(file_path, sheet = sheet_name)
    }
  }
}

HP_2 <- c()
HP_4 <- c()
HP_6 <- c()

for(i in 1:47){
      foo <- eph[[i]]
      colnames(foo) <- c(
        "CODUSU_C_8","NRO_HOGAR_N_2_0","ANO4_N_4_0","TRIMESTRE_N_1_0","REGION_N_2_0",
        "CH04_N_1_0","CH06_N_2_0","NIVEL_ED_N_1_0",         
        "ESTADO_N_1_0","CAT_OCUP_N_1_0","PP3E_TOT_N_3_0", 
        "PP3F_TOT_N_3_0","P47T_N_6_0", "TOTAL_TRABAJADO_MENSUAL","SALARIO_HORARIO")
      e2 <- foo %>% filter(NIVEL_ED_N_1_0 == 2) %>%summarize(mean_TOTAL_TRABAJADO_MENSUAL = mean(TOTAL_TRABAJADO_MENSUAL, na.rm = TRUE))
      e4 <- foo %>% filter(NIVEL_ED_N_1_0 == 4) %>%summarize(mean_TOTAL_TRABAJADO_MENSUAL = mean(TOTAL_TRABAJADO_MENSUAL, na.rm = TRUE))
      e6 <- foo %>% filter(NIVEL_ED_N_1_0 == 6) %>%summarize(mean_TOTAL_TRABAJADO_MENSUAL = mean(TOTAL_TRABAJADO_MENSUAL, na.rm = TRUE))
      
      
      HP_2[i] <- e2
      HP_4[i] <- e4
      HP_6[i] <- e6
}

HP_2 <- as.data.frame(HP_2)
HP_4 <- as.data.frame(HP_4)
HP_6 <- as.data.frame(HP_6)

library(data.table)
HP_2 <- transpose(HP_2)
HP_4 <- transpose(HP_4)
HP_6 <- transpose(HP_6)


hp_educ <- cbind(HP_2, HP_4, HP_6)
library(writexl)

write_xlsx(hp_educ, "C:/Users/marti/Downloads/hp_educ.xlsx")

## now for occupation (bf level of study)

years <- 2003:2015
quarters <- c("1", "2", "3", "4")

eph <- list()

for (year in years) {
  for (quarter in quarters) {
    sheet_name <- paste0(year, ".", quarter)
    if (sheet_name %in% excel_sheets(file_path)) { # Check if the sheet exists
      eph[[sheet_name]] <- read_excel(file_path, sheet = sheet_name)
    }
  }
}

HP_1 <- c()
HP_2 <- c()
HP_3 <- c()

for(i in 1:47){
  foo <- eph[[i]]
  colnames(foo) <- c(
    "CODUSU_C_8","NRO_HOGAR_N_2_0","ANO4_N_4_0","TRIMESTRE_N_1_0","REGION_N_2_0",
    "CH04_N_1_0","CH06_N_2_0","NIVEL_ED_N_1_0",         
    "ESTADO_N_1_0","CAT_OCUP_N_1_0","PP3E_TOT_N_3_0", 
    "PP3F_TOT_N_3_0","P47T_N_6_0", "TOTAL_TRABAJADO_MENSUAL","SALARIO_HORARIO")
  e1 <- foo %>% filter(CAT_OCUP_N_1_0 == 1) %>%summarize(mean_TOTAL_TRABAJADO_MENSUAL = mean(TOTAL_TRABAJADO_MENSUAL, na.rm = TRUE))
  e2 <- foo %>% filter(CAT_OCUP_N_1_0 == 2) %>%summarize(mean_TOTAL_TRABAJADO_MENSUAL = mean(TOTAL_TRABAJADO_MENSUAL, na.rm = TRUE))
  e3 <- foo %>% filter(CAT_OCUP_N_1_0 == 3) %>%summarize(mean_TOTAL_TRABAJADO_MENSUAL = mean(TOTAL_TRABAJADO_MENSUAL, na.rm = TRUE))
  
  
  HP_1[i] <- e1
  HP_2[i] <- e2
  HP_3[i] <- e3
}
HP_1 <- as.data.frame(HP_1)
HP_2 <- as.data.frame(HP_2)
HP_3 <- as.data.frame(HP_3)

HP_1 <- transpose(HP_1)
HP_2 <- transpose(HP_2)
HP_3 <- transpose(HP_3)

hp_cat <- cbind(HP_1, HP_2, HP_3)
library(writexl)

write_xlsx(hp_cat, "C:/Users/marti/Downloads/hp_cat.xlsx")